SQL Query Tool Issue In VB 2005

[LifeFlasher]

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.

  Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        Dim state As String
        state = StateTextBox.Text
        FindByState(state)
 
        If CustInfoDataSet.tblNames.Count = 0 Then
            MessageBox.Show("No records found.", "Search Results")
        End If
 
    End Sub

This calls...

Private Sub FindByState(ByVal state As String)
        CustInfoDataSet.Clear()
        Me.TblNamesTableAdapter.FillBy(Me.CustInfoDataSet.tblNames)
    End Sub

Now, FillBy is my custom query, which reads:

SELECT        fldKey, fldEmail, fldFirst, fldLast, fldHomePhone, fldWorkPhone, fldCellPhone, fldStreet, fldCity, fldState, fldPostalCode
FROM            tblNames
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.

[debasisdas]

Try this

"SELECT  fldKey, fldEmail, fldFirst, fldLast, fldHomePhone, fldWorkPhone, fldCellPhone, fldStreet, fldCity, fldState, fldPostalCode
FROM  tblNames WHERE  fldState = '" & state & "'"

[LifeFlasher]

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.

Using:

Private Sub btnShowAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShowAll.Click
        CustInfoDataSet.Clear()
        Me.TblNamesTableAdapter.Fill(Me.CustInfoDataSet.tblNames)
    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.

[debasisdas]

Question moved to .NET forum.

[LifeFlasher]

Ah, thank you and sorry I posted in the wrong area. Didn't realize, appreciate the assistance.

[LifeFlasher]

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.