Help with Adding A Row

4 Sep 2006 9:51 PM

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

5 Sep 2006 1:48 AM

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()

5 Sep 2006 3:12 AM

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

Show quoteHide quote

"Mike C#" <x**@xyz.com> wrote 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 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()
>
>

5 Sep 2006 4:14 AM

Mike C#

"Miro" <miron***@golden.net> wrote in message
news:u8bkpjJ0GHA.3440@TK2MSFTNGP06.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.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.

Show quoteHide quote

> "Mike C#" <x**@xyz.com> wrote 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 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()
>>
>>
>
>

5 Sep 2006 2:26 PM

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

Show quoteHide quote

"Mike C#" <x**@xyz.com> wrote in message
news:zW6Lg.365$7U4.229@newsfe12.lga...
>
> "Miro" <miron***@golden.net> wrote in message
> news:u8bkpjJ0GHA.3440@TK2MSFTNGP06.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.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.
>
>> "Mike C#" <x**@xyz.com> wrote 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 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()
>>>
>>>
>>
>>
>
>

14 Sep 2006 2:31 AM

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

20 Sep 2006 4:07 AM

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 :)

Show quoteHide quote

"Miro" <miron***@golden.net> wrote in message
news:%23yQW4W61GHA.4796@TK2MSFTNGP06.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 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
>

20 Sep 2006 1:27 PM

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

Show quoteHide quote

"Mike C#" <x**@xyz.com> wrote in message
news:d83Qg.57$pA2.16@newsfe10.lga...
> 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" <miron***@golden.net> wrote in message
> news:%23yQW4W61GHA.4796@TK2MSFTNGP06.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 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
>>
>
>