Simple Database Record Creation

[fillibar]

I have Zero database knowledge I think, but in VB6 I was able to write regarding a huge number of files I need to create into a database for tracking purposes. I connected to the database like:
Public Sub dbConnect(DataSource As String)
Set dbConnection = New ADODB.Connection
dbConnection.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DataSource & ";Persist Security Info=False")

Set dbRecord = New ADODB.Recordset
dbRecord.CursorType = adOpenKeyset
dbRecord.LockType = adLockOptimistic
dbRecord.Open "docIndexTable", dbConnection, , , adCmdTable
End Sub

And added my records like:
dbRecord.AddNew
dbRecord!DocumentNumber = dbIndex
dbRecord!DocumentTitle = "PlaceHolder"
dbRecord!DocumentRelativePath = PathName
dbRecord!DocumentFilename = FileName
dbRecord!DocumentNeeded = True
dbRecord!SearchKeywords = Null
dbRecord!RelatedDocumentNumbers = Null
dbRecord!DateDocumentLastEdited = Date
dbRecord.Update

I am looking to do the same thing with VB.Net, but so far it has eluded me, and all the examples I can find focus on USING the database. I just need to write to it, never read the data (except by opening Access).

Any help would be appreciated.

 

[Robby]

You can use this ADO example.... You can also use AOD.NET.

        Dim cnnTest As New ADODB.Connection()
        Dim rsChamps As ADODB.Recordset


        cnnTest.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=C:\test.mdb;")

        rsChamps = New ADODB.Recordset()
        rsChamps.Open(table, cnnTest, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockPessimistic)

        we put the cursor in place
        rsChamps.MoveLast()
        rsChamps.AddNew()

        rsChamps.Fields(0).Value = txt1.Text
        rsChamps.Fields(1).Value = txt2.Text
        rsChamps.Fields(2).Value = Txt3.Text
        rsChamps.Fields(3).Value = txt4.Text
        rsChamps.Fields(4).Value = txt5.Text
        rsChamps.Fields(5).Value = txt6.Text
        rsChamps.Fields(6).Value = txt7.Text
        rsChamps.Fields(7).Value = txt8.Text


        Try
            rsChamps.Update()
        Catch
            if error
            MsgBox("Error " & Err.Description & Err.Number, , "Dummy")
            Exit Sub
        End Try

        cnnTest.Close()

 

[fillibar]

Thanks!

Thanks for that piece, you just solved a problem Ive been banging my head on for a LONG time...

 

[Derek Stone]

Try to avoid using COM components, ADO included, in .NET. The exact same results can be achieved using ADO.NET.

Public Sub CreateMyOleDbCommand(myExecuteQuery As String, _
myConnectionString As String)
    Dim myConnection As New OleDbConnection(myConnectionString)
    Dim myCommand As New OleDbCommand(myExecuteQuery, myConnection)
    myCommand.Connection.Open()
    myCommand.ExecuteNonQuery()
    MyConnection.Close()
End Sub

 

[fillibar]

How would I represent the AddNew with all the fields of data as myExecuteQuery?

I do have to admit, while my program creates a nicely populated database now, it takes over 10 times as long to run...
Program Description: It creates on the order of 5K (at this time, will increase) files, and has to record all of them in the DB.

 

[Derek Stone]

Youd call the above function like so:

CreateMyOleDbCommand("INSERT INTO myTable (field1,field2,field3) " & _
"VALUES(Tom,Dick,Harry)", & _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
DataSource & ";Persist Security Info=False")

 

[Derek Stone]

Ideally though you should ditch the function I posted and only open the connection one time, loop through and add the new records, and then close. Ive given you all the code to do it, so Ill leave this part up to you.

 

[fillibar]

It makes sense to have the function not contain the open and close...
But I receive this error when it reaches the ExecuteNonQuery:
An unhandled exception of type System.Data.OleDb.OleDbException occurred in system.data.dll

Code looks like this (edited for fields and data/datasource):

Imports System.Data.OleDb
Module modADO
    Public DocsDBConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DataSource & ";Persist Security Info=False")
    Public Sub OpenDB()
        DocsDBConnection.Open()
    End Sub
    Public Sub CloseDB()
        DocsDBConnection.Close()
    End Sub
    Public Function TestDB() As Boolean
        Dim CurrentDate As Date = Now
        Dim DocsDBCommand As New OleDbCommand("INSERT INTO DocIndexTable (field1,field2,field3) VALUES(1,Text," & CurrentDate & ")", DocsDBConnection)
        DocsDBCommand.ExecuteNonQuery()
        TestDB = True
    End Function
End Module

 

[Derek Stone]

You never declared the DataSource variable.