Ok Paul, this is down and dirty proof of concept code, it's just a quick
test of doing in VB.net what you're doing with what really looks like
Access-VBA. There's no exception handling and I'm just letting style take a
nap, but it seems to work.
For this quickie I used a copy of the standard Bibilo.mdb, I named this copy
AuthorsTest.mdb. The table we're going to work with is Authors and the
following changes had to be made to that table:
1) converted the AU_ID column from Number/Autoincrement/PK to an non-indexed
Text column (because we're going to shove records in there with values that
the autoincrement wouldn't like)
2) Took that silly space out of the"Year Born" column name because the
commandbuilder can't deal with such nonsense (I've always been annoyed that
MS decided to use such bad form in a sample database, haven't you?) and
just for simplicity I converted that number column to Text also.
3) removed the relationships so that we could dump the records without
erroring.
Then I exported the table as a Tab delimited file (using Access' advanced
options to get rid of the wrapping quotes). The export file was namced
"authors_in.dat"
I put both of these files in a folder "C:\testarea\"
Added an "Imports System.Data.OleDB" to the top of a form's code
Added a button to the form and put this in the click event:
Remember... this is for demonstration purposes only, it's more than ugly ...
but I have to get to dinner so I just made it quick and it does at least
show how to point-for-point port your logic ;-)
Private Sub butPaulTest_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles butPaulTest.Click
Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=C:\testarea\testauthors.MDB")
Dim cmd As New OleDbCommand("DELETE * FROM AUTHORS")
cmd.Connection = cn
cn.Open()
Dim iDeleted As Integer = cmd.ExecuteNonQuery()
MsgBox(iDeleted.ToString & " records were deleted from the Jet file")
Dim ds As New DataSet
Dim da As New OleDbDataAdapter("SELECT * FROM AUTHORS", cn)
da.Fill(ds)
Dim dTable As DataTable = ds.Tables(0)
Dim dRow As DataRow
Dim f As New IO.FileInfo("C:\testarea\Authors_in.dat")
If f.Exists Then
Dim fs As New IO.FileStream(f.FullName, IO.FileMode.Open)
Dim sr As New IO.StreamReader(fs)
Dim sTemp As String = sr.ReadLine
Do While Not sTemp = Nothing
Dim ar As String() = sTemp.Split(vbTab)
dRow = dTable.NewRow
For i As Integer = 0 To ar.Length - 1
dRow(i) = ar(i).Trim
Next
dTable.Rows.Add(dRow)
sTemp = sr.ReadLine
Loop
Dim cmdBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(da)
da.InsertCommand = cmdBuilder.GetInsertCommand()
Dim iAdded = da.Update(dTable)
MsgBox(iAdded & " records added")
'Can't wait till VB gets USING :)
sr.Close()
fs.Close()
fs = Nothing
End If
f = Nothing
da.Dispose()
da = Nothing
dRow = Nothing
dTable.Dispose()
dTable = Nothing
ds.Dispose()
ds = Nothing
cn.Close()
cn.Dispose()
cn = Nothing
End Sub
Hope that helps in some way.
Robert Smith
Kirkland, WA
www.smithvoice.com
"Paul Ilacqua" <pi******@twcny.rr.com> wrote in message
news:%2******************@tk2msftngp13.phx.gbl...
I have spent the last 7 years doing things like this... relatively simple.
Now with the move to .NET I'm lost. Can someone give me a headstart on how
to do this in vb.net. I appreciate any help I can get I learn the best from
having a working model and then expanding on it.
Function InsertFlat() As Long
Dim s As String
Dim iLine As Long
Dim n
Dim i As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Dim iFile As Integer
Set rs = db.OpenRecordset("ActiveInventory_ITEMS")
db.Execute "Delete * From ActiveInventory_ITEMS"
iFile = FreeFile
Open "C:\AAAA\ActiveInventory_ITEMS.dat" For Input As #iFile
Do Until EOF(iFile)
Line Input #iFile, s
n = Split(s, vbTab)
rs.AddNew
For i = 0 To UBound(n) -1
rs(i) = C_Null(RTrim$(n(i)))
Next i
rs.Update
iLine = iLine + 1
Loop
Close #iFile
InsertFlat = iLine
End Function
