SQL Querry question

[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

 

[W.G. Ryan eMVP]

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.

 

[Oenone]

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.

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.