VB.NET - Can't write to storeprocedure via ODBC

Asked By phil taylor on 13-Nov-11 02:16 AM

I've tried all loads of forums and and examples and can't find any examples that work.
All I want to do is call a simple storeproc. I've tried something similar via sql  and it works at try , but via ODBC it's a pain.
I get the message An OdbcParameter with ParameterName '@Param2' is not contained by this OdbcParameterCollection.

Can anyone help please



Imports System.Data.Odbc

Imports System.Data.Odbc
Public Class Form1


      Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim objConn As System.Data.Odbc.OdbcConnection
        Dim objCmd As System.Data.Odbc.OdbcCommand
        Dim strConnString, strSQL As String


        strConnString = "Dsn=carparts;uid=me;trusted_connection=Yes; wsid=DOWNSTAIRS;database=carparts"
        objConn = New System.Data.Odbc.OdbcConnection(strConnString)
        objConn.Open()


  
        Dim odbcCom = New System.Data.Odbc.OdbcCommand()
        With odbcCom
            .Connection = objConn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "testinsert" ' name of storeproc. expects 2 params  (1) @partdesc (2) @partdesc
            odbcCom.Parameters.Add("@param1", OdbcType.VarChar, 255)
            odbcCom.Parameters("@param1").Value = "abc123"


            odbcCom.Parameters.Add("@param2", OdbcType.VarChar, 255)
            odbcCom.Parameters("@param2").Value = "xyz789"


            odbcCom.ExecuteNonQuery()






            objCmd = Nothing
            objConn.Close()
            objConn = Nothing
        End With
    End Sub



Stored proc = 
USE [carparts]
GO
/****** Object:  StoredProcedure [dbo].[testinsert]    Script Date: 11/13/2011 05:47:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[testinsert]  
-- Add the parameters for the stored procedure here
@Param1  as nvarchar(3), 
@Param2 as nvarchar(3) 
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;


    -- Insert statements for procedure here
Insert into zbody (partcode, partdesc )
values ( @Param1,@Param2)  
END
Kirtan Patel replied to phil taylor on 13-Nov-11 02:22 AM
you had done some mistakes in code and in stored procedure here is corrected code of both 

Imports System.Data.Odbc
 
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  Dim objConn As System.Data.Odbc.OdbcConnection
  Dim strConnString, strSQL As String
 
  strConnString = "Dsn=carparts;uid=me;trusted_connection=Yes; wsid=DOWNSTAIRS;database=carparts"
  objConn = New System.Data.Odbc.OdbcConnection(strConnString)
  objConn.Open()
 
  Dim odbcCom = New System.Data.Odbc.OdbcCommand()
  odbcCom.Connection = objConn
  odbcCom.CommandType = CommandType.StoredProcedure
  odbcCom.CommandText = "testinsert"
  odbcCom.Parameters.AddWithValue("@partcode", "abc123")
  odbcCom.Parameters.AddWithValue("@partdesc", "xyz789")
  odbcCom.ExecuteNonQuery()
 
  objConn.Close()
  objConn = Nothing
End Sub
 
 
Stored Procedure
------------------
USE [carparts]
GO
/****** Object:  StoredProcedure [dbo].[testinsert]    Script Date: 11/13/2011 05:47:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[testinsert]  
    -- Add the parameters for the stored procedure here
    @partcode  as nvarchar(3),
    @partdesc as nvarchar(3)
AS
BEGIN
    SET NOCOUNT ON;
 
  -- Insert statements for procedure here
    INSERT INTO zbody(partcode, partdesc) VALUES(@partcode,@partdesc)
END
 
phil taylor replied to Kirtan Patel on 13-Nov-11 02:27 AM
Sorry to be a pain but it's still not working. I'm getting the same error that it expects param1 which was not supplied.
Kirtan Patel replied to phil taylor on 13-Nov-11 03:00 AM
looks like you have not used my code .. i have renamed the param1 as your original parameter name  as column in table how come you get error of Param1 not found ? look at my code properly or just copy paste it in your code.
both codes Stored Procedure and C# code.

Thanks
phil taylor replied to Kirtan Patel on 13-Nov-11 03:25 AM
I've copy the storedproc, verified it, executed it, copy the code , rebuilt it, run it through debug (visual studio 2010, VB.net ) and still get the same result.

you mention c# code.... this is VB.net


phil taylor replied to Kirtan Patel on 13-Nov-11 07:32 AM
Anyone ?
phil taylor replied to phil taylor on 14-Nov-11 04:52 AM
Worked it out for myself. This works under Visual Studio 2010  its VB.net not c#
Just change the stored procedure name and parameter names.

I've trawled dozens and dozens of forums with people giving bad, and incomplete examples, even mircorosft can't be arsed to give a complete example in VB.net , but this DOES WORK


ODBC call to stored procedures with 3 paratmetres

 
Imports System.Data
Imports System.Data.Odbc
Public Class Form1

 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 
        Dim cn As New OdbcConnection
        Dim cmd As New OdbcCommand
        Dim prm As New OdbcParameter
        'Dim dr As New OdbcDataReader


        Try
            cn = New OdbcConnection("Dsn=carparts;uid=me;trusted_connection=Yes; wsid=DOWNSTAIRS;database=carparts")


            cmd = New OdbcCommand("{? = call testinsert (?, ?,?)}", cn)


            prm = cmd.Parameters.Add("@RETURN_VALUE", OdbcType.Int)
            prm.Direction = ParameterDirection.ReturnValue


            prm = cmd.Parameters.Add("@partcode", OdbcType.Char, 5)
            prm.Value = "abc1"


            prm = cmd.Parameters.Add("@partdesc", OdbcType.Char, 5)
            prm.Value = "def2"


            prm = cmd.Parameters.Add("@partdesc", OdbcType.SmallInt)
            prm.Value = 4


            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub


End Class