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

can one update a record thru reader

P: n/a
Hello,
I am looking to update a record that was read in with OleDbDataReader.
I have the record in reader but now with the reader connection open;
how do I update it.

Any advice would be appreciated.
Thank you,
lmnorms

Aug 30 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Here is what I am trying to do but this does not work for update:
shtDt = String.Format("{0:d}", Today)

'Open connection to database
Dim gConnString As String =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Customer
development\VBNET\CSFV\CSFV.mdb;"
Dim Conn As New OleDbConnection(gConnString) 'Actual
Connection to database.
Conn.Open()
'Run statement and execute
'String.Format("{0:x}", hex));

'Dim LetterQuery As String = _ Select * from LetterData where
CollectionCompany = " & " '" & custName & "' " & " and " & "
DateProcessed = " & " '" & Today.ToString & "' " & ";"
Dim LetterQuery As String = "SELECT * FROM LetterData " & _
"WHERE CollectionCompany = '" &
custName & "' " & _
"AND DateProcessed = #" & shtDt &
"# " & _
";"

Dim Command As New OleDbCommand(LetterQuery, Conn)
Dim LetterReader As OleDbDataReader = Command.ExecuteReader()

While LetterReader.Read()
Dim LetterCode As String = Trim(LetterReader.GetString(9))
'Create NCOA File
If LetterReader.GetBoolean(12) = True Then
Dim DataString As String =
LetterReader.GetInt32(0).ToString.PadRight(10) &
LetterReader.GetString(9).PadRight(10) &
LetterReader.GetString(7).PadRight(50) &
LetterReader.GetString(8).PadRight(20) & Today & " CSFV"
PrintLine(3, "1" &
LetterReader.GetString(1).PadRight(35) &
LetterReader.GetString(2).PadRight(30) &
LetterReader.GetString(3).PadRight(30) &
LetterReader.GetString(4).PadRight(30) &
LetterReader.GetString(5).PadRight(2) &
LetterReader.GetString(6).PadRight(5) & Space(5) & DataString)
'Output Letters
Else
'Set the fields here for readability
Dim Name As String = LetterReader.GetString(1) & "|"
Dim Address1 As String = LetterReader.GetString(2) &
"|"
Dim Address2 As String = LetterReader.GetString(3) &
"|"
Dim City As String = LetterReader.GetString(4) & "|"
Dim State As String = LetterReader.GetString(5) & "|"
Dim ZipCode As String = LetterReader.GetString(6) & "|"
Dim AcctNum As String = LetterReader.GetString(8) & "|"
Dim SkipTrace As String = LetterReader.GetString(7) &
"|"
Dim LetterFields As String = LetterReader.GetString(10)
Dim DetailRecords As String =
LetterReader.GetString(11)

PrintLine(2, LetterCode.PadRight(15) & "|" & Name &
Address1 & Address2 & City & State & ZipCode & AcctNum)
PrintLine(2, (LetterCode & "_SKIP").PadRight(15) & "|"
& SkipTrace & LetterCode & "|")
PrintLine(2, (LetterCode & "_LT").PadRight(15) & "|" &
LetterFields)


'split detail records back out INTO ITERATED RECORD FOR
DOC1
DetailFields = Split(DetailRecords, "|")
For FieldCounter = 0 To (DetailFields.Length - 1)
PrintLine(2, (LetterCode & "_DT").PadRight(15) &
"|" & Trim(DetailFields(FieldCounter)) & "|")
Next

PrintLine(2, "NCOA Flag".PadRight(15) & "|" &
LetterReader.GetBoolean(12))
'Create and call routine to update date printed on
current record. Use Id number
'Call Update_date_processed(LetterReader.GetInt32(0))
RecordID = LetterReader.GetInt32(0)
Dim SqlStatement As String = "UPDATE * from LetterData
" & _
"Set DateSenttoPrint = #" & shtDt
& "# " & _
"WHERE ID = " & RecordID & " "

Command = New OleDbCommand(SqlStatement, Conn)
Command.ExecuteNonQuery()
'close db
'Conn.Close()

End If
End While

'close db
Conn.Close()
'Close output files
'2= output file for Doc1 containing non-NCOA records
'3=NCOA File
FileClose(2)
FileClose(3)

lm******@yahoo.com wrote:
Hello,
I am looking to update a record that was read in with OleDbDataReader.
I have the record in reader but now with the reader connection open;
how do I update it.

Any advice would be appreciated.
Thank you,
lmnorms
Aug 30 '06 #2

