Solved

Adding DataGrid to SQL Database - ASP.NET, VB.NET

Posted on 2004-10-26
1,265 Views
Last Modified: 2012-06-21
I have a DataGrid (dt) that is being pulled in from a Session variable:

         Dim dt = CType(Session("getTimes"),DataTable)
         showReservations.DataSource = dt
         showReservations.DataBind()

I now need to know how to add the contents of this datagrid to a SQL Server database.  I have 3 fields in the datagrid that I would like to add:  tmpStartDate, tmpStartTime, tmpEndTime into the table tmpReservations.

How can I go about doing this?  Here is my code:

<%@ Page Language="VB" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.DataTable" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">

    sub Page_Load(sender as Object, e as EventArgs)
   
         Dim dt = CType(Session("getTimes"),DataTable)
         showReservations.DataSource = dt
         showReservations.DataBind()
   
       end sub
   
    Sub Button1_Click(sender As Object, e As EventArgs)
   
        Dim dsn As String = ConfigurationSettings.AppSettings("DSN")
        Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(dsn)
   
        Dim insertContact = new SqlCommand("INSERT INTO Contacts(firstName, lastName, emailAddress, officeNumber, officeLocation) VALUES('" & Session.Contents("firstName") & "', '" & Session.Contents("lastName") & "', '" & Session.Contents("email") & "', '" & Session.Contents("phoneNum") & "', '" & Session.Contents("location") & "')", dbConnection)
        Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
   
        Dim strSQL As String
   
        'Write data to Contacts Table
        dbConnection.Open()
        insertContact.ExecuteNonQuery()
        insertContact.CommandText = "SELECT @@Identity as 'contactKey'"
        insertContact.ExecuteScalar()
        Dim strContactKey = CStr(insertContact.ExecuteScalar())
        dbConnection.Close()
   
        Response.Write("Information saved!")
   
    End Sub 'Button1_Click

</script>
<html>
<head>
</head>
<body>
    <form runat="server">
        Summary of entered data:
        <asp:DataGrid id="showReservations" runat="server" AutoGenerateColumns="False" Width="317px" CellPadding="4" BackColor="#CCCCCC" BorderColor="#999999" BorderWidth="3px" BorderStyle="Solid" HeaderStyle-Font-Bold="True" HeaderStyle-BackColor="LightGray" CellSpacing="2" ForeColor="Black">
            <FooterStyle backcolor="#CCCCCC"></FooterStyle>
            <HeaderStyle font-bold="True" forecolor="White" backcolor="Black"></HeaderStyle>
            <PagerStyle horizontalalign="Left" forecolor="Black" backcolor="#CCCCCC" mode="NumericPages"></PagerStyle>
            <SelectedItemStyle font-bold="True" forecolor="White" backcolor="#000099"></SelectedItemStyle>
            <ItemStyle backcolor="White"></ItemStyle>
            <Columns>
                <asp:BoundColumn DataField="startDate" HeaderText="Date"></asp:BoundColumn>
                <asp:BoundColumn DataField="startTime" HeaderText="Starting Time"></asp:BoundColumn>
                <asp:BoundColumn DataField="endTime" HeaderText="Ending Time"></asp:BoundColumn>
            </Columns>
        </asp:DataGrid>
        <br />
        <% Response.Write(Session.Contents("meetingTitle")) %>
        <br />
        <% Response.Write(Session.Contents("firstName")) %>
        <br />
        <% Response.Write(Session.Contents("lastName")) %>
        <br />
        <% Response.Write(Session.Contents("holdOrBook")) %>
        <br />
        <% Response.Write(Session.Contents("phoneNum")) %>
        <br />
        <% Response.Write(Session.Contents("location")) %>
        <br />
        Please click if you'd like to make this reservation:
        <br />
        <asp:Button id="Button1" onclick="Button1_Click" runat="server" Text="Place Reservation"></asp:Button>
        <br />
        <asp:Label id="lblMsg" runat="Server"></asp:Label>
        <br />
    </form>
