Unable to Run an Update Query Against DB2

[cruncher06]

Here is an update query that I am trying to run:

comm = New Data.OleDb.OleDbCommand("UPDATE TESTGEN.ITMGT.ACCESS_LOG SET REQUEST_TYPE=:newRequestType, EMPLOYEE_NAME=:newEmployeeName, PERM_TEMP=:newPermTemp, COMPUTER_NAME=:newComputerName, SIMILIAR_TO=:newSimilarTo, UPDATE_REASON =:newUpdateReason, PHONE_NUM=:newPhoneNumber, CONTACT_PERSON=:newContactPerson, CONTACT_NUM=:newContactNumber WHERE (RECORD_ID=" & requestId & ")", conn)


        'http://forums.asp.net/t/1821702.aspx - currently not using .Text for each line; left this line in for possible code if needed

        comm.Parameters.Add("RECORD_ID", OleDb.OleDbType.SmallInt)
        comm.Parameters("RECORD_ID").Value = requestId

        comm.Parameters.Add(":REQUEST_TYPE", OleDb.OleDbType.Char)
        comm.Parameters(":REQUEST_TYPE").Value = newRequestType

        comm.Parameters.Add(":EMPLOYEE_NAME", OleDb.OleDbType.VarChar)
        comm.Parameters(":EMPLOYEE_NAME").Value = newEmployeeName

        comm.Parameters.Add(":PERM_TEMP", OleDb.OleDbType.Char)
        comm.Parameters(":PERM_TEMP").Value = newPermTemp

        comm.Parameters.Add(":COMPUTER_NAME", OleDb.OleDbType.VarChar)
        comm.Parameters(":COMPUTER_NAME").Value = newComputerName

        comm.Parameters.Add(":SIMILIAR_TO", OleDb.OleDbType.VarChar)
        comm.Parameters(":SIMILIAR_TO").Value = newSimilarTo

        comm.Parameters.Add(":UPDATE_REASON", OleDb.OleDbType.VarChar)
        comm.Parameters(":UPDATE_REASON").Value = newUpdateReason

        comm.Parameters.Add(":PHONE_NUM", OleDb.OleDbType.VarChar)
        comm.Parameters(":PHONE_NUM").Value = newPhoneNumber

        comm.Parameters.Add(":CONTACT_PERSON", OleDb.OleDbType.VarChar)
        comm.Parameters(":CONTACT_PERSON").Value = newContactPerson

        comm.Parameters.Add(":CONTACT_NUM", OleDb.OleDbType.VarChar)
        comm.Parameters(":CONTACT_NUM").Value = newContactNumber

When I run it, I receive this error:
Event Type: Information
Event Source: My Application
Event Category: None
Event ID: 0
Date: 10/8/2013
Time: 8:36:49 AM
User: N/A
Computer: CVCMS116
Description:
Index #0
Message: [DB2/NT64] SQL0313N The number of variables in the EXECUTE statement, the number of variables in the OPEN statement, or the number of arguments in an OPEN statement for a parameterized cursor is not equal to the number of values required. SQLSTATE=07004

NativeError: -313
Source: IBM OLE DB Provider for DB2
SQLState: 07001

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Any ideas on what I am doing wrong?

[Rabbit]

You added 10 parameters but your query only has 9. Also, your parameter names are different in your query.

[cruncher06]

I have updated the code as follows. Do you know how I should write it. I have added the control that I am trying to update after this?

