Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
SQL syntax problem 1
- Thread starter msay
- Start date
hellferret
Programmer
The quotes have all gone walkabout.
SQL = "SELECT * FROM reservations WHERE rsdate = #" & A & "# " & " and name =" & Chr(34) & B & Chr(34)& " and plane = " & Chr(34) & C & Chr(34)
Apart from that it looks OK. But you never know.
If it doesn't work then try it with example values in Query Analyzer. Then try it while missing out one of the WHERE clauses as this is where the problem is most likely to lie.
HF
SQL = "SELECT * FROM reservations WHERE rsdate = #" & A & "# " & " and name =" & Chr(34) & B & Chr(34)& " and plane = " & Chr(34) & C & Chr(34)
Apart from that it looks OK. But you never know.
If it doesn't work then try it with example values in Query Analyzer. Then try it while missing out one of the WHERE clauses as this is where the problem is most likely to lie.
HF
-
1
- #3
msay -
I try and format my SQL for readability. It usually points out problems like this. Plus it makes it easier to maintain 6 months from now.
[tt]
Dim sSQL as string
sSQL = ""
sSQL = sSQL & " SELECT *"
sSQL = sSQL & " FROM reservations"
sSQL = sSQL & " WHERE rsdate=" & A
sSQL = sSQL & " AND name='" & B & "'"
sSQL = sSQL & " AND plane='" & C & "'"
[/tt]
Note that I've replaced your double-quotes with ANSI SQL standard single-quotes. I've also removed the pound-symbols around your date value, as that's also not ANSI standard (typically used with MS-Access).
Something you may want to consider is replacing the concatenation of variables with ADO command-object parameters. It would look something like this:
[tt]
Dim adoComm as ADODB.Command
Dim adoRS as ADODB.Recordset
Dim sSQL as string
sSQL = ""
sSQL = sSQL & " SELECT NumPassengers,"
sSQL = sSQL & " Fare,"
sSQL = sSQL & " DepartAirportCode"
sSQL = sSQL & " FROM reservations"
sSQL = sSQL & " WHERE rsdate=?"
sSQL = sSQL & " AND name=?"
sSQL = sSQL & " AND plane=?"
Set adoComm = New ADODB.Command
Set adoRS = New ADODB.Recordset
adoComm.ActiveConnection = adoConn [tab]'Use connection object
adoComm.CommandText = sSQL
adoComm.Parameters.Append adoComm.CreateParameter("rsdate", adDate, adParamInput, , A)
adoComm.Parameters.Append adoComm.CreateParameter("name", adVarChar, adParamInput, , B)
adoComm.Parameters.Append adoComm.CreateParameter("plane", adVarChar, adParamInput, , C)
adoRS = adoComm.Execute
If Not (adoRS.BOF And adoRS.EOF) Then
[tab]' Code to loop through recordset
End If
[/tt]
Using the adoCommand object has the benefit of replacing any single or double-quotes in your string variables so they'll show up correctly in the database. They also execute faster.
I also changed your SELECT clause to spell out the column names. Looks like a pain to type all that in, and it is
. But it executes faster since the database engine doesn't need to query the system catalog to find out what columns are in the table.
Hope this helps.
Chip H.
I try and format my SQL for readability. It usually points out problems like this. Plus it makes it easier to maintain 6 months from now.
[tt]
Dim sSQL as string
sSQL = ""
sSQL = sSQL & " SELECT *"
sSQL = sSQL & " FROM reservations"
sSQL = sSQL & " WHERE rsdate=" & A
sSQL = sSQL & " AND name='" & B & "'"
sSQL = sSQL & " AND plane='" & C & "'"
[/tt]
Note that I've replaced your double-quotes with ANSI SQL standard single-quotes. I've also removed the pound-symbols around your date value, as that's also not ANSI standard (typically used with MS-Access).
Something you may want to consider is replacing the concatenation of variables with ADO command-object parameters. It would look something like this:
[tt]
Dim adoComm as ADODB.Command
Dim adoRS as ADODB.Recordset
Dim sSQL as string
sSQL = ""
sSQL = sSQL & " SELECT NumPassengers,"
sSQL = sSQL & " Fare,"
sSQL = sSQL & " DepartAirportCode"
sSQL = sSQL & " FROM reservations"
sSQL = sSQL & " WHERE rsdate=?"
sSQL = sSQL & " AND name=?"
sSQL = sSQL & " AND plane=?"
Set adoComm = New ADODB.Command
Set adoRS = New ADODB.Recordset
adoComm.ActiveConnection = adoConn [tab]'Use connection object
adoComm.CommandText = sSQL
adoComm.Parameters.Append adoComm.CreateParameter("rsdate", adDate, adParamInput, , A)
adoComm.Parameters.Append adoComm.CreateParameter("name", adVarChar, adParamInput, , B)
adoComm.Parameters.Append adoComm.CreateParameter("plane", adVarChar, adParamInput, , C)
adoRS = adoComm.Execute
If Not (adoRS.BOF And adoRS.EOF) Then
[tab]' Code to loop through recordset
End If
[/tt]
Using the adoCommand object has the benefit of replacing any single or double-quotes in your string variables so they'll show up correctly in the database. They also execute faster.
I also changed your SELECT clause to spell out the column names. Looks like a pain to type all that in, and it is
. But it executes faster since the database engine doesn't need to query the system catalog to find out what columns are in the table.Hope this helps.
Chip H.
- Thread starter
- #4
- Status
Similar threads
