How to insert values into IBM DB2 from VB.NET

See more:

.NET

Visual-Studio

string

ODBC

DB2

, +

I'm trying to take the information the user types into these textboxes/drop down lists and stored them in my DB2 database. I believe we are having trouble with the CALL procedure and it keeps erroring at at

Line 109:        'Execute the procedure.<br />
Line 110:        ODC.ExecuteNonQuery()<br />
Line 111:        'Close the connection<br />
Line 112:        db2conn.Close()

ERROR [22005] [IBM][CLI Driver] CLI0112E Error in assignment. SQLSTATE=22005

Our connection strings and stored procedure are:

Protected Sub Button6_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button6.Click
        Dim db2conn As New OdbcConnection("Driver={IBM DB2 ODBC DRIVER - DB2COPY1};Database=xxxx;Hostname=xxxxxxx;Port=50000; Protocol=TCPIP;Uid=xxxxx;Pwd=xxxxx;")
        Dim ODC As OdbcCommand
        'open connection
        db2conn.Open()
        ODC = New OdbcCommand
        'Retrieve stored procedure
        ODC.Connection = db2conn
        ODC.CommandType = SqlDataSourceCommandType.StoredProcedure
        'Input and Ouput is denoted by parameters
        ODC.CommandText = ("{CALL CUSTOMER_INFO('p_paytype','p_cardnum','p_cardtype','p_security','p_exp','p_bfirst','p_blast','p_baddress','p_bcity','p_bstate','p_bzip','p_dfirst','p_dlast','p_daddress','p_dcity','p_dstate','p_dzip')}")
        'This sends the billing info to the server
        ODC.Parameters.Add(New OdbcParameter("@p_paytype", OdbcType.VarChar, 20))
        ODC.Parameters("@p_paytype").Direction = Data.ParameterDirection.Input
        ODC.Parameters("@p_paytype").Value = paytype.SelectedValue

        ODC.Parameters.Add(New OdbcParameter("@p_cardnum", OdbcType.Decimal, 16))
        ODC.Parameters("@p_cardnum").Direction = Data.ParameterDirection.Input
        ODC.Parameters("@p_cardnum").Value = cardtext.Text

        ODC.Parameters.Add(New OdbcParameter("@p_cardtype", OdbcType.VarChar, 20))
        ODC.Parameters("@p_cardtype").Direction = Data.ParameterDirection.Input
        ODC.Parameters("@p_cardtype").Value = cardtype.SelectedValue

        ODC.Parameters.Add(New OdbcParameter("@p_security", OdbcType.Decimal, 3))
        ODC.Parameters("@p_security").Direction = Data.ParameterDirection.Input
        ODC.Parameters("@p_security").Value = securitytext.Text

        ODC.Parameters.Add(New OdbcParameter("@p_exp", OdbcType.Date))
        ODC.Parameters("@p_exp").Direction = Data.ParameterDirection.Input
        ODC.Parameters("@p_exp").Value = datetext.Text

        ODC.Parameters.Add(New OdbcParameter("@p_bfirst", OdbcType.VarChar, 20))
        ODC.Parameters("@p_bfirst").Direction = Data.ParameterDirection.Input
        ODC.Parameters("@p_bfirst").Value = bfirsttext.Text

        ODC.Parameters.Add(New OdbcParameter("@p_blast", OdbcType.VarChar, 20))
        ODC.Parameters("@p_blast").Direction = Data.ParameterDirection.Input
        ODC.Parameters("@p_blast").Value = blasttext.Text

        ODC.Parameters.Add(New OdbcParameter("@p_baddress", OdbcType.VarChar, 20))
        ODC.Parameters("@p_baddress").Direction = Data.ParameterDirection.Input
        ODC.Parameters("@p_baddress").Value = billingtext.Text

        ODC.Parameters.Add(New OdbcParameter("@p_bcity", OdbcType.VarChar, 40))
        ODC.Parameters("@p_bcity").Direction = Data.ParameterDirection.Input
        ODC.Parameters("@p_bcity").Value = bcitytext.Text

        ODC.Parameters.Add(New OdbcParameter("@p_bstate", OdbcType.VarChar, 20))
        ODC.Parameters("@p_bstate").Direction = Data.ParameterDirection.Input
        ODC.Parameters("@p_bstate").Value = bstatetext.Text

        ODC.Parameters.Add(New OdbcParameter("@p_bzip", OdbcType.VarChar, 20))
        ODC.Parameters("@p_bzip").Direction = Data.ParameterDirection.Input
        ODC.Parameters("@p_bzip").Value = bziptext.Text

        ODC.Parameters.Add(New OdbcParameter("@p_dfirst", OdbcType.VarChar, 20))
        ODC.Parameters("@p_dfirst").Direction = Data.ParameterDirection.Input
        ODC.Parameters("@p_dfirst").Value = dfirst.Text

        ODC.Parameters.Add(New OdbcParameter("@p_dlast", OdbcType.VarChar, 20))
        ODC.Parameters("@p_dlast").Direction = Data.ParameterDirection.Input
        ODC.Parameters("@p_dlast").Value = dlast.Text

        ODC.Parameters.Add(New OdbcParameter("@p_daddress", OdbcType.VarChar, 20))
        ODC.Parameters("@p_daddress").Direction = Data.ParameterDirection.Input
        ODC.Parameters("@p_daddress").Value = deliverytext.Text

        ODC.Parameters.Add(New OdbcParameter("@p_dcity", OdbcType.VarChar, 20))
        ODC.Parameters("@p_dcity").Direction = Data.ParameterDirection.Input
        ODC.Parameters("@p_dcity").Value = dcitytext.Text

        ODC.Parameters.Add(New OdbcParameter("@p_dstate", OdbcType.VarChar, 20))
        ODC.Parameters("@p_dstate").Direction = Data.ParameterDirection.Input
        ODC.Parameters("@p_dstate").Value = dstatetext.Text

        ODC.Parameters.Add(New OdbcParameter("@p_dzip", OdbcType.VarChar, 20))
        ODC.Parameters("@p_dzip").Direction = Data.ParameterDirection.Input
        ODC.Parameters("@p_dzip").Value = dziptext.Text
        'Returns a value for user authentication
        '1 = Authenticated
        '0 = Not authenticated

        'Value is then put into the v_auth variable
        'ODC.Parameters.Add(New OdbcParameter("@p_auth", OdbcType.Char, 1))
        'ODC.Parameters("@p_auth").Direction = Data.ParameterDirection.Output
        'Execute the procedure.
        ODC.ExecuteNonQuery()
        'Close the connection
        db2conn.Close()
        'Validation, Value = 1 then valid user Value = 0 bad user.
        'If ODC.Parameters("@p_auth").ToString = "1" Then
        Response.Redirect("Confirm_Order.aspx")
    End Sub

