eidting two tables wthin a datagrid

F

f

Apologies if this has been asked before and I have searched on the Internet
to no avail.

I would like to use a Datagrid to edit data held in two tables that are
'joined' by a common field. I know hoe to write the code to edit a row from
one table, however, using two tables is causing me pain.

Any ideas, suggestion or links to examples?

Thanks

Brian
 
M

Mike Moore [MSFT]

Hi Brian,

There are many possible solutions. Here is one.

I used the titles and sales tables from the Pubs sample database. I linked
these on title_id and created a datagrid which can update both the title
and the sales tables at the same time.

Here is the HTML for my datagrid (note that it has several read only
fields).

<asp:datagrid id="DataGrid1" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:EditCommandColumn ButtonType="LinkButton" UpdateText="Update"
CancelText="Cancel" EditText="Edit"></asp:EditCommandColumn>
<asp:BoundColumn DataField="title_id" ReadOnly="True"
HeaderText="title_id"></asp:BoundColumn>
<asp:BoundColumn DataField="title"
HeaderText="title"></asp:BoundColumn>
<asp:BoundColumn DataField="stor_id" ReadOnly="True"
HeaderText="stor_id"></asp:BoundColumn>
<asp:BoundColumn DataField="ord_num" ReadOnly="True"
HeaderText="ord_num"></asp:BoundColumn>
<asp:BoundColumn DataField="qty" HeaderText="qty"></asp:BoundColumn>
</Columns>
</asp:datagrid></form>



Here is my code behind. Notice how it uses multiple SqlCommand objects, one
for each table. My code behind uses: Imports System.Data.SqlClient

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
If Not IsPostBack Then
Bind()
End If
End Sub

Private Sub Bind()
Dim Qry1 As System.Data.SqlClient.SqlDataReader
Dim connectionString As String = "server='localhost';
trusted_connection=true; Database='pubs'"
Dim sqlConnection As SqlConnection = New SqlConnection(connectionString)
Dim queryString As String = "SELECT t.title_id, t.title, s.stor_id,
s.ord_num, s.qty FROM titles t inner join sales s on t.title_id =
s.title_id"
Dim sqlCommand As SqlCommand = New SqlCommand(queryString,
sqlConnection)
sqlConnection.Open()
Qry1 =
sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
DataGrid1.DataSource = Qry1
DataGrid1.DataBind()
Qry1.Close()
sqlCommand.Dispose()
sqlConnection.Close()
sqlConnection.Dispose()
End Sub

Private Sub DataGrid1_EditCommand(ByVal source As Object, ByVal e As
System.Web.UI.WebControls.DataGridCommandEventArgs) Handles
DataGrid1.EditCommand
DataGrid1.EditItemIndex = e.Item.ItemIndex
Bind()
End Sub

Private Sub DataGrid1_CancelCommand(ByVal source As Object, ByVal e As
System.Web.UI.WebControls.DataGridCommandEventArgs) Handles
DataGrid1.CancelCommand
DataGrid1.EditItemIndex = -1
DataGrid1.SelectedIndex = -1
Bind()
End Sub

Private Sub DataGrid1_UpdateCommand(ByVal source As Object, ByVal e As
System.Web.UI.WebControls.DataGridCommandEventArgs) Handles
DataGrid1.UpdateCommand
Dim connectionString As String = "server='localhost';
trusted_connection=true; Database='pubs'"
Dim Conn As SqlConnection = New SqlConnection(connectionString)
Dim queryString As String
Dim cmd1 As SqlCommand
Dim cmd2 As SqlCommand

'Create a SqlCommand with parameters & give values to the parameters.
queryString = "UPDATE titles set title = @Title WHERE title_id = @ID"
cmd1 = New SqlCommand(queryString, Conn)
cmd1.CommandType = CommandType.Text
cmd1.Parameters.Add(New SqlParameter("@ID", SqlDbType.NVarChar, 6))
cmd1.Parameters.Add(New SqlParameter("@Title", SqlDbType.NVarChar, 80))
cmd1.Parameters("@ID").Value = e.Item.Cells(1).Text
cmd1.Parameters("@Title").Value = CType(e.Item.Cells(2).Controls(0),
WebControls.TextBox).Text

'Create a SqlCommand with parameters & give values to the parameters.
queryString = "UPDATE sales set qty = @Qty WHERE stor_id = @Stor and
ord_num = @Ord and title_id = @ID"
cmd2 = New SqlCommand(queryString, Conn)
cmd2.CommandType = CommandType.Text
'The sales table uses three fields to define a unique entry: title_id,
stor_id and ord_num
'Therefore, we use all three fields here so that we only update the one
row and no others.
cmd2.Parameters.Add(New SqlParameter("@Id", SqlDbType.NVarChar, 6))
cmd2.Parameters.Add(New SqlParameter("@Stor", SqlDbType.Char, 4))
cmd2.Parameters.Add(New SqlParameter("@Ord", SqlDbType.NVarChar, 20))
cmd2.Parameters.Add(New SqlParameter("@Qty", SqlDbType.SmallInt))
cmd2.Parameters("@ID").Value = e.Item.Cells(1).Text
cmd2.Parameters("@Stor").Value = e.Item.Cells(3).Text
cmd2.Parameters("@Ord").Value = e.Item.Cells(4).Text
cmd2.Parameters("@Qty").Value = CType(e.Item.Cells(5).Controls(0),
WebControls.TextBox).Text

'Keep the connection open only as long as necessary.
Conn.Open()
cmd1.ExecuteNonQuery()
cmd1.Dispose()
cmd2.ExecuteNonQuery()
cmd2.Dispose()
Conn.Close()
Conn.Dispose()
DataGrid1.EditItemIndex = -1
Bind()
End Sub


Thank you, Mike
Microsoft, ASP.NET Support Professional

Microsoft highly recommends to all of our customers that they visit the
http://www.microsoft.com/protect site and perform the three straightforward
steps listed to improve your computer’s security.

This posting is provided "AS IS", with no warranties, and confers no rights.


--------------------
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top