Multiple Criteria in SQL setup problem with parameter order.

  • Thread starter Thread starter Jesper Jensen
  • Start date Start date
J

Jesper Jensen

I have the following problem:
I have created the following SQL for my app. With the below shown code
(Example 1) I am able to retrieve the records I need into dataset
dsFind.

Now however I want to do another search as in Example 2, this doesnt
work and I get no records.

However if I switch around the order of the parameters as seen in
Example 3 I get my records after EfterNavn.

This makes it difficult to make an SQL with more criterias like:
SQLFindString = "SELECT PersonsInfo.* FROM PersonsInfo"
SQLFindString = SQLFindString & " WHERE (EfterNavn = ?) AND ([By] =
?)"

So my question what is wrong with my parameter addition to the
parameter collection. I cant believe it matters what the order is of
the parameter declarations. If anyone has any experience with this,
please post a reply.

Best regards,
Jesper Jensen


Example 3:
daPersons.SelectCommand.Parameters.Add("EfterNavn", OleDbType.VarChar,
70).Value = EfterNavn
daPersons.SelectCommand.Parameters.Add("[By]", OleDbType.VarChar,
50).Value = By

Example 2:
SQLFindString = "SELECT PersonsInfo.* FROM PersonsInfo"
SQLFindString = SQLFindString & " WHERE (EfterNavn = ?)"

Example 1:
Dim SQLFindString As String = ""
Dim iniWhere As Boolean = False

SQLFindString = "SELECT PersonsInfo.* FROM PersonsInfo"
SQLFindString = SQLFindString & " WHERE ([By] = ?)"

OleDbSelectCommand1 = New OleDbCommand(SQLFindString)
daPersons.SelectCommand = OleDbSelectCommand1

OleDbSelectCommand1.Connection = DbCon
daPersons.SelectCommand.Parameters.Add("[By]", OleDbType.VarChar,
50).Value = By
daPersons.SelectCommand.Parameters.Add("EfterNavn", OleDbType.VarChar,
70).Value = EfterNavn

daPersons.Fill(dsFind, "PersonsInfo")
datGrid.SetDataBinding(dsFind, "PersonsInfo")
 
When you use unnamed parameters (?) with OLEDB, the parameters must be added
in the order in which they're referenced in the SELECT statement. If you're
using the Sql Server .NET Provider, you can use named parameters in your
SELECT statements (I don't know what dat source you're using...).
 
For me it works like this

daTEMPTEK.SelectCommand.Parameters.Add(New SqlParameter("@Name1",SqlDbType.Int))
daTEMPTEK.SelectCommand.Parameters.Add(New SqlParameter("@Name2",SqlDbType.Int))
daTEMPTEK.SelectCommand.Parameters("@Name1").Value = pVariable1
daTEMPTEK.SelectCommand.Parameters("@Name2").Value = pVariable2

hope this helps
greetz
 
Thank You,
The sort order had to be synchronised with the SELECT statement as you
wrote. I was using OleDB dat Source (Access DB).

Best regards,
Jesper Jensen

CT said:
When you use unnamed parameters (?) with OLEDB, the parameters must be added
in the order in which they're referenced in the SELECT statement. If you're
using the Sql Server .NET Provider, you can use named parameters in your
SELECT statements (I don't know what dat source you're using...).

--
Carsten Thomsen
Enterprise Development with VS .NET, UML, and MSF
http://www.apress.com/book/bookDisplay.html?bID=105
Jesper Jensen said:
I have the following problem:
I have created the following SQL for my app. With the below shown code
(Example 1) I am able to retrieve the records I need into dataset
dsFind.

Now however I want to do another search as in Example 2, this doesnt
work and I get no records.

However if I switch around the order of the parameters as seen in
Example 3 I get my records after EfterNavn.

This makes it difficult to make an SQL with more criterias like:
SQLFindString = "SELECT PersonsInfo.* FROM PersonsInfo"
SQLFindString = SQLFindString & " WHERE (EfterNavn = ?) AND ([By] =
?)"

So my question what is wrong with my parameter addition to the
parameter collection. I cant believe it matters what the order is of
the parameter declarations. If anyone has any experience with this,
please post a reply.

Best regards,
Jesper Jensen


Example 3:
daPersons.SelectCommand.Parameters.Add("EfterNavn", OleDbType.VarChar,
70).Value = EfterNavn
daPersons.SelectCommand.Parameters.Add("[By]", OleDbType.VarChar,
50).Value = By

Example 2:
SQLFindString = "SELECT PersonsInfo.* FROM PersonsInfo"
SQLFindString = SQLFindString & " WHERE (EfterNavn = ?)"

Example 1:
Dim SQLFindString As String = ""
Dim iniWhere As Boolean = False

SQLFindString = "SELECT PersonsInfo.* FROM PersonsInfo"
SQLFindString = SQLFindString & " WHERE ([By] = ?)"

OleDbSelectCommand1 = New OleDbCommand(SQLFindString)
daPersons.SelectCommand = OleDbSelectCommand1

OleDbSelectCommand1.Connection = DbCon
daPersons.SelectCommand.Parameters.Add("[By]", OleDbType.VarChar,
50).Value = By
daPersons.SelectCommand.Parameters.Add("EfterNavn", OleDbType.VarChar,
70).Value = EfterNavn

daPersons.Fill(dsFind, "PersonsInfo")
datGrid.SetDataBinding(dsFind, "PersonsInfo")
Click to expand...
Click to expand...
 
Back
Top