eidting two tables wthin a datagrid

Discussion in 'ASP .Net Datagrid Control' started by f, Nov 10, 2003.

  1. f

    f Guest

    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
     
    f, Nov 10, 2003
    #1
    1. Advertising

  2. 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.


    --------------------
    > From: "f" <r>
    > Newsgroups: microsoft.public.dotnet.framework.aspnet.datagridcontrol
    > Subject: eidting two tables wthin a datagrid
    > Date: Mon, 10 Nov 2003 15:10:54 -0000
    > X-Priority: 3
    > X-MSMail-Priority: Normal
    > X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
    > X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
    > Lines: 14
    > Message-ID: <3fafaa79$0$12707$>
    > Organization: Zen Internet
    > NNTP-Posting-Host: 217.155.95.134
    > X-Trace: 1068477049 lovejoy.zen.co.uk 12707 217.155.95.134
    > X-Complaints-To:
    > Path:

    cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onlin
    e.de!npeer.de.kpn-eurorings.net!news2.telebyte.nl!zen.net.uk!lovejoy.zen.co.
    uk.POSTED!not-for-mail
    > Xref: cpmsftngxa06.phx.gbl

    microsoft.public.dotnet.framework.aspnet.datagridcontrol:7379
    > X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet.datagridcontrol
    >
    > 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
    >
    >
    >
     
    Mike Moore [MSFT], Nov 11, 2003
    #2
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. David Wier

    Re: Datagrid based on two tables

    David Wier, Aug 5, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    352
    David Wier
    Aug 5, 2003
  2. Vik

    Datagrid on two tables

    Vik, Apr 14, 2004, in forum: ASP .Net
    Replies:
    4
    Views:
    3,187
  3. TN Bella
    Replies:
    1
    Views:
    468
    TN Bella
    Jun 15, 2004
  4. Mike

    two tables one datagrid

    Mike, Jul 5, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    619
    Craig Deelsnyder
    Jul 5, 2004
  5. Paolo

    Two dropdownlist and two tables...

    Paolo, Sep 19, 2006, in forum: ASP .Net Web Controls
    Replies:
    0
    Views:
    155
    Paolo
    Sep 19, 2006
Loading...

Share This Page