Stored Procedure

  • Thread starter Thread starter Catalin Porancea
  • Start date Start date
C

Catalin Porancea

Hello,

I need to execute a sp from a command at run time. If the command is a
"select" statement I use:

cmd.commandtext = "select * from table"
cmd.executenonquery

I tried

cmd.commandtext = "exec sp_procedure"
cmd.executenonquery

but it didn't work.

My connection is ODBC.

Can anybody help?

Thank you.
 
Hi,

You need to set the CommandType to StoredProcedure. Like the following

cmd.CommandTest = "sp_procedure"
cmd.CommandType = CommandType.StoredProcedure
cmd.ExecuteNonQuery

If there are any parameters you can add them to the Parameters Collection,
before executing the SP

cmd.Parameters.Add( "@ParamName", value )

Hope this helps

Chris Taylor
 
Thanks Chris, but it didn't work.


This is the code:
---------------------------------------------
cmd_stat.CommandText = "sp_stat"
cmd_stat.CommandType = CommandType.StoredProcedure
cmd_stat.CommandTimeout = 600
cmd_stat.ExecuteNonQuery()
MsgBox("done", MsgBoxStyle.Information, "test")
----------------------------------------------
cmd_stat already has a connection assigned in design mode.

this is the error:
-----------------------------------------------
An unhandled exception of
type 'System.Data.Odbc.OdbcException' occurred in
system.data.dll

Additional information: System error.
------------------------------------------------
 
did you add:
System.Data.SqlClient

to the project?

Tom
--
==========================================
= Tom Vande Stouwe MCSD.net, MCAD.net, MCP
= 45Wallstreet.com (www.45wallstreet.com)
= (803)-345-5001
==========================================
= If you are not making any mistakes
..= ..you are not trying hard enough.
==========================================
This is the spam catching address: (e-mail address removed) do not use this address
or you will be blocked at the server ;)
 
No, where and how do i do that?
does it make any difference if i use odbc?

thank you.
 
as you are using ODBC then try this

myCommand.CommandType = CommandType.StoredProcedure;
myCommand.CommandText = "{call Store_Proc_Name(?)}";

//add number of parameters by mentining (?,?,?) for example if you have 3
paramters for store proc then use this

myCommand.CommandType = CommandType.StoredProcedure;
myCommand.CommandText = "{call Store_Proc_Name(?,?,?)}";

Let me know if this worked for u or not !

Cheers !
 
Nope, it didn't work.
Same error.

I don't pass any parameter to the sp and the sp doesn't
return a record set.
 
If you are firing a Simple Select statement via Stored proc,
executeNonQuery is probably the problem.

Typically you'd fire something like DataReader dr = cmd.ExecuteReader

or cmd.ExecuteScalar for single values after setting commandType to
CommandType.StoredProcedure.
 
Try this code (Replace with your correct sp name)

imports system.data
imports system.data.sqlclient
....

Dim l_Schema As New DataSet
Dim l_Query1 As String = "Exec sp_procname "
Dim l_conn1 As New SqlConnection(sConnStr)
Dim l_DA As New SqlDataAdapter(l_Query1, l_conn1)
l_DA.Fill(l_Schema)

Tom
--
==========================================
= Tom Vande Stouwe MCSD.net, MCAD.net, MCP
= 45Wallstreet.com (www.45wallstreet.com)
= (803)-345-5001
==========================================
= If you are not making any mistakes
..= ..you are not trying hard enough.
==========================================
This is the spam catching address: (e-mail address removed) do not use this address
or you will be blocked at the server ;)
 
Catalin:

Also, in addition to getting rid of the non-query, wrap the execute
statement in a try catch and check exception.ToSTring and see what else it
tells you.

I'm pretty sure it's the non-query
 
Are you sure you have the permissions to do this? On SQL Server you need to
grant permission to public or whatever user for it to work.

Wrap the execute in a try catch and see exactly what exception info you
get..use Exception.ToSTring vs. Exception.Message

Also, it's probably not the problem, but do you have an open connection?

Let me know.

Bill
 
Back
Top