Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: .NET Visual-Studio 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.
Line 110: ODC.ExecuteNonQuery()
Line 111: 'Close the connection
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.
Posted 26-Apr-12 14:28pm

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Your baddress and bcity sizes don't match between the parms and the procedure. You have 40-20 and 20-40.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 428
1 Maciej Los 249
2 BillWoodruff 199
3 /\jmot 180
4 Suraj Sahoo | Coding Passion 150
0 OriginalGriff 8,484
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,639
3 Maciej Los 5,159
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web02 | 2.8.1411023.1 | Last Updated 27 Apr 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100