473,378 Members | 1,416 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

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

21
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.Update(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 1328

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

Similar topics

2
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...
4
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...
19
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...
3
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...
16
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,...
15
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...
9
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...
20
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...
8
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...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
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,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
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$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
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...
0
BarryA
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...
1
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...

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.