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: 167626, member: 21091"] Thanks for your response jmcilhinney. Man I spent hours researching TextFieldParser. Pretty neat stuff. I decided not to go that way because of the complexity of defining fieldwidths and then doing the update they change this thing all the time and maintenance world be a bear. So I decided to continue the direction I was going and just figure out how to fix the error. Turns out I was trying to use .net automation without really going through the full process. But thanks I am sure I will use that at some point and now I know how. Here's what I ended up with that works. [CODE] Public Sub ParseOneCodeFiles(ByVal InFileName As String) Dim lngTotalACS As Long Dim strCreateDate As String Dim strFileName As String Dim totalread As Integer Dim daHeader As New ImportACS300DataSetTableAdapters.RecordHeaderTableAdapter Dim daSnglSrc As New ImportACS300DataSetTableAdapters.SingleSrc_TempTableAdapter daHeader.Connection = New OleDb.OleDbConnection(conString) daSnglSrc.Connection = New OleDb.OleDbConnection(conString) Dim ds As New ImportACS300DataSet '*********************************************** '**** Create Work Tables *** '*********************************************** daHeader.Fill(ds.RecordHeader) daSnglSrc.Fill(ds.SingleSrc_Temp) '*********************************************** '**** 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 ImportACS300DataSet.RecordHeaderRow dsNewRow = ds.RecordHeader.NewRecordHeaderRow dsNewRow.MailerID = CStr(Trim(Mid(strread, 2, 6))) dsNewRow.FileName = strFileName dsNewRow.CreateDate = CStr(Mid(strread, 8, 8)) strCreateDate = CStr(Mid(strread, 8, 8)) dsNewRow.TotalACS = CInt(Trim(Mid(strread, 16, 9))) lngTotalACS = CInt(Trim(Mid(strread, 16, 9))) dsNewRow.TotalCOA = CInt(Trim(Mid(strread, 25, 9))) dsNewRow.TotalNIXIE = CInt(Trim(Mid(strread, 34, 9))) dsNewRow.ShipmentNum = CStr(Trim(Mid(strread, 43, 8))) 'dsNewRow.m = CStr(Trim(Mid(strread, 51, 1))) dsNewRow.MediaType = CStr(Trim(Mid(strread, 52, 1))) dsNewRow.EntryType = "S" ds.RecordHeader.AddRecordHeaderRow(dsNewRow) ElseIf "2" = Mid(strread, 1, 1) Then 'This is a COA/NIXIE - Write to COA dB totalread = +1 Dim dsNewRow As ImportACS300DataSet.SingleSrc_TempRow dsNewRow = ds.SingleSrc_Temp.NewSingleSrc_TempRow dsNewRow.RecType = CStr(Trim(Mid(strread, 1, 1))) dsNewRow.FileName = strFileName dsNewRow.SeqNum = CStr(Trim(Mid(strread, 2, 8))) dsNewRow.MailerId6 = CStr(Trim(Mid(strread, 10, 7))) dsNewRow.MailPieceId = CStr(Trim(Mid(strread, 17, 9))) dsNewRow.MoveCCYYMM = CStr(Trim(Mid(strread, 33, 6))) dsNewRow.MoveType = CStr(Trim(Mid(strread, 39, 1))) dsNewRow.DelivbleCode = CStr(Trim(Mid(strread, 40, 1))) dsNewRow.POSiteId = CStr(Trim(Mid(strread, 41, 3))) dsNewRow.COALName = CStr(Trim(Mid(strread, 44, 20))) dsNewRow.COAFNameMI = CStr(Trim(Mid(strread, 64, 15))) dsNewRow.COAPrefix = CStr(Trim(Mid(strread, 79, 6))) dsNewRow.COASufix = CStr(Trim(Mid(strread, 85, 6))) dsNewRow.OldAddrType = CStr(Trim(Mid(strread, 91, 1))) dsNewRow.OldUrbName = CStr(Trim(Mid(strread, 92, 28))) dsNewRow.OldPrimNumber = CStr(Trim(Mid(strread, 120, 10))) dsNewRow.OldPreDir = CStr(Trim(Mid(strread, 130, 2))) dsNewRow.OldStreetName = CStr(Trim(Mid(strread, 132, 28))) dsNewRow.OldStreetSfx = CStr(Trim(Mid(strread, 160, 4))) dsNewRow.OldPostDir = CStr(Trim(Mid(strread, 164, 2))) dsNewRow.OldUnitDsgn = CStr(Trim(Mid(strread, 166, 4))) dsNewRow.OldSecNumber = CStr(Trim(Mid(strread, 170, 10))) dsNewRow.OldCity = CStr(Trim(Mid(strread, 180, 28))) dsNewRow.OldState = CStr(Trim(Mid(strread, 208, 2))) dsNewRow.OldZip = CStr(Trim(Mid(strread, 210, 5))) If dsNewRow.OldZip = "00000" Then dsNewRow.OldZip = CStr(Trim(Mid(strread, 448, 5))) End If dsNewRow.NewAddrType = CStr(Trim(Mid(strread, 215, 1))) dsNewRow.NewUrbName = CStr(Trim(Mid(strread, 216, 28))) dsNewRow.NewPrimNumber = CStr(Trim(Mid(strread, 244, 10))) dsNewRow.NewPreDir = CStr(Trim(Mid(strread, 254, 2))) dsNewRow.NewStreetName = CStr(Trim(Mid(strread, 256, 28))) dsNewRow.NewStreetSfx = CStr(Trim(Mid(strread, 284, 4))) dsNewRow.NewPostDir = CStr(Trim(Mid(strread, 288, 2))) dsNewRow.NewUnitDsgn = CStr(Trim(Mid(strread, 290, 4))) dsNewRow.NewSecNumber = CStr(Trim(Mid(strread, 294, 10))) dsNewRow.NewCity = CStr(Trim(Mid(strread, 304, 28))) dsNewRow.NewState = CStr(Trim(Mid(strread, 332, 2))) dsNewRow.NewZip = CStr(Trim(Mid(strread, 334, 5))) dsNewRow.NewZip4 = CStr(Trim(Mid(strread, 340, 4))) dsNewRow.NewDPBC = CStr(Trim(Mid(strread, 344, 3))) dsNewRow.LableFmtAddr = CStr(Trim(Mid(strread, 347, 66))) dsNewRow.FeeNoticeType = CStr(Trim(Mid(strread, 413, 1))) dsNewRow.PostageDue = CStr(Trim(Mid(strread, 415, 4))) ' rstTrad!PMB") = CStr(Trim(Mid(strread, 419, 8))) dsNewRow.NoticeType = CStr(Trim(Mid(strread, 427, 1))) dsNewRow.OriginalIMB = CStr(Trim(Mid(strread, 428, 31))) dsNewRow.MailerId9 = CStr(Trim(Mid(strread, 460, 9))) 'dsNewRow.CreateDate") = strCreateDate dsNewRow.AccumCCYYMMDD = Now.ToString("yyyyMMdd") dsNewRow.ProcDate = "00000000" dsNewRow.EntryType = "S" ds.SingleSrc_Temp.AddSingleSrc_TempRow(dsNewRow) ElseIf "" = Mid(strread, 1, 1) Then 'MsgBox("EOF Reached") Else MsgBox("This is not a valid record type" & strread) 'DeleteWorkFiles() 'GoTo ParseOneCodeFiles_exit End If End If End While 'update the database with the new rows daHeader.Update(ds.RecordHeader) daSnglSrc.Update(ds.SingleSrc_Temp) If totalread <> lngTotalACS Then 'DeleteWorkFiles() MsgBox("Import not sussessful Input file counts do not match Header", vbOKOnly) End If ParseOneCodeFiles_exit: daHeader.Connection.Close() daSnglSrc.Connection.Close() 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