Override SqlCommand placeholder process???

[JCauble]

I have run into a snag with an application I am working on. Basically we get
data from an outside source. It's primary key is a string (kind of like a
Guid but 8 characters long). Some of these id's contain the @ symbol. The
problem is that when we go to try and get our data using the following
example it never returns our data even though the same query works in the DB.

select * from table_a where id = '@123ASCF'

The best guess I have is that the @ sign is the place holder for parameters
in the SqlCommand object. I have tried to put this value into a SqlParameter
object as well. I have tried changing it to a double @@, I also cannot
remove it as it's part of the primary key.

Also, I am unable to create stored procs and so need to find a solution that
will work using the SqlCommand / SqlReader objects to retrieve the data in
question. If I can escape it somehow to make it work that is fine too. I
just have no idea what I need to do and so far in looking at the code for the
SqlClient objects using reflector has turned up nothing usefull.

So, How in the world can I query this data from Ado.net using the SqlClient
objects?

Thanks for any help.

 

[Kerry Moorman]

JCauble,

Using parameters should work. For example:

Dim cmd As New SqlCommand

cmd.CommandText = "select * from table_a where id = @ID"
cmd.Parameters.AddWithValue("@ID", "@123ASCF")
etc

Kerry Moorman

 

[JCauble]

Unfortunately I've tried that too. It still does not let it pass thru. Not
sure why but still pretty sure that it's the @ as it's probably still trying
to parse it out as a placeholder.

Thanks.

 

[Kerry Moorman]

JCauble,

Well, I tried it before I posted and it worked fine for me. Maybe you should
post your code where you tried it.

Kerry Moorman

 

[JCauble]

Ok, I have been able to nail it down some more after finding some key
elements in Reflector.

The .Net code does not appear to be the problem.

It's Sql Server. We are running Sql 2000.

Basically what happens is the SqlCommand process runs sp_executesql under
the covers for the string I pass in.

If I put the following in SQL Server and try to run directly it fails there
as well.

declare @sql as nvarchar(4000)
set @sql = 'select * from table_1 where id = ''@123asdf''
exec sp_executesql @sql

So it looks like a resolution of the sp_executesql that is causing my
inherent problem vs Ado.net.

Go figure. Since this is not a parameter it should work especially since it
has the '' around it but it looks like it thinks it's a parameter anyway. So
now I guess I need to figure out how to escape it in sql server then I can
send it through the way it wants it.

Thanks.

 

[JCauble]

I appreciate the help from you guys. Apparently I was having a senior moment
yesterday. We have 5 different databases that we run against and I was
passing an id from the wrong server to the server I was connected to for my
unit test so of course it would not find anything.

The problem that started this whole thing ended up being simple in that the
value that was being sent in from the 3rd party system needed to be trimmed
of whitespace prior to sending to the db query.

Just goes to show what an 80 work week does to the mind after a bit.

Thanks all.