Executing stored procedures as CommandType=Text

  • Thread starter Thread starter Gavin G. Jones
  • Start date Start date
G

Gavin G. Jones

I've got an urgent problem - I'm converting a legacy VB app to .NET.

I've got thousands of pre-saved strings such as "exec sp_name 'p1', 'p2',
'p3'" that execute on a VB 6 application and ADO.

These now need to execute on C# and ADO.NET.

I receive the SQL requests in the string format with all the parameters
filled in, so I can't use the strongly typed version of the SQLCommand
object's CommandType=StoredProcedure, it's got to be CommandType=Text.

....
SqlDataAdapter da = new SQLDataAdapter("exec sp_name 'p1','p2','p3'", cn );
da.Fill(ds);
....

When I try to fill my DataSet I get a SQLException - "System Error".

I can see that the stored procedure executes from within the SQL Profiler.

This worked fine under ADO.

Should I ditch ADO.Net and use an interop to ADO?

Can anyone help on this?

Regards,
Gavin G. Jones
 
----- Original Message -----
From: "Gavin G. Jones" <[email protected]>
Newsgroups: microsoft.public.dotnet.framework.adonet
Sent: Thursday, February 24, 2005 4:21 PM
Subject: Executing stored procedures as CommandType=Text

I've got thousands of pre-saved strings such as "exec sp_name 'p1', 'p2',
'p3'" that execute on a VB 6 application and ADO.

These now need to execute on C# and ADO.NET.
Click to expand...
Use DAAB and convert each instruction with the new format. It is worth it
and not that long.
Should I ditch ADO.Net and use an interop to ADO?
Click to expand...
You may reconsider this. ADO.Net is very efficient, at least for SQL Server.

John
 
Sorry Guys, I was being passed a malformed sp query string. The stored
procedure call as Text does work.
 
Hi Gavin,

I don't think it is problem of sql query. It's OK to
execute "exec spName 'parameter1', 'parameter2', ." or
even simply "spName 'parameter1', 'parameter2', ." in
command or dataadapter.

You might need to check the connection ot connection
string.

HTH

Elton Wang
(e-mail address removed)
 
Back
Top