Click here to Skip to main content
15,935,728 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am using following code and trying to get data by given parameters.  I donot know how to pass the parameter value to my query.

Dim con As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String

con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = D:\.Net Programs\DB Experiments\AddressBook.mdb"
        sql = "SELECT * FROM tblContacts where Name=? and City=?"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "AddressBook")

Please advise.

sql = "SELECT * FROM tblContacts where Name=@NM and City=@CT"
da = New OleDb.OleDbDataAdapter(sql, con)
da.SelectCommand.Parameters.AddWithValue("@NM", name)
da.SelectCommand.Parameters.AddWithValue("@CT", city)
Share this answer
Furqan Sehgal 19-Sep-11 11:18am    
I have been trying this and the error is
No value given for one or more required parameters.
Geoff Williams 19-Sep-11 12:02pm    
The original query was correct. Using OLE, you need to use the '<bold>?/<bold>' character as place holders for the data values.

<pre lang="vb">sql = "SELECT * FROM tblContacts where Name=? and City=?"
da = New OleDb.OleDbDataAdapter(sql, con)
da.SelectCommand.Parameters.AddWithValue("@name", name)
da.SelectCommand.Parameters.AddWithValue("@city", city)</pre>
You need to add the parameters to your data adapter using da.SelectCommand.Parameters.Add in the order they appear in the SQL statement.

See OleDbDataAdapter.SelectCommand Property[^] and OleDbCommand.Parameters Property[^] for details.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900