P: n/a
ISSUE is resolved. Thank you all that helped.

Loaded array with ID and then after this module is done then did a
function that stepped thru the array and updated the table.
lm******@yahoo.com wrote:
Here is what I am trying to do but this does not work for update:
shtDt = String.Format("{0:d}", Today)

'Open connection to database
Dim gConnString As String =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Customer
development\VBNET\CSFV\CSFV.mdb;"
Dim Conn As New OleDbConnection(gConnString) 'Actual
Connection to database.
Conn.Open()
'Run statement and execute
'String.Format("{0:x}", hex));

'Dim LetterQuery As String = _ Select * from LetterData where
CollectionCompany = " & " '" & custName & "' " & " and " & "
DateProcessed = " & " '" & Today.ToString & "' " & ";"
Dim LetterQuery As String = "SELECT * FROM LetterData " & _
"WHERE CollectionCompany = '" &
custName & "' " & _
"AND DateProcessed = #" & shtDt &
"# " & _
";"

Dim Command As New OleDbCommand(LetterQuery, Conn)
Dim LetterReader As OleDbDataReader = Command.ExecuteReader()

While LetterReader.Read()
Dim LetterCode As String = Trim(LetterReader.GetString(9))
'Create NCOA File
If LetterReader.GetBoolean(12) = True Then
Dim DataString As String =
LetterReader.GetInt32(0).ToString.PadRight(10) &
LetterReader.GetString(9).PadRight(10) &
LetterReader.GetString(7).PadRight(50) &
LetterReader.GetString(8).PadRight(20) & Today & " CSFV"
PrintLine(3, "1" &
LetterReader.GetString(1).PadRight(35) &
LetterReader.GetString(2).PadRight(30) &
LetterReader.GetString(3).PadRight(30) &
LetterReader.GetString(4).PadRight(30) &
LetterReader.GetString(5).PadRight(2) &
LetterReader.GetString(6).PadRight(5) & Space(5) & DataString)
'Output Letters
Else
'Set the fields here for readability
Dim Name As String = LetterReader.GetString(1) & "|"
Dim Address1 As String = LetterReader.GetString(2) &
"|"
Dim Address2 As String = LetterReader.GetString(3) &
"|"
Dim City As String = LetterReader.GetString(4) & "|"
Dim State As String = LetterReader.GetString(5) & "|"
Dim ZipCode As String = LetterReader.GetString(6) & "|"
Dim AcctNum As String = LetterReader.GetString(8) & "|"
Dim SkipTrace As String = LetterReader.GetString(7) &
"|"
Dim LetterFields As String = LetterReader.GetString(10)
Dim DetailRecords As String =
LetterReader.GetString(11)

PrintLine(2, LetterCode.PadRight(15) & "|" & Name &
Address1 & Address2 & City & State & ZipCode & AcctNum)
PrintLine(2, (LetterCode & "_SKIP").PadRight(15) & "|"
& SkipTrace & LetterCode & "|")
PrintLine(2, (LetterCode & "_LT").PadRight(15) & "|" &
LetterFields)


'split detail records back out INTO ITERATED RECORD FOR
DOC1
DetailFields = Split(DetailRecords, "|")
For FieldCounter = 0 To (DetailFields.Length - 1)
PrintLine(2, (LetterCode & "_DT").PadRight(15) &
"|" & Trim(DetailFields(FieldCounter)) & "|")
Next

PrintLine(2, "NCOA Flag".PadRight(15) & "|" &
LetterReader.GetBoolean(12))
'Create and call routine to update date printed on
current record. Use Id number
'Call Update_date_processed(LetterReader.GetInt32(0))
RecordID = LetterReader.GetInt32(0)
Dim SqlStatement As String = "UPDATE * from LetterData
" & _
"Set DateSenttoPrint = #" & shtDt
& "# " & _
"WHERE ID = " & RecordID & " "

Command = New OleDbCommand(SqlStatement, Conn)
Command.ExecuteNonQuery()
'close db
'Conn.Close()

End If
End While

'close db
Conn.Close()
'Close output files
'2= output file for Doc1 containing non-NCOA records
'3=NCOA File
FileClose(2)
FileClose(3)

lm******@yahoo.com wrote:
Hello,
I am looking to update a record that was read in with OleDbDataReader.
I have the record in reader but now with the reader connection open;
how do I update it.

Any advice would be appreciated.
Thank you,
lmnorms
Aug 31 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.