By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,846 Members | 1,211 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,846 IT Pros & Developers. It's quick & easy.

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

P: 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
Share this question for a faster answer!
Share on Google+

Post your reply

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