SqlDataAdapter Parameterized Query - Apostrophe Issue

[herlihyboy]

I have searched and can't find any articles pertaining to this issue...

I am filling a dataset using a SqlDataAdapter that was instantiated
with a SqlCommand object. When the Fill method is called, I get the
"incorrect syntax near..." error message that you typically see when a
query is constructed and apostrophes aren't doubled up.

However, it was my understanding that using the SqlCommand object and
creating/populating my parameters here should help avoid the apostrophe
issue. Here's a bit o' code...

Dim MyParamValue As String = "Bob's Gym" 'APOSTROPHE IN PARAM

Dim command As New SqlClient.SqlCommand
With command
.CommandText = "my_sproc"
.CommandType = CommandType.StoredProcedure
.Parameters.Add(New SqlClient.SqlParameter("@myParameter",
MyParamValue))
.Connection = New
SqlClient.SqlConnection("server=myserver;database=mydbs;uid=myuid;pwd=mypwd")
End With
Dim adapt As New SqlClient.SqlDataAdapter(command)
Dim ds As New DataSet
adapt.Fill(ds)

The "incorrect syntax near..." error is thrown on Fill. If I
explicitly double up all occurrences of apostrophes in my parameter
values before execution, it runs fine.

Is this a known thing? Am I missing something obvious?

Thanks guys and gals,

Ryan

 

[Larry Lard]

I have searched and can't find any articles pertaining to this issue...

I am filling a dataset using a SqlDataAdapter that was instantiated
with a SqlCommand object. When the Fill method is called, I get the
"incorrect syntax near..." error message that you typically see when a
query is constructed and apostrophes aren't doubled up.

Is there any dynamic SQL in the sproc?

 

[herlihyboy]

Is there any dynamic SQL in the sproc?

Good call, Larry. Thanks a bunch.

Ryan