470,822 Members | 1,427 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,822 developers. It's quick & easy.

SQL Query Tool Issue In VB 2005

Hrm, I'm not at all sure my title is clear, but I spent 15 minutes trying to figure out what my real problem was. I'm new here, and brand new to VB and I'm only trying to access a database, search through it, and subsequently display it after the query has finished.

Expand|Select|Wrap|Line Numbers
  1.   Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
  2.         Dim state As String
  3.         state = StateTextBox.Text
  4.         FindByState(state)
  6.         If CustInfoDataSet.tblNames.Count = 0 Then
  7.             MessageBox.Show("No records found.", "Search Results")
  8.         End If
  10.     End Sub
This calls...

Expand|Select|Wrap|Line Numbers
  1. Private Sub FindByState(ByVal state As String)
  2.         CustInfoDataSet.Clear()
  3.         Me.TblNamesTableAdapter.FillBy(Me.CustInfoDataSet.tblNames)
  4.     End Sub
Now, FillBy is my custom query, which reads:

Expand|Select|Wrap|Line Numbers
  1. SELECT        fldKey, fldEmail, fldFirst, fldLast, fldHomePhone, fldWorkPhone, fldCellPhone, fldStreet, fldCity, fldState, fldPostalCode
  2. FROM            tblNames
  3. WHERE        (fldState = '" & state & "')
I'm positive that my query here is the culprit. When I hit search I get no results no matter what. However, if I change '" & state & "' to something like 'NC' or 'TN', states that are in my database, it works just fine, pulling up the proper clients who live in those respective states.

Please tell me this is just a syntax issue, because I've been trying to get this VERY simple code to work for days and I'm pulling my hair out. Sorry for being such a newbie, but alas, I had to start somewhere.

If I'm completely leaving something out that you guys need to know, just tell me. It's 5:11 in the morning so I'm sure I forgot something.

Thanks in advance, really appreciate any help you guys can give me.
Oct 3 '07 #1
5 1224
8,127 Expert 4TB
Try this

Expand|Select|Wrap|Line Numbers
  1. "SELECT  fldKey, fldEmail, fldFirst, fldLast, fldHomePhone, fldWorkPhone, fldCellPhone, fldStreet, fldCity, fldState, fldPostalCode
  2. FROM  tblNames WHERE  fldState = '" & state & "'"
Oct 3 '07 #2
I'm not sure what exactly changed besides the parenthesis. It auto adds them to my entry when it reformats my code. I c&ped it into my query and again it still says no results.

I'm using Access database (OLE DB). Forgot to mention that earlier, and obviously its pretty important. Sorry for that.

It seems that this is down to it not seeing the value of state. "state" being a variable, is it the syntax of the query? What I guess I'm asking is could it be something else?

I know the database if fine, because I coded in a show all button.


Expand|Select|Wrap|Line Numbers
  1. Private Sub btnShowAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShowAll.Click
  2.         CustInfoDataSet.Clear()
  3.         Me.TblNamesTableAdapter.Fill(Me.CustInfoDataSet.tblNames)
  4.     End Sub
This refreshes the table and shows all my original information at runtime, so that rules out the database itself being the problem.

I'm still trouble shooting, but I keep hitting brick walls. I have a book on 2003, but it's very little help, since it's quite obvious database connectivity got a major overhaul in 2005.
Oct 3 '07 #3
8,127 Expert 4TB
Question moved to .NET forum.
Oct 3 '07 #4
Ah, thank you and sorry I posted in the wrong area. Didn't realize, appreciate the assistance.
Oct 3 '07 #5
I'm still currently trying to figure this one out, I've found ways around this, by actually making a seperate form, that has a place to type in everything, so each individual txtbox has its one value.

Unfortunately this method doesn't fit my design, so I'm still searching for a way to fix this problem.
Oct 4 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

16 posts views Thread by Durumdara | last post: by
3 posts views Thread by sebnunes | last post: by
5 posts views Thread by Darin L. Miller | last post: by
1 post views Thread by Darsin | last post: by
reply views Thread by mihailmihai484 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.