Pass Variable to SQL Query

[InDistress]

Hi All,

To begin with, you should know that VBA is my forte and I am moving into VB.Net to develop my skills.

I have a problem with how to pass a variable to an SQL statement. It's just a small app I'm designing as a test/personal development program. Essentially it's just a postcode search tool and I've got it all down pat except for passing a variable into my SQL statement. My code is as follows:

Dim conString AsString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\pcdata.mdb;Jet OLEDBatabase Password=password"
Dim MyConnection AsNew System.Data.OleDb.OleDbConnection
Dim oleCommand AsNew System.Data.OleDb.OleDbCommand
Dim reader As System.Data.OleDb.OleDbDataReader
Dim oledbStr AsString
MyConnection = New System.Data.OleDb.OleDbConnection(conString)
oledbStr = "SELECT Pcode, Locality, State, Comments, Category FROM pcimport WHERE Pcode = '%EnteredPC%'" AND State = '%SelectedState%'

Hopefully you can see the 2 variables I am trying to pass into the query (EnteredPC & SelectedState). For some reason, it's just not picking up the variable values.

I'm probably missing something completely obvious, but it's doing my head in, so any help with prevent me from ripping my hair out.

Thanks in advance
InDistress

 

[cjard]

SELECT Pcode, Locality, State, Comments, Category FROM pcimport WHERE Pcode = ? AND State = ?



Do not put ' ' around the ? marks
Remember to add the parameters to your oleDbCommand
The order that you add the parameters in is crucial. It should be the same as in the query


Take a read of the DW2 link in my signature..

 

[InDistress]

How do I add it to the oleCommand? I currently have:

oleCommand.Connection = (MyConnection)
oleCommand.CommandText = oledbStr
MyConnection.Open()
reader = oleCommand.ExecuteReader()

 

[cjard]

Take a read of the DW2 link in my signature..

Really, do this.. I didnt put that amazing tutorial in my signature for the good of my health! (Actually, i did.. thinking about it.. it helps save my sanity )