My dataset is not updating my database after the user modifies the datagrid.
I populate my data with the load sub below.
In the Save Sub (below), I have generated my DataSet 'dataSet11' from my
DataAdaptor 'SqlDataAdapter1' and the DataConnection 'SqlConnection1' and
they all seem to be connected correctly. But my data does not update.
the dataAdaptor is configured for Insert/Update/delete and the datagrid
datasource is DataSet11.TableName. Any ideas?
Private Sub Thresholds_Load(ByVal sender As....
Try
cn = New SqlClient.SqlConnection("user id=" & UserName.Text & ";password="
& Password.Text & ";database=" & Database.Text & ";server=" & Server.Text)
cn.Open()
cmdSelect.Connection = cn
Dim da As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter("Select
* from MISRE_Threshold", cn)
Dim dsThreshold As DataSet = New DataSet
' fill dataset
da.Fill(dsThreshold, "MISRE_Threshold")
'Attach DataSet to DataGrid
dgThreshold.DataSource = dsThreshold.DefaultViewManager
Catch ex As Exception
MessageBox.Show("Error: Could not establish database connection")
End Try
Private Sub btnSave_Click(ByVal sender....
SqlDataAdapter1.Update(DataSet11)
EndSubThe DataAdapter InsertCommand/UpdateCommand/DeleteCommand properties need to
be set in order for the DataAdapter to execute the appropriate commands to
update your table. The SqlCommandBuilder can be used to generate the needed
commands (if you have a primary key) or you can create those the commands
yourself.
Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)
Hope this helps.
Dan Guzman
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:392099C1-6587-46B7-BD60-A20AD3C884AB@.microsoft.com...
> My dataset is not updating my database after the user modifies the
> datagrid.
> I populate my data with the load sub below.
> In the Save Sub (below), I have generated my DataSet 'dataSet11' from my
> DataAdaptor 'SqlDataAdapter1' and the DataConnection 'SqlConnection1' and
> they all seem to be connected correctly. But my data does not update.
> the dataAdaptor is configured for Insert/Update/delete and the datagrid
> datasource is DataSet11.TableName. Any ideas?
> Private Sub Thresholds_Load(ByVal sender As....
> Try
> cn = New SqlClient.SqlConnection("user id=" & UserName.Text &
> ";password="
> & Password.Text & ";database=" & Database.Text & ";server=" & Server.Text)
> cn.Open()
> cmdSelect.Connection = cn
> Dim da As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter("Select
> * from MISRE_Threshold", cn)
> Dim dsThreshold As DataSet = New DataSet
> ' fill dataset
> da.Fill(dsThreshold, "MISRE_Threshold")
> 'Attach DataSet to DataGrid
> dgThreshold.DataSource = dsThreshold.DefaultViewManager
> Catch ex As Exception
> MessageBox.Show("Error: Could not establish database
> connection")
> End Try
>
> Private Sub btnSave_Click(ByVal sender....
> SqlDataAdapter1.Update(DataSet11)
> EndSub|||They are configured, that's why i'm stumped!
Its the update one i'm interested in as follows:
UPDATE MISRE_Threshold
SET ThresholdType = @.ThresholdType, Threshold = @.Threshold,
Threshold_Flag = @.Threshold_Flag, Actual = @.Actual, Fail = @.Fail,
Category = @.Category, ID = @.ID
WHERE (ThresholdType = @.Original_ThresholdType) AND (Actual =
@.Original_Actual) AND (Category = @.Original_Category OR
@.Original_Category IS NULL AND Category IS NULL) AND
(Fail = @.Original_Fail) AND (ID = @.Original_ID OR
@.Original_ID IS NULL AND ID IS NULL) AND (Threshold =
@.Original_Threshold OR
@.Original_Threshold IS NULL AND Threshold IS NULL) AND
(Threshold_Flag = @.Original_Threshold_Flag OR
@.Original_Threshold_Flag IS NULL AND Threshold_Flag IS
NULL);
SELECT ThresholdType, Threshold,
Threshold_Flag, Actual, Fail, Category, ID
FROM MISRE_Threshold
WHERE (ThresholdType = @.ThresholdType)|||> They are configured, that's why i'm stumped!
The original code you posted instantiates and uses a new untyped dataset:
Dim dsThreshold As DataSet = New DataSet
However, your update routine uses the DataAdapter and DataSet generated by
the windows form designer:
SqlDataAdapter1.Update(DataSet11)
DataSet11 is never filled in the code snippets you posted so it will always
be empty. I believe your intention is to fill DataSet11 in the load
routine:
SqlDataAdapter1.Fill(DataSet11)
Hope this helps.
Dan Guzman
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:E94A87F0-9D2B-4DB1-85E3-5AE7635656DA@.microsoft.com...
> They are configured, that's why i'm stumped!
> Its the update one i'm interested in as follows:
>
> UPDATE MISRE_Threshold
> SET ThresholdType = @.ThresholdType, Threshold = @.Threshold,
> Threshold_Flag = @.Threshold_Flag, Actual = @.Actual, Fail = @.Fail,
> Category = @.Category, ID = @.ID
> WHERE (ThresholdType = @.Original_ThresholdType) AND (Actual =
> @.Original_Actual) AND (Category = @.Original_Category OR
> @.Original_Category IS NULL AND Category IS NULL) AND
> (Fail = @.Original_Fail) AND (ID = @.Original_ID OR
> @.Original_ID IS NULL AND ID IS NULL) AND (Threshold =
> @.Original_Threshold OR
> @.Original_Threshold IS NULL AND Threshold IS NULL)
> AND
> (Threshold_Flag = @.Original_Threshold_Flag OR
> @.Original_Threshold_Flag IS NULL AND Threshold_Flag
> IS
> NULL);
> SELECT ThresholdType, Threshold,
> Threshold_Flag, Actual, Fail, Category, ID
> FROM MISRE_Threshold
> WHERE (ThresholdType = @.ThresholdType)
