如何从VB.NET将值插入IBM DB2 [英] How to insert values into IBM DB2 from VB.NET

问题描述

我正在尝试将用户键入的信息带入这些文本框/下拉列表，并将其存储在我的DB2数据库中.我相信我们在CALL程序上遇到了麻烦，并且在
上总是出错

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()

错误[22005] [IBM] [CLI驱动程序] CLI0112E分配错误. SQLSTATE = 22005

我们的连接字符串和存储过程为:

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

我是VB.NET的新手，任何帮助或建议都将不胜感激.

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

推荐答案

您的地址和bcity大小在参数之间不匹配和程序.您有40-20和20-40.

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

这篇关于如何从VB.NET将值插入IBM DB2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！