Help calling a stored procedure

  • Thread starter Thread starter Mike
  • Start date Start date
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.
 
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...
 
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...
 
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...
 
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...
 
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...
 
Back
Top