Help with Adding A Row

[Miro]

Im a VB Newbie so I hope I'm going about this in the right direction.

I have a simple DB that has 1 Table called DBVersion and in that table the
column is CurVersion ( String )

Im trying to connect to the db, and then add a record to the DBVersion
table.
Except I cant.
I have 1 line that crashes and if i rem it out it works but nothing gets
added.
Can someone have a peek to let me know what im missing or doing wrong.

Thanks,

Miro

====Code
Imports System.Data
Imports System.Data.OleDb

Sub AddInitialRecords()
'Create Connection String
Dim myConnectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
SystemFileDB & FileDBExtention

'Create the Connection
Dim myConnection As New OleDbConnection() '= New OleDbConnection()
' ADODB.Connection()
MyConnection.ConnectionString = myConnectionString

myConnection.Open()

'Whats the difference ? - Im assuming nothing for now
'Dim myDataAdapter As OleDbDataAdapter = New
OleDbDataAdapter("Select * From DBVersion", MyConnection)
'Create the Data Adapter
Dim myDataAdapter As New OleDbDataAdapter("Select * From DBVersion",
MyConnection)

'Creates a Dataset Object and Fills with Data
'Create new Dataset
Dim myDataSet As New DataSet()

'Fill The Dataset
myDataAdapter.Fill(myDataSet, "DBVersion")

'Now lets try to write a record into one field of this Table.
Dim NewVersionRow As DataRow = myDataSet.Tables("DBVersion").NewRow
NewVersionRow("CurVersion") = "2.00"
myDataSet.Tables("DBVersion").Rows.Add(NewVersionRow)

'Crashes but because its remmed out it may be why i dont actually add a
datarow.
'myDataAdapter.Update(myDataSet, "DBVersion")

myDataSet.Tables("DBVersion").AcceptChanges()

myConnection.Close()

End Sub

 

