Saving data to a dataset that has been programmatically configured
I am creating a form where I need to programmatically set bindings and dataset (since it is a variable and different documents can be loaded into it). I was able to get a form to upload any information to DataGridView
, and also load this information from DataGridView
into some text fields for structured editing:
However, I am struggling to get the edited information to save in the database. He won't even update DataGridView
anything. Here's the code I'm currently using:
Imports System.Data.SqlClient
Imports System.Data.OleDb
Module DataGridView_Setup
Public Sub Set_Datasource(mode As Integer)
Dim connString As String = My.Settings.Database_String
Dim myConnection As OleDbConnection = New OleDbConnection
myConnection.ConnectionString = connString
' create a data adapter
Dim da As OleDbDataAdapter = New OleDbDataAdapter("SELECT ID, [Name Of Person], [SAP Job Number], [Site Name], [Asset Description], [Spares Supplier], [Supplier Contact Name], [Supplier Contact Phone Number], [Supplier Contact Email], [Spares Description], [Part Number], [Quantity To Order], Cost, [Request Date], [Date Ordered], [Ordered By], [Invoice Received], [Invoice Paid], [Method Of Payment], [Date Item Received], [Additional Comments], [Quote Attatchment] FROM Spares", myConnection)
' create a new dataset
Dim ds As DataSet = New DataSet
' fill dataset
da.Fill(ds, "Spares")
Main.DataGridView1.DataSource = ds.Tables(0)
Main.DataGridView1.AllowUserToAddRows = False
'Set Site Listbox
Dim SiteString = My.Settings.SETTINGS_SiteNames
Dim SiteBox = Main.VIEW_Site.Items
SiteBox.Clear()
Do Until SiteString = ""
Dim ActiveSiteName = Left(SiteString, InStr(SiteString, "¦"))
ActiveSiteName = ActiveSiteName.Remove(ActiveSiteName.Length - 1)
With SiteBox
.Add(ActiveSiteName)
End With
SiteString = Replace(SiteString, ActiveSiteName + "¦", "")
Loop
'Set DataBindings
Main.VIEW_Ref.DataBindings.Clear()
Main.VIEW_Ref.DataBindings.Add(New Binding("Text", ds, "Spares.ID", False, DataSourceUpdateMode.OnPropertyChanged))
Main.VIEW_NameOfPerson.DataBindings.Clear()
Main.VIEW_NameOfPerson.DataBindings.Add(New Binding("Text", ds, "Spares.Name Of Person", False, DataSourceUpdateMode.OnPropertyChanged))
Main.VIEW_SAPJobNo.DataBindings.Clear()
Main.VIEW_SAPJobNo.DataBindings.Add(New Binding("Text", ds, "Spares.SAP Job Number", False, DataSourceUpdateMode.OnPropertyChanged))
Main.VIEW_Site.DataBindings.Clear()
Main.VIEW_Site.DataBindings.Add(New Binding("Text", ds, "Spares.Site Name", False, DataSourceUpdateMode.OnPropertyChanged))
Main.VIEW_AssetDesc.DataBindings.Clear()
Main.VIEW_AssetDesc.DataBindings.Add(New Binding("Text", ds, "Spares.Asset Description", False, DataSourceUpdateMode.OnPropertyChanged))
Main.VIEW_SparesSupplier.DataBindings.Clear()
Main.VIEW_SparesSupplier.DataBindings.Add(New Binding("Text", ds, "Spares.Spares Supplier", False, DataSourceUpdateMode.OnPropertyChanged))
Main.VIEW_SupplierContactName.DataBindings.Clear()
Main.VIEW_SupplierContactName.DataBindings.Add(New Binding("Text", ds, "Spares.Supplier Contact Name", False, DataSourceUpdateMode.OnPropertyChanged))
Main.VIEW_SupplierContactNumber.DataBindings.Clear()
Main.VIEW_SupplierContactNumber.DataBindings.Add(New Binding("Text", ds, "Spares.Supplier Contact Phone Number", False, DataSourceUpdateMode.OnPropertyChanged))
Main.VIEW_SupplierContactNumber.DataBindings.Clear()
Main.VIEW_SupplierContactNumber.DataBindings.Add(New Binding("Text", ds, "Spares.Supplier Contact Phone Number", False, DataSourceUpdateMode.OnPropertyChanged))
Main.VIEW_SupplierContactEmail.DataBindings.Clear()
Main.VIEW_SupplierContactEmail.DataBindings.Add(New Binding("Text", ds, "Spares.Supplier Contact Email", False, DataSourceUpdateMode.OnPropertyChanged))
Main.VIEW_SparesDesc.DataBindings.Clear()
Main.VIEW_SparesDesc.DataBindings.Add(New Binding("Text", ds, "Spares.Spares Description", False, DataSourceUpdateMode.OnPropertyChanged))
Main.VIEW_PartNumber.DataBindings.Clear()
Main.VIEW_PartNumber.DataBindings.Add(New Binding("Text", ds, "Spares.Part Number", False, DataSourceUpdateMode.OnPropertyChanged))
Main.VIEW_QuantityToOrder.DataBindings.Clear()
Main.VIEW_QuantityToOrder.DataBindings.Add(New Binding("Text", ds, "Spares.Quantity To Order", False, DataSourceUpdateMode.OnPropertyChanged))
Main.VIEW_CostEach.DataBindings.Clear()
Main.VIEW_CostEach.DataBindings.Add(New Binding("Text", ds, "Spares.Cost", False, DataSourceUpdateMode.OnPropertyChanged))
Main.VIEW_DateRequested.DataBindings.Clear()
Main.VIEW_DateRequested.DataBindings.Add(New Binding("Text", ds, "Spares.Request Date", False, DataSourceUpdateMode.OnPropertyChanged))
Main.VIEW_DateOrdered.DataBindings.Clear()
Main.VIEW_DateOrdered.DataBindings.Add(New Binding("Text", ds, "Spares.Date Ordered", False, DataSourceUpdateMode.OnPropertyChanged))
Main.VIEW_OrderedBy.DataBindings.Clear()
Main.VIEW_OrderedBy.DataBindings.Add(New Binding("Text", ds, "Spares.Ordered By", False, DataSourceUpdateMode.OnPropertyChanged))
Main.VIEW_InvoiceReceivedDate.DataBindings.Clear()
Main.VIEW_InvoiceReceivedDate.DataBindings.Add(New Binding("Text", ds, "Spares.Invoice Received", False, DataSourceUpdateMode.OnPropertyChanged))
Main.VIEW_InvoicePaidDate.DataBindings.Clear()
Main.VIEW_InvoicePaidDate.DataBindings.Add(New Binding("Text", ds, "Spares.Invoice Paid", False, DataSourceUpdateMode.OnPropertyChanged))
DataGridView_Setup.BindingUpdates()
End Sub
Public Sub BindingUpdates()
Dim curr As DataGridViewRow = Main.DataGridView1.CurrentRow
Main.VIEW_Ref.Text = curr.Cells("ID").Value
Main.VIEW_NameOfPerson.Text = curr.Cells("Name Of Person").Value
Main.VIEW_SAPJobNo.Text = curr.Cells("SAP Job Number").Value
Main.VIEW_Site.Text = curr.Cells("Site Name").Value
Main.VIEW_AssetDesc.Text = curr.Cells("Asset Description").Value
Main.VIEW_SparesSupplier.Text = curr.Cells("Spares Supplier").Value
Main.VIEW_SupplierContactName.Text = curr.Cells("Supplier Contact Name").Value
Main.VIEW_SupplierContactNumber.Text = curr.Cells("Supplier Contact Phone Number").Value
Main.VIEW_SupplierContactEmail.Text = curr.Cells("Supplier Contact Email").Value
Main.VIEW_SparesDesc.Text = curr.Cells("Spares Description").Value
Main.VIEW_PartNumber.Text = curr.Cells("Part Number").Value
Main.VIEW_QuantityToOrder.Text = curr.Cells("Quantity To Order").Value
Main.VIEW_CostEach.Text = "£" + CStr(curr.Cells("Cost").Value)
Main.VIEW_DateRequested.Text = curr.Cells("Request Date").Value
'Handle DBNULL From now on
If IsDBNull(curr.Cells("Date Ordered").Value) = True Then
With Main.VIEW_DateOrdered
.Text = "Not Ordered Yet"
.BackColor = Color.LightPink
End With
Else
With Main.VIEW_DateOrdered
.Text = curr.Cells("Date Ordered").Value
.BackColor = Color.White
End With
End If
If IsDBNull(curr.Cells("Ordered By").Value) = True Then
With Main.VIEW_OrderedBy
.Text = "Not Ordered Yet"
.BackColor = Color.LightPink
End With
Else
With Main.VIEW_OrderedBy
.Text = curr.Cells("Ordered By").Value
.BackColor = Color.White
End With
End If
If IsDBNull(curr.Cells("Invoice Received").Value) = True Then
With Main.VIEW_InvoiceReceivedDate
.Text = "No Invoice"
.BackColor = Color.LightPink
End With
Else
With Main.VIEW_InvoiceReceivedDate
.Text = curr.Cells("Invoice Received").Value
.BackColor = Color.White
End With
End If
If IsDBNull(curr.Cells("Invoice Paid").Value) = True Then
With Main.VIEW_InvoicePaidDate
.Text = "Not Paid"
.BackColor = Color.LightPink
End With
Else
With Main.VIEW_InvoicePaidDate
.Text = curr.Cells("Invoice Paid").Value
.BackColor = Color.White
End With
End If
End Sub
End Module
I installed DataSourceUpdateMode.OnPropertyChanged
and assumed that this means that when the textbox is changed, it will update the data source (which is the database). I am guessing this is not the case as it does not work.
I would really like to be able to edit multiple fields in a single data line (via text fields) and then click the Save Changes button to refresh the database.
thank
UPDATE 1
I did a little more research, following some comments and answers, and wrote this code for my button Save
:
Public Sub Save()
Dim myCon = New OleDbConnection(My.Settings.Database_String)
myCon.Open()
Dim sqr = "UPDATE [Spares] SET [Name Of Person] = '" & Main.VIEW_NameOfPerson.Text & "', [SAP Job Number] = '" & CInt(Main.VIEW_SAPJobNo.Text) & "', " & _
"[Site Name] = '" & Main.VIEW_Site.Text & "', [Asset Description] = '" & Main.VIEW_AssetDesc.Text & "', " & _
"[Spares Supplier] = '" & Main.VIEW_SparesSupplier.Text & "', [Supplier Contact Name] = '" & Main.VIEW_SupplierContactName.Text & "', " & _
"[Supplier Contact Phone Number] = '" & Main.VIEW_SupplierContactNumber.Text & "', " & _
"[Supplier Contact Email] = '" & Main.VIEW_SupplierContactEmail.Text & "', [Spares Description] = '" & Main.VIEW_SparesDesc.Text & "', " & _
"[Part Number] = '" & Main.VIEW_PartNumber.Text & "', [Quantity To Order] = '" & CInt(Main.VIEW_QuantityToOrder.Text) & "', " & _
"[Cost] = '" & CDbl(Main.VIEW_CostEach.Text) & "', [Request Date] = '" & CDate(Main.VIEW_DateRequested.Text) & "' WHERE [ID] = '" & CInt(Main.VIEW_Ref.Text) & "'"
Dim Command = New OleDbCommand(sqr, myCon)
Command.ExecuteNonQuery()
myCon.Close()
End Sub
When the line Command.ExecuteNonQuery
tries to execute, I get the error:
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
More information: Data type mismatch in criteria expression.
NB: The line created in sqr
is:
UPDATE [Spares] SET [Name Of Person] = 'Name', [SAP Job Number] = '2', [Site Name] = 'Site', [Asset Description] = 'Asset', [Spares Supplier] = 'Spares Supplier', [Supplier Contact Name] = 'Contact Name', [Supplier Contact Phone Number] = 'Contact Email', [Supplier Contact Email] = 'Contact Number', [Spares Description] = 'Spare Desc', [Part Number] = 'Part Number', [Quantity To Order] = '1', [Cost] = '1', [Request Date] = '12/02/02' WHERE [ID] = '5'"
I am obviously using bogus information
Now I can't be too far away!
source to share
I finally solved the issue with the following code placed inside the Save button:
Public Sub Save()
Dim myCon = New OleDbConnection(My.Settings.Database_String)
myCon.Open()
Dim sqr = "UPDATE [Spares] SET [Name Of Person] = """ & Main.VIEW_NameOfPerson.Text & """, [SAP Job Number] = " & CInt(Main.VIEW_SAPJobNo.Text) & ", " & _
"[Site Name] = """ & Main.VIEW_Site.Text & """, [Asset Description] = """ & Main.VIEW_AssetDesc.Text & """, " & _
"[Spares Supplier] = """ & Main.VIEW_SparesSupplier.Text & """, [Supplier Contact Name] = """ & Main.VIEW_SupplierContactName.Text & """, " & _
"[Supplier Contact Phone Number] = """ & Main.VIEW_SupplierContactNumber.Text & """, " & _
"[Supplier Contact Email] = """ & Main.VIEW_SupplierContactEmail.Text & """, [Spares Description] = """ & Main.VIEW_SparesDesc.Text & """, " & _
"[Part Number] = """ & Main.VIEW_PartNumber.Text & """, [Quantity To Order] = " & CInt(Main.VIEW_QuantityToOrder.Text) & ", " & _
"[Cost] = " & CDbl(Main.VIEW_CostEach.Text) & ", [Request Date] = " & CDate(Main.VIEW_DateRequested.Text) & " WHERE [ID] = " & CInt(Main.VIEW_Ref.Text) & ""
Dim Command = New OleDbCommand(sqr, myCon)
Command.ExecuteNonQuery()
myCon.Close()
End Sub
End Module
source to share
If the DataTable you are using to bind the grid is global, then when you click the button, you need to delete the entry from the DataTable that you want to update, and then add the edited data back to the DataTable using the same ID and then repeat to your grid. This will at least update your visual fields. To update the database, you need to invoke a query Update
sending the new data the ID of the records.
If your DataTable is not global, setting up the visual is different from having to scroll through each entry in the grid and then delete the correct one and then add the edited entry again with the same ID. The premise for the database call will still be the same.
Hope this helps you move in the right direction.
source to share