VB help with SQL variables and textbox

[Bsayre]

Hi all, I'm new here and new to VB and development in general. I'm trying to write a simple CSR app with a VB front-end connected to an access database. The problem I'm having is writing a SQL query that will read a textbox value in the WHERE statement.

SELECT     [Event Date], [Due Date], Priority, [Event Status], Category, Contact, [Event Description], [Event Solution]
FROM    contactname
WHERE (Contact = form2.namesearch.text)

I've also tried things such as, WHERE (Contact = '" & form2.namesearch.text & "')
This doesn't work either.

I'm fairly ignorant when it comes to coding and claim Google as my only resource so I'm sure that there is a simple answer. Thanks in advance for the help with this.

[tjjones70]

Hi all, I'm new here and new to VB and development in general. I'm trying to write a simple CSR app with a VB front-end connected to an access database. The problem I'm having is writing a SQL query that will read a textbox value in the WHERE statement.

SELECT     [Event Date], [Due Date], Priority, [Event Status], Category, Contact, [Event Description], [Event Solution]
FROM    contactname
WHERE (Contact = form2.namesearch.text)

I've also tried things such as, WHERE (Contact = '" & form2.namesearch.text & "')
This doesn't work either.

I'm fairly ignorant when it comes to coding and claim Google as my only resource so I'm sure that there is a simple answer. Thanks in advance for the help with this.

I use VB6 and I recently had a similar problem. And I have found that quotes are very important. :) Variable or objects need to been with in single quotes in side of double quotes like " 'variable' " So, give this a try.

SELECT (what you want)
FROM (where you want)
WHERE 'form2.namesearch.text'

So it should look something like:

RS = "SELECT [whatever] FROM [WHEREVER] WHERE contact ='form2.namesearch.text' "

Let me know what you get.

[Killer42]

Bsayre, you are on the right track.

You see, VB and SQL are quite separate. Generally, you are simply building building a string which will be passed to an SQL interpreter. This interpreter is outside of the VB environment and knows nothing about your controls. So you need to take the value (not the name) from your textbox and place it in the string, to be passed to the SQL interpreter.

That's just a general overview, and of course circumstances can vary depending on what type of database connection you're using, what DB-aware controls and so on.

I don't know exactly how you're building or using this SQL query. But here's an example which will build the query in a string, that can then be passed to the database...

Dim strQuery As String
strQuery = "SELECT [Event Date], [Due Date], Priority, [Event Status], Category, Contact, [Event Description], [Event Solution] FROM contactname WHERE (Contact = '" & Form2.namesearch.Text & "')"
 

As you can see, this is pretty much what you said that you have already tried. So I think we need to see more details about exactly how this query is being used.

[Killer42]

... So it should look something like:

RS = "SELECT [whatever] FROM [WHEREVER] WHERE contact ='form2.namesearch.text' "

I really think you've got that wrong. This would simply search for records that had the actual text "form2.namesearch.text" stored in the [contact] field. It's pretty unlikely that this is the desired result.

[tjjones70]

Bsayre, you are on the right track.

You see, VB and SQL are quite separate. Generally, you are simply building building a string which will be passed to an SQL interpreter. This interpreter is outside of the VB environment and knows nothing about your controls. So you need to take the value (not the name) from your textbox and place it in the string, to be passed to the SQL interpreter.

That's just a general overview, and of course circumstances can vary depending on what type of database connection you're using, what DB-aware controls and so on.

I don't know exactly how you're building or using this SQL query. But here's an example which will build the query in a string, that can then be passed to the database...

Dim strQuery As String
strQuery = "SELECT [Event Date], [Due Date], Priority, [Event Status], Category, Contact, [Event Description], [Event Solution] FROM contactname WHERE (Contact = '" & Form2.namesearch.Text & "')"
 

As you can see, this is pretty much what you said that you have already tried. So I think we need to see more details about exactly how this query is being used.

What's the difference between my post and yours other than you added all of the tables.

my Select [whatever] equals your Select [Event Date], [Due Date], Priority, [Event Status], Category, Contact, [Event Description], [Event Solution]

