Cheryl, you are correct. Im not sure if
tsp1 is using ASP.NET, but if so theres a wonderful bit of code that eliminates the need to worry about properly delimiting the query or command.
You can alleviate this by using a
Parameterized Command like so:
BAD CODE
dim Conn as New SQLConnection(ConnString)
dim strSQL as string = "SELECT This, That, TheOther " & _
"FROM Table " & _
"WHERE This='" & txtBox.Text & "' AND That=" & Cstr(integer_value) & " AND TheOther=" & Cstr(drpTheOther.SelectedItem.Value) & " " & _
"ORDER BY This DESC"
dim C as New SQLCommand(strSQL, Conn)
dim DR as SQLDataReader
Conn.Open()
'write out or bind data here
Conn.Close() |
dim Conn as New SQLConnection(ConnString)
dim strSQL as string = "SELECT This, That, TheOther " & _
"FROM Table " & _
"WHERE This=@This AND That=@That AND TheOther=@TO " & _
"ORDER BY This DESC"
dim C as New SQLCommand(strSQL, Conn)
dim DR as SQLDataReader
'Add Parameters here... great stuff ;)
With C.Parameters
.Add(New SQLParameter("@This", txtBox.Text))
.Add(New SQLParameter("@That", integer_value))
.Add(New SQLParameter("@TO", drpTheOther.SelectedItem.Value))
End With
Conn.Open()
Try
'write out or bind data here
Catch Exc as Exception
response.write(Exc.ToString().Replace(Environment.NewLine(), "<br />"))
End Try
Conn.Close() |
The Parameters take care of all conversions, delimiters, and anything else that could get in the way of a successful execution of the command/query.
Also notice the use of the
Try... Catch block... Great stuff so that when your code errors out the page finishes processing, but you can see where the errors are popping up also.
And just for kicks, here's how to accomplish this with classic ASP
:
Option Explicit
dim C, RS, strSQL, sEmpId, sHireDate, sCountry
set C = server.CreateObject("ADODB.Command")
C.ActiveConnection = ConnString
'For simplicity sake, I hardcoded these values.
sEmpId = 1
sHireDate = "1 January 1993"
sCountry = "USA"
strSQL = "SELECT FirstName, LastName, Title " & _
"FROM Employees " & _
" WHERE (EmployeeID > ? AND HireDate > ? AND Country = ?)"
C.CommandText = strSQL
set RS = C.Execute (,Array(sEmpId,sHireDate,sCountry))
Do While NOT RS.EOF
'do stuff w/ data here.
Loop |
Notice the use of the
?'s, and then how we added the associated variables using an
Array call... great stuff there. Again, adds the delimeters, etc. all by itself. The only important thing to remember is to add the items in the array in the order that they show up in the command. As long as you remember that you'll have no problems.
