|
Hi all,
I have got a problem with retrieving data from the tables, because I found out that the keys to join these two tables are mismatching. The key in one table HAS SPACES in it, but the key in the other table does not. Even I have tried to use the REPLACE function to get rid of the spaces to make them matching, it still does not work. Please have a look at the code below, and give me some ideas how to solve it.
SELECT P.UniformItem, P.ProductCode, P.ProductSize, U.Description, U.Rank, U.RepPolicy
FROM tProductCode P INNER JOIN vUniformItemJoin U
ON Replace(P.UniformItem,' ','') = Replace(U.UniformItem,' ','')
Very much appreciate if you can help me out.
Cheers
|
|
|
|
|
GoldenStar wrote:
SELECT P.UniformItem, P.ProductCode, P.ProductSize, U.Description, U.Rank, U.RepPolicy
FROM tProductCode P INNER JOIN vUniformItemJoin U
ON Replace(P.UniformItem,' ','') = Replace(U.UniformItem,' ','')
Wow - you match using string values, and then call functions like this within your select statement ? You need to fix your database, I'd suggest that P and U should both have an index which represents a UniformItem, and then a table should match the index to a string representation. That's called database normalisation. If you're not going to do that ( and you should ), then you should at least massage data going in, so you don't need this sort of stuff making the most simple select statement messy.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
ADO/ADOX Gurus out there:
I cannot seem to find any way to read the description of an existing field in an existing Access DB. The frustrating part is that I can certainly write one when creating a new field and appending it to a table, but I can never seem to read the description for an existing field.
The following code will work and display the description on the webpage, but only because it reads it from the object it just appended. If I were to close everything, set it to nothing, then open a new object, it cannot be read. One would think that if you can write it, you can read it...am I wrong here?!?
I have tried any number of combinations of objects, properties, and collections from ADO and ADOX. Has anyone actually been able to do this?
Your help is deeply appreciated,
Brian
biomedbz@hotmail.com
dim objConnDatabase,objCat,objTable
Set objConnDatabase = Server.CreateObject("ADODB.Connection")
objConnDatabase.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strConnDatabase
Set objCat = CreateObject("ADOX.Catalog")
Set objCat.ActiveConnection = objConnDatabase
Set objTable = CreateObject("ADOX.Table")
with objTable
.Name = "newtable"
.ParentCatalog = objCat
end with
with objTable
with .Columns
.Append "Field_A",adDouble
.Item("Field_A").Properties("Description") = "This is a field of numbers"
response.write "The ""Field_A"" field has been added!
"
end with
end with
response.write "description=" & .Item("Field_A").Properties("Description") & "
"
|
|
|
|
|
Hi, my friend have a database in SQL server 2000 which already apply service pack 4. Recently he try to export the data from his database to Microsoft Excel. During the middle of data transformation progress an error message box appear with the following information: "Incorrect syntax near the keyword 'column name'. Statement(s) could not be prepared". I have check the microsoft knowledge base for the solution and found the information from here[^] and here[^]. But I think that the information is not relate to what i need. Does anyone has any idea or solution? I need the comment to solve the problem.
A thousand mile of journey, begin with the first step.
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
Have seen this when colum names uses reserved SQL names like DATE, TYPE, CASE..ect
Perhaps this is part off the problem
Hmm...
|
|
|
|
|
Hi All
I am developing a system that will retrieve information from pastel account information, it will have to retrieve the invoice number and store it in the SQL server database so I need help on how to connect to pastel so that I can be able to get the information from pastel and from the database to pastel. The two applications have to share some sort of information. The help that is provided must be in visual basic 6.0 because that is the one I am using.
Hope you can help
thanks in advanced
|
|
|
|
|
|
string strSQL= " Select * From Category" ;
SqlConnection cnn = new SqlConnection(CNN_STRING);
SqlCommand cmd = new SqlCommand(strSQL,cnn);
DataSet ds = new DataSet("Products");
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
ds.Tables[0].TableName = "Product";
TextBox1.Text = ds.GetXml() ;
Here GetXml() returns the out put as tag ways
< Products><br />
< Product><br />
<CategoryID>3</CategoryID><br />
<Name>A</Name><br />
<Description>ADescription</Description><br />
</Product><br />
< Product><br />
<CategoryID>4</CategoryID><br />
<Name>B</Name><br />
<Description>B Description</Description><br />
</Product> <br />
</Products>
I need to change it as Attributes in a simple way
< Products>
< Product CategoryID="3" Name="A" Description = "ADescription" />
< Product CategoryID="4" Name="B" Description = "B Description" />
</Products>
|
|
|
|
|
As far as I know there is no method to change the output of the GetXml function. So you will have to convert it afterwards. If you are willing to read some docs have a look at the XslTransformclass. I provides a .Net wrapper around XSLT which can be used to transform the structure of XML files.
|
|
|
|
|
I want to publish my site and want to register new server from the enteprize maager.i entered the name of the server and followed the wizard but the"sql server doenot exist or access is denied"has bee shown.(i used the windows autentication).also when i used server au.. and enter the login and the user name, the message has bee shown again.
what is the problem?
mabey it is from the way that i setup the sqlserver or the version of sqlserver,because the sqlserver work correectly when i use the local server.
Thank you!
|
|
|
|
|
|
Hi:
Please check on the following:
1) Since it is a remote machine, ping it and see whether ICMP Packets are being acknowledged. These will ascertain you that the system is up.
2) You need to ensure that you are connecting to a proper instance of SQLServer. Not always Instance Name and System Names can be identical. You could have chosen a different instance for the SQLServer in case there are multiple on the same system.
3) You need to ensure that the SQLServer Service you are connecting is running on the remote system and on the correct port.
4) In line with (3), also ensure that port 443 communication between your system and the remote system are not blocked by either your firewall or the remote system firewall.
Did this help you?
Deepak Kumar Vasudevan
Personal Web: http://vdeepakkumar.netfirms.com/
I Blog At: http://deepak.blogdrive.com/
|
|
|
|
|
Hi All,
I'm trying to work with stored procedure in VB.NET but there were some problems with that, I hope someone can help me.
First, is there a way to manage a stored procedure's transaction from VB.NET?, I tried something like this (see code below) but when I do commit an error appears because the transaction is already closed.
................
Dim objConexion As New OleDbConnection(strConexion)
Dim trans As OleDbTransaction
Dim objComando As OleDbCommand = New OleDbCommand("fun_codigos", objConexion)
objComando.CommandType = CommandType.StoredProcedure
.......defining parameters...............
objConexion.Open()
trans = objConexion.BeginTransaction
objComando.Transaction = trans
Dim objDataReader As OleDbDataReader = objComando.ExecuteReader
objDataReader.Close()
objConexion.Close()
Dim salida As Int64
salida = objComando.Parameters("@p_cod_servicio").Value
Label1.Text = salida
trans.Commit() -------> here an error appears!
I have to manage the transaction from VB.Net not from inside the stored procedure.
Second, I want to pass some parameters from VB.NET to an stored procedure but there is an error like this: "ORA-06550: line 1, column 18: PLS-00382: expression is of wrong type ORA-06550: line 1, column 7: PL/SQL: Statement ignored "
I checked parameter's type in VB.NET and there were the same as in stored procedure.
I'm working with Oracle 8i.
I hope you can help me.
Thanks in advance
|
|
|
|
|
DISCLAIMER: I don't know much about oracle so this is just a guess out of the air.
Does your stored procedure have a begin/commit transaction block in it already? I'm guessing that if your stored procedure commits the transaction already then putting a BeginTransaction() and Commit() in your code may not be necessary.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
the stored procedure makes an autocommit transaction when finish, but I don´t want it to do that. It´s like the stored procedure is inside a .NET loop and I want to commit when the loop (in .NET) finish so I don´t want the stored procedure autocommiting every time I call it.
|
|
|
|
|
Hi I have written a basic Webservice that returns values from a Dataset here is a code snippit
[WebMethod]
public DataSet CTX (string filter)
{
try
{
iDB2Connection connDB2=new iDB2Connection{
"DataSource=TDC01;" +
"userid=user;password=pass;DefaultCollection=collection;");
DataSet ds = new DataSet();
iDB2DataAdapter adpt = new iDB2DataAdapter();
string szSQLstmt = "Select * from table where REF='" +@filter+"'";
adpt.SelectCommand = new iDB2Command(szSQLstmt,connDB2);
adpt.Fill(ds);
return ds;
}
This works fine when i test it in ie returns the data in xml however what i want to be able to do is use this in Biztalk however biztalk can only see the root node <dataset> and nothing else can anyone offer me any advice of how to solve this problem.
Thanks in advance
Rohan
|
|
|
|
|
|
Hi,
I have written a stored procedure in SQL Server which uses temp tables and returns the rows of a temptable.
When i try to use this sp as datasource for Crystal report(Add Database), the fields are not listed.
Why is that? If this wouldnt work how do i implement this.
Am using a custom version of crystal report bundled in VS .NET.
Any help would be appreciated.
Thanks in advance
Geetha
|
|
|
|
|
|
Thanks for the help.
I use VB.Net, ADO.Net to execute the stored procedure. Even if i use "set noCount On", am not able to create the .xsd file.
Pls tell me how to implement it in .Net? I want the .xsd file to be created.
Thanks in advance.
|
|
|
|
|
hi
how can i increase the security of a designed program via SQL server.what is the role of stored procedures ?
|
|
|
|
|
daneshmand wrote:
how can i increase the security of a designed program via SQL server
Just a few things off the top of my head:
Uses specific logins.
Do not grant access to the public role.
Grant access only to the objects that the users/roles actually require.
daneshmand wrote:
what is the role of stored procedures ?
Stored procedures can be used to protect the tables from direct access. You can revoke access to the tables and grant access only to stored procedures. That way the only way to access a table is through the stored procedure. This layer of abstraction can protect you as you can set up additional checks in the stored procedure before allowing the operation to complete.
You may also want to read: SQL Injection Attacks and some tips on how to prevent them[^]
Does this help?
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
i'm using SQL in building my Database but I want to know what is the best code to design my web withen SQL
A.M
|
|
|
|
|
|
i know that but what i meant is what is the best code which will be combatable with the SQL
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
