Need an example of updating an item within a SQL Table

[JimN1]

Hi,
I am looking for an example of how to update a SQL table after you have done the SELECT query and using IDataReader or SQLAdapter. I think the format is something like Table.row.update(0) (5) to update the 1st row, the sixth data element. I want to update just the sixth element, not the whole row.

Does anyone have a code example of this?

Pleaes Help.

Thanks,

[willakawill]

Please post the code you have so far and we will take it from there

[JimN1]

Please post the code you have so far and we will take it from there

Below is the test I have been working on. The section P010_Claim_Loop:
is the part I am having trouble with.

Imports System
Imports System.Reflection
Imports System.Text
Imports System.Collections
Imports System.Globalization
Imports System.Data.SqlClient
Imports System.Data
Imports System.IO

Module Module1
'Global Defs
Dim EDIConn As SqlConnection
Dim AAConn As SqlConnection
Dim SQLCmd As SqlCommand
Dim SQLAdapter As SqlDataAdapter
Dim trans As SqlTransaction
Dim DSet As DataSet
Dim DTable As DataTable
Dim SQLQuery As String = ""
Dim RecsAffected As Integer = 0
Dim connstring As String
Dim MyTimeout As String



Sub Main()

P010_Claim_Type_Selection()
MsgBox("..What Does It Look Like? ..")

End Sub
Function P010_Claim_Type_Selection() As Boolean

Dim h As Integer = 0
Dim i As Integer = 0
Dim drow As DataRow

P010_Select_Claims:
SQLQuery = "SELECT * " + vbCrLf + _
" FROM Remit.Remit_Temp_Serv " + vbCrLf + _
" WHERE Rmt_Svc_Clm_Num = '061009B02097' " + vbCrLf + _
" ORDER BY Rec_Seq"
'trans = EDIConn.BeginTransaction()
'SQLCmd = New SqlCommand()
'SQLCmd.Connection = EDIConn
'SQLCmd.Transaction = trans
'SQLCmd.CommandText = SQLQuery
'SQLCmd.CommandTimeout = 30
'SQLCmd.CommandType = CommandType.Text
'RecsAffected = SQLCmd.ExecuteNonQuery()
'trans.Commit()
'SQLCmd = Nothing
'trans = Nothing

If P920_LoadEDITable() Then
DTable = DSet.Tables(0)
P010_Claim_Loop:
For Each drow In DTable.Rows
If DTable.Rows(i)(63).ToString() = "-" Then
If h > 0 Then
h = i - 1
Else
h = 0
End If
Try
AddHandler SQLAdapter.RowUpdating, AddressOf OnRowUpdating
AddHandler SQLAdapter.RowUpdated, AddressOf OnRowUpdated

DTable.Rows(h)(12) = DTable.Rows(i)(12).ToString() ' -- Tot_Paid_Amt
DTable.Rows(h)(59) = DTable.Rows(i)(59).ToString() ' -- ClmChk_CPT
DTable.Rows(h)(61) = DTable.Rows(i)(61).ToString() ' -- ClmChk_Bun
' Will the following update the SQL table or does it need a command to execute?
SQLAdapter.Update(DTable) Catch ex As SqlException
Console.WriteLine("... Error UPDATING Temp Table: " & ex.Message & "...")
Console.WriteLine(".." & ex.Message & "..")
Console.WriteLine("... Error UPDATING Temp Table: " & ex.Message & "...")
End Try
i = i + 1
h = h + 1
End If
Next
End If

P010_Return:
Return P010_Claim_Type_Selection
End Function


Function P920_LoadEDITable() As Boolean

P920_LoadEDITable = True

SQLCmd = New SqlCommand()
SQLCmd.Connection = EDIConn
SQLCmd.CommandText = SQLQuery
SQLCmd.CommandType = CommandType.Text

SQLAdapter = New SqlDataAdapter()
SQLAdapter.SelectCommand = SQLCmd

DSet = New DataSet()
SQLAdapter.Fill(DSet)
Return P920_LoadEDITable()
End Function
' Handler for OnRowUpdating
Sub OnRowUpdating(ByVal sender As Object, ByVal e As SqlRowUpdatingEventArgs)
Console.WriteLine("OnRowUpdating Event")
If Not e.Status = UpdateStatus.Continue Then
Console.WriteLine("RowStatus = " & e.Status.ToString())
End If
End Sub

' Handler for OnRowUpdated
Sub OnRowUpdated(ByVal sender As Object, ByVal e As SqlRowUpdatedEventArgs)
Console.WriteLine("OnRowUpdated Event")
Console.WriteLine("Records Affected = " & e.RecordsAffected.ToString())
End Sub



End Module