473,782 Members | 2,448 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Need help with SQL message I am getting in my VB code

21 New Member
Error: Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

This is a continuation of my previous table element update question. I am now getting the above error message. I need help with the format of the SQLAdapter.Upda te(DSet.Tables( 0)) command.
I have included my code below. The line is in bold text.

Expand|Select|Wrap|Line Numbers
  1. Imports System
  2. Imports System.Reflection
  3. Imports System.Text
  4. Imports System.Collections
  5. Imports System.Globalization
  6. Imports System.Data.SqlClient
  7. Imports System.Data
  8. Imports System.IO
  9.  
  10. Module Module1
  11.     'Global Defs
  12.     Dim EDIConn As SqlConnection
  13.     Dim AAConn As SqlConnection
  14.     Dim SQLCmd As SqlCommand
  15.     Dim SQLAdapter As SqlDataAdapter
  16.     Dim trans As SqlTransaction
  17.     Dim DSet As DataSet
  18.     Dim DTable As DataTable
  19.     Dim SQLQuery As String = ""
  20.     Dim RecsAffected As Integer = 0
  21.     Dim MyTimeout As String
  22.     Dim connstring As String = "Integrated Security=SSPI; Data Source=" + vbCrLf + _
  23.                                "SCSQLDEV; Initial Catalog = HIPAA_EDI;"
  24.  
  25.  
  26.  
  27.  
  28.     Sub Main()
  29.         EDIConn = New SqlConnection(connstring)
  30.         EDIConn.Open()
  31.         P010_Claim_Type_Selection()
  32.         MsgBox("..What Does It Look Like? ..")
  33.  
  34.     End Sub
  35.     Function P010_Claim_Type_Selection() As Boolean
  36.  
  37.         Dim h As Integer = 0
  38.         Dim i As Integer = 0
  39.         Dim drow As DataRow
  40.  
  41. P010_Select_Claims:
  42.         Console.WriteLine("..P010_Select_Claims ..")
  43.         SQLQuery = "SELECT *                                    " + vbCrLf + _
  44.                    "  FROM Remit.Remit_Temp_Serv                " + vbCrLf + _
  45.                    " WHERE Rmt_Svc_Clm_Num = '061009B02097'     " + vbCrLf + _
  46.                    " ORDER BY Rec_Seq"
  47.  
  48.         If P920_LoadEDITable() Then
  49.             DTable = DSet.Tables(0)
  50. P010_Claim_Loop:
  51.             Console.WriteLine("..P010_Claim_Loop ..")
  52.             h = 0
  53.             i = 0
  54.             For Each drow In DTable.Rows
  55.                 If DTable.Rows(i)(63).ToString() = "-" Then
  56.                     If h > 0 Then
  57.                         h = i - 1
  58.                     Else
  59.                         h = 0
  60.                     End If
  61.                     Try
  62.                         AddHandler SQLAdapter.RowUpdating, AddressOf OnRowUpdating
  63.                         AddHandler SQLAdapter.RowUpdated, AddressOf OnRowUpdated
  64.  
  65.                         DTable.Rows(h)(12) = DTable.Rows(i)(12).ToString()   ' -- Tot_Paid_Amt
  66.                         DTable.Rows(h)(59) = DTable.Rows(i)(59).ToString()   ' -- ClmChk_CPT
  67.                         DTable.Rows(h)(61) = DTable.Rows(i)(61).ToString()   ' -- ClmChk_Bun
  68.                         ' Will the following update the SQL table or does it need a command to execute?
  69.                         'SQLAdapter.Update(DTable)
  70.                         SQLAdapter.Update(DSet.Tables(0))
  71.                     Catch ex As SqlException
  72.                         Console.WriteLine("... Error UPDATING Temp Table: " & ex.Message & "...")
  73.                         Console.WriteLine(".." & ex.Message & "..")
  74.                         Console.WriteLine("... Error UPDATING Temp Table: " & ex.Message & "...")
  75.                     End Try
  76.                 End If
  77.                 i = i + 1
  78.  
  79.  
  80.             Next
  81.         End If
  82.  
  83. P010_Return:
  84.         Return P010_Claim_Type_Selection
  85.     End Function
  86.  
  87.  
  88.  
  89.     Function P920_LoadEDITable() As Boolean
  90.         Console.WriteLine("..P920_LoadEDITable ..")
  91.  
  92.         P920_LoadEDITable = True
  93.         SQLCmd = New SqlCommand()
  94.         SQLCmd.Connection = EDIConn
  95.         SQLCmd.CommandText = SQLQuery
  96.         SQLCmd.CommandType = CommandType.Text
  97.         SQLAdapter = New SqlDataAdapter()
  98.         SQLAdapter.SelectCommand = SQLCmd
  99.         DSet = New DataSet()
  100.         SQLAdapter.Fill(DSet)
  101.         'Return P920_LoadEDITable()
  102.     End Function
  103.     ' Handler for OnRowUpdating
  104.     Sub OnRowUpdating(ByVal sender As Object, ByVal e As SqlRowUpdatingEventArgs)
  105.         Console.WriteLine("OnRowUpdating Event")
  106.         If Not e.Status = UpdateStatus.Continue Then
  107.             Console.WriteLine("RowStatus = " & e.Status.ToString())
  108.         End If
  109.     End Sub
  110.  
  111.     ' Handler for OnRowUpdated
  112.     Sub OnRowUpdated(ByVal sender As Object, ByVal e As SqlRowUpdatedEventArgs)
  113.         Console.WriteLine("OnRowUpdated Event")
  114.         Console.WriteLine("Records Affected = " & e.RecordsAffected.ToString())
  115.     End Sub
  116.  
  117.  
  118.  
  119. End Module
