problem with sql on dataset

[ninjaimp]

hi



i am just starting with vb.net and have set a connection to an existing access db that i have and am able to get the data displayed on a form.



my problem lies where as i want to setup a basic search. i have added a query to a datagrid to dispaly data based on text in a text box.



now my sql query is this:



SELECT CustID, CustName, CustAddress, CustPostcode, CustTel, CustEmail, CustNotes, CustRoom, CustStayArrive, CustStayLeave
FROM tblCustData
WHERE CustName = '"& SearchBox.text &"'




now i get no errors but also no data gets returned. If i add the actual value of one of the records that one gets returned fine but doesnt really help with a search.



wondered if anyone had any ideas?



regards

 

[MattP]

Look into using parameterized queries.

Your line

WHERE CustName = '" & SearchBox.text & "'

should have an additional double quote at the end

... SearchBox.text & "'"

to enclose the single quote as a string.

 

[ninjaimp]

if i add that then i get an error. Please note that im building this query in the query configuration wizard, if that makes a difference

 

[MattP]

If you're building it in the Query Builder then you should be able to put @CustName in the filter box which will change your query to:

...WHERE CustName = @CustName

Clicking on the Execute Query button will test the query out and show you that it's waiting to have a value passed in.

 

[ninjaimp]

now i have tried this based on the tutorials i have seen. When i put in @SearchBox it gives an error. What does the @ bit do?

thanks for your help

 

[ninjaimp]

i should mention that it is connecting to an access db not SQL

 

[MattP]

i should mention that it is connecting to an access db not SQL

Access will use a ? rather than the named parameter and then accept them in the order that your provide them.

----

I would suggest going to http://msdn.microsoft.com/en-us/vbasic/bb466226.aspx and taking a look at the Forms over Data Video Series.

 

[ninjaimp]

how does it know that ? refers to a text box on the form?

i will give the tutorials a look