Topic
  • 1 reply
  • Latest Post - ‏2013-10-23T05:40:45Z by Prashant Kulkarni
cruncher06
cruncher06
1 Post

Pinned topic UPDATE Query not Working in .NET

‏2013-10-10T16:16:53Z |

I am trying to update records in a DB2 database using a DetailsView control in edit mode from an ASP.NET application and I am receiving an error. Below is the error description, the code for the control from the .aspx page and the code that I am using to update the data. Any idea on how this code should be written?

 

Error Information:

Event Type: Information
Event Source: My Application
Event Category: None
Event ID: 0
Date: 10/10/2013
Time: 9:06:44 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
Error Code: -2147467259
Type: System.Data.OleDb.OleDbError
 
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
 

 

Here is the DetailsView control:

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

 

and here is the code that I have written to update the records:

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()
  • Prashant Kulkarni
    Prashant Kulkarni
    7 Posts

    Re: UPDATE Query not Working in .NET

    ‏2013-10-23T05:40:45Z  

    Hi

    the sequence  of parameters to be added by comm.Parameters.Add(..) command  should match the 
    sequence of parameters in Update SQL query as there is 1:1 co-relationship 
     
      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)
     
      so RECORD_ID in omm.Parameters.Add should be last one instead of first  as it seems it is mapping to :NewRequestType in update query 
          comm.Parameters.Add(":RECORD_ID", OleDb.OleDbType.SmallInt)
            comm.Parameters(":RECORD_ID").Value = requestId
    .....
    .....
           comm.Parameters.Add(":NewContactNumber", OleDb.OleDbType.VarChar)
            comm.Parameters(":NewContactNumber").Value = newContactNumber
     
    so you can move the  comm.Parameters.Add(":RECORD_ID..) at end  i.e after comm.Parameters.Add(":NewContactNumber",) and execute it
     
    2)  I dont; see RECORD_ID is being declared like rest of the parameters ..you can check if the declaration is needed similar to 
     
    Dim newRequestTypeTextBox As TextBox = requestDetails.FindControl("editRequestTypeTextBox")
    Dim newEmployeeNameTextBox As TextBox = requestDetails.FindControl("editEmployeeNameTextBox")
     
    3) if still giving problem i have suspect that RECORD_ID might be causing problem . to verify it ,hardcode your update SQL query for testing and replace
    WHERE (RECORD_ID=:RECORD_ID) with  some number  used in where clause which exist in table for eg WHERE (RECORD_ID=10 )  and also remove the add parameter  code.  i.e remove this
     
    comm.Parameters.Add(":RECORD_ID", OleDb.OleDbType.SmallInt)
            comm.Parameters(":RECORD_ID").Value = requestId
     
    so  update sql query will have only 9 parameters and parameters to be added via 
    comm.Parameters.Add(..A) will also have 9 parameters and check the execution 
     
    if it works then the problem could be in the way the :RECORD_ID in where clause is supplied.
     
    4) it following steps 3 also problem persist ...you can follow the scaling down approach ..i.e  create a some table with say only 1 or 2 columns
    and give that many parameters in   add and update query and see it works..if  you make it work for table having just 2 columns and say 2 parameters then you can make it work for 10 parameters with 10 column ...

    regards

    Prashant

     
    Updated on 2013-10-23T05:41:34Z at 2013-10-23T05:41:34Z by Prashant Kulkarni