Mar 13 '07 #1
0 1343

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

Similar topics

2
3056
by: lawrence | last post by:
I've been bad about documentation so far but I'm going to try to be better. I've mostly worked alone so I'm the only one, so far, who's suffered from my bad habits. But I'd like other programmers to have an easier time understanding what I do. Therefore this weekend I'm going to spend 3 days just writing comments. Before I do it, I thought I'd ask other programmers what information they find useful. Below is a typical class I've...
4
1497
by: suzy | last post by:
it seems like a simple enough problem, but i need some help...... i have a table of messages (like newsgroup messages) in sql server. my application will allow people to read threads/messages and reply to messages over the net. the table has the following columns: messageId - unique primary key parent message id - if someone replies to an existing message, the parent message's id goes here. (if message has no parent then this will be...
19
4109
by: James Fortune | last post by:
I have a lot of respect for David Fenton and Allen Browne, but I don't understand why people who know how to write code to completely replace a front end do not write something that will automate the code that implements managing unbound controls on forms given the superior performance of unbound controls in a client/server environment. I can easily understand a newbie using bound controls or someone with a tight deadline. I guess I need...
3
1580
by: luna | last post by:
theres no errors in my code - but it does nothing at all - its supposed to be populating text boxes!! any ideas ? (im am pretty new to all this) If Not Page.IsPostBack Then Dim search As String Dim strConn As String = "server=Gringotts;uid=sa;pwd=password;database=database"
16
2317
by: peshekeedweller | last post by:
Using asp.net 1.1. vb.net 2003. I am trying to connect to a remote sql server 2000 on a virtual machine running windows 2000 server. I can connect through the server explorer in visual studio, but cannot connect through code. I have done it before and can not do it now; I don't know what changed. (I also cannot connect to local Access database through code though I can from server explorer.) The code is: conn = New SqlConnection("data...
15
4644
by: Cheryl Langdon | last post by:
Hello everyone, This is my first attempt at getting help in this manner. Please forgive me if this is an inappropriate request. I suddenly find myself in urgent need of instruction on how to communicate with a MySQL database table on a web server, from inside of my company's Access-VBA application. I know VBA pretty well but have never before needed to do this HTTP/XML/MySQL type functions.
9
2198
by: MrHelpMe | last post by:
Hello again experts, I have successfully pulled data from an LDAP server and now what I want to do is drop the data into a database table. The following is my code that will insert the data but that has problems. FullName=Request.Form("Name") Email=Request.Form("Email") GivenName=Request.Form("GivenName")
20
4285
by: mike | last post by:
I help manage a large web site, one that has over 600 html pages... It's a reference site for ham radio folks and as an example, one page indexes over 1.8 gb of on-line PDF documents. The site is structured as an upside-down tree, and (if I remember correctly) never more than 4 levels. The site basically grew (like the creeping black blob) ... all the pages were created in Notepad over the last
8
2935
by: =?Utf-8?B?UHVjY2E=?= | last post by:
Hi, I'm using vs2005, .net 2, C# for Windows application. I use DllImport so I can call up a function written in C++ as unmanaged code and compiled as a dll us vs2005. My application is able to call the function, EncodeAsnUser. And it's returning OK but when I display the decoded data in another part of my application it shows no data has been decoded, all fiedls are either null or blanks. For some reason, I am not able to step through...
0
9639
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9479
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10311
Oralloy
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10080
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9942
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8967
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7492
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
1
4043
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
3639
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.