The stored procedure I'm using really doesn't alter any of the data, but I got my login to work with the stored procedure so, I thought i'd give this way a shot too:

CREATE PROCEDURE CUSTOMER_INFO (IN  p_paytype   VARCHAR(20),
                IN  p_cardnum   DECIMAL(16),
                IN  p_cardtype  VARCHAR(20),
                IN  p_security  DECIMAL(3),
                IN  p_exp       DATE,
                IN  p_bfirst    VARCHAR(20),
                IN  p_blast     VARCHAR(20),
                IN  p_baddress  VARCHAR(40),
                IN  p_bcity     VARCHAR(20),
                IN  p_bstate    VARCHAR(20),
                IN  p_bzip      VARCHAR(20),
                IN  p_dfirst    VARCHAR(20),
                IN  p_dlast     VARCHAR(20),
                IN  p_daddress  VARCHAR(20),
                IN  p_dcity     VARCHAR(20),
                IN  p_dstate    VARCHAR(20),
                IN  p_dzip      VARCHAR(20))

Language SQL

------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN

    INSERT INTO BILLING (PAYMENT_ID, PAYMENT_TYPE, CARD_NUMBER, CARD_TYPE, SECURITY_CODE, CARD_EXPIRATION, BILLING.FIRST_NAME, BILLING.LAST_NAME, BILLING_ADDRESS, BILLING_CITY, BILLING_STATE, BILLING_ZIP)
     VALUES (DEFAULT, p_paytype, p_cardnum, p_cardtype, p_security, p_exp, p_bfirst, p_blast, p_baddress, p_bcity, p_bstate, p_bzip);
    INSERT INTO DELIVERY (DELIVERY_ID, DELIVERY.FIRST_NAME, DELIVERY.LAST_NAME, DELIVERY_ADDRESS, DELIVERY_CITY, DELIVERY_STATE, DELIVERY_ZIP)
    VALUES (DEFAULT, p_dfirst, p_dlast, p_daddress, p_dcity, p_dstate, p_dzip);


END P1

I'm am fairly new to VB.NET, any help or advice would be appreciated.