and my FROM [wherever] equals your FROM contact name

and my WHERE contact = 'form2.namesearch.text'

Im assuming youre adding the &"" to handle nulls. And maybe I misread his post but I thought that's exact what he wanted to do is read the textbox value for the WHERE function. Anyways, I just gave the format not actual tables. But if you see something different let me know. Another big helper for me is using the Visual Data Manger and using the SQL Statement function or the query builder. You can test your queries on your DB to insure they are producing the records youre looking for.

[Killer42]

What's the difference between my post and yours other than you added all of the tables.

The tables aren't significant. The fundamental difference is that you included the name of the control property in the string, while I told VB to include the value of the property.

As an analogy, consider the difference between these two stetements...

Debug.Print "form2.namesearch.text"
Debug.Print form2.namesearch.text

Do you think they will produce the same result?

Another big helper for me is using the Visual Data Manger and using the SQL Statement function or the query builder. You can test your queries on your DB to insure they are producing the records youre looking for.

I'd have to agree. The details will vary depending on what version you're using, of course. I use VB6, and if I need SQL I generally fire up MS Access, use its query builder to generate the SQL, then copy it back to VB.

[Bsayre]

Thanks for the feedback. I may try building the WHERE statement in access and taking the code from there. Right now I'm just using a freeware copy of MS VB 2005 Express. It has a query builder which I've tried to use but it doesn't (that I've found) have anything to "build" the variables that I'm looking for.

I'm using the query to simply limit search results by contact name, and I'd eventually like to also filter by status (open, closed, pending). So the user will input their name and have access to view all of the help requests they've submitted.

[Killer42]

Sorry I couldn't help more. My comments basically relate to the general process of building a string in VB, which will then be processed by an SQL interpreter outside of VB. It's possible that the situation has changed significantly in VB2005 with its query builder.

[tjjones70]

The tables aren't significant. The fundamental difference is that you included the name of the control property in the string, while I told VB to include the value of the property.

As an analogy, consider the difference between these two stetements...

Debug.Print "form2.namesearch.text"
Debug.Print form2.namesearch.text

Do you think they will produce the same result?


I'd have to agree. The details will vary depending on what version you're using, of course. I use VB6, and if I need SQL I generally fire up MS Access, use its query builder to generate the SQL, then copy it back to VB.

I agree with you when your printing quotes " " to the screen however, I have found that quotes in the query are reversed ' " " ' depending on where they are used. The following are examples that I have been using in my own applications.

DATAtow.RecordSource = "select * from messages where user =' " & (Mid(from(Index).Caption, 1, 3)) & " ' order by sent desc"
 

This line is parsing out the senders name from a lable array CAPTION property and using it in the WHERE. But since im in quotes I have to reverse the quotes to retrieve the value of objects.

db.rs = "select * from messages where messageid=' " & infoAR(4) & " ' "
 

Same thing here. But to use your example it would be like printing a quote to the screen. You have to reverse the quotes to do it. Anyways, that's how I have been getting it done. :)

[Killer42]

You're right that I used double quotes (") because it was a Print statement. In SQL you generally use single quotes ('). I was merely pointing out that an SQL string which contained something like this...
WHERE contact = 'form2.namesearch.text'
...would not be searching for the contents of a textbox. It would be searching for records matching the name of the textbox. You would need to concatenate the contents of the textbox between the single quote characters.

I think we just need to concede that we're both saying the same thing, in slightly different ways.

[tjjones70]

You're right that I used double quotes (") because it was a Print statement. In SQL you generally use single quotes ('). I was merely pointing out that an SQL string which contained something like this...
WHERE contact = 'form2.namesearch.text'
...would not be searching for the contents of a textbox. It would be searching for records matching the name of the textbox. You would need to concatenate the contents of the textbox between the single quote characters.

I think we just need to concede that we're both saying the same thing, in slightly different ways.

haha. Conceded. But youre right, I flipped my quotes around in the first post. I used " ' instead of ' ". But I concede. You are the victor. :)