Inserting Access Table from VB.Net

13 Nov 2005 5:48 AM

roy_ware

I searched some of the previous posts and cam eup with some code I thought
would work for my application, but when I ran it, I got the following error:

An unhandled exception of type 'System.NullReferenceException' occurred in
DBLoad.exe
Additional information: Object reference not set to an instance of an object.

Here's the code (it may look familiar!) The line with the trailing
astericks is the offending line.

Private Sub DatabaseFunction()
       ' Variables
       Dim ConnAccess As OleDbConnection
       Dim da As OleDbDataAdapter
       Dim cmd As OleDbCommandBuilder
       Dim ds As DataSet
       Dim strConn As String

        ' Some flat file processing to poplate the string fields

        ' Write the record to the database
        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source =
c:\test.mdb"

        ConnAccess = New OleDbConnection(strConnection)
        Dim dr As DataRow = ds.Tables("ResidentBirthday").NewRow **********

        With dr
            .Item(0) = strUnit
            .Item(1) = strResidentFullName
            .Item(3) = strUnitPhone
            .Item(4) = strWorkPhone
        End With

        ds.Tables("ResidentBirthday").Rows.Add(dr)
        da.Update(ds, "ResidentBirthday")
        ds.AcceptChanges()

    End Sub

What did I copy wrong?????

13 Nov 2005 7:57 AM

