By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,825 Members | 1,026 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,825 IT Pros & Developers. It's quick & easy.

Multiple Criteria in SQL setup problem with parameter order.

P: n/a
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")
Nov 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
CT
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" <Je*****@post8.tele.dk> wrote in message
news:dc**************************@posting.google.c om...
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")

Nov 20 '05 #2

P: n/a
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


Je*****@post8.tele.dk (Jesper Jensen) wrote in message news:<dc**************************@posting.google. com>...
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")

Nov 20 '05 #3

P: n/a
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" <ca******@spammersgoawaydotnetservices.biz> wrote in message news:<uS**************@TK2MSFTNGP09.phx.gbl>...
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" <Je*****@post8.tele.dk> wrote in message
news:dc**************************@posting.google.c om...
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")

Nov 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.