Help calling a stored procedure

M

Mike

Hello all, I need a little help. I'm calling a stored procedure from my
vb.net prog and I keep getting an error. Could someone look at my code and
help me out a little. Thanks in advance. I need to use odbc because of
multiple database types.

Here is the procedure:

AddMember:

if exists (select * from sysobjects where id = object_id('dbo.AddMember')
and sysstat & 0xf = 4)
drop procedure dbo.AddMember
GO

CREATE PROCEDURE AddMember
@mbr_acctnum decimal(12,0),
@mbr_name varchar(50),
@mbr_ssn varchar(9),
@mbr_employee char(1)

AS
INSERT INTO Members (Account_Number, Member_Name, Social_Security_Number,
Employee)
VALUES (@mbr_acctnum, @mbr_name, @mbr_ssn, @mbr_employee)
GO

Here is the code:

'Create the ODBC objects
Dim OdbcCon As System.Data.Odbc.OdbcConnection
Dim OdbcCmd As System.Data.Odbc.OdbcCommand
'Instantiate new instances
OdbcCon = New System.Data.Odbc.OdbcConnection
OdbcCmd = New System.Data.Odbc.OdbcCommand

'Open a connection to an iSeries data source
OdbcCon.ConnectionString = _
"Driver={SQL Server};Server=" & _
pserver & ";Database=" & database & _
";Uid=" & puserid & "pwd=" & password

'Set up the procedure call
OdbcCmd.CommandText = "AddMember"
OdbcCmd.CommandType = CommandType.StoredProcedure
'Accosicate the command with the connection
OdbcCmd.Connection = OdbcCon
'Open the connection
OdbcCon.Open()
'Create the parameter objects to pass and get data from procedure
OdbcCmd.Parameters.Add("@mbr_acctnum", Odbc.OdbcType.Decimal, _
12, 0).Direction = ParameterDirection.Input
OdbcCmd.Parameters.Add("@mbr_name", Odbc.OdbcType.VarChar, _
50).Direction = ParameterDirection.Input
OdbcCmd.Parameters.Add("@mbr_ssn", Odbc.OdbcType.VarChar, _
9).Direction = ParameterDirection.Input
OdbcCmd.Parameters.Add("@mbr_employee", Odbc.OdbcType.Char, _
1).Direction = ParameterDirection.Input
'set the value of the parms to pass
OdbcCmd.Parameters(0).Value = 12
OdbcCmd.Parameters(1).Value = "xxxx wwww"
OdbcCmd.Parameters(2).Value = "444229999"
OdbcCmd.Parameters(3).Value = "Y"
'call the procedure
OdbcCmd.ExecuteNonQuery()
'put the results into a textbox control
'txtResult.Text = OdbcCmd.Parameters(1).Value
'close the connection
OdbcCon.Close()

Here is the error I receive:

MSSQLJOB 964-9a4 EXIT SQLExecDirectW with return code -1
(SQL_ERROR)
HSTMT 03A31BC0
WCHAR * 0x010C08E8 [ -3] "AddMember\ 0"
SDWORD -3