[Mike C#]

If you *just* want to add a single row to the database, you're working
wayyyy too hard. Try something like this:

Dim myConnectionString As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
SystemFileDB & FileDBExtension
Dim myConnection As New OleDbConnection(myConnectionString)
myConnection.Open()
Dim myCommand As New OleDbCommand("INSERT INTO DBVersion (CurVersion) VALUES
(?)", myConnection)
myCommand.Parameters.Add("Param1", OleDbType.VarChar, 50).Value = "2.00"
myCommand.ExecuteNonQuery()
myCommand.Dispose()
myConnection.Close()

 

[Miro]

Yes, that did work perfectly.

Im just trying to figure out what you did here.

What does the Values (?) mean ?

and also, what was I doing wrong? ( If i was on the right rack - what would
I be creating this sub for ? )

Or better yet, where can I go / what can I google to find examples like
this. ( If you know of any )

-Thanks for the spelling error - FileDBExtension as I had it Extention.
ahha I did laugh when I seen that.
I wrote the code and then copied the variable all over the place.

Im sure its a lot easier to do it by "Form" and bind all the tables to
fields on teh form ( i hope ) but Im trying to
figure out how to do it by a function all inbehind the scenes.

Thanks,

Miro

 

[Mike C#]

Yes, that did work perfectly.

Im just trying to figure out what you did here.

What does the Values (?) mean ?

You'll notice that the text:

INSERT INTO DBVersion (CurVersion) VALUES (?)

is in quotes. It's a parameterized SQL statement that tells Access to
insert a row into the table DBVersion and set the value of the CurVersion
column to the parameterized value (?). The ? is replaced in the statement
with the parameter that is added with the line:

myCommand.Parameters.Add("Param1", OleDbType.VarChar, 50).Value = "2.00"

So it's just a SQL statement, and the ? is a placeholder for the parameter
(the value to insert in this case).

and also, what was I doing wrong? ( If i was on the right rack - what
would I be creating this sub for ? )

The route you were taking was to load a DataAdapter first. This basically
uses a dataset to read the data from the table and allow you to manipulate
it in a disconnected fashion. You could make that option work, but unless
you're planning on manipulating existing data and allowing a lot of
disconnected editing/adding/deleting on the table, it's overkill.

For what you want, a simple INSERT of one row into an existing table, the
DataAdapters and DataSets aren't necessary. If you do want to use
DataAdapters and DataSets, it might be best to try adding them to a form to
see the code that's generated. When using the DataAdapter, you have to set
the InsertCommand if you want to insert new rows, and the
UpdateCommand/DeleteCommand properties to update/delete rows.

Or better yet, where can I go / what can I google to find examples like
this. ( If you know of any )

http://www.thecodeproject.com has lots of examples. Mostly I work with SQL
Server (not Access), but a lot of the basic concepts are the same. You
might try googling combinations of "OleDb", ".NET", "DataAdapter", "Access",
"DataSets", "sample code", "VB.NET", "InsertCommand".

-Thanks for the spelling error - FileDBExtension as I had it Extention.
ahha I did laugh when I seen that.
I wrote the code and then copied the variable all over the place.

No prob I assumed it was a typo or a non-American English spelling

Im sure its a lot easier to do it by "Form" and bind all the tables to
fields on teh form ( i hope ) but Im trying to
figure out how to do it by a function all inbehind the scenes.

Binding it by form is a great way to learn how to use it, since it generates
a lot of code for you automatically. Just bind to the forms and look at the
code generated to get ideas on how it does what it does.

 

[Miro]

Thanks Mike,

I will give that a try.

I never thought to consider to make a dummy form and look at the generated
code.

Miro

 

[Miro]

For us newbies who are learning on how to add a row and are wonding why my
first example wasnt working...
here it is.

Thanks for all your help Mike C#.
( I couldnt put it down till i figured it out )

Sub AddInitialRecords()
''''Add a quick Record thru SQL - works
''''Dim myConnectionString As String = _
''''"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
''''SystemFileDB & FileDBExtension
''''Dim myConnection As New OleDbConnection(myConnectionString)
''''myConnection.Open()
''''Dim myCommand As New OleDbCommand("INSERT INTO DBVersion
(CurVersion) VALUES (?)", _
'''' myConnection)
''''myCommand.Parameters.Add("Param1", OleDbType.VarChar, 50).Value
= "2.00"
''''myCommand.ExecuteNonQuery()
''''myCommand.Dispose()
''''myConnection.Close()

'Add a record the long way thru normal statements. - works
Dim cnADONetConnection As New OleDb.OleDbConnection()
Dim myConnectionString As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
SystemFileDB & FileDBExtension
cnADONetConnection.ConnectionString = myConnectionString

cnADONetConnection.Open()

Dim daDataAdapter As New OleDb.OleDbDataAdapter()
daDataAdapter = _
New OleDb.OleDbDataAdapter("Select * From DBVersion",
cnADONetConnection)


Dim cbCommandBuilder As OleDb.OleDbCommandBuilder

cbCommandBuilder = New OleDb.OleDbCommandBuilder(daDataAdapter)

Dim dtVersion As New DataTable()
Dim dtRowPosition As Integer = 0
'Fill with data
daDataAdapter.Fill(dtVersion)

Dim NoOfRecs As Integer = 0
'Go to first row
Dim rwVersion As DataRow '= dtVersion.Rows(0)
NoOfRecs = dtVersion.Rows.Count()

If NoOfRecs = 0 Then
MsgBox("no recs")
rwVersion = dtVersion.NewRow()

rwVersion("CurVersion") = "3.33"

dtVersion.Rows.Add(rwVersion)
daDataAdapter.Update(dtVersion)

Debug.WriteLine("added record - " +
dtVersion.Rows(dtVersion.Rows.Count - 1)("CurVersion").ToString)

Else
MsgBox("there are recs")
rwVersion = dtVersion.Rows(0)
Debug.WriteLine("read record - " + _
rwVersion("CurVersion").GetType.ToString)

'dtVersion.Rows(dtVersion.Rows.Count -
1)("CurVersion").ToString)
End If

'Dim blastring As String = dtVersion.Rows(0)("CurVersion").ToString


Debug.WriteLine("Done debuging")
cnADONetConnection.Close()

End Sub

 

[Mike C#]

Very nice. The second method is very useful when you are doing
"disconnected" data updates. Just one thing (I left it off of my example
also), but don't forget to put Try...Catch exception handling around all
code that accesses the database

 

[Miro]

I never thought to put one around there.
I suppose if the mdb file doesnt exist at this point the Open() will error
out.

Thanks

Miro