[PseudoPrawn]

Hi there,
I'm doing a database project with accdb 2010, and VB.net 2010.
It's working fine reading and updating the dataset and database, but I can't add a new row. Here's the code, it come up with this error: "NullReferenceException was unhandled. Object reference not set to instance of an object" on the line that reads "DataSetNewRow = DataSet.Tables("CustomerDatabase").NewRow".

Dim Connection As New OleDb.OleDbConnection
    Dim dbProvider As String = "Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password=cSSO3sEEhrM4vY3je0qC;" 'connect to the access 2010 database, including DB Password
    Dim dbSource As String = "Data Source = CourseworkDatabase.accdb" 'at this path
    Dim DataSet As New DataSet
    Dim Sql As String
    Dim DataAdapter As New OleDb.OleDbDataAdapter
    Dim inc As Integer
    Dim temp As String
    Dim CommandBuilder As New OleDb.OleDbCommandBuilder(DataAdapter)
    Dim DataSetNewRow As DataRow

        DataSetNewRow = DataSet.Tables("CustomerDatabase").NewRow
        DataSetNewRow.Item("FirstName") = HomeScreen.txtCustID.Text
        DataSetNewRow.Item("Surname") = HomeScreen.txtCustFName.Text
        DataSetNewRow.Item("Landline") = HomeScreen.txtCustLLine.Text
        DataSetNewRow.Item("Mobile") = HomeScreen.txtCustMob.Text
        DataSetNewRow.Item("Email") = HomeScreen.txtCustEmail.Text
        DataSetNewRow.Item("Address1") = HomeScreen.txtCustAdd1.Text
        DataSetNewRow.Item("Address2") = HomeScreen.txtCustAdd2.Text
        DataSetNewRow.Item("City") = HomeScreen.txtCustAddCi.Text
        DataSetNewRow.Item("County") = HomeScreen.txtCustAddCo.Text
        DataSetNewRow.Item("Postcode") = HomeScreen.txtCustPCode.Text
        DataSet.Tables("CustomerDatabase").Rows.Add(DataSetNewRow)
        DataAdapter.Update(DataSet, "CustomerDatabase")

        DataSetNewRow = DataSet.Tables("OrderDatabase").NewRow
        DataSetNewRow.Item("DateCreated") = HomeScreen.dtpOrderCreate.Text
        DataSetNewRow.Item("DateTBC") = HomeScreen.dtpOrderTBC.Text
        DataSetNewRow.Item("Delivery") = HomeScreen.chkOrderDel.CheckState
        DataSetNewRow.Item("DeliveryDate") = HomeScreen.dtpOrderDDate.Text
        DataSetNewRow.Item("Description") = HomeScreen.txtOrderDesc.Text
        DataSetNewRow.Item("Frame") = HomeScreen.chkOrderFrame.CheckState
        DataSetNewRow.Item("Type") = HomeScreen.cmbOrderType.Text
        DataSetNewRow.Item("Size") = HomeScreen.cmbOrderSize.Text
        DataSetNewRow.Item("Height") = HomeScreen.txtOrderH.Text
        DataSetNewRow.Item("Width") = HomeScreen.txtOrderW.Text
        If HomeScreen.cmbOrderUnits.Text = "in" Then
            DataSetNewRow.Item("Inches") = "True"
        Else
            DataSetNewRow.Item("Inches") = "False"
        End If
        DataSetNewRow.Item("Price") = HomeScreen.txtOrderPrice.Text
        DataSet.Tables("OrderDatabase").Rows.Add(DataSetNewRow)
        DataAdapter.Update(DataSet, "OrderDatabase")

        MsgBox("New customer and order added to the database!")
    End Sub 

Let me know if I'm doing something stupid, or missing something! All the fields in both tables are added to, except the Customer and Order ID fields, which are set to autonumber in the accdb. Maybe this is the problem, if so, what do I have to do?

Thank you!

[raziel_]

your dataset is not populated because your OleDbDataAdapter dont execute query to the db. The container of OleDbDataAdapter is Nothing and this is why the error shows up try this:

        Dim DataAdapter As New OleDb.OleDbDataAdapter(strSQL, myconnection)

where strSQL is the query and myconnection is the connection to your DB

This post has been edited by NoBrain: 09 March 2011 - 08:37 AM

[PseudoPrawn]

NoBrain, on 09 March 2011 - 08:36 AM, said:

