Query question with form driving query

[GaryWilsonCPA]

I have a form with a textbox1 located on it. I want the user to put the desired zip code in textbox1, then have the query use the textbox1 data to drive the query.


SELECT [Client Demographics].[Last Name], [Client Demographics].[First Name], [Client Demographics].[Middle Initial], [Client Demographics].Address1, [Client Demographics].Address2, [Client Demographics].City, [Client Demographics].State, [Client Demographics].Zip
FROM [Client Demographics]
WHERE ((([Client Demographics].Zip) Like [DATAFORM1].[TEXTBOX1]))

Does this make sense to do it this way???

 

[GaryWilsonCPA]

I have also tried my where statement to be

WHERE (Zip = 43081)

This works correctly, by picking only info with a zip of 43081.


But I want the zip to be picked by the user of the form. I have also tried this with no luck.

WHERE (Zip = DATAFORM.ZIPINPUT)

Any ideas would be appreciated.

 

[RiverGuy]

Are you sure you posted this in the correct forum? It looks similar to Access forms code since you seem to be trying to do some embedded type SQL similar to what Access uses.

But, if are you using VB.Net, remember that you are passing a string to your database engine. According to your first post, it is passing EXACTLY what you have posted to the database. But you want the VALUE of the Textbox, not the word "Textbox".

It would be best to use parameters for this. Which database are you using? That will help decide how you code the parameters.

 

[GaryWilsonCPA]

Thanks for your reply. I am trying to do this in vb.net. I am a novice with vb.net and was trying to use a method that works for me in access.

I think you are saying that the textbox value, not the textbox needs embedded in the query formula. Can you point me in the direction needed to understand how to make the textbox value part of my query.

Thanks for you help.

 

[RiverGuy]

You should be able to do something like the following:

YourDataCommand.CommandType = CommandType.Text
YourDataCommand.Connection = YourConnection
YourDataCommand.CommandText = "SELECT .... WHERE ... LIKE @Param1"
YourDataCommand.Parameters.Add("@Param1", TextBox1.Text)

 

[GaryWilsonCPA]

Me.OleDbSelectCommand1.CommandType = CommandType.Text
Me.OleDbSelectCommand1.Connection = OleDbConnection1
Me.OleDbSelectCommand1.CommandText = "SELECT Address1, Address2, FirstName, ID, LastName, Notes, State, Telephone, Town" & _
", Zip FROM Members WHERE (Zip LIKE @Zip)"
Me.OleDbSelectCommand1.Parameters.Add("@Zip", TextBox1.Text)

When I try this, my code doesnt generate any records. I put 43081 in the textbox and enter click Loadbutton.

The form works when i remove the above """Where (zip Like @Zip)""" , it selects all records properly.

 

[RiverGuy]

Try "=" instead of "LIKE", just to see if the parameters are working or not.