</body>
</html>
0
Question by:rreister
    26 Comments
     
    LVL 17

    Expert Comment

    by:AerosSaga
    <asp:datagrid id="dg" runat="server" Width="488px" ShowFooter="True" BorderColor="#FE7B43" BorderStyle="None"
                                                    CellSpacing="2" BorderWidth="1px" BackColor="#FE7B43" CellPadding="3" AutoGenerateColumns="False">
                                                    <SelectedItemStyle Font-Bold="True" ForeColor="White" BackColor="#738A9C"></SelectedItemStyle>
                                                    <ItemStyle ForeColor="Black" BackColor="#F4F1CC"></ItemStyle>
                                                    <HeaderStyle Font-Bold="True" BorderWidth="1px" ForeColor="#8C4510" BackColor="#F7DFB5"></HeaderStyle>
                                                    <FooterStyle Font-Bold="True" ForeColor="#8C4510" BackColor="#F7DFB5"></FooterStyle>
                                                    <Columns>
                                                          <asp:EditCommandColumn ButtonType="PushButton" UpdateText="Update" CancelText="Cancel" EditText="Edit">
                                                                <ItemStyle HorizontalAlign="Center" CssClass="border: 1px solid RGB(58,110,165) "></ItemStyle>
                                                          </asp:EditCommandColumn>
                                                          <asp:ButtonColumn Text="Delete" ButtonType="PushButton" CommandName="Delete">
                                                                <ItemStyle HorizontalAlign="Center" CssClass="border: 1px solid RGB(58,110,165) "></ItemStyle>
                                                          </asp:ButtonColumn>
                                                          <asp:BoundColumn DataField="ProductName" ReadOnly="True" HeaderText="Name"></asp:BoundColumn>
                                                          <asp:BoundColumn DataField="Quantity" HeaderText="Quantity"></asp:BoundColumn>
                                                          <asp:BoundColumn DataField="ProductPrice" ReadOnly="True" HeaderText="Price" DataFormatString=" {0:C}"></asp:BoundColumn>
                                                          <asp:BoundColumn Visible="False" DataField="ProductID" HeaderText="ProductID"></asp:BoundColumn>
                                                    </Columns>
                                                    <PagerStyle HorizontalAlign="Center" ForeColor="#8C4510" Mode="NumericPages"></PagerStyle>
                                              </asp:datagrid>

      Private Sub dg_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles dg.ItemDataBound
            Dim decTempSum, decFinalTotal As Decimal
            If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
                Dim ProductPrice As Decimal = (DataBinder.Eval(e.Item.DataItem, "ProductPrice"))
                Dim Quantity As Integer = (DataBinder.Eval(e.Item.DataItem, "Quantity"))
                decTempSum = ProductPrice * Quantity
                decFinalTotal = decFinalTotal + decTempSum
                viewCountSum += decFinalTotal
            ElseIf e.Item.ItemType = ListItemType.Footer Then
                e.Item.Cells(3).Text = "Subtotal:"
                e.Item.Cells(4).Text = String.Format("{0:$#,###.##}", viewCountSum)
            End If
        End Sub
        Private Sub dg_DeleteCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dg.DeleteCommand
            Dim cnn As New OleDb.OleDbConnection(ConfigurationSettings.AppSettings("SiteDB"))
            Dim cmd As New OleDb.OleDbCommand
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "DELETE * FROM TempSession WHERE ProductID = " & e.Item.Cells(5).Text & " AND SessionString = '" & Me.Session.SessionID & "'"
            cmd.Connection = cnn
            cnn.Open()
            cmd.ExecuteNonQuery()
            cnn.Close()
            cmd.Dispose()
            cnn.Dispose()
            LoadProductData()
            CalculateTotal()
        End Sub
        Private Sub dg_EditCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dg.EditCommand
            dg.EditItemIndex = e.Item.ItemIndex
            LoadProductData()
            CalculateTotal()
        End Sub
        Private Sub dg_UpdateCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dg.UpdateCommand
            Dim cnn As New OleDb.OleDbConnection(ConfigurationSettings.AppSettings("SiteDB"))
            Dim cmd As New OleDb.OleDbCommand
            Dim intQuantity As String = CType(e.Item.Cells(3).Controls(0), TextBox).Text
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "UPDATE TempSession SET Quantity = " & intQuantity.ToString & " WHERE SessionString LIKE '" & Me.Session.SessionID.ToString & "' AND ProductName LIKE '" & e.Item.Cells(2).Text & "'"
            cmd.Connection = cnn
            cnn.Open()
            cmd.ExecuteNonQuery()
            cnn.Close()
            cmd.Dispose()
            cnn.Dispose()
            dg.EditItemIndex = -1
            LoadProductData()
            CalculateTotal()
        End Sub
        Private Sub dg_CancelCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dg.CancelCommand
            dg.EditItemIndex = -1
            LoadProductData()
            CalculateTotal()
        End Sub

    Aeros
    0
     

    Author Comment

    by:rreister
    AerosSaga

    That doesn't help me at all.  I need something to simply insert my datagrid into my SQL Server database.
    0
     
    LVL 17

    Expert Comment

    by:AerosSaga
    You want to loop through your datagrid and insert each row as a new record?  
    0
     
    LVL 17

    Expert Comment

    by:AerosSaga
    <asp:datagrid id="dgPlaysOn" runat="server" Height="103px"
    AutoGenerateColumns="False">
          <Columns>
                <asp:BoundColumn DataField="IdTour"
    HeaderText="IdTour"></asp:BoundColumn>
                <asp:BoundColumn DataField="name" HeaderText="Plays
    on:"></asp:BoundColumn>
                <asp:TemplateColumn>
                <ItemStyle HorizontalAlign="Center"></ItemStyle>
                      <ItemTemplate>
                            <asp:CheckBox ID="chkPlaysOn"
    OnCheckedChanged="GetIdTour" Runat="server" />
                      </ItemTemplate>
                </asp:TemplateColumn>
          </Columns>
    </asp:datagrid></TD>

    And the code in the code behind section is as follows:

    Dim i As Byte 'Loop index
    Dim item As DataGridItem 'used to access the individual grid items
    Dim cbPlaysOn As CheckBox 'Used to acces the individual checkbox
    Dim bPlaysOn As Boolean

    For i = 0 To dgPlaysOn.Items.Count - 1
          item = dgPlaysOn.Items(i)
          cbPlaysOn = CType(item.FindControl("chkPlaysOn"), CheckBox)
          bPlaysOn = cbPlaysOn.Checked
          'Put code to access a specific cell in the column IdTour

          'Put code to call the insert statement
    Next
    0
     

    Author Comment

    by:rreister
    Still way confused.  Very new to this:

    I'm not using a checkbox at all, so I'm not sure why I need that code.  

    Also, not too sure how to access the specific cells of my three bound columns (startDate, startTime, endTime)?

    Here is what I have so far.

            Dim i As Byte 'Loop index
    Dim item As DataGridItem 'used to access the individual grid items

    For i = 0 To showReservations.Items.Count - 1
         item = showReservations.Items(i)

          'Put code to access a specific cell in the column IdTour
         
         'Put code to call the insert statement
         insertReservation = new SqlCommand("INSERT INTO Reservations(startDate, startTime, endTime, holdOrBook, contactKey, meetingTitle) VALUES('" & Session.Contents("startDate") & "', '" & Session.Contents("startTime") & "', '" & Session.Contents("endTime") & "', '" & Session.Contents("holdOrBook") & "', " & strContactKey & ", '" & Session.Contents("meetingTitle") & "')", dbConnection)
    Next

    0
     
    LVL 17

    Expert Comment

    by:AerosSaga
    those were for demonstration only
    0
     

    Author Comment

    by:rreister
    Right.  Could you help me fine-tune my code so I can get this to work?
    0
     
    LVL 17

    Expert Comment

    by:AerosSaga
    ok so when you step through is item getting assigned correctly?
    0
     

    Author Comment

    by:rreister
    I do a response.write(item) and get the following:

    System.Web.UI.WebControls.DataGridItemSystem.Web.UI.WebControls.DataGridItem
    0
     

    Author Comment

    by:rreister
    So, to be more specific, it's keeping count correctly (since I have 2 entries in my datagrid right now).
    0
     
    LVL 17

    Expert Comment

    by:AerosSaga
    ok try a quick watch for
     item
    0
     

    Author Comment

    by:rreister
    Unfortunately, I'm using Web Matrix for design.  Don't have Visual Studio.
    0
     
    LVL 17

    Expert Comment

    by:AerosSaga
    also see if you can type item.cells(1) thats how you need to access each cell in each row.
    0
     
    LVL 17

    Expert Comment

    by:AerosSaga
    ouch
    0
     
    LVL 17

    Expert Comment

    by:AerosSaga
    check here for your options since its at least getting put into the datagridItem

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemwebuiwebcontrolsdatagriditemclasstopic.asp

    You will have to expose the item.cells() to get each cell value.

    0
     

    Author Comment

    by:rreister
    I put this function in:

          Sub Item_Bound(sender As Object, e As DataGridItemEventArgs)


             If e.Item.ItemType = ListItemType.Item Or _
                 e.Item.ItemType = ListItemType.AlternatingItem Then

                Dim startDate As String = e.Item.Cells(0).Text
                Dim startTime As String = e.Item.Cells(1).Text
                Dim endTime As String = e.Item.Cells(2).Text

    'Response.Write(startDate &"<BR>")
    'Response.Write(startTime &"<BR>")
    'Response.Write(endTime &"<BR>")

             End If

          End Sub

    In the datagrid I added: OnItemDataBound="Item_Bound">

    The correct values of what was inserted are being displayed on the screen.

    Now what?
    0
     
    LVL 17

    Expert Comment

    by:AerosSaga
    now where you have your response.write perform your insert.
    0
     

    Author Comment

    by:rreister
    One more problem:

    I want the update to happen when a button (Button1) is clicked.  What it does it insert other information to the database, and grabs the primary key of the newly inserted information and stored it in a variable (strContactKey).  I would like the value stored in strContactKey to also be inserted into the database.  The way I have the code now, the database connection (dbConnection) nor the strContactKey is recognized since it's in a different function.

    How can I call Item_Bound when Button1 is clicked and have it do the insert?

    Sub Button1_Click(sender As Object, e As EventArgs)

            Dim insertReservation as String
            Dim dsn As String = ConfigurationSettings.AppSettings("DSN")
            Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(dsn)

            Dim insertContact = new SqlCommand("INSERT INTO Contacts(firstName, lastName, emailAddress, officeNumber, officeLocation) VALUES('" & Session.Contents("firstName") & "', '" & Session.Contents("lastName") & "', '" & Session.Contents("email") & "', '" & Session.Contents("phoneNum") & "', '" & Session.Contents("location") & "')", dbConnection)
            Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand

            Dim strSQL As String

            'Write data to Contacts Table
            dbConnection.Open()
            insertContact.ExecuteNonQuery()
            insertContact.CommandText = "SELECT @@Identity as 'contactKey'"
            insertContact.ExecuteScalar()
            Dim strContactKey = CStr(insertContact.ExecuteScalar())
            dbConnection.Close()

             Response.Write("Information saved!")
        End Sub 'Button1_Click


          Sub Item_Bound(sender As Object, e As DataGridItemEventArgs)

             If e.Item.ItemType = ListItemType.Item Or _
                 e.Item.ItemType = ListItemType.AlternatingItem Then

                Dim startDate As String = e.Item.Cells(0).Text
                Dim startTime As String = e.Item.Cells(1).Text
                Dim endTime As String = e.Item.Cells(2).Text

    Dim insertRooms As new SqlCommand("INSERT INTO tmpReservation(tmpContact, tmpStartDate, tmpStartTime, tmpEndTime) VALUES (" & strContactKey & ", " & startDate & ", " & startTime & ", " & endTime & ")", dbconnection)

             End If

          End Sub
    0
     
    LVL 17

    Expert Comment

    by:AerosSaga
    well your gonna have to manipulate some things then put this routine in your submit routine, and see if it complains. I'm not sure the same e arugments are going to be applicable, but I guess start there.
    0
     

    Author Comment

    by:rreister
    Is there any other possible way to do this?  Would it be easier to store each row from the datatable to the database instead of a storing a datagrid?  Because, it's actually a datatable being brought in by a session.
    0
     
    LVL 17

    Expert Comment

    by:AerosSaga
    I think so
    0
     
    LVL 17

    Expert Comment

    by:AerosSaga
    having to use the e arguments in the datagrid does take some getting used to and is always tedious
    0
     
    LVL 17

    Accepted Solution

    by:
    assuming dt is your datatable

     Dim dt As New DataTable
     Dim datar As DataRow
      For Each datar In dt.Rows
                    cmd.CommandType = CommandType.Text
                    cmd.Connection = cnnFinalWrite
                    cnnFinalWrite.Open()
                    cmd.CommandText = "INSERT INTO OrderedItems (OrderID, ProductName, Quantity, UnitPrice, SKU) Values ('" & _
                    Me.lblOrderTrackingID.Text & "','" & datar(0).ToString & "','" & datar(1).ToString & "','" & _
                    datar(2).ToString & "','" & datar(3).ToString & "')"
                    cmd.ExecuteNonQuery()
                Next
    0
     
    LVL 17

    Expert Comment

    by:AerosSaga
    of course it won't be a new datatable for you, I just put that in so you would know dt is my datatable in this example.
    0
     

    Author Comment

    by:rreister
    That worked perfectly!  Thank you so much!  Woo hoo!
    0
     
    LVL 17

    Expert Comment

    by:AerosSaga
    Your very welcome.

    Aeros
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
    One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
    In this Experts Exchange video Micro Tutorial, I'm going to show how small business owners who use Google Apps can save money by setting up what is called a catch-all email address in their Gmail accounts. By using the catch-all feature, small busin…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    933 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now