DataGrid / DataSet / DataAdaptor / DataBase update issue

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I'm using vb.net.
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
 
Hey,

When i press save I want all the data that has been edited to return to the
dataset and update the database. I am finding it very difficult to work it
out and the nothing on www seems to deal eith this.

I inserted
Me.BindingContext(DataSet11, "MISRE_Threshold").EndCurrentEdit()
as the first line of the save event and still nothing updates.

I'm not sure i undserstood fully what you were asking me.

Thanks for the help.
 
It's the update iu'm interested in.
The update is configured 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)
 
Marc,

I did not see it direct

dgThreshold.DataSource = dsThreshold.DefaultViewManager
SqlDataAdapter1.Update(DataSet11)

You should update the dataset from that is the datasource of your datagrid

I as well don't see that .DefaultViewManager what that does. (Can be a lack
of my knowledge but mostly is it the table you want to show and can than be.
dsThreshold.tables(myTable).defaultview which you can than use direct in
your update as well without the defaultview.

I hope this helps,

Cor
 
Thankyou. I now have this but it still does not update the database. Have you
found this as frustrating as me.

Me.BindingContext(DataSet11).EndCurrentEdit()

SqlDataAdapter1.Update(DataSet11.MISRE_Threshold) ' This is the actual name
of the dataSource connected to the dataGrid

DataSet11.AcceptChanges()
 
Marcm,

Can you do after that you have entered the data in the datagrid do some
rowchanges, just to test.

Cor
 
Cor, I began again. I think the issue may be behind my connection screen
generated at the time when i define my dataAdaptor.

This code has a connection state of closed!

Try
MessageBox.Show(SqlConnection1.State.ToString)
SqlDataAdapter2.Fill(DataSet11)
Catch
MessageBox.Show("gui based SQLConnection1 not connected")
End Try
 
Marc,

The dataadapter is a mechanisme that opens and close himself the connection
if that is closed. I never open a connection with a dataadapter. If it is
opened already than you should keep yourself track that it will be closed.

Cor
 
I know that.
Anyway the issue is fixed.

Cor Ligthert said:
Marc,

The dataadapter is a mechanisme that opens and close himself the connection
if that is closed. I never open a connection with a dataadapter. If it is
opened already than you should keep yourself track that it will be closed.

Cor
Click to expand...
 
Back
Top