Here is the connection and SQL command string:
Dim sqlStr As String = "select name, value, id from infparameter where sectionID = (select ID from InfSection where name = 'Printers' and applicationID = " & _
"( select ID from infApplication where code = 'INF'))"
' Create data adapter object
Dim da As SqlDataAdapter = New SqlDataAdapter(sqlStr, dbconnect)
' Create a dataset object and fill with data using data adapter's Fill method
Dim ds As DataSet = New DataSet
The following is after you the display button is clicked to fill the DataSet:
#Region "Display Button"
Private Sub displaybtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles displaybtn.Click
Try
ds.Clear()
da.Fill(ds, "infparameter")
'Dim dv As DataView = ds.Tables("infparameter").DefaultView
'DataGrid1.DataSource = dv
ds.Tables(0).Columns.Item(0).ColumnName = "Section"
ds.Tables(0).Columns.Item(1).ColumnName = "Printer"
Dim ts As New DataGridTableStyle
ts.MappingName = "infparameter"
DataGrid1.TableStyles.Clear()
DataGrid1.TableStyles.Add(ts)
DataGrid1.DataSource = ds.Tables(0)
ts.GridColumnStyles(0).Width = 100
ts.GridColumnStyles(1).Width = 100
ts.GridColumnStyles.Remove(DataGrid1.TableStyles("infparameter").GridColumnStyles("id"))
'MessageBox.Show("This is all I found for your search string " & searchinput1 & ".", msggood.ToString(), MessageBoxButtons.OK)
Call logfile("-------Displayed Current Printer Settings in InfPrinters " & Now() & vbCrLf)
Catch ex As Exception
MessageBox.Show(ex.Message.ToString(), msgbad.ToString(), MessageBoxButtons.OK)
Finally
End Try
End Sub
#End Region
The following is when you update the cells the changed. To affect the DataSet must containe the ID(PrimaryKey) of the line so the .NET’s code can update the right ones.
#Region "Update Button"
Private Sub updatebtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles updatebtn.Click
Try
ds.Tables(0).Columns.Item(0).ColumnName = "name"
ds.Tables(0).Columns.Item(1).ColumnName = "value"
da.UpdateCommand = New SqlCommand("update infparameter set mainttime=getdate(), value=@value where id = @id", dbconnect)
da.UpdateCommand.Parameters.Add("@value", SqlDbType.VarChar, 500, "value")
Dim workParm As SqlParameter = da.UpdateCommand.Parameters.Add("@id", SqlDbType.Int)
workParm.SourceColumn = "id"
workParm.SourceVersion = DataRowVersion.Original
da.Update(ds, "infparameter")
'MessageBox.Show("Records have been updated.", msggood.ToString(), MessageBoxButtons.OK)
displaybtn.PerformClick()
'MessageBox.Show("Coming soon", msggood.ToString(), MessageBoxButtons.OK)
Call ModFunc.logfile("-------Changed Printers in InfPrinters " & Now() & vbCrLf)
Catch ex As Exception
MessageBox.Show(ex.Message.ToString(), msgbad.ToString(), MessageBoxButtons.OK)
End Try
End Sub
#End Region
Notice that the colunms names are not the same from the display since this will not be displayed to the end user, but ran in the back ground. The name are the exact match to the SQL table and SQL string above.
The SQLParameter is passed to use the ID as the records ID and so .NET’s code knows which records have changed and which need updating.