A newbie question

[Tina]

I am following some examples in the Microsoft ADO.net book and did the
example in Chapter four where we drag an olecomand onto a form and do build
a query such as...

SELECT CustomerID, CompanyName, ContactName, ContactTitle
FROM Customers
WHERE (CustomerID LIKE ?)

and then put in some code in the load event like....

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Try

OleDbConnection1.Open()
OleDbCommand1.Parameters(0).Value = "%"
Dim rdr As OleDb.OleDbDataReader = OleDbCommand1.ExecuteReader()
While rdr.Read
ListBox1.Items.Add(rdr("CompanyName"))
End While
rdr.Close()
OleDbConnection1.Close()
Catch ex As OleDbException
MsgBox(ex.Message & " " & ex.ErrorCode)
End Try

End Sub

and it all works just fine. However when I do the same thing with SQL
Server tools like...

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Try
SqlConnection1.Open()
SqlCommand1.Parameters(0).Value = "%"
Dim rdr As SqlDataReader = SqlCommand1.ExecuteReader()
While rdr.Read()
ListBox1.Items.Add(rdr("CompanyName"))
End While
rdr.Close()
SqlConnection1.Close()
Catch ex As SqlException
MsgBox(ex.Message & " " & ex.Number)
End Try
End Sub

I get "incorrect syntax near '?'. Why am I getting this with sql but not
with ole?
Thanks,
T

 

[Michael Lang]

OLEDB uses ? as a replacement in the query where a parameter value is
supposed to go. SQL server uses "@parameterName".

.... sorry this is C#... but same idea in VB.NET ...
SqlCommand sqlCmd1 = new SqlCommand();
sqlCmd1.Connection = mySqlConnection;
sqlCmd1.CommandText = "SELECT CustomerID, CompanyName, ContactName,
ContactTitle "
+ "FROM Customers WHERE (CustomerID LIKE @CustomerID)";
SqlParameter sqlPar1 = new SqlParameter("@CustomerID", SqlDbType.VarChar);
//init parameter with name and type
sqlPar1.Value = "%";
sqlCmd1.Parameters.Add(sqlPar1);
.... next execute command as you have ...

And yet Oracle has another replacement. I can't think of what it is right
now.

If you are going to select all customers you could just leave out the WHERE
clause.

Michael Lang, MCSD