Question Object Reference

[stuaz]

Hi I have the following bit of code:

        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dsNewRow As DataRow

        dsNewRow = ds.Tables("DeliveryNotes").NewRow

        dsNewRow.Item("id") = "1"
        dsNewRow.Item("date") = txtRef.Text
        dsNewRow.Item("ref") = txtRef.Text
        dsNewRow.Item("driver") = txtDriver.Text
        dsNewRow.Item("vehicle") = txtVehicle.Text
        dsNewRow.Item("customer") = txtCustomer.Text
        dsNewRow.Item("acnumber") = txtACNum.Text
        dsNewRow.Item("charge") = txtCharge.Text
        dsNewRow.Item("contractno1") = txtContractNo1.Text
        dsNewRow.Item("collect_details") = txtCollect_details.Text
        dsNewRow.Item("contractno2") = txtContractNo2.Text
        dsNewRow.Item("deliver_details") = txtDeliver_details.Text
        dsNewRow.Item("goods_details") = txtGoods_details.Text
        dsNewRow.Item("meter_reading") = txtMeter_reading.Text
        dsNewRow.Item("serial_numbers") = txtSerial_Numbers.Text

        ds.Tables("DeliveryNotes").Rows.Add(dsNewRow)

        da.Update(ds, "DeliveryNotes")

        MsgBox("New Record added to the Database")

And I cant for the life of me work out why I get a:

Object reference not set to an instance of an object.

error on row:

dsNewRow = ds.Tables("DeliveryNotes").NewRow

The goal is to simply add a new row to a Access2003 database file.

Thanks in advance.

 

[jmcilhinney]

Presumably there is no table with that name in the DataSet.

 

[stuaz]

Presumably there is no table with that name in the DataSet.

Thank you for your reply.

       Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dsNewRow As DataRow

        da.Fill(ds, "DeliveryNotes")

        dsNewRow = ds.Tables("DeliveryNotes").NewRow

        dsNewRow.Item("id") = "1"
        dsNewRow.Item("date") = txtRef.Text
        dsNewRow.Item("ref") = txtRef.Text
        dsNewRow.Item("driver") = txtDriver.Text
        dsNewRow.Item("vehicle") = txtVehicle.Text
        dsNewRow.Item("customer") = txtCustomer.Text
        dsNewRow.Item("acnumber") = txtACNum.Text
        dsNewRow.Item("charge") = txtCharge.Text
        dsNewRow.Item("contractno1") = txtContractNo1.Text
        dsNewRow.Item("collect_details") = txtCollect_details.Text
        dsNewRow.Item("contractno2") = txtContractNo2.Text
        dsNewRow.Item("deliver_details") = txtDeliver_details.Text
        dsNewRow.Item("goods_details") = txtGoods_details.Text
        dsNewRow.Item("meter_reading") = txtMeter_reading.Text
        dsNewRow.Item("serial_numbers") = txtSerial_Numbers.Text

        ds.Tables("DeliveryNotes").Rows.Add(dsNewRow)

        da.Update(ds, "DeliveryNotes")

        MsgBox("New Record added to the Database")

I added the

da.Fill(ds, "DeliveryNotes")

To the above code, and now the program gets further, and fails on:

da.Update(ds, "DeliveryNotes")

It states: "Syntax error in INSERT INTO statement"


All I want is a simple button to add to a Access Database. I can read the database no problem, but adding is giving me trouble...

 

[jmcilhinney]

That would be because one or more of your column names is a reserved word. In that case you have four choices:

1. Change the offending column name(s).

2. Change your query and don't use a wild card for the column list. Type out the column list in full and escape, i.e. wrap in brackets, the names that are reserved words. The command builder should then follow suit in the SQL code that it generates.

3. Get the commands generated by the command builder before calling Update and edit them yourself.

4. Don't use a command builder.

 

[stuaz]

That would be because one or more of your column names is a reserved word. In that case you have four choices:

1. Change the offending column name(s).

2. Change your query and don't use a wild card for the column list. Type out the column list in full and escape, i.e. wrap in brackets, the names that are reserved words. The command builder should then follow suit in the SQL code that it generates.

3. Get the commands generated by the command builder before calling Update and edit them yourself.

4. Don't use a command builder.

Hi, Thanks, I just did as you suggested and refered to each column in the select statement.

Thanks again.