Calling a Stored Procedure from Code

[Woody Splawn]

I am using SQL Server 2000 as the back-end. I have created a stored
procedure in SQL server called usp_AddContract. This Stored procedure
inserts a new contract into a contracts table. I have tested the code in
Enterprise Manager and it works correctly. At the time that I run the SP I
pass a contract number and a SSN to the SP and it creates a new contract in
the contracts table with a unique value in the ConNum field and SSN field.
In order to run the stored procedure from within SQL Server, in Query
Analizer I do the following:

DECLARE @retCode int
DECLARE @ConNum nvarchar(10)
DECLARE @SSN nvarchar(11)
SET @ConNum = 'T00003'
SET @SSN = '554319132'
EXECUTE @retCode = usp_AddContract @ConNum,@SSN
PRINT @retCode

My question is, how do I do call the stored procedure under code in Visual
Studio. If I had a button on a form, what code would I write to call and
execute the stored procedure above?

I have fiddled with it myself and tried the following code in a button but I
seem to be missing something with regard to how to actually execute the SP.
(mySQLConnection and myConnectionString are public variables declared
elsewhere in my form)

Dim cmdContracts As New SqlCommand
mySqlConnection = New SqlConnection(myConnectionString)
mySqlConnection.Open()
cmdContracts = mySqlConnection.CreateCommand
cmdContracts.CommandType = CommandType.StoredProcedure
cmdContracts.CommandText = "usp_AddContract"
cmdContracts.Parameters.Add(New SqlParameter("@ConNum", "T00005"))
cmdContracts.Parameters.Add(New SqlParameter("@SSN", "554319132"))
mySqlConnection.Close()

Thank You

[Mike Bulava]

Yeah, you're are not ever executing the Stored Procedure
add line
cmdContracts.ExecuteNonQuery
after you've filled the parameters

"Woody Splawn" <wo***@splawns.com> wrote in message
news:es**************@TK2MSFTNGP09.phx.gbl...

I am using SQL Server 2000 as the back-end. I have created a stored
procedure in SQL server called usp_AddContract. This Stored procedure
inserts a new contract into a contracts table. I have tested the code in
Enterprise Manager and it works correctly. At the time that I run the SP I pass a contract number and a SSN to the SP and it creates a new contract in the contracts table with a unique value in the ConNum field and SSN field.
In order to run the stored procedure from within SQL Server, in Query
Analizer I do the following:

DECLARE @retCode int
DECLARE @ConNum nvarchar(10)
DECLARE @SSN nvarchar(11)
SET @ConNum = 'T00003'
SET @SSN = '554319132'
EXECUTE @retCode = usp_AddContract @ConNum,@SSN
PRINT @retCode

My question is, how do I do call the stored procedure under code in Visual
Studio. If I had a button on a form, what code would I write to call and
execute the stored procedure above?

I have fiddled with it myself and tried the following code in a button but I seem to be missing something with regard to how to actually execute the SP. (mySQLConnection and myConnectionString are public variables declared
elsewhere in my form)

Dim cmdContracts As New SqlCommand
mySqlConnection = New SqlConnection(myConnectionString)
mySqlConnection.Open()
cmdContracts = mySqlConnection.CreateCommand
cmdContracts.CommandType = CommandType.StoredProcedure
cmdContracts.CommandText = "usp_AddContract"
cmdContracts.Parameters.Add(New SqlParameter("@ConNum", "T00005"))
cmdContracts.Parameters.Add(New SqlParameter("@SSN", "554319132"))
mySqlConnection.Close()

Thank You

[Josh Moody [MSFT]]

You're pretty close; now you need to execute your sp. Before you close your
connection, do this:
dim ret as object = cmdContracts.ExecuteScalar()
Which will give you the return value that you want.

HTH

Josh Moody
VSU Team

--

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

Note: For the benefit of the community-at-large, all responses to this
message are best directed to the newsgroup/thread from which they
originated.
--------------------

From: "Woody Splawn" <wo***@splawns.com>
Subject: Calling a Stored Procedure from Code
Date: Thu, 11 Dec 2003 12:45:18 -0800
Lines: 41
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <es**************@TK2MSFTNGP09.phx.gbl>
Newsgroups: microsoft.public.dotnet.languages.vb
NNTP-Posting-Host: 168.158-60-66-fuji-dsl.static.surewest.net 66.60.158.168
Path: cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTN GP08.phx.gbl!TK2MSFTNGP09.
phx.gblXref: cpmsftngxa07.phx.gbl microsoft.public.dotnet.languages.vb:163333
X-Tomcat-NG: microsoft.public.dotnet.languages.vb

I am using SQL Server 2000 as the back-end. I have created a stored
procedure in SQL server called usp_AddContract. This Stored procedure
inserts a new contract into a contracts table. I have tested the code in
Enterprise Manager and it works correctly. At the time that I run the SP I
pass a contract number and a SSN to the SP and it creates a new contract in
the contracts table with a unique value in the ConNum field and SSN field.
In order to run the stored procedure from within SQL Server, in Query
Analizer I do the following:

DECLARE @retCode int
DECLARE @ConNum nvarchar(10)
DECLARE @SSN nvarchar(11)
SET @ConNum = 'T00003'
SET @SSN = '554319132'
EXECUTE @retCode = usp_AddContract @ConNum,@SSN
PRINT @retCode

My question is, how do I do call the stored procedure under code in Visual
Studio. If I had a button on a form, what code would I write to call and
execute the stored procedure above?

I have fiddled with it myself and tried the following code in a button but Iseem to be missing something with regard to how to actually execute the SP.
(mySQLConnection and myConnectionString are public variables declared
elsewhere in my form)

Dim cmdContracts As New SqlCommand
mySqlConnection = New SqlConnection(myConnectionString)
mySqlConnection.Open()
cmdContracts = mySqlConnection.CreateCommand
cmdContracts.CommandType = CommandType.StoredProcedure
cmdContracts.CommandText = "usp_AddContract"
cmdContracts.Parameters.Add(New SqlParameter("@ConNum", "T00005"))
cmdContracts.Parameters.Add(New SqlParameter("@SSN", "554319132"))
mySqlConnection.Close()

Thank You