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
db2conn.Open()
ODC = New OdbcCommand
ODC.Connection = db2conn
ODC.CommandType = SqlDataSourceCommandType.StoredProcedure
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')}")
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
ODC.ExecuteNonQuery()
db2conn.Close()
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
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.