microsoft.public.dotnet.framework.odbcnet

Mike

6/12/2006 9:02:00 PM

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" <ward007@swbell.net> wrote in message
news:y5fjg.44310$4L1.7489@newssvr11.news.prodigy.com...
> Hello all, I need a little help. I''m calling a stored procedure from my
> vb.net prog and I keep getting a syntax error. Could someone look at my
> code and help me out a little. Thanks in advance.
>
> 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.
>