SQL Question?

[liquidspaces]

Im not sure what Im doing wrong here. It could be any number of things, though I suspect the SQL statement isnt doing what its supposed to.

Im trying to populate a list box whose data are determined by a previous list box. The first one lists companies. Then, depending on which one they choose, the second one will display all the invoice dates for that company. Thats what Im trying to do here.

LBto is the first list box.

Correction. I just discovered that the for loop isnt executing at all for some reason. Man, Im new at this. Dont laugh if the answers poking me in the face.

Dim strSelected As String = LBTo.SelectedItem

        Dim objDateDataAdapter As New OleDb.OleDbDataAdapter("SELECT DATE FROM Shipping WHERE SHIP_TO = " & ("strSelected"), OleDbConnection1)
        Dim objDateCommand As New OleDb.OleDbCommandBuilder(objDateDataAdapter)
        Dim objDateDataSet As New DataSet()

        objDateDataSet.Clear()
        objDateDataAdapter.FillSchema(objDateDataSet, SchemaType.Source, "Shipping")
        objDateDataAdapter.Fill(objDateDataSet, "Shipping")
        lbDate.Items.Clear()

        Dim i As Integer 
        Dim strCurrent As String

        For i = 1 To objDateDataSet.Tables("Shipping").Rows.Count
            strCurrent = objDateDataSet.Tables("Shipping").Rows(i - 1).Item("DATE")
            lbDate.Items.Add(strCurrent)
        Next

 

[Robby]

the sql shoul look like this...

Dim objDateDataAdapter As New OleDb.OleDbDataAdapter("SELECT DATE FROM Shipping WHERE SHIP_TO =  " & strSelected & "  ", OleDbConnection1)

 

[liquidspaces]

Thanks, Robby. That clears up the SQL, but my loop still isnt executing. Any ideas?

 

[liquidspaces]

The problem is still with the SQL. When I hardcoded it like this:

"SELECT DATE FROM Shipping WHERE SHIP_TO = " ("Kevin"), Connection)

It worked beautifully. When I did it the way you suggested, it was searching the database for "strSelection" which of course wasnt in there.

So Im not sure how to use a string variable properly in an SQL statement. Thats what I need help on now. Any ideas?

 

[Derek Stone]

strSQL = "SELECT DATE FROM Shipping WHERE SHIP_TO = " & strSelection & ""

 

[liquidspaces]

DEREK!!!
Youre my hero. Thanks so much, it works like a charm.

That little snippets going in my wallet. I cant forget that.

Thanks again,
Kevin

 

[Robby]

hmm, I thought you had it working when I gave you the exact same answer?

 

[Derek Stone]

You had spaces between the apostrophes and the text. A difference of " text " and "text". *Shrug*

 

[Robby]

I figured the best way for him to understand it was to leave visible spaces, I find it wierd that the ide doesnt fix it.
For sure C# doesnt but usually VB does. Oh well.

 

[liquidspaces]

Robby:
I thought it was working, because it wasnt getting caught up on the SQL anymore. It was acting like it understood, past the dataadapter.fill that it gets hung up on if the SQL is bad.

That led me to believe that the loop was a problem, when I hadnt nailed the SQL yet.

I see you did give me the exact same answer, so youre my hero too. Im very new at this, and didnt think to toy around with the quotes. I will next time, though.

Thanks,
Kevin

 

[Robby]

It was bothering me that the ide didnt remove the spaces that were not within quotes.

Idiot me, the spaces I had were inside the quotes. OOOOOPS.

 

[Robby]

In all fairness, Dereks my hero too, hes the guru around here.