DataGrid / DataSet / DataAdaptor / DataBase update issue

[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

 

[Cor Ligthert [MVP]]

Marc,

Is this all the data or only the latest changed row, if it is that than you
should have to use the endcurrentedit because of the fact that the data is
pushed in the datasource because of a rowchange or by an endcurrenedit.

http://msdn2.microsoft.com/en-us/library/system.windows.forms.currencymanager.endcurrentedit.aspx

I assume this is about a version 2002/2003

Cor

 

[Guest]

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.

 

[Guest]

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)

 

[Cor Ligthert [MVP]]

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

 

[Guest]

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()

 

[Cor Ligthert [MVP]]

Marcm,

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

Cor

 

[Guest]

I did it doesn't work yet, am i missing something blatently obvious?

 

[Cor Ligthert [MVP]]

Can you show what you have sofar at the moment

I did it doesn't work yet, am i missing something blatently obvious?

 

[Guest]

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

 

[Guest]

Can you please try this simple exercise and see what i am talking about.

 

[Guest]

i got it, maybe i should be the MVP!

Can you please try this simple exercise and see what i am talking about.

 

[Cor Ligthert [MVP]]

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

 

[Guest]

I know that.
Anyway the issue is fixed.

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