Executing stored procedures as CommandType=Text

[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

 

[Marina]

There should be a more discriptive error message then that in the exception.

 

[WJ]

----- 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.

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?

You may reconsider this. ADO.Net is very efficient, at least for SQL Server.

John

 

[Gavin G. Jones]

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

 

[Elton Wang]

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)