How to update data in Windows Datagrid

[shil]

Hi,

I am writing a windows app in .net 2003. I have a datagrid which gets
data from a storedprocedure. My question is how can I update the data
in the datagrid? I want to call another storedprocedure to update the
data in the datagrid.

Thanks in advance.

 

[Cor Ligthert [MVP]]

Shil,

The datagrid reflects the data in a datasource, therefore you will have to
update that datasource.

(How you get that data is not so important, the stored procedure to get data
is the same as a text procedure in ADONET, the only difference is where it
is located.)

I hope this helps,

Cor

 

[shil]

Hi Cor,

I created a save button on the same form where I have datagrid. Here is
the code on that button click I worte.

Private Sub Save_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Save.Click
Dim myDa1 As SqlDataAdapter
Dim myCmd As SqlCommand

myCmd = New SqlCommand("sp_AP_Upd_AssignedUsers", myConn)
myCmd.CommandType = CommandType.StoredProcedure

Dim paramUserID As SqlParameter = New SqlParameter("@UserID",
SqlDbType.SmallInt)
myCmd.Parameters.Add(paramUserID)
paramUserID.Value = dgAssignedUsers.Item(0, 12)

Dim paramEmail2 As SqlParameter = New SqlParameter("@Email2",
SqlDbType.NVarChar, 50)
myCmd.Parameters.Add(paramEmail2)
paramEmail2.Value = dgAssignedUsers.Item(0, 7)

myCmd.ExecuteNonQuery()
End Sub

This works fine for the first row since I hardcoded that. How can I
loop through all rows in the datagrid? For example in web forms
datagrid I can do

For i = 0 To dgAssignedUses.Items.Count-1

Next

How can I perform the same thing in windows datagrid?

Thanks.

 

[Cor Ligthert [MVP]]

Shil,

In a windowforms datagrid or datagridview should you forget to loop through
the rows of a datagrid. It are just the cells that represents the
datasource.

In a windowform you loop through the rows of your datasource which is mostly
a datatable or a dataview. (You have not the problem that your datasource is
in fact at the service side).

The looping is than the same let say a dataview

\\\
for i as integer = 0 to dv.count - 1
if dv(i)("myfield") = "whatever" then
'the action to be done
end if
next
///

I hope this gives an idea

Cor

 

[Cerebrus]

Hi Shil,

You're doing it the wrong way, if you're trying to loop through the
cells of the Datagrid and manually update each item at the back-end.

Remember that the Datagrid just represents a visual view of the data
present in your DataTable or Dataset as the case might be.
Therefore, any changes made to the contents of the Datagrid are
automatically reflected in the contents of the Datatable or Dataset.
So, you're next task remains to update this information back to the
Database.

Hopefully the following will clear the doubt : (lines might break up)

DataGrid -----------------> DataSet / DataTable ---------> Database
(SQL Server)
(visual representation) (Logical representation) (Data store)

So, to update the Dataset back to the Database, just use the
SqlDataAdapter.Update(myDataSet, "TableName") method.

If you plan to have a stored procedure run when updating the Database,
make sure that the UpdateCommand property of your SqlDataAdapter points
to command object which is the stored procedure.

Regards,

Cerebrus.

 

[shil]

Cerebrus,

My select query into dataset is quering multiple tables. It is not
simple select. So when I call Update command of dataadapter, I get this
error "Additional information: Dynamic SQL generation is not supported
against multiple base tables." How do I update data of a datagrid which
has data from multiple tables?

Thanks.

 

[Cerebrus]

Hi shil,

Yes, that is natural. Use your own Update SQL statement as the
UpdateCommand.

myDataAdapter.UpdateCommand.CommandText = "UPDATE Categories SET
Description='Cheeses, Milk, Ice Cream' WHERE CategoryName='Dairy
Products'"

Regards,

Cerebrus.