SQL Querry question

  • Thread starter Thread starter Anthony Sox
  • Start date Start date
A

Anthony Sox

I have code that looks like this:

Dim Cmd As OleDb.OleDbCommand
Dim Data As OleDb.OleDbDataReader

with Cmd
.Connection = _Conn

.CommandText = "SELECT * FROM Operators WHERE OperatorID='" &
Me._OperatorID & "'"

Data = .ExecuteReader
end with

This works fine for as lond as the patameter Me._operatorID does not contain
an apostrophe ("'") - , for example a name like O'Brien would cause an
error.

How can i go around this problem of solve it.

Thanks in advance
 
Paramaterize it.

SELECT * FROM Operators WHERE OperatorID=?

cmd.Paramaters.Add("SomeName", Value);

There are many overaloads to adding params but that's essentially the
syntax. IN oledb it's important that the place you add the paramaters in the
collection matches where they are in the query.
 
Anthony said:
This works fine for as lond as the patameter Me._operatorID does not
contain an apostrophe ("'") - , for example a name like O'Brien would
cause an error.
Click to expand...

In addition to the solution suggested by W.G.Ryan, you can also just double
any apostrophes in your parameters, so O'Brien becomes O''Brien:

.CommandText = "SELECT * FROM Operators WHERE OperatorID='" &
Replace(Me._OperatorID, "'", "''") & "'"

The parameterised approached described by W.G.Ryan is definitely a safer way
to do it (there are other characters that can cause problems, for example,
and the parameterisation method will take care of them all for you), but
this method is still worth knowing.
 
Back
Top