Miha Markic [MVP C#]

You didn't create OleDbDataAdapter at all.
You should create an instance of it and configure it properly or use the
wizard to create one.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Show quoteHide quote

"roy_ware" <royw***@discussions.microsoft.com> wrote in message
news:1492FC2B-1F5E-4F4B-A20D-70D86D94DE37@microsoft.com...
>I searched some of the previous posts and cam eup with some code I thought
> would work for my application, but when I ran it, I got the following
> error:
>
> An unhandled exception of type 'System.NullReferenceException' occurred in
> DBLoad.exe
> Additional information: Object reference not set to an instance of an
> object.
>
> Here's the code (it may look familiar!) The line with the trailing
> astericks is the offending line.
>
> Private Sub DatabaseFunction()
>       ' Variables
>       Dim ConnAccess As OleDbConnection
>       Dim da As OleDbDataAdapter
>       Dim cmd As OleDbCommandBuilder
>       Dim ds As DataSet
>       Dim strConn As String
>
>        ' Some flat file processing to poplate the string fields
>
>        ' Write the record to the database
>        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source =
> c:\test.mdb"
>
>        ConnAccess = New OleDbConnection(strConnection)
>        Dim dr As DataRow = ds.Tables("ResidentBirthday").NewRow **********
>
>        With dr
>            .Item(0) = strUnit
>            .Item(1) = strResidentFullName
>            .Item(3) = strUnitPhone
>            .Item(4) = strWorkPhone
>        End With
>
>        ds.Tables("ResidentBirthday").Rows.Add(dr)
>        da.Update(ds, "ResidentBirthday")
>        ds.AcceptChanges()
>
>    End Sub
>
> What did I copy wrong?????

14 Nov 2005 2:34 AM

roy_ware

It was your source code, honey. Where did you forget to create the
OleDbDataAdapter? How about an example of HOW to "create an instance and
configure it correctly" or an idea of where to find the bloody wizard?

Sorry if I come across a bit upset - I've struggled with this for a week and
a smartass answer is NOT what I need right now.

Show quoteHide quote

"Miha Markic [MVP C#]" wrote:

> You didn't create OleDbDataAdapter at all.
> You should create an instance of it and configure it properly or use the
> wizard to create one.
> --
> Miha Markic [MVP C#]
> RightHand .NET consulting & development www.rthand.com
> Blog: http://cs.rthand.com/blogs/blog_with_righthand/
>
> "roy_ware" <royw***@discussions.microsoft.com> wrote in message
> news:1492FC2B-1F5E-4F4B-A20D-70D86D94DE37@microsoft.com...
> >I searched some of the previous posts and cam eup with some code I thought
> > would work for my application, but when I ran it, I got the following
> > error:
> >
> > An unhandled exception of type 'System.NullReferenceException' occurred in
> > DBLoad.exe
> > Additional information: Object reference not set to an instance of an
> > object.
> >
> > Here's the code (it may look familiar!) The line with the trailing
> > astericks is the offending line.
> >
> > Private Sub DatabaseFunction()
> >       ' Variables
> >       Dim ConnAccess As OleDbConnection
> >       Dim da As OleDbDataAdapter
> >       Dim cmd As OleDbCommandBuilder
> >       Dim ds As DataSet
> >       Dim strConn As String
> >
> >        ' Some flat file processing to poplate the string fields
> >
> >        ' Write the record to the database
> >        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source =
> > c:\test.mdb"
> >
> >        ConnAccess = New OleDbConnection(strConnection)
> >        Dim dr As DataRow = ds.Tables("ResidentBirthday").NewRow **********
> >
> >        With dr
> >            .Item(0) = strUnit
> >            .Item(1) = strResidentFullName
> >            .Item(3) = strUnitPhone
> >            .Item(4) = strWorkPhone
> >        End With
> >
> >        ds.Tables("ResidentBirthday").Rows.Add(dr)
> >        da.Update(ds, "ResidentBirthday")
> >        ds.AcceptChanges()
> >
> >    End Sub
> >
> > What did I copy wrong?????
>
>
>

14 Nov 2005 5:16 PM

Marina

The dataset variable 'ds' is never set to an instance of an object. Just
because you declare a variable, doesn't mean it points to an object. That is
why there is the 'New' keyword, that you use to actually create a new
object.
Nor is there any code here to add any tables to it.

I dont' think you really need a data set here.

Something like this:

Dim dt as DataTable

dt = new DataTable
Dim dr as DataRow = dt.NewRow()

But you have a lot of objects here, none of which you are instantiating.
Declaring variables is just not enough, you need to create objects, set
their properties, etc, in order for them to work properly.

Show quoteHide quote

"roy_ware" <royw***@discussions.microsoft.com> wrote in message
news:1492FC2B-1F5E-4F4B-A20D-70D86D94DE37@microsoft.com...
>I searched some of the previous posts and cam eup with some code I thought
> would work for my application, but when I ran it, I got the following
> error:
>
> An unhandled exception of type 'System.NullReferenceException' occurred in
> DBLoad.exe
> Additional information: Object reference not set to an instance of an
> object.
>
> Here's the code (it may look familiar!) The line with the trailing
> astericks is the offending line.
>
> Private Sub DatabaseFunction()
>       ' Variables
>       Dim ConnAccess As OleDbConnection
>       Dim da As OleDbDataAdapter
>       Dim cmd As OleDbCommandBuilder
>       Dim ds As DataSet
>       Dim strConn As String
>
>        ' Some flat file processing to poplate the string fields
>
>        ' Write the record to the database
>        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source =
> c:\test.mdb"
>
>        ConnAccess = New OleDbConnection(strConnection)
>        Dim dr As DataRow = ds.Tables("ResidentBirthday").NewRow **********
>
>        With dr
>            .Item(0) = strUnit
>            .Item(1) = strResidentFullName
>            .Item(3) = strUnitPhone
>            .Item(4) = strWorkPhone
>        End With
>
>        ds.Tables("ResidentBirthday").Rows.Add(dr)
>        da.Update(ds, "ResidentBirthday")
>        ds.AcceptChanges()
>
>    End Sub
>
> What did I copy wrong?????

14 Nov 2005 6:28 PM

roy_ware

ARGH! That's what I get for upgrading from VB 5 to .Net! lol! I know - I
had to upgrade because VB 5 doesn't play well with Access 2003 (and higher).

I guess this is what I get for gleaning from several different sources
trying to perform insert/update/delete functions strictly from code level.
See, my users are pretty computer illiterate - the peak of their ability is
playing Solitaire (no joke) - and I am converting my Access-developed
application to VB.Net. The texts from which I have been learning syntax are
geared towards intelligent (users, that is, not necessarily applications)
user-interface applications - for which parts of the application will work
just fine - however, there are many functions which I need to take place
strictly behind the scenes to prevent user errors. I simply haven't been
able to locate a text book with those kind of coding examples.

Does anyone know of any good books that cover using VB.Net with Access
databases programmatically?

Show quoteHide quote

"Marina" wrote:

> The dataset variable 'ds' is never set to an instance of an object. Just
> because you declare a variable, doesn't mean it points to an object. That is
> why there is the 'New' keyword, that you use to actually create a new
> object.
> Nor is there any code here to add any tables to it.
>
> I dont' think you really need a data set here.
>
> Something like this:
>
> Dim dt as DataTable
>
> dt = new DataTable
> Dim dr as DataRow = dt.NewRow()
>
> But you have a lot of objects here, none of which you are instantiating.
> Declaring variables is just not enough, you need to create objects, set
> their properties, etc, in order for them to work properly.
>
> "roy_ware" <royw***@discussions.microsoft.com> wrote in message
> news:1492FC2B-1F5E-4F4B-A20D-70D86D94DE37@microsoft.com...
> >I searched some of the previous posts and cam eup with some code I thought
> > would work for my application, but when I ran it, I got the following
> > error:
> >
> > An unhandled exception of type 'System.NullReferenceException' occurred in
> > DBLoad.exe
> > Additional information: Object reference not set to an instance of an
> > object.
> >
> > Here's the code (it may look familiar!) The line with the trailing
> > astericks is the offending line.
> >
> > Private Sub DatabaseFunction()
> >       ' Variables
> >       Dim ConnAccess As OleDbConnection
> >       Dim da As OleDbDataAdapter
> >       Dim cmd As OleDbCommandBuilder
> >       Dim ds As DataSet
> >       Dim strConn As String
> >
> >        ' Some flat file processing to poplate the string fields
> >
> >        ' Write the record to the database
> >        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source =
> > c:\test.mdb"
> >
> >        ConnAccess = New OleDbConnection(strConnection)
> >        Dim dr As DataRow = ds.Tables("ResidentBirthday").NewRow **********
> >
> >        With dr
> >            .Item(0) = strUnit
> >            .Item(1) = strResidentFullName
> >            .Item(3) = strUnitPhone
> >            .Item(4) = strWorkPhone
> >        End With
> >
> >        ds.Tables("ResidentBirthday").Rows.Add(dr)
> >        da.Update(ds, "ResidentBirthday")
> >        ds.AcceptChanges()
> >
> >    End Sub
> >
> > What did I copy wrong?????
>
>
>