11 Replies - 11459 Views - Last Post: 13 March 2011 - 01:20 PM Rate Topic: -----

#1 PseudoPrawn  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 09-March 11

VB.net 2010/Accdb 2010 not saving changes to Database

Posted 10 March 2011 - 11:37 AM

Code runs through with no errors, but doesn't actually save the new row to the database, help?!

    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 temp2 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)
        DataSetNewRow.AcceptChanges()
        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") = CInt(HomeScreen.txtOrderH.Text)
        DataSetNewRow("Width") = CInt(HomeScreen.txtOrderW.Text)
        If HomeScreen.cmbOrderUnits.Text = "in" Then
            DataSetNewRow("Inches") = "True"
        Else
            DataSetNewRow("Inches") = "False"
        End If
        DataSetNewRow("Price") = CInt(HomeScreen.txtOrderPrice.Text)
        DataSet.Tables("OrderDatabase").Rows.Add(DataSetNewRow)
        DataSetNewRow.AcceptChanges()
        DataAdapter.Update(DataSet, "OrderDatabase")




Let me know if you need any more info, sorry for being such a noob!

Cheers

Is This A Good Question/Topic? 0
  • +

Replies To: VB.net 2010/Accdb 2010 not saving changes to Database

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9585
  • View blog
  • Posts: 36,323
  • Joined: 12-June 08

Re: VB.net 2010/Accdb 2010 not saving changes to Database

Posted 10 March 2011 - 12:06 PM

Where's your update sql statement? What does it look like?
Was This Post Helpful? 0
  • +
  • -

#3 demausdauth  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 182
  • View blog
  • Posts: 664
  • Joined: 03-February 10

Re: VB.net 2010/Accdb 2010 not saving changes to Database

Posted 10 March 2011 - 12:06 PM

You need to create the commands (Select, Insert and Update) for the dataadapter. Also I am pretty sure that you cannot call Update() in order to insert a new row, just doesn't make sense.

Check this link out. Yes it references the SQL version, which shouldn't be too hard to convert to OleDb.
Was This Post Helpful? 0
  • +
  • -

#4 AdamSpeight2008  Icon User is offline

  • MrCupOfT
  • member icon


Reputation: 2271
  • View blog
  • Posts: 9,500
  • Joined: 29-May 08

Re: VB.net 2010/Accdb 2010 not saving changes to Database

Posted 10 March 2011 - 12:15 PM

Have a look at my [WalkThrough] UserNames & Password Database. It shows you how to use the Dataset Designer available in Visual Studio.
Was This Post Helpful? 0
  • +
  • -

#5 PseudoPrawn  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 09-March 11

Re: VB.net 2010/Accdb 2010 not saving changes to Database

Posted 10 March 2011 - 01:02 PM

Are you sure it shouldn't do it for me?
See:
http://msdn.microsof...ter.update.aspx
Was This Post Helpful? 0
  • +
  • -

#6 PseudoPrawn  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 09-March 11

Re: VB.net 2010/Accdb 2010 not saving changes to Database

Posted 11 March 2011 - 06:48 AM

*BUMP*
Anyone?
This should work fine!
Was This Post Helpful? 0
  • +
  • -

#7 PseudoPrawn  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 09-March 11

Re: VB.net 2010/Accdb 2010 not saving changes to Database

Posted 11 March 2011 - 07:13 AM

Okay, if you remove the "DataSetNewRow.Update()" then I get the error "Update requires a valid InsertCommand when passed DataRow collection with new rows.".

I understand something is missing, but after hours of research online I can't say what it is. I've got lots of samples of code here that read EXACTLY the same as mine, and work fine.

Okay, if you remove the "DataSetNewRow.Update()" then I get the error "Update requires a valid InsertCommand when passed DataRow collection with new rows.".

I understand something is missing, but after hours of research online I can't say what it is. I've got lots of samples of code here that read EXACTLY the same as mine, and work fine.
Was This Post Helpful? 0
  • +
  • -

#8 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9585
  • View blog
  • Posts: 36,323
  • Joined: 12-June 08

Re: VB.net 2010/Accdb 2010 not saving changes to Database

Posted 11 March 2011 - 07:59 AM

Okay... so if you use the adapter it looks at the row.state. Rows that are 'added' get routed to the 'insert' part of the adapter. Rows that are modified get routed to the 'update' part. Rows removed to the delete. You need a specific sql statement for all of them unless you are going to only get those specific row states.
Was This Post Helpful? 0
  • +
  • -

#9 PseudoPrawn  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 09-March 11

Re: VB.net 2010/Accdb 2010 not saving changes to Database

Posted 12 March 2011 - 05:27 AM

How about this?

 Connection.ConnectionString = dbProvider & dbSource 'create the connection string
        Connection.Open()

        Dim CommandBuilder As New OleDb.OleDbCommandBuilder(DataAdapter)

        Sql = "SELECT * FROM CustomerDatabase"
        DataAdapter = New OleDb.OleDbDataAdapter(Sql, Connection)
        DataAdapter.Fill(DataSet, "CustomerDatabase")
        Dim DataSetNewRow As DataRow = DataSet.Tables("CustomerDatabase").NewRow

        DataSetNewRow("FirstName") = HomeScreen.txtCustFName.Text
        DataSetNewRow("Surname") = HomeScreen.txtCustSName.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.InsertCommand.CommandText = "INSERT INTO CustomerDatabase (CustomerID, FirstName, Surname, Landline, Mobile, Email, Address1, Address2, City, County, Postcode) VALUES (" & 0 & HomeScreen.txtCustFName.Text & "," & HomeScreen.txtCustSName.Text & "," & HomeScreen.txtCustLLine.Text & "," & HomeScreen.txtCustMob.Text & "," & HomeScreen.txtCustEmail.Text & "," & HomeScreen.txtCustAdd1.Text & "," & HomeScreen.txtCustAdd2.Text & "," & HomeScreen.txtCustAddCi.Text & "," & HomeScreen.txtCustAddCo.Text & "," & HomeScreen.txtCustPCode.Text & ")"
        DataAdapter.Update(DataSet, "CustomerDatabase")
        Connection.Close()



I get a nullreferenceexception againt the DataAdapter.InsertCommand.CommandText line
Was This Post Helpful? 0
  • +
  • -

#10 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9585
  • View blog
  • Posts: 36,323
  • Joined: 12-June 08

Re: VB.net 2010/Accdb 2010 not saving changes to Database

Posted 12 March 2011 - 01:29 PM

Well yeah that's not an object yet! Make a new command object... fill it with your insert information.. and assign the adapter's insert object. That or instantiate your adapter's insert object.
Was This Post Helpful? 0
  • +
  • -

#11 PseudoPrawn  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 09-March 11

Re: VB.net 2010/Accdb 2010 not saving changes to Database

Posted 13 March 2011 - 12:40 PM

Fancy running that one by me again, keeping in mind I'm quite a noob? Sorry!
Was This Post Helpful? 0
  • +
  • -

#12 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9585
  • View blog
  • Posts: 36,323
  • Joined: 12-June 08

Re: VB.net 2010/Accdb 2010 not saving changes to Database

Posted 13 March 2011 - 01:20 PM

Okay.. so objects are classes that have methods and members and what not. You declare an instance of them with 'new'. Now objects can have properties that are objects. In this case the insert part of the adapter is an object. It needs to be instantiated. aka.. declared with a 'new'.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1