your dataset is not populated because your OleDbDataAdapter dont execute query to the db. The container of OleDbDataAdapter is Nothing and this is why the error shows up try this:

        Dim DataAdapter As New OleDb.OleDbDataAdapter(strSQL, myconnection)

where strSQL is the query and myconnection is the connection to your DB

Thank you, have put that in, different error now, on the line DataAdapter.update(DataSet, "CustomerDatabase") : "InvalidOperationException, Update requires a valid InsertCommand when passed DataRow collection with new rows."

Here is the updated code

 Dim Connection As New OleDb.OleDbConnection
    Dim dbProvider As String = "Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password=cSSO3sEEhrM4vY3je0qC;" 'connect to the access 2010 database, including DB Password
    Dim dbSource As String = "Data Source = CourseworkDatabase.accdb" 'at this path
    Dim DataSet As New DataSet
    Dim Sql As String
    Dim DataAdapter As New OleDb.OleDbDataAdapter
    Dim inc As Integer
    Dim temp As String
Connection.ConnectionString = dbProvider & dbSource 'create the connection string
        Connection.Open()
        Dim CommandBuilder As New OleDb.OleDbCommandBuilder(DataAdapter)
        Dim DataSetNewRow As DataRow

        Sql = "SELECT * FROM CustomerDatabase"
        DataAdapter = New OleDb.OleDbDataAdapter(Sql, Connection)
        DataAdapter.Fill(DataSet, "CustomerDatabase")

        DataSetNewRow = DataSet.Tables("CustomerDatabase").NewRow()
        DataSetNewRow("FirstName") = HomeScreen.txtCustID.Text
        DataSetNewRow("Surname") = HomeScreen.txtCustFName.Text
        DataSetNewRow("Landline") = HomeScreen.txtCustLLine.Text
        DataSetNewRow("Mobile") = HomeScreen.txtCustMob.Text
        DataSetNewRow("Email") = HomeScreen.txtCustEmail.Text
        DataSetNewRow("Address1") = HomeScreen.txtCustAdd1.Text
        DataSetNewRow("Address2") = HomeScreen.txtCustAdd2.Text
        DataSetNewRow("City") = HomeScreen.txtCustAddCi.Text
        DataSetNewRow("County") = HomeScreen.txtCustAddCo.Text
        DataSetNewRow("Postcode") = HomeScreen.txtCustPCode.Text
        DataSet.Tables("CustomerDatabase").Rows.Add(DataSetNewRow)
        DataAdapter.Update(DataSet, "CustomerDatabase")


        Sql = "SELECT * FROM OrderDatabase"
        DataAdapter = New OleDb.OleDbDataAdapter(Sql, Connection)
        DataAdapter.Fill(DataSet, "OrderDatabase")

        DataSetNewRow = DataSet.Tables("OrderDatabase").NewRow
        DataSetNewRow("DateCreated") = HomeScreen.dtpOrderCreate.Text
        DataSetNewRow("DateTBC") = HomeScreen.dtpOrderTBC.Text
        DataSetNewRow("Delivery") = HomeScreen.chkOrderDel.CheckState
        DataSetNewRow("DeliveryDate") = HomeScreen.dtpOrderDDate.Text
        DataSetNewRow("Description") = HomeScreen.txtOrderDesc.Text
        DataSetNewRow("Frame") = HomeScreen.chkOrderFrame.CheckState
        DataSetNewRow("Type") = HomeScreen.cmbOrderType.Text
        DataSetNewRow("Size") = HomeScreen.cmbOrderSize.Text
        DataSetNewRow("Height") = HomeScreen.txtOrderH.Text
        DataSetNewRow("Width") = HomeScreen.txtOrderW.Text
        If HomeScreen.cmbOrderUnits.Text = "in" Then
            DataSetNewRow("Inches") = "True"
        Else
            DataSetNewRow("Inches") = "False"
        End If
        DataSetNewRow("Price") = HomeScreen.txtOrderPrice.Text
        DataSet.Tables("OrderDatabase").Rows.Add(DataSetNewRow)
        DataAdapter.Update(DataSet, "OrderDatabase")
     End Sub

[raziel_]

See i dont recall correct but i think you need to use the command builder check this link out:
http://www.eggheadca...atecommand.aspx

Also another thing your DataSet dont name your local variable as the original object call its bad habit and can cause you lots of problems

This post has been edited by NoBrain: 09 March 2011 - 01:35 PM