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 I've 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]

You'd 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. I've given you all the code to do it, so I'll 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

Edited September 15, 2002 by Derek Stone

[Derek Stone]

You never declared the DataSource variable.