SQL Question?

[liquidspaces]

I'm not sure what I'm doing wrong here. It could be any number of things, though I suspect the SQL statement isn't doing what it's supposed to.

 

I'm 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. That's what I'm trying to do here.

 

LBto is the first list box.

 

Correction. I just discovered that the for loop isn't executing at all for some reason. Man, I'm new at this. Don't laugh if the answer's 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

Edited December 3, 2002 by Robby

[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 isn't 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 wasn't in there.

 

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

[Derek Stone]

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

[liquidspaces]

DEREK!!!

You're my hero. Thanks so much, it works like a charm.

 

That little snippet's going in my wallet. I can't 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 doesn't fix it.

For sure C# doesn't but usually VB does. Oh well.

[liquidspaces]

Robby:

I thought it was working, because it wasn't 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 hadn't nailed the SQL yet.

 

I see you did give me the exact same answer, so you're my hero too. I'm very new at this, and didn't think to toy around with the quotes. I will next time, though.

 

Thanks,

Kevin

[Robby]

It was bothering me that the ide didn't remove the spaces that were not within quotes.

 

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

[Robby]

In all fairness, Derek's my hero too, he's the guru around here.