DIAG [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
AddMember expects parameter @mbr_acctnum, which was not supplied. (201)

DIAG [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
AddMember expects parameter @mbr_name, which was not supplied. (201)

DIAG [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
AddMember expects parameter @mbr_ssn, which was not supplied. (201)

DIAG [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
AddMember expects parameter @mbr_employee, which was not supplied. (201)

Any help appreciated.
 
G

Guest

Try using this syntax instead. I think the parameters are not actually being
added to the collection.

dim prm as odbcparameter = new odbcparameter("@mbr_acctnum",
Odbc.OdbcType.Decimal, 12, 0)
prm.Direction = ParameterDirection.Input
OdbcCmd.Parameters.Add(prm)

....etc

--
Brad

"Software is like melted pudding..."


Mike said:
Hello all, I need a little help. I'm calling a stored procedure from my
vb.net prog and I keep getting an error. Could someone look at my code and
help me out a little. Thanks in advance. I need to use odbc because of
multiple database types.

Here is the procedure:

AddMember:

if exists (select * from sysobjects where id = object_id('dbo.AddMember')
and sysstat & 0xf = 4)
drop procedure dbo.AddMember
GO

CREATE PROCEDURE AddMember
@mbr_acctnum decimal(12,0),
@mbr_name varchar(50),
@mbr_ssn varchar(9),
@mbr_employee char(1)

AS
INSERT INTO Members (Account_Number, Member_Name, Social_Security_Number,
Employee)
VALUES (@mbr_acctnum, @mbr_name, @mbr_ssn, @mbr_employee)
GO

Here is the code:

'Create the ODBC objects
Dim OdbcCon As System.Data.Odbc.OdbcConnection
Dim OdbcCmd As System.Data.Odbc.OdbcCommand
'Instantiate new instances
OdbcCon = New System.Data.Odbc.OdbcConnection
OdbcCmd = New System.Data.Odbc.OdbcCommand

'Open a connection to an iSeries data source
OdbcCon.ConnectionString = _
"Driver={SQL Server};Server=" & _
pserver & ";Database=" & database & _
";Uid=" & puserid & "pwd=" & password

'Set up the procedure call
OdbcCmd.CommandText = "AddMember"
OdbcCmd.CommandType = CommandType.StoredProcedure
'Accosicate the command with the connection
OdbcCmd.Connection = OdbcCon
'Open the connection
OdbcCon.Open()
'Create the parameter objects to pass and get data from procedure
OdbcCmd.Parameters.Add("@mbr_acctnum", Odbc.OdbcType.Decimal, _
12, 0).Direction = ParameterDirection.Input
OdbcCmd.Parameters.Add("@mbr_name", Odbc.OdbcType.VarChar, _
50).Direction = ParameterDirection.Input
OdbcCmd.Parameters.Add("@mbr_ssn", Odbc.OdbcType.VarChar, _
9).Direction = ParameterDirection.Input
OdbcCmd.Parameters.Add("@mbr_employee", Odbc.OdbcType.Char, _
1).Direction = ParameterDirection.Input
'set the value of the parms to pass
OdbcCmd.Parameters(0).Value = 12
OdbcCmd.Parameters(1).Value = "xxxx wwww"
OdbcCmd.Parameters(2).Value = "444229999"
OdbcCmd.Parameters(3).Value = "Y"
'call the procedure
OdbcCmd.ExecuteNonQuery()
'put the results into a textbox control
'txtResult.Text = OdbcCmd.Parameters(1).Value
'close the connection
OdbcCon.Close()

Here is the error I receive:

MSSQLJOB 964-9a4 EXIT SQLExecDirectW with return code -1
(SQL_ERROR)
HSTMT 03A31BC0
WCHAR * 0x010C08E8 [ -3] "AddMember\ 0"
SDWORD -3

DIAG [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
AddMember expects parameter @mbr_acctnum, which was not supplied. (201)

DIAG [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
AddMember expects parameter @mbr_name, which was not supplied. (201)

DIAG [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
AddMember expects parameter @mbr_ssn, which was not supplied. (201)

DIAG [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
AddMember expects parameter @mbr_employee, which was not supplied. (201)

Any help appreciated.
Click to expand...
 
M

Mike

Brad, thanks for the reply. I tried what you suggested but I can only get
one parameter set. When I try to do the rest I get a local variable 'prm'
is already declared in the local block. Like this:

Dim prm As OdbcParameter = New OdbcParameter("@mbr_acctnum", _
Odbc.OdbcType.Decimal, 12, 0)
prm.Direction = ParameterDirection.Input
OdbcCmd.Parameters.Add(prm)
Dim prm As OdbcParameter = New OdbcParameter("@mbr_name", _
<-------- I get error here.
Odbc.OdbcType.VarChar, 50)

How do I set more than one?


Brad Roberts said:
Try using this syntax instead. I think the parameters are not actually
being
added to the collection.

dim prm as odbcparameter = new odbcparameter("@mbr_acctnum",
Odbc.OdbcType.Decimal, 12, 0)
prm.Direction = ParameterDirection.Input
OdbcCmd.Parameters.Add(prm)

...etc

--
Brad

"Software is like melted pudding..."


Mike said:
Hello all, I need a little help. I'm calling a stored procedure from my
vb.net prog and I keep getting an error. Could someone look at my code
and
help me out a little. Thanks in advance. I need to use odbc because of
multiple database types.

Here is the procedure:

AddMember:

if exists (select * from sysobjects where id = object_id('dbo.AddMember')
and sysstat & 0xf = 4)
drop procedure dbo.AddMember
GO

CREATE PROCEDURE AddMember
@mbr_acctnum decimal(12,0),
@mbr_name varchar(50),
@mbr_ssn varchar(9),
@mbr_employee char(1)

AS
INSERT INTO Members (Account_Number, Member_Name, Social_Security_Number,
Employee)
VALUES (@mbr_acctnum, @mbr_name, @mbr_ssn, @mbr_employee)
GO

Here is the code:

'Create the ODBC objects
Dim OdbcCon As System.Data.Odbc.OdbcConnection
Dim OdbcCmd As System.Data.Odbc.OdbcCommand
'Instantiate new instances
OdbcCon = New System.Data.Odbc.OdbcConnection
OdbcCmd = New System.Data.Odbc.OdbcCommand

'Open a connection to an iSeries data source
OdbcCon.ConnectionString = _
"Driver={SQL Server};Server=" & _
pserver & ";Database=" & database & _
";Uid=" & puserid & "pwd=" & password

'Set up the procedure call
OdbcCmd.CommandText = "AddMember"
OdbcCmd.CommandType = CommandType.StoredProcedure
'Accosicate the command with the connection
OdbcCmd.Connection = OdbcCon
'Open the connection
OdbcCon.Open()
'Create the parameter objects to pass and get data from procedure
OdbcCmd.Parameters.Add("@mbr_acctnum", Odbc.OdbcType.Decimal, _
12, 0).Direction = ParameterDirection.Input
OdbcCmd.Parameters.Add("@mbr_name", Odbc.OdbcType.VarChar, _
50).Direction = ParameterDirection.Input
OdbcCmd.Parameters.Add("@mbr_ssn", Odbc.OdbcType.VarChar, _
9).Direction = ParameterDirection.Input
OdbcCmd.Parameters.Add("@mbr_employee", Odbc.OdbcType.Char, _
1).Direction = ParameterDirection.Input
'set the value of the parms to pass
OdbcCmd.Parameters(0).Value = 12
OdbcCmd.Parameters(1).Value = "xxxx wwww"
OdbcCmd.Parameters(2).Value = "444229999"
OdbcCmd.Parameters(3).Value = "Y"
'call the procedure
OdbcCmd.ExecuteNonQuery()
'put the results into a textbox control
'txtResult.Text = OdbcCmd.Parameters(1).Value
'close the connection
OdbcCon.Close()

Here is the error I receive:

MSSQLJOB 964-9a4 EXIT SQLExecDirectW with return code -1
(SQL_ERROR)
HSTMT 03A31BC0
WCHAR * 0x010C08E8 [ -3] "AddMember\ 0"
SDWORD -3

DIAG [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
AddMember expects parameter @mbr_acctnum, which was not supplied. (201)

DIAG [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
AddMember expects parameter @mbr_name, which was not supplied. (201)

DIAG [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
AddMember expects parameter @mbr_ssn, which was not supplied. (201)

DIAG [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
AddMember expects parameter @mbr_employee, which was not supplied. (201)

Any help appreciated.
Click to expand...
Click to expand...
 
M

Mike

The following code resolved my problem:


'Create the ODBC objects
Dim OdbcCon As System.Data.Odbc.OdbcConnection
OdbcCon = New System.Data.Odbc.OdbcConnection
'Set up Connection String
OdbcCon.ConnectionString = _
"Driver={SQL Server};Server=" & _
pserver & ";Database=" & database & _
";Uid=" & puserid & ";Pwd=" & password
'Open the connection
OdbcCon.Open()

Dim OdbcCmd As OdbcCommand = OdbcCon.CreateCommand()
OdbcCmd.CommandText = "{ call AddMember(?,?,?,?) }"

Dim param As New OdbcParameter
param.DbType = DbType.Decimal
param.Value = 12
OdbcCmd.Parameters.Add(param)

param = New OdbcParameter
param.DbType = DbType.String
param.Value = "Mikey Ward"
OdbcCmd.Parameters.Add(param)

param = New OdbcParameter
param.DbType = DbType.String
param.Value = "444229999"
OdbcCmd.Parameters.Add(param)

param = New OdbcParameter
param.DbType = DbType.StringFixedLength
param.Value = "Y"
OdbcCmd.Parameters.Add(param)

OdbcCmd.ExecuteNonQuery()

OdbcCon.Close()

Mike said:
Brad, thanks for the reply. I tried what you suggested but I can only get
one parameter set. When I try to do the rest I get a local variable 'prm'
is already declared in the local block. Like this:

Dim prm As OdbcParameter = New OdbcParameter("@mbr_acctnum", _
Odbc.OdbcType.Decimal, 12, 0)
prm.Direction = ParameterDirection.Input
OdbcCmd.Parameters.Add(prm)
Dim prm As OdbcParameter = New OdbcParameter("@mbr_name", _
<-------- I get error here.
Odbc.OdbcType.VarChar, 50)

How do I set more than one?


Brad Roberts said:
Try using this syntax instead. I think the parameters are not actually
being
added to the collection.

dim prm as odbcparameter = new odbcparameter("@mbr_acctnum",
Odbc.OdbcType.Decimal, 12, 0)
prm.Direction = ParameterDirection.Input
OdbcCmd.Parameters.Add(prm)

...etc

--
Brad

"Software is like melted pudding..."


Mike said:
Hello all, I need a little help. I'm calling a stored procedure from my
vb.net prog and I keep getting an error. Could someone look at my code
and
help me out a little. Thanks in advance. I need to use odbc because of
multiple database types.

Here is the procedure:

AddMember:

if exists (select * from sysobjects where id =
object_id('dbo.AddMember')
and sysstat & 0xf = 4)
drop procedure dbo.AddMember
GO

CREATE PROCEDURE AddMember
@mbr_acctnum decimal(12,0),
@mbr_name varchar(50),
@mbr_ssn varchar(9),
@mbr_employee char(1)

AS
INSERT INTO Members (Account_Number, Member_Name,
Social_Security_Number,
Employee)
VALUES (@mbr_acctnum, @mbr_name, @mbr_ssn, @mbr_employee)
GO

Here is the code:

'Create the ODBC objects
Dim OdbcCon As System.Data.Odbc.OdbcConnection
Dim OdbcCmd As System.Data.Odbc.OdbcCommand
'Instantiate new instances
OdbcCon = New System.Data.Odbc.OdbcConnection
OdbcCmd = New System.Data.Odbc.OdbcCommand

'Open a connection to an iSeries data source
OdbcCon.ConnectionString = _
"Driver={SQL Server};Server=" & _
pserver & ";Database=" & database & _
";Uid=" & puserid & "pwd=" & password

'Set up the procedure call
OdbcCmd.CommandText = "AddMember"
OdbcCmd.CommandType = CommandType.StoredProcedure
'Accosicate the command with the connection
OdbcCmd.Connection = OdbcCon
'Open the connection
OdbcCon.Open()
'Create the parameter objects to pass and get data from procedure
OdbcCmd.Parameters.Add("@mbr_acctnum", Odbc.OdbcType.Decimal, _
12, 0).Direction = ParameterDirection.Input
OdbcCmd.Parameters.Add("@mbr_name", Odbc.OdbcType.VarChar, _
50).Direction = ParameterDirection.Input
OdbcCmd.Parameters.Add("@mbr_ssn", Odbc.OdbcType.VarChar, _
9).Direction = ParameterDirection.Input
OdbcCmd.Parameters.Add("@mbr_employee", Odbc.OdbcType.Char, _
1).Direction = ParameterDirection.Input
'set the value of the parms to pass
OdbcCmd.Parameters(0).Value = 12
OdbcCmd.Parameters(1).Value = "xxxx wwww"
OdbcCmd.Parameters(2).Value = "444229999"
OdbcCmd.Parameters(3).Value = "Y"
'call the procedure
OdbcCmd.ExecuteNonQuery()
'put the results into a textbox control
'txtResult.Text = OdbcCmd.Parameters(1).Value
'close the connection
OdbcCon.Close()

Here is the error I receive:

MSSQLJOB 964-9a4 EXIT SQLExecDirectW with return code -1
(SQL_ERROR)
HSTMT 03A31BC0
WCHAR * 0x010C08E8 [ -3] "AddMember\ 0"
SDWORD -3

DIAG [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
AddMember expects parameter @mbr_acctnum, which was not supplied. (201)

DIAG [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
AddMember expects parameter @mbr_name, which was not supplied. (201)

DIAG [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
AddMember expects parameter @mbr_ssn, which was not supplied. (201)

DIAG [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
AddMember expects parameter @mbr_employee, which was not supplied. (201)

Any help appreciated.
Click to expand...
Click to expand...
Click to expand...
 
C

Cowboy \(Gregory A. Beamer\)

Bleck! ODBC.

The problem is, unlike other libraries, you have to use CALL syntax with the
ODBC provider. There is a KB
http://www.dotnet247.com/247referen...rosoft.com/default.aspx?scid=kb;EN-US;Q309486

ANSWER =
'change this line
OdbcCmd.CommandText = "{ CALL AddMember (?,?,?,?) }"

You're welcome! :)

This is better code, in both order and control. I have left some of the
syntax direction you were using in your code.

---------------------

Dim connString As String = _
"Driver={SQL Server};Server=" & _
pserver & ";Database=" & database & _
";Uid=" & puserid & "pwd=" & password
Dim sql As String = "{ CALL AddMember (?,?,?,?) }"

Dim memberId As New Decimal(53)
Dim memberName As String = "John Doe"
Dim memberSsn As String = "123456789"
Dim employee As String = "Y"

Dim conn As New OdbcConnection(connString)
Dim cmd As New OdbcCommand(sql, conn)

cmd.Parameters.Add("@mbr_acctnum", Odbc.OdbcType.Decimal, _
12, 0).Direction = ParameterDirection.Input
cmd.Parameters.Add("@mbr_name", Odbc.OdbcType.VarChar, _
50).Direction = ParameterDirection.Input
cmd.Parameters.Add("@mbr_ssn", Odbc.OdbcType.VarChar, _
9).Direction = ParameterDirection.Input
cmd.Parameters.Add("@mbr_employee", Odbc.OdbcType.Char, _
1).Direction = ParameterDirection.Input

cmd.Parameters(0).Value = memberId
cmd.Parameters(1).Value = memberName
cmd.Parameters(2).Value = memberSsn
cmd.Parameters(3).Value = employee

Try
Try
conn.Open()
cmd.ExecuteNonQuery()
Catch exception1 As Exception
Dim text6 As String = exception1.Message
End Try
Finally
conn.Dispose()
End Try

-----------------

NOTE: If at all possible, move to SqlClient or even OleDb, as ODBC is a
pain.


--
Gregory A. Beamer

*************************************************
Think Outside the Box!
*************************************************
Mike said:
Hello all, I need a little help. I'm calling a stored procedure from my
vb.net prog and I keep getting an error. Could someone look at my code and
help me out a little. Thanks in advance. I need to use odbc because of
multiple database types.

Here is the procedure:

AddMember:

if exists (select * from sysobjects where id = object_id('dbo.AddMember')
and sysstat & 0xf = 4)
drop procedure dbo.AddMember
GO

CREATE PROCEDURE AddMember
@mbr_acctnum decimal(12,0),
@mbr_name varchar(50),
@mbr_ssn varchar(9),
@mbr_employee char(1)

AS
INSERT INTO Members (Account_Number, Member_Name, Social_Security_Number,
Employee)
VALUES (@mbr_acctnum, @mbr_name, @mbr_ssn, @mbr_employee)
GO

Here is the code:

'Create the ODBC objects
Dim OdbcCon As System.Data.Odbc.OdbcConnection
Dim OdbcCmd As System.Data.Odbc.OdbcCommand
'Instantiate new instances
OdbcCon = New System.Data.Odbc.OdbcConnection
OdbcCmd = New System.Data.Odbc.OdbcCommand

'Open a connection to an iSeries data source
OdbcCon.ConnectionString = _
"Driver={SQL Server};Server=" & _
pserver & ";Database=" & database & _
";Uid=" & puserid & "pwd=" & password

'Set up the procedure call
OdbcCmd.CommandText = "AddMember"
OdbcCmd.CommandType = CommandType.StoredProcedure
'Accosicate the command with the connection
OdbcCmd.Connection = OdbcCon
'Open the connection
OdbcCon.Open()
'Create the parameter objects to pass and get data from procedure
OdbcCmd.Parameters.Add("@mbr_acctnum", Odbc.OdbcType.Decimal, _
12, 0).Direction = ParameterDirection.Input
OdbcCmd.Parameters.Add("@mbr_name", Odbc.OdbcType.VarChar, _
50).Direction = ParameterDirection.Input
OdbcCmd.Parameters.Add("@mbr_ssn", Odbc.OdbcType.VarChar, _
9).Direction = ParameterDirection.Input
OdbcCmd.Parameters.Add("@mbr_employee", Odbc.OdbcType.Char, _
1).Direction = ParameterDirection.Input
'set the value of the parms to pass
OdbcCmd.Parameters(0).Value = 12
OdbcCmd.Parameters(1).Value = "xxxx wwww"
OdbcCmd.Parameters(2).Value = "444229999"
OdbcCmd.Parameters(3).Value = "Y"
'call the procedure
OdbcCmd.ExecuteNonQuery()
'put the results into a textbox control
'txtResult.Text = OdbcCmd.Parameters(1).Value
'close the connection
OdbcCon.Close()

Here is the error I receive:

MSSQLJOB 964-9a4 EXIT SQLExecDirectW with return code -1
(SQL_ERROR)
HSTMT 03A31BC0
WCHAR * 0x010C08E8 [ -3] "AddMember\ 0"
SDWORD -3

DIAG [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
AddMember expects parameter @mbr_acctnum, which was not supplied. (201)

DIAG [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
AddMember expects parameter @mbr_name, which was not supplied. (201)

DIAG [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
AddMember expects parameter @mbr_ssn, which was not supplied. (201)

DIAG [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
AddMember expects parameter @mbr_employee, which was not supplied. (201)

Any help appreciated.
Click to expand...
 
C

Cowboy \(Gregory A. Beamer\)

Did not see the additions before firing off my answer. If you get a chance,
the reordering of code I posted is a better organization. I would get rid of
the Catch and just leave the finally once you get past your intial error
phase.

--
Gregory A. Beamer

*************************************************
Think Outside the Box!
*************************************************
Mike said:
The following code resolved my problem:


'Create the ODBC objects
Dim OdbcCon As System.Data.Odbc.OdbcConnection
OdbcCon = New System.Data.Odbc.OdbcConnection
'Set up Connection String
OdbcCon.ConnectionString = _
"Driver={SQL Server};Server=" & _
pserver & ";Database=" & database & _
";Uid=" & puserid & ";Pwd=" & password
'Open the connection
OdbcCon.Open()

Dim OdbcCmd As OdbcCommand = OdbcCon.CreateCommand()
OdbcCmd.CommandText = "{ call AddMember(?,?,?,?) }"

Dim param As New OdbcParameter
param.DbType = DbType.Decimal
param.Value = 12
OdbcCmd.Parameters.Add(param)

param = New OdbcParameter
param.DbType = DbType.String
param.Value = "Mikey Ward"
OdbcCmd.Parameters.Add(param)

param = New OdbcParameter
param.DbType = DbType.String
param.Value = "444229999"
OdbcCmd.Parameters.Add(param)

param = New OdbcParameter
param.DbType = DbType.StringFixedLength
param.Value = "Y"
OdbcCmd.Parameters.Add(param)

OdbcCmd.ExecuteNonQuery()

OdbcCon.Close()

Mike said:
Brad, thanks for the reply. I tried what you suggested but I can only get
one parameter set. When I try to do the rest I get a local variable 'prm'
is already declared in the local block. Like this:

Dim prm As OdbcParameter = New OdbcParameter("@mbr_acctnum", _
Odbc.OdbcType.Decimal, 12, 0)
prm.Direction = ParameterDirection.Input
OdbcCmd.Parameters.Add(prm)
Dim prm As OdbcParameter = New OdbcParameter("@mbr_name", _
<-------- I get error here.
Odbc.OdbcType.VarChar, 50)

How do I set more than one?


Brad Roberts said:
Try using this syntax instead. I think the parameters are not actually
being
added to the collection.

dim prm as odbcparameter = new odbcparameter("@mbr_acctnum",
Odbc.OdbcType.Decimal, 12, 0)
prm.Direction = ParameterDirection.Input
OdbcCmd.Parameters.Add(prm)

...etc

--
Brad

"Software is like melted pudding..."


:

Hello all, I need a little help. I'm calling a stored procedure from my
vb.net prog and I keep getting an error. Could someone look at my code
and
help me out a little. Thanks in advance. I need to use odbc because of
multiple database types.

Here is the procedure:

AddMember:

if exists (select * from sysobjects where id =
object_id('dbo.AddMember')
and sysstat & 0xf = 4)
drop procedure dbo.AddMember
GO

CREATE PROCEDURE AddMember
@mbr_acctnum decimal(12,0),
@mbr_name varchar(50),
@mbr_ssn varchar(9),
@mbr_employee char(1)

AS
INSERT INTO Members (Account_Number, Member_Name,
Social_Security_Number,
Employee)
VALUES (@mbr_acctnum, @mbr_name, @mbr_ssn, @mbr_employee)
GO

Here is the code:

'Create the ODBC objects
Dim OdbcCon As System.Data.Odbc.OdbcConnection
Dim OdbcCmd As System.Data.Odbc.OdbcCommand
'Instantiate new instances
OdbcCon = New System.Data.Odbc.OdbcConnection
OdbcCmd = New System.Data.Odbc.OdbcCommand

'Open a connection to an iSeries data source
OdbcCon.ConnectionString = _
"Driver={SQL Server};Server=" & _
pserver & ";Database=" & database & _
";Uid=" & puserid & "pwd=" & password

'Set up the procedure call
OdbcCmd.CommandText = "AddMember"
OdbcCmd.CommandType = CommandType.StoredProcedure
'Accosicate the command with the connection
OdbcCmd.Connection = OdbcCon
'Open the connection
OdbcCon.Open()
'Create the parameter objects to pass and get data from procedure
OdbcCmd.Parameters.Add("@mbr_acctnum", Odbc.OdbcType.Decimal, _
12, 0).Direction = ParameterDirection.Input
OdbcCmd.Parameters.Add("@mbr_name", Odbc.OdbcType.VarChar, _
50).Direction = ParameterDirection.Input
OdbcCmd.Parameters.Add("@mbr_ssn", Odbc.OdbcType.VarChar, _
9).Direction = ParameterDirection.Input
OdbcCmd.Parameters.Add("@mbr_employee", Odbc.OdbcType.Char, _
1).Direction = ParameterDirection.Input
'set the value of the parms to pass
OdbcCmd.Parameters(0).Value = 12
OdbcCmd.Parameters(1).Value = "xxxx wwww"
OdbcCmd.Parameters(2).Value = "444229999"
OdbcCmd.Parameters(3).Value = "Y"
'call the procedure
OdbcCmd.ExecuteNonQuery()
'put the results into a textbox control
'txtResult.Text = OdbcCmd.Parameters(1).Value
'close the connection
OdbcCon.Close()

Here is the error I receive:

MSSQLJOB 964-9a4 EXIT SQLExecDirectW with return code -1
(SQL_ERROR)
HSTMT 03A31BC0
WCHAR * 0x010C08E8 [ -3] "AddMember\ 0"
SDWORD -3

DIAG [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
AddMember expects parameter @mbr_acctnum, which was not supplied. (201)

DIAG [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
AddMember expects parameter @mbr_name, which was not supplied. (201)

DIAG [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
AddMember expects parameter @mbr_ssn, which was not supplied. (201)

DIAG [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
AddMember expects parameter @mbr_employee, which was not supplied.
(201)

Any help appreciated.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

ODBC - Error 9
passing parms to sql stored procedure 5
problem with simple stored procedure execution 8
Procedure xxx expects parameter '@ID' which was not supplied 9
Sending parameters to a stored procedure (VB.Net 2003 + SQL Server) 3
Calling Stored Procedure and have it return records for a report 4
problem with odbc 1
Executing SQL Server Stored Procedures using ODBC.NET 4

Top