Dim requestId As Integer = requestDetails.DataKey.Value


        Dim newRequestTypeTextBox As TextBox = requestDetails.FindControl("editRequestTypeTextBox")
        Dim newEmployeeNameTextBox As TextBox = requestDetails.FindControl("editEmployeeNameTextBox")
        Dim newPermTempTextBox As TextBox = requestDetails.FindControl("editPermTempTextBox")
        Dim newComputerNameTextBox As TextBox = requestDetails.FindControl("editComputerNameTextBox")
        Dim newSimilarToTextBox As TextBox = requestDetails.FindControl("editSimilarToTextBox")
        Dim newUpdateReasonTextBox As TextBox = requestDetails.FindControl("editUpdateReasonTextBox")
        Dim newPhoneNumberTextBox As TextBox = requestDetails.FindControl("editPhoneNumberTextBox")
        Dim newContactPersonTextBox As TextBox = requestDetails.FindControl("editContactPersonTextBox")
        Dim newContactNumberBox As TextBox = requestDetails.FindControl("editContactNumberTextBox")

        Dim newRequestType As String = newRequestTypeTextBox.Text
        Dim newEmployeeName As String = newEmployeeNameTextBox.Text
        Dim newPermTemp As String = newPermTempTextBox.Text
        Dim newComputerName As String = newComputerNameTextBox.Text
        Dim newSimilarTo As String = newSimilarToTextBox.Text
        Dim newUpdateReason As String = newUpdateReasonTextBox.Text
        Dim newPhoneNumber As String = newPhoneNumberTextBox.Text
        Dim newContactPerson As String = newContactPersonTextBox.Text
        Dim newContactNumber As String = newContactNumberBox.Text



        Dim conn = New Data.OleDb.OleDbConnection
        Dim comm As Data.OleDb.OleDbCommand

        conn = New Data.OleDb.OleDbConnection(Session("connectionString"))
       
        comm = New Data.OleDb.OleDbCommand("UPDATE TESTGEN.ITMGT.ACCESS_LOG SET REQUEST_TYPE=:NewRequestType, EMPLOYEE_NAME=:NewEmployeeName, PERM_TEMP=:NewPermTemp, COMPUTER_NAME=:NewComputerName, SIMILIAR_TO=:NewSimilarTo, UPDATE_REASON =:NewUpdateReason, PHONE_NUM=:NewPhoneNumber, CONTACT_PERSON=:NewContactPerson, CONTACT_NUM=:NewContactNumber WHERE (RECORD_ID=:RECORD_ID)", conn)


        'http://forums.asp.net/t/1821702.aspx - currently not using .Text for each line; left this line in for possible code if needed

        comm.Parameters.Add(":RECORD_ID", OleDb.OleDbType.SmallInt)
        comm.Parameters(":RECORD_ID").Value = requestId

        comm.Parameters.Add(":NewRequestType", OleDb.OleDbType.Char)
        comm.Parameters(":NewRequestType").Value = newRequestType

        comm.Parameters.Add(":NewEmployeeName", OleDb.OleDbType.VarChar)
        comm.Parameters(":NewEmployeeName").Value = newEmployeeName

        comm.Parameters.Add(":NewPermTemp", OleDb.OleDbType.Char)
        comm.Parameters(":NewPermTemp").Value = newPermTemp

        comm.Parameters.Add(":NewComputerName", OleDb.OleDbType.VarChar)
        comm.Parameters(":NewComputerName").Value = newComputerName

        comm.Parameters.Add(":NewSimilarTo", OleDb.OleDbType.VarChar)
        comm.Parameters(":NewSimilarTo").Value = newSimilarTo

        comm.Parameters.Add(":NewUpdateReason", OleDb.OleDbType.VarChar)
        comm.Parameters(":NewUpdateReason").Value = newUpdateReason

        comm.Parameters.Add(":NewPhoneNumber", OleDb.OleDbType.VarChar)
        comm.Parameters(":NewPhoneNumber").Value = newPhoneNumber

        comm.Parameters.Add(":NewContactPerson", OleDb.OleDbType.VarChar)
        comm.Parameters(":NewContactPerson").Value = newContactPerson

        comm.Parameters.Add(":NewContactNumber", OleDb.OleDbType.VarChar)
        comm.Parameters(":NewContactNumber").Value = newContactNumber


        Try
            conn.Open()
            comm.ExecuteNonQuery()

        Catch er As OleDbException
            Dim errorMessages As String = ""
            Dim i As Integer

            For i = 0 To er.Errors.Count - 1
                errorMessages += "Index #" & i.ToString() & ControlChars.Cr _
                    & "Message: " & er.Errors(i).Message & ControlChars.Cr _
                    & "NativeError: " & er.Errors(i).NativeError & ControlChars.Cr _
                    & "Source: " & er.Errors(i).Source & ControlChars.Cr _
                    & "SQLState: " & er.Errors(i).SQLState & ControlChars.Cr _
                    & "Error Code: " & er.ErrorCode & ControlChars.Cr _
                    & "Type: " & er.Errors(i).GetType.ToString




            Next i

            Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog()
            log.Source = "My Application"
            log.WriteEntry(errorMessages)
            Console.WriteLine("An exception occurred. Please contact your system administrator.")

        Finally
            conn.Close()
        End Try

        requestDetails.ChangeMode(DetailsViewMode.ReadOnly)
        BindGrid()
        BindDetails()

.aspx code:

      <asp:DetailsView ID="requestDetails" runat="server" AutoGenerateRows="False">
                        <Fields>
                            <asp:TemplateField HeaderText="Request Type">
                                <EditItemTemplate>
                                    <asp:TextBox ID="editRequestTypeTextBox" runat="server" Text='<%# Bind("REQUEST_TYPE") %>'></asp:TextBox>
                                </EditItemTemplate>
                                <InsertItemTemplate>
                                    <asp:TextBox ID="insertRequestTypeTextBox" runat="server" Text='<%# Bind("REQUEST_TYPE") %>'></asp:TextBox>
                                </InsertItemTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="requestTypeLabel" runat="server" Text='<%# Bind("REQUEST_TYPE") %>'></asp:Label>
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField HeaderText="Employee Name">
                                <EditItemTemplate>
                                    <asp:TextBox ID="editEmployeeNameTextBox" runat="server" Text='<%# Bind("EMPLOYEE_NAME") %>'></asp:TextBox>
                                </EditItemTemplate>
                                <InsertItemTemplate>
                                    <asp:TextBox ID="insertEmployeeNameTextBox" runat="server" Text='<%# Bind("EMPLOYEE_NAME") %>'></asp:TextBox>
                                </InsertItemTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="employeeNameLabel" runat="server" Text='<%# Bind("EMPLOYEE_NAME") %>'></asp:Label>
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField HeaderText="Perm/Temp">
                                <EditItemTemplate>
                                    <asp:TextBox ID="editPermTempTextBox" runat="server" Text='<%# Bind("PERM_TEMP") %>'></asp:TextBox>
                                </EditItemTemplate>
                                <InsertItemTemplate>
                                    <asp:TextBox ID="insertPermTempTextBox" runat="server" Text='<%# Bind("PERM_TEMP") %>'></asp:TextBox>
                                </InsertItemTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="permTempLabel" runat="server" Text='<%# Bind("PERM_TEMP") %>'></asp:Label>
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField HeaderText="Computer Name">
                                <EditItemTemplate>
                                    <asp:TextBox ID="editComputerNameTextBox" runat="server" Text='<%# Bind("COMPUTER_NAME") %>'></asp:TextBox>
                                </EditItemTemplate>
                                <InsertItemTemplate>
                                    <asp:TextBox ID="insertComputerNameTextBox" runat="server" Text='<%# Bind("COMPUTER_NAME") %>'></asp:TextBox>
                                </InsertItemTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="computerNameLabel" runat="server" Text='<%# Bind("COMPUTER_NAME") %>'></asp:Label>
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField HeaderText="Similar To">
                                <EditItemTemplate>
                                    <asp:TextBox ID="editSimilarToTextBox" runat="server" Text='<%# Bind("SIMILIAR_TO") %>'></asp:TextBox>
                                </EditItemTemplate>
                                <InsertItemTemplate>
                                    <asp:TextBox ID="insertSimilarToTextBox" runat="server" Text='<%# Bind("SIMILIAR_TO") %>'></asp:TextBox>
                                </InsertItemTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="similarToLabel" runat="server" Text='<%# Bind("SIMILIAR_TO") %>'></asp:Label>
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField HeaderText="Update Reason">
                                <EditItemTemplate>
                                    <asp:TextBox ID="editUpdateReasonTextBox" runat="server" Text='<%# Bind("UPDATE_REASON") %>'></asp:TextBox>
                                </EditItemTemplate>
                                <InsertItemTemplate>
                                    <asp:TextBox ID="insertUpdateReasonTextBox" runat="server" Text='<%# Bind("UPDATE_REASON") %>'></asp:TextBox>
                                </InsertItemTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="updateReasonLabel" runat="server" Text='<%# Bind("UPDATE_REASON") %>'></asp:Label>
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField HeaderText="Phone Number">
                                <EditItemTemplate>
                                    <asp:TextBox ID="editPhoneNumberTextBox" runat="server" Text='<%# Bind("PHONE_NUM") %>'></asp:TextBox>
                                </EditItemTemplate>
                                <InsertItemTemplate>
                                    <asp:TextBox ID="insertPhoneNumberTextBox" runat="server" Text='<%# Bind("PHONE_NUM") %>'></asp:TextBox>
                                </InsertItemTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="labelPhoneNumber" runat="server" Text='<%# Bind("PHONE_NUM") %>'></asp:Label>
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField HeaderText="Contact Person">
                                <EditItemTemplate>
                                    <asp:TextBox ID="editContactPersonTextBox" runat="server" Text='<%# Bind("CONTACT_PERSON") %>'></asp:TextBox>
                                </EditItemTemplate>
                                <InsertItemTemplate>
                                    <asp:TextBox ID="insertContactPersonTextBox" runat="server" Text='<%# Bind("CONTACT_PERSON") %>'></asp:TextBox>
                                </InsertItemTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="contactPersonLabel" runat="server" Text='<%# Bind("CONTACT_PERSON") %>'></asp:Label>
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField HeaderText="Contact Number">
                                <EditItemTemplate>
                                    <asp:TextBox ID="editContactNumberTextBox" runat="server" Text='<%# Bind("CONTACT_NUM") %>'></asp:TextBox>
                                </EditItemTemplate>
                                <InsertItemTemplate>
                                    <asp:TextBox ID="insertContactNumberTextBox" runat="server" Text='<%# Bind("CONTACT_NUM") %>'></asp:TextBox>
                                </InsertItemTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="contactNumberLabel" runat="server" Text='<%# Bind("CONTACT_NUM") %>'></asp:Label>
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:CommandField ShowEditButton="true" />
                        </Fields>
                        <HeaderTemplate>
                            <%#Eval("RECORD_ID")%>
                        </HeaderTemplate>
                    </asp:DetailsView>

[Rabbit]

And are you saying that the updated code is not working? You have to tell us what the error code and text is.