This is an example of ADODB code that calls a stored procedure and does not expect a return dataset
First, we'll create a table, and a stored procedure:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.eConnectError
(
RowID int identity NOT NULL,
SOPNUMBE varchar(21) NOT NULL,
SOPTYPE int NOT NULL,
ERRORTEXT varchar(MAX) NOT NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.eConnectError SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
IF exists (select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME = 'dd_eConnectError_INS') begin
DROP proc dd_eConnectError_INS
end
GO
CREATE proc dd_eConnectError_INS
@sopnumbe varchar(21) ,
@soptype int,
@errortext varchar(max)
AS
insert into eConnectError (sopnumbe, soptype, errortext) values (@sopnumbe, @soptype, @errortext)
GO
GRANT EXEC ON dd_eConnectError_INS TO PUBLIC
Next, here is the .NET code to call the stored procedure and insert a line into the table
Public Shared Sub StoredProcWithParamsNoReturnDataset(strServer As String, strDatabase As String)
' Create and open the connection in a using block. This
' ensures that all resources will be closed and disposed
' when the code exits.
Using oConn As New SqlConnection(String.Format("Data Source={0};Initial Catalog={1};Integrated Security=true", strServer, strDatabase))
Dim oCmd As SqlCommand = New SqlCommand()
oCmd.Connection = oConn
oCmd.CommandText = "dd_eConnectError_INS"
oCmd.CommandType = CommandType.StoredProcedure
' Create the Command and Parameter objects.
Dim parameter As SqlParameter
parameter = New SqlParameter("@sopnumbe", "ORD1234")
oCmd.Parameters.Add(parameter)
parameter = New SqlParameter("@soptype", "2")
oCmd.Parameters.Add(parameter)
parameter = New SqlParameter("@errortext", "somthing bad happenned")
oCmd.Parameters.Add(parameter)
' Open the connection in a try/catch block.
' Create and execute the DataReader, writing the result
' set to the console window.
Try
oConn.Open()
oCmd.ExecuteNonQuery()
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End Using
End Function