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.Ole Db

Sub AddInitialRecor ds()
'Create Connection String
Dim myConnectionStr ing As String =
"Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & _
SystemFileDB & FileDBExtention

'Create the Connection
Dim myConnection As New OleDbConnection () '= New OleDbConnection ()
' ADODB.Connectio n()
MyConnection.Co nnectionString = myConnectionStr ing

myConnection.Op en()

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

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

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

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

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

myDataSet.Table s("DBVersion"). AcceptChanges()

myConnection.Cl ose()

End Sub

[Mike C#]

Re: Help with Adding A Row

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

Dim myConnectionStr ing As String = _
"Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & _
SystemFileDB & FileDBExtension
Dim myConnection As New OleDbConnection (myConnectionSt ring)
myConnection.Op en()
Dim myCommand As New OleDbCommand("I NSERT INTO DBVersion (CurVersion) VALUES
(?)", myConnection)
myCommand.Param eters.Add("Para m1", OleDbType.VarCh ar, 50).Value = "2.00"
myCommand.Execu teNonQuery()
myCommand.Dispo se()
myConnection.Cl ose()

[Miro]

Re: Help with Adding A Row

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#" <xyz@xyz.comwro te in message
news:vN4Lg.187$ fm1.92@newsfe10 .lga...

If you *just* want to add a single row to the database, you're working
wayyyy too hard. Try something like this:
>
Dim myConnectionStr ing As String = _
"Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & _
SystemFileDB & FileDBExtension
Dim myConnection As New OleDbConnection (myConnectionSt ring)
myConnection.Op en()
Dim myCommand As New OleDbCommand("I NSERT INTO DBVersion (CurVersion)
VALUES (?)", myConnection)
myCommand.Param eters.Add("Para m1", OleDbType.VarCh ar, 50).Value = "2.00"
myCommand.Execu teNonQuery()
myCommand.Dispo se()
myConnection.Cl ose()
>
>

[Mike C#]

Re: Help with Adding A Row


"Miro" <mironagy@golde n.netwrote in message
news:u8bkpjJ0GH A.3440@TK2MSFTN GP06.phx.gbl...

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.Param eters.Add("Para m1", OleDbType.VarCh ar, 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", "DataAdapte r", "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.

"Mike C#" <xyz@xyz.comwro te in message
news:vN4Lg.187$ fm1.92@newsfe10 .lga...

>If you *just* want to add a single row to the database, you're working
>wayyyy too hard. Try something like this:
>>
>Dim myConnectionStr ing As String = _
>"Provider=Micr osoft.Jet.OLEDB .4.0;Data Source=" & _
>SystemFileDB & FileDBExtension
>Dim myConnection As New OleDbConnection (myConnectionSt ring)
>myConnection.O pen()
>Dim myCommand As New OleDbCommand("I NSERT INTO DBVersion (CurVersion)
>VALUES (?)", myConnection)
>myCommand.Para meters.Add("Par am1", OleDbType.VarCh ar, 50).Value = "2.00"
>myCommand.Exec uteNonQuery()
>myCommand.Disp ose()
>myConnection.C lose()
>>
>>

>
>

[Miro]

Re: Help with Adding A Row

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

"Mike C#" <xyz@xyz.comwro te in message
news:zW6Lg.365$ 7U4.229@newsfe1 2.lga...

>
"Miro" <mironagy@golde n.netwrote in message
news:u8bkpjJ0GH A.3440@TK2MSFTN GP06.phx.gbl...

>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.Param eters.Add("Para m1", OleDbType.VarCh ar, 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", "DataAdapte r",
"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.
>

>"Mike C#" <xyz@xyz.comwro te in message
>news:vN4Lg.187 $fm1.92@newsfe1 0.lga...

>>If you *just* want to add a single row to the database, you're working
>>wayyyy too hard. Try something like this:
>>>
>>Dim myConnectionStr ing As String = _
>>"Provider=Mic rosoft.Jet.OLED B.4.0;Data Source=" & _
>>SystemFileD B & FileDBExtension
>>Dim myConnection As New OleDbConnection (myConnectionSt ring)
>>myConnection. Open()
>>Dim myCommand As New OleDbCommand("I NSERT INTO DBVersion (CurVersion)
>>VALUES (?)", myConnection)
>>myCommand.Par ameters.Add("Pa ram1", OleDbType.VarCh ar, 50).Value = "2.00"
>>myCommand.Exe cuteNonQuery()
>>myCommand.Dis pose()
>>myConnection. Close()
>>>
>>>

>>
>>

>
>

[Miro]

Re: Help with Adding A Row

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 AddInitialRecor ds()
''''Add a quick Record thru SQL - works
''''Dim myConnectionStr ing As String = _
''''"Provider=M icrosoft.Jet.OL EDB.4.0;Data Source=" & _
''''SystemFileD B & FileDBExtension
''''Dim myConnection As New OleDbConnection (myConnectionSt ring)
''''myConnectio n.Open()
''''Dim myCommand As New OleDbCommand("I NSERT INTO DBVersion
(CurVersion) VALUES (?)", _
'''' myConnection)
''''myCommand.P arameters.Add(" Param1", OleDbType.VarCh ar, 50).Value
= "2.00"
''''myCommand.E xecuteNonQuery( )
''''myCommand.D ispose()
''''myConnectio n.Close()

'Add a record the long way thru normal statements. - works
Dim cnADONetConnect ion As New OleDb.OleDbConn ection()
Dim myConnectionStr ing As String = _
"Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & _
SystemFileDB & FileDBExtension
cnADONetConnect ion.ConnectionS tring = myConnectionStr ing

cnADONetConnect ion.Open()

Dim daDataAdapter As New OleDb.OleDbData Adapter()
daDataAdapter = _
New OleDb.OleDbData Adapter("Select * From DBVersion",
cnADONetConnect ion)


Dim cbCommandBuilde r As OleDb.OleDbComm andBuilder

cbCommandBuilde r = New OleDb.OleDbComm andBuilder(daDa taAdapter)

Dim dtVersion As New DataTable()
Dim dtRowPosition As Integer = 0
'Fill with data
daDataAdapter.F ill(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.NewRo w()

rwVersion("CurV ersion") = "3.33"

dtVersion.Rows. Add(rwVersion)
daDataAdapter.U pdate(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("CurV ersion").GetTyp e.ToString)

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

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


Debug.WriteLine ("Done debuging")
cnADONetConnect ion.Close()

End Sub

[Mike C#]

Re: Help with Adding A Row

Very nice. The second method is very useful when you are doing
"disconnect ed" 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" <mironagy@golde n.netwrote in message
news:%23yQW4W61 GHA.4796@TK2MSF TNGP06.phx.gbl. ..

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 AddInitialRecor ds()
''''Add a quick Record thru SQL - works
''''Dim myConnectionStr ing As String = _
''''"Provider=M icrosoft.Jet.OL EDB.4.0;Data Source=" & _
''''SystemFileD B & FileDBExtension
''''Dim myConnection As New OleDbConnection (myConnectionSt ring)
''''myConnectio n.Open()
''''Dim myCommand As New OleDbCommand("I NSERT INTO DBVersion
(CurVersion) VALUES (?)", _
'''' myConnection)
''''myCommand.P arameters.Add(" Param1", OleDbType.VarCh ar, 50).Value
= "2.00"
''''myCommand.E xecuteNonQuery( )
''''myCommand.D ispose()
''''myConnectio n.Close()
>
'Add a record the long way thru normal statements. - works
Dim cnADONetConnect ion As New OleDb.OleDbConn ection()
Dim myConnectionStr ing As String = _
"Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & _
SystemFileDB & FileDBExtension
cnADONetConnect ion.ConnectionS tring = myConnectionStr ing
>
cnADONetConnect ion.Open()
>
Dim daDataAdapter As New OleDb.OleDbData Adapter()
daDataAdapter = _
New OleDb.OleDbData Adapter("Select * From DBVersion",
cnADONetConnect ion)
>
>
Dim cbCommandBuilde r As OleDb.OleDbComm andBuilder
>
cbCommandBuilde r = New OleDb.OleDbComm andBuilder(daDa taAdapter)
>
Dim dtVersion As New DataTable()
Dim dtRowPosition As Integer = 0
'Fill with data
daDataAdapter.F ill(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.NewRo w()
>
rwVersion("CurV ersion") = "3.33"
>
dtVersion.Rows. Add(rwVersion)
daDataAdapter.U pdate(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("CurV ersion").GetTyp e.ToString)
>
'dtVersion.Rows (dtVersion.Rows .Count -
1)("CurVersion" ).ToString)
End If
>
'Dim blastring As String = dtVersion.Rows( 0)("CurVersion" ).ToString
>
>
Debug.WriteLine ("Done debuging")
cnADONetConnect ion.Close()
>
End Sub
>

[Miro]

Re: Help with Adding A Row

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

"Mike C#" <xyz@xyz.comwro te in message
news:d83Qg.57$p A2.16@newsfe10. lga...

Very nice. The second method is very useful when you are doing
"disconnect ed" 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" <mironagy@golde n.netwrote in message
news:%23yQW4W61 GHA.4796@TK2MSF TNGP06.phx.gbl. ..

>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 AddInitialRecor ds()
> ''''Add a quick Record thru SQL - works
> ''''Dim myConnectionStr ing As String = _
> ''''"Provider=M icrosoft.Jet.OL EDB.4.0;Data Source=" & _
> ''''SystemFileD B & FileDBExtension
> ''''Dim myConnection As New OleDbConnection (myConnectionSt ring)
> ''''myConnectio n.Open()
> ''''Dim myCommand As New OleDbCommand("I NSERT INTO DBVersion
>(CurVersion) VALUES (?)", _
> '''' myConnection)
> ''''myCommand.P arameters.Add(" Param1", OleDbType.VarCh ar,
>50).Value = "2.00"
> ''''myCommand.E xecuteNonQuery( )
> ''''myCommand.D ispose()
> ''''myConnectio n.Close()
>>
> 'Add a record the long way thru normal statements. - works
> Dim cnADONetConnect ion As New OleDb.OleDbConn ection()
> Dim myConnectionStr ing As String = _
> "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & _
> SystemFileDB & FileDBExtension
> cnADONetConnect ion.ConnectionS tring = myConnectionStr ing
>>
> cnADONetConnect ion.Open()
>>
> Dim daDataAdapter As New OleDb.OleDbData Adapter()
> daDataAdapter = _
> New OleDb.OleDbData Adapter("Select * From DBVersion",
>cnADONetConnec tion)
>>
>>
> Dim cbCommandBuilde r As OleDb.OleDbComm andBuilder
>>
> cbCommandBuilde r = New OleDb.OleDbComm andBuilder(daDa taAdapter)
>>
> Dim dtVersion As New DataTable()
> Dim dtRowPosition As Integer = 0
> 'Fill with data
> daDataAdapter.F ill(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.NewRo w()
>>
> rwVersion("CurV ersion") = "3.33"
>>
> dtVersion.Rows. Add(rwVersion)
> daDataAdapter.U pdate(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("CurV ersion").GetTyp e.ToString)
>>
> 'dtVersion.Rows (dtVersion.Rows .Count -
>1)("CurVersion ").ToString )
> End If
>>
> 'Dim blastring As String =
>dtVersion.Rows (0)("CurVersion ").ToString
>>
>>
> Debug.WriteLine ("Done debuging")
> cnADONetConnect ion.Close()
>>
> End Sub
>>

>
>