Code runs but database doesn't update

Code runs but database doesn't update

• Favonio

  Jules,

  You must add the row to the Parts table in the DataSet. In the code you have now, you add a row to a new table, and do nothing with it after that.

  Instead of creating a new PartsDataTable, use the one in the PartsDataSet

  ds.PartsDataTable.AddPartsRow(row)

  Sven

  
  
  

• rccopter

  Thanks Sven, have updated the code as follows, but still same problem.

  Dim strPN As String

  Dim strDesc As String

  strPN = Me.txtPN.Text

  strDesc = Me.txtDesc.Text

  PartConnection.Open()

  Dim ds As New PartDataSet

  strSQL = "SELECT PartNo, PartDesc FROM Parts"

  partDataAdapter.SelectCommand = New OleDb.OleDbCommand(strSQL, PartConnection)

  partDataAdapter.Fill(ds, "Parts")

  Dim table As New PartDataSet.PartsDataTable

  Dim row As PartDataSet.PartsRow

  row = table.NewPartsRow

  row.PartNo = strPN

  row.PartDesc = strDesc

  table.AddPartsRow(row)

  partDataAdapter.Update(ds, "Parts")

  ds.AcceptChanges()

  PartConnection.Close()

  
  
  

• Ken.Saraf

  Sorry for this Sohail, I've been off work for a week and only just had a chance to look at your response.

  I'm afraid this doesn't work either. The only options I'm getting if I type in PartDataSet. are "PartsDataTable", "PartsRow", "PartsRowChangeEvent" and "PartsRowChangeEventHandler".

  I think the problem must be my approach - I must be doing something fundamentally wrong and should take another approach Am I right What's the best way of doing this

  Julia

  
  
  

• NickWebb

  Hi!

  Try this code>>>>>>>>>>>>>>>

  Dim strPN As String

  Dim strDesc As String

  strPN = Me.txtPN.Text

  strDesc = Me.txtDesc.Text

  PartConnection.Open()

  Dim ds As New PartDataSet

  strSQL = "SELECT PartNo, PartDesc FROM Parts"

  partDataAdapter.SelectCommand = New OleDb.OleDbCommand(strSQL, PartConnection)

  partDataAdapter.Fill(ds, "Parts")

  Dim table As New ds.Tables("Parts")

  Dim row As PartDataSet.PartsRow

  row = table.NewPartsRow

  row.PartNo = strPN

  row.PartDesc = strDesc

  table.AddPartsRow(row)

  partDataAdapter.Update(ds, "Parts")

  ds.AcceptChanges()

  PartConnection.Close()

  Sohail.

  
  
  

• Erkki

  Sorry all, I checked again and I don't have an insert command - this wasn't a dataadapter I created using the GUI interface. I did say I was new to this didn't I ;-)....

  I'll try that and see if that works.

  
  
  

• Jonathan Sealby

  Thanks, I really appreciate your help on this, unfortunately, the code won't even compile.

  I get errors in the following places:

  da.Fill(ds.PartsDataTable) - error PartsDataTable is a type in PartDataSet and cannot be used as an expression

  Dim row As PartDataSet.PartsRow = ds.PartsDataTable.NewPartsRow - error Reference to a non-shared member requires an object reference

  ds.PartsDataTable.AddPartsRow(row) - error Reference to a non-shared member requires an object reference

  da.Update(ds.PartsDataTable) - error PartsDataTable is a type in PartDataSet and cannot be used as an expression

  Julia

  
  
  

• WCF_Thomas80

  Sorry Sohail, this still doesn't work, I appreciate your help on this! The problem I get is:

  partDataAdapter.Fill(PartDataSet.PartsDataTable)

  The blue error line appears under PartDataSet.PartsDataTable, error is: Reference to a non-shared member requires an object reference. Have I set up the dataset incorrectly The GUI command dataset name is PartDataSet1 if that helps.

  Julia

  
  
  

• Nate Skousen

  Hi Julia!

  Change PartsDataTable to Parts in this routine wherever you find it then it should work fine. Otherwise don't hesitate to ask.

  cheers.

  Sohail.

  
  
  

• Pape

  The problem is the order in which you are executing the command.

  The Update() method of the dataset will only process rows that are modifed (added, changed or deleted). When you call AcceptChanges(), you will set the status of all rows in the DataSet (or DataTable) to unchanged. So calling AcceptChanges() BEFORE you call Update(), will cause the Update method to do nothing.

  Try the following sequence:

  1. Fill the dataset with the Fill command.

  2. Add your row to the dataset.

  3. Call the Update method on the DataSet

  4. Call AcceptChanges to mark the rows as unchanged

  Sven

  
  
  

• Poprocks83

  Your DataAdapter also needs the appropriate Insert Command. You may either build this by hand, or use the CommandBuilder Object.
  
  

• GvS

  Thanks Sohail, but this doesn't work.... I can't change the code as when I get to Dim table as New ds. intellisence shows no properties of ds. Could that be the problem
  
  

• KSchlegel

  Hi JulesB!

  Try this code>>>>>>>>>>>>>>>

  Dim strPN As String

  Dim strDesc As String

  strPN = Me.txtPN.Text

  strDesc = Me.txtDesc.Text

  ''''''''''''Dim ds As New PartDataSet''''''''''''Dont use this

  strSQL = "SELECT PartNo, PartDesc FROM Parts"

  partDataAdapter.SelectCommand = New OleDb.OleDbCommand(strSQL, PartConnection)

  PartConnection.Open()

  partDataAdapter.Fill(PartDataSet.PartsDataTable)

  ''''''''Dim table As New ds.Tables("Parts")''''''''''Dont use this

  Dim row As PartDataSet.PartsRow

  row = PartDataSet.PartsDataTable.NewPartsRow

  row.PartNo = strPN

  row.PartDesc = strDesc

  PartDataSet.PartsDataTable.AddPartsRow(row)

  partDataAdapter.Update(PartDataSet.PartsDataTable)

  PartDataSet.AcceptChanges()

  PartConnection.Close()

  Sohail.

  
  
  

• Eric Kehr

  Thanks Brandon, my data adapter does have an inser command. I used the GUI command, so the insert/delete/update etc commands were created automatically for me.
  
  

• AhmetBolac

  Hi Julia!

  Try the following code hopefully this will solve your problem, if it doesn't just copy the complete excetion that you are getting

  Dim strPN As String

  Dim strDesc As String

  strPN = Me.txtPN.Text

  strDesc = Me.txtDesc.Text

  Dim ds As New PartDataSet

  PartConnection.Open()

  strSQL = "SELECT * FROM Parts"

  Dim da AS System.Data.OleDb.OleDbDataAdapter = new System.Data.OleDb.OleDbDataAdapter(strSQL, PartConnection)

  Dim cb AS System.Data.OleDb.OleDbCommandBuilder = new System.Data.OleDb.OleDbCommandBuilder(da)

  da.Fill(ds.PartsDataTable)

  Dim row As PartDataSet.PartsRow = ds.PartsDataTable.NewPartsRow

  row.PartNo = strPN

  row.PartDesc = strDesc

  ds.PartsDataTable.AddPartsRow(row)

  da.Update(ds.PartsDataTable)

  ds.AcceptChanges()

  PartConnection.Close()

  cheers

  Sohail.

  
  
  
• Code runs but database doesn't update