Forums
New posts
Search forums
What's new
New posts
New profile posts
Latest activity
Members
Current visitors
New profile posts
Search profile posts
C# Community
Log in
Register
What's new
Search
Search
Search titles only
By:
New posts
Search forums
Menu
Log in
Register
Install the app
Install
Database
MS Access
Update requires a valid Insert Command
JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser
.
Reply to thread
Message
[QUOTE="wjburke2, post: 167612, member: 21091"] I am new to VB.Net and am trying to migrate a Access application. I have text file(s) I need to read and use to updated an access database. I did some research and came up with the code below. I am connected to the database and cycle through the records I assume updateing the dataset in my application but when I get to da.Update(ds, "rstHeader") it blows with "Update requires a valid Insert Command when passed DataRow collection with new rows." Everything I read about this error points to using a Insert command I am not thrilled about writing that command it going to be three pages long and terrible to maintain. Can I use this code or do I need a total rewrite? [CODE] Public Sub ParseOneCodeFiles(ByVal InFileName As String) Dim lngTotalACS As Long Dim strCreateDate As String Dim strFileName As String Dim strSQL As String Dim totalread As Integer Dim da As New OleDb.OleDbDataAdapter Dim ds As New DataSet Dim dt As New DataTable ' *********************************************** ' **** Create Work Tables *** ' *********************************************** strSQL = "SELECT * FROM RecordHeader" da.SelectCommand = New OleDb.OleDbCommand(strSQL, conn) da.Fill(ds, "rstHeader") dt = ds.Tables(0) strSQL = "SELECT * FROM SingleSrc_Temp" da.SelectCommand = New OleDb.OleDbCommand(strSQL, conn) da.Fill(ds, "rstSSrc") dt = ds.Tables(0) ' *********************************************** ' **** Import from Text *** ' *********************************************** strFileName = Mid(InFileName, 1 + InStrRev(InFileName, "\")) Dim reader = File.OpenText(InFileName) Dim strread As String = Nothing Dim lines As Integer = 0 While (reader.Peek() <> -1) strread = reader.ReadLine() lines = lines + 1 If Not (Trim(strread) = "") Then 'Ignore blank line 'Test for Header Record If "H" = Mid(strread, 1, 1) Then If lines <> 1 Then MsgBox("Invalid header file please corect") GoTo ParseOneCodeFiles_exit End If Dim dsNewRow As DataRow = ds.Tables("rstHeader").NewRow() dsNewRow.Item("MailerID") = CStr(Trim(Mid(strread, 2, 6))) dsNewRow.Item("FileName") = strFileName dsNewRow.Item("CreateDate") = CStr(Mid(strread, 8, 8)) strCreateDate = CStr(Mid(strread, 8, 8)) dsNewRow.Item("TotalACS") = CInt(Trim(Mid(strread, 16, 9))) lngTotalACS = CInt(Trim(Mid(strread, 16, 9))) dsNewRow.Item("TotalCOA") = CInt(Trim(Mid(strread, 25, 9))) dsNewRow.Item("TotalNIXIE") = CInt(Trim(Mid(strread, 34, 9))) dsNewRow.Item("ShipmentNum") = CStr(Trim(Mid(strread, 43, 8))) dsNewRow.Item("Class") = CStr(Trim(Mid(strread, 51, 1))) dsNewRow.Item("MediaType") = CStr(Trim(Mid(strread, 52, 1))) dsNewRow.Item("EntryType") = "S" ds.Tables("rstHeader").Rows.Add(dsNewRow) ElseIf "2" = Mid(strread, 1, 1) Then totalread = totalread + 1 Dim dsNewRow As DataRow = ds.Tables("rstSSrc").NewRow() dsNewRow.Item("RecType") = CStr(Trim(Mid(strread, 1, 1))) dsNewRow.Item("FileName") = strFileName dsNewRow.Item("SeqNum") = CStr(Trim(Mid(strread, 2, 8))) dsNewRow.Item("MailerId6") = CStr(Trim(Mid(strread, 10, 7))) dsNewRow.Item("MailPieceId") = CStr(Trim(Mid(strread, 17, 9))) dsNewRow.Item("MoveCCYYMM") = CStr(Trim(Mid(strread, 33, 6))) dsNewRow.Item("MoveType") = CStr(Trim(Mid(strread, 39, 1))) dsNewRow.Item("DelivbleCode") = CStr(Trim(Mid(strread, 40, 1))) dsNewRow.Item("POSiteId") = CStr(Trim(Mid(strread, 41, 3))) dsNewRow.Item("COALName") = CStr(Trim(Mid(strread, 44, 20))) dsNewRow.Item("COAFNameMI") = CStr(Trim(Mid(strread, 64, 15))) dsNewRow.Item("COAPrefix") = CStr(Trim(Mid(strread, 79, 6))) dsNewRow.Item("COASufix") = CStr(Trim(Mid(strread, 85, 6))) dsNewRow.Item("AccumCCYYMMDD") = Now.ToString("yyyyMMdd") dsNewRow.Item("ProcDate") = "00000000" dsNewRow.Item("EntryType") = "S" ds.Tables("rstSSrc").Rows.Add(dsNewRow) Else MsgBox("This is not a valid record type" & strread) End If End If End While 'update the database with the new rows da.Update(ds, "rstHeader") da.Update(ds, "rstSSrc") If totalread <> lngTotalACS Then 'DeleteWorkFiles() MsgBox("Import not sussessful Input file counts do not match Header", vbOKOnly) End If ParseOneCodeFiles_exit: Exit Sub End Sub [/CODE] [/QUOTE]
Insert quotes…
Verification
Post reply
Database
MS Access
Update requires a valid Insert Command
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.
Accept
Learn more…
Top
Bottom