473,386 Members | 1,699 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

home > topics > asp.net > questions > unable to run an update query against db2

Join Bytes to post your question to a community of 473,386 software developers and data experts.

Unable to Run an Update Query Against DB2

Here is an update query that I am trying to run:
Expand|Select|Wrap|Line Numbers
  1. 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)
  2.  
  3.  
  4.         'http://forums.asp.net/t/1821702.aspx - currently not using .Text for each line; left this line in for possible code if needed
  5.  
  6.         comm.Parameters.Add("RECORD_ID", OleDb.OleDbType.SmallInt)
  7.         comm.Parameters("RECORD_ID").Value = requestId
  8.  
  9.         comm.Parameters.Add(":REQUEST_TYPE", OleDb.OleDbType.Char)
  10.         comm.Parameters(":REQUEST_TYPE").Value = newRequestType
  11.  
  12.         comm.Parameters.Add(":EMPLOYEE_NAME", OleDb.OleDbType.VarChar)
  13.         comm.Parameters(":EMPLOYEE_NAME").Value = newEmployeeName
  14.  
  15.         comm.Parameters.Add(":PERM_TEMP", OleDb.OleDbType.Char)
  16.         comm.Parameters(":PERM_TEMP").Value = newPermTemp
  17.  
  18.         comm.Parameters.Add(":COMPUTER_NAME", OleDb.OleDbType.VarChar)
  19.         comm.Parameters(":COMPUTER_NAME").Value = newComputerName
  20.  
  21.         comm.Parameters.Add(":SIMILIAR_TO", OleDb.OleDbType.VarChar)
  22.         comm.Parameters(":SIMILIAR_TO").Value = newSimilarTo
  23.  
  24.         comm.Parameters.Add(":UPDATE_REASON", OleDb.OleDbType.VarChar)
  25.         comm.Parameters(":UPDATE_REASON").Value = newUpdateReason
  26.  
  27.         comm.Parameters.Add(":PHONE_NUM", OleDb.OleDbType.VarChar)
  28.         comm.Parameters(":PHONE_NUM").Value = newPhoneNumber
  29.  
  30.         comm.Parameters.Add(":CONTACT_PERSON", OleDb.OleDbType.VarChar)
  31.         comm.Parameters(":CONTACT_PERSON").Value = newContactPerson
  32.  
  33.         comm.Parameters.Add(":CONTACT_NUM", OleDb.OleDbType.VarChar)
  34.         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?
Oct 8 '13 #1
3 1862
Rabbit
12,516 Expert Mod 8TB
You added 10 parameters but your query only has 9. Also, your parameter names are different in your query.
Oct 8 '13 #2
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?

Expand|Select|Wrap|Line Numbers
  1. Dim requestId As Integer = requestDetails.DataKey.Value
  2.  
  3.  
  4.         Dim newRequestTypeTextBox As TextBox = requestDetails.FindControl("editRequestTypeTextBox")
  5.         Dim newEmployeeNameTextBox As TextBox = requestDetails.FindControl("editEmployeeNameTextBox")
  6.         Dim newPermTempTextBox As TextBox = requestDetails.FindControl("editPermTempTextBox")
  7.         Dim newComputerNameTextBox As TextBox = requestDetails.FindControl("editComputerNameTextBox")
  8.         Dim newSimilarToTextBox As TextBox = requestDetails.FindControl("editSimilarToTextBox")
  9.         Dim newUpdateReasonTextBox As TextBox = requestDetails.FindControl("editUpdateReasonTextBox")
  10.         Dim newPhoneNumberTextBox As TextBox = requestDetails.FindControl("editPhoneNumberTextBox")
  11.         Dim newContactPersonTextBox As TextBox = requestDetails.FindControl("editContactPersonTextBox")
  12.         Dim newContactNumberBox As TextBox = requestDetails.FindControl("editContactNumberTextBox")
  13.  
  14.         Dim newRequestType As String = newRequestTypeTextBox.Text
  15.         Dim newEmployeeName As String = newEmployeeNameTextBox.Text
  16.         Dim newPermTemp As String = newPermTempTextBox.Text
  17.         Dim newComputerName As String = newComputerNameTextBox.Text
  18.         Dim newSimilarTo As String = newSimilarToTextBox.Text
  19.         Dim newUpdateReason As String = newUpdateReasonTextBox.Text
  20.         Dim newPhoneNumber As String = newPhoneNumberTextBox.Text
  21.         Dim newContactPerson As String = newContactPersonTextBox.Text
  22.         Dim newContactNumber As String = newContactNumberBox.Text
  23.  
  24.  
  25.  
  26.         Dim conn = New Data.OleDb.OleDbConnection
  27.         Dim comm As Data.OleDb.OleDbCommand
  28.  
  29.         conn = New Data.OleDb.OleDbConnection(Session("connectionString"))
  30.  
  31.         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)
  32.  
  33.  
  34.         'http://forums.asp.net/t/1821702.aspx - currently not using .Text for each line; left this line in for possible code if needed
  35.  
  36.         comm.Parameters.Add(":RECORD_ID", OleDb.OleDbType.SmallInt)
  37.         comm.Parameters(":RECORD_ID").Value = requestId
  38.  
  39.         comm.Parameters.Add(":NewRequestType", OleDb.OleDbType.Char)
  40.         comm.Parameters(":NewRequestType").Value = newRequestType
  41.  
  42.         comm.Parameters.Add(":NewEmployeeName", OleDb.OleDbType.VarChar)
  43.         comm.Parameters(":NewEmployeeName").Value = newEmployeeName
  44.  
  45.         comm.Parameters.Add(":NewPermTemp", OleDb.OleDbType.Char)
  46.         comm.Parameters(":NewPermTemp").Value = newPermTemp
  47.  
  48.         comm.Parameters.Add(":NewComputerName", OleDb.OleDbType.VarChar)
  49.         comm.Parameters(":NewComputerName").Value = newComputerName
  50.  
  51.         comm.Parameters.Add(":NewSimilarTo", OleDb.OleDbType.VarChar)
  52.         comm.Parameters(":NewSimilarTo").Value = newSimilarTo
  53.  
  54.         comm.Parameters.Add(":NewUpdateReason", OleDb.OleDbType.VarChar)
  55.         comm.Parameters(":NewUpdateReason").Value = newUpdateReason
  56.  
  57.         comm.Parameters.Add(":NewPhoneNumber", OleDb.OleDbType.VarChar)
  58.         comm.Parameters(":NewPhoneNumber").Value = newPhoneNumber
  59.  
  60.         comm.Parameters.Add(":NewContactPerson", OleDb.OleDbType.VarChar)
  61.         comm.Parameters(":NewContactPerson").Value = newContactPerson
  62.  
  63.         comm.Parameters.Add(":NewContactNumber", OleDb.OleDbType.VarChar)
  64.         comm.Parameters(":NewContactNumber").Value = newContactNumber
  65.  
  66.  
  67.         Try
  68.             conn.Open()
  69.             comm.ExecuteNonQuery()
  70.  
  71.         Catch er As OleDbException
  72.             Dim errorMessages As String = ""
  73.             Dim i As Integer
  74.  
  75.             For i = 0 To er.Errors.Count - 1
  76.                 errorMessages += "Index #" & i.ToString() & ControlChars.Cr _
  77.                     & "Message: " & er.Errors(i).Message & ControlChars.Cr _
  78.                     & "NativeError: " & er.Errors(i).NativeError & ControlChars.Cr _
  79.                     & "Source: " & er.Errors(i).Source & ControlChars.Cr _
  80.                     & "SQLState: " & er.Errors(i).SQLState & ControlChars.Cr _
  81.                     & "Error Code: " & er.ErrorCode & ControlChars.Cr _
  82.                     & "Type: " & er.Errors(i).GetType.ToString
  83.  
  84.  
  85.  
  86.  
  87.             Next i
  88.  
  89.             Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog()
  90.             log.Source = "My Application"
  91.             log.WriteEntry(errorMessages)
  92.             Console.WriteLine("An exception occurred. Please contact your system administrator.")
  93.  
  94.         Finally
  95.             conn.Close()
  96.         End Try
  97.  
  98.         requestDetails.ChangeMode(DetailsViewMode.ReadOnly)
  99.         BindGrid()
  100.         BindDetails()
  101.  
.aspx code:
Expand|Select|Wrap|Line Numbers
  1.       <asp:DetailsView ID="requestDetails" runat="server" AutoGenerateRows="False">
  2.                         <Fields>
  3.                             <asp:TemplateField HeaderText="Request Type">
  4.                                 <EditItemTemplate>
  5.                                     <asp:TextBox ID="editRequestTypeTextBox" runat="server" Text='<%# Bind("REQUEST_TYPE") %>'></asp:TextBox>
  6.                                 </EditItemTemplate>
  7.                                 <InsertItemTemplate>
  8.                                     <asp:TextBox ID="insertRequestTypeTextBox" runat="server" Text='<%# Bind("REQUEST_TYPE") %>'></asp:TextBox>
  9.                                 </InsertItemTemplate>
  10.                                 <ItemTemplate>
  11.                                     <asp:Label ID="requestTypeLabel" runat="server" Text='<%# Bind("REQUEST_TYPE") %>'></asp:Label>
  12.                                 </ItemTemplate>
  13.                             </asp:TemplateField>
  14.                             <asp:TemplateField HeaderText="Employee Name">
  15.                                 <EditItemTemplate>
  16.                                     <asp:TextBox ID="editEmployeeNameTextBox" runat="server" Text='<%# Bind("EMPLOYEE_NAME") %>'></asp:TextBox>
  17.                                 </EditItemTemplate>
  18.                                 <InsertItemTemplate>
  19.                                     <asp:TextBox ID="insertEmployeeNameTextBox" runat="server" Text='<%# Bind("EMPLOYEE_NAME") %>'></asp:TextBox>
  20.                                 </InsertItemTemplate>
  21.                                 <ItemTemplate>
  22.                                     <asp:Label ID="employeeNameLabel" runat="server" Text='<%# Bind("EMPLOYEE_NAME") %>'></asp:Label>
  23.                                 </ItemTemplate>
  24.                             </asp:TemplateField>
  25.                             <asp:TemplateField HeaderText="Perm/Temp">
  26.                                 <EditItemTemplate>
  27.                                     <asp:TextBox ID="editPermTempTextBox" runat="server" Text='<%# Bind("PERM_TEMP") %>'></asp:TextBox>
  28.                                 </EditItemTemplate>
  29.                                 <InsertItemTemplate>
  30.                                     <asp:TextBox ID="insertPermTempTextBox" runat="server" Text='<%# Bind("PERM_TEMP") %>'></asp:TextBox>
  31.                                 </InsertItemTemplate>
  32.                                 <ItemTemplate>
  33.                                     <asp:Label ID="permTempLabel" runat="server" Text='<%# Bind("PERM_TEMP") %>'></asp:Label>
  34.                                 </ItemTemplate>
  35.                             </asp:TemplateField>
  36.                             <asp:TemplateField HeaderText="Computer Name">
  37.                                 <EditItemTemplate>
  38.                                     <asp:TextBox ID="editComputerNameTextBox" runat="server" Text='<%# Bind("COMPUTER_NAME") %>'></asp:TextBox>
  39.                                 </EditItemTemplate>
  40.                                 <InsertItemTemplate>
  41.                                     <asp:TextBox ID="insertComputerNameTextBox" runat="server" Text='<%# Bind("COMPUTER_NAME") %>'></asp:TextBox>
  42.                                 </InsertItemTemplate>
  43.                                 <ItemTemplate>
  44.                                     <asp:Label ID="computerNameLabel" runat="server" Text='<%# Bind("COMPUTER_NAME") %>'></asp:Label>
  45.                                 </ItemTemplate>
  46.                             </asp:TemplateField>
  47.                             <asp:TemplateField HeaderText="Similar To">
  48.                                 <EditItemTemplate>
  49.                                     <asp:TextBox ID="editSimilarToTextBox" runat="server" Text='<%# Bind("SIMILIAR_TO") %>'></asp:TextBox>
  50.                                 </EditItemTemplate>
  51.                                 <InsertItemTemplate>
  52.                                     <asp:TextBox ID="insertSimilarToTextBox" runat="server" Text='<%# Bind("SIMILIAR_TO") %>'></asp:TextBox>
  53.                                 </InsertItemTemplate>
  54.                                 <ItemTemplate>
  55.                                     <asp:Label ID="similarToLabel" runat="server" Text='<%# Bind("SIMILIAR_TO") %>'></asp:Label>
  56.                                 </ItemTemplate>
  57.                             </asp:TemplateField>
  58.                             <asp:TemplateField HeaderText="Update Reason">
  59.                                 <EditItemTemplate>
  60.                                     <asp:TextBox ID="editUpdateReasonTextBox" runat="server" Text='<%# Bind("UPDATE_REASON") %>'></asp:TextBox>
  61.                                 </EditItemTemplate>
  62.                                 <InsertItemTemplate>
  63.                                     <asp:TextBox ID="insertUpdateReasonTextBox" runat="server" Text='<%# Bind("UPDATE_REASON") %>'></asp:TextBox>
  64.                                 </InsertItemTemplate>
  65.                                 <ItemTemplate>
  66.                                     <asp:Label ID="updateReasonLabel" runat="server" Text='<%# Bind("UPDATE_REASON") %>'></asp:Label>
  67.                                 </ItemTemplate>
  68.                             </asp:TemplateField>
  69.                             <asp:TemplateField HeaderText="Phone Number">
  70.                                 <EditItemTemplate>
  71.                                     <asp:TextBox ID="editPhoneNumberTextBox" runat="server" Text='<%# Bind("PHONE_NUM") %>'></asp:TextBox>
  72.                                 </EditItemTemplate>
  73.                                 <InsertItemTemplate>
  74.                                     <asp:TextBox ID="insertPhoneNumberTextBox" runat="server" Text='<%# Bind("PHONE_NUM") %>'></asp:TextBox>
  75.                                 </InsertItemTemplate>
  76.                                 <ItemTemplate>
  77.                                     <asp:Label ID="labelPhoneNumber" runat="server" Text='<%# Bind("PHONE_NUM") %>'></asp:Label>
  78.                                 </ItemTemplate>
  79.                             </asp:TemplateField>
  80.                             <asp:TemplateField HeaderText="Contact Person">
  81.                                 <EditItemTemplate>
  82.                                     <asp:TextBox ID="editContactPersonTextBox" runat="server" Text='<%# Bind("CONTACT_PERSON") %>'></asp:TextBox>
  83.                                 </EditItemTemplate>
  84.                                 <InsertItemTemplate>
  85.                                     <asp:TextBox ID="insertContactPersonTextBox" runat="server" Text='<%# Bind("CONTACT_PERSON") %>'></asp:TextBox>
  86.                                 </InsertItemTemplate>
  87.                                 <ItemTemplate>
  88.                                     <asp:Label ID="contactPersonLabel" runat="server" Text='<%# Bind("CONTACT_PERSON") %>'></asp:Label>
  89.                                 </ItemTemplate>
  90.                             </asp:TemplateField>
  91.                             <asp:TemplateField HeaderText="Contact Number">
  92.                                 <EditItemTemplate>
  93.                                     <asp:TextBox ID="editContactNumberTextBox" runat="server" Text='<%# Bind("CONTACT_NUM") %>'></asp:TextBox>
  94.                                 </EditItemTemplate>
  95.                                 <InsertItemTemplate>
  96.                                     <asp:TextBox ID="insertContactNumberTextBox" runat="server" Text='<%# Bind("CONTACT_NUM") %>'></asp:TextBox>
  97.                                 </InsertItemTemplate>
  98.                                 <ItemTemplate>
  99.                                     <asp:Label ID="contactNumberLabel" runat="server" Text='<%# Bind("CONTACT_NUM") %>'></asp:Label>
  100.                                 </ItemTemplate>
  101.                             </asp:TemplateField>
  102.                             <asp:CommandField ShowEditButton="true" />
  103.                         </Fields>
  104.                         <HeaderTemplate>
  105.                             <%#Eval("RECORD_ID")%>
  106.                         </HeaderTemplate>
  107.                     </asp:DetailsView>
  108.  
Oct 10 '13 #3
Rabbit
12,516 Expert Mod 8TB
And are you saying that the updated code is not working? You have to tell us what the error code and text is.
Oct 10 '13 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

10
Update query help
by: Randy Harris | last post by:
I imported records into a table, later found out that many of them had trailing spaces in one of the fields. If I'd caught it sooner, I could have trimmed the spaces before the import. This...
Microsoft Access / VBA
4
UPDATE query syntax question
by: deko | last post by:
I'm trying to update the address record of an existing record in my mdb with values from another existing record in the same table. In pseudo code it might look like this: UPDATE tblAddress SET...
Microsoft Access / VBA
1
Update query not updating
by: John | last post by:
Hi We have this strange problem on an access 97 app over a network. The backend db is on a win2003 server and each pc has the front rend linking to the back end. The app runs an update query...
Microsoft Access / VBA
1
UPDATE query not happy with date fields
by: Riley DeWiley | last post by:
I have an UPDATE query that is always setting 0 records. When I cut and paste the SQL into Access and use it, it fails in the same way unless I coerce the date fields to be '=now()', in which case...
Microsoft Access / VBA
11
Update Query "Key Violations" error
by: gnortenjones | last post by:
I have a linked table (to an oracle db), and I am trying to run a simple update query against it to change some data, but I am getting the following error: "...didn't update 0 fields due to a type...
Microsoft Access / VBA
5
update query in ms-access doesn't working
by: abhilashcashok | last post by:
hi guys, my prblem is that I cannot update sql query against MS ACCESS using C#. Everytime wen i do so, i got an exception (at runtime) as 'UPDATE syntax not correct'. I don find any error in my...
C# / C Sharp
7
Running Insert/update SQL against ADO.NET dataset
by: Matt | last post by:
So as you all know the great thing about ADO.NET is that I can take an entire table from a database and dump it into an in memory datatable using ADO.NET. Well my question is that now that I...
Visual Basic .NET
2
zachster17
Update query not working in SQL Server
by: zachster17 | last post by:
Hello everyone, I'm having difficult getting an update query to work. What I'm doing is search a table with filenames against another table that has filename wildcards that search against the...
Microsoft SQL Server
5
Unable to run Update Query...Please Help
by: dougmeece | last post by:
Hello experts, I have an update query that I want to modify records meeting a certain criteria in a table based on information on a form. I am having trouble recognizing the table for a...
Microsoft Access / VBA
4
Select Case in Update Query
by: hapnendad | last post by:
In the question statement below Field names are in and variables are in (). All fields referenced are in what I have named the ‘PAR’ Table. Using MS Access 2003, I am working on a project...
Microsoft Access / VBA
0
Easy Steps to Fix "Canon Printer Won't Connect to WiFi Network"
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
General
0
How to turn on java script in a villaon keypad mobile phone
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
Java
0
Basic Javascript concepts
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
Javascript
0
Merging data from multiple Excel files
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
Data Management
0
BarryA
Navigating the Data Structures and Algorithms (DSA)
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
Algorithms / Advanced Math
1
Is that possible of reading the .csv file in column wise and the column have different lengths ?
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
C / C++
0
How to build RAID in BIOS?
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
Computer Hardware
0
Changing the language in Windows 10
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
Windows Server
0
Oralloy
Problem With Comparison Operator <=> in G++
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
C / C++

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.

BYTES.COM © 2024
About Bytes
Terms Of Use
Privacy Policy
Sitemap
Advertise on Bytes:
Post a Job
Sponsored Posts
Platinum & Gold Sponsors
Hire Now!