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

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>
rreisterAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
AerosSagaConnect With a Mentor Commented:
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
 
AerosSagaCommented:
<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
 
rreisterAuthor Commented:
AerosSaga

That doesn't help me at all.  I need something to simply insert my datagrid into my SQL Server database.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
AerosSagaCommented:
You want to loop through your datagrid and insert each row as a new record?  
0
 
AerosSagaCommented:
<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
 
rreisterAuthor Commented:
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
 
AerosSagaCommented:
those were for demonstration only
0
 
rreisterAuthor Commented:
Right.  Could you help me fine-tune my code so I can get this to work?
0
 
AerosSagaCommented:
ok so when you step through is item getting assigned correctly?
0
 
rreisterAuthor Commented:
I do a response.write(item) and get the following:

System.Web.UI.WebControls.DataGridItemSystem.Web.UI.WebControls.DataGridItem
0
 
rreisterAuthor Commented:
So, to be more specific, it's keeping count correctly (since I have 2 entries in my datagrid right now).
0
 
AerosSagaCommented:
ok try a quick watch for
 item
0
 
rreisterAuthor Commented:
Unfortunately, I'm using Web Matrix for design.  Don't have Visual Studio.
0
 
AerosSagaCommented:
also see if you can type item.cells(1) thats how you need to access each cell in each row.
0
 
AerosSagaCommented:
ouch
0
 
AerosSagaCommented:
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
 
rreisterAuthor Commented:
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
 
AerosSagaCommented:
now where you have your response.write perform your insert.
0
 
rreisterAuthor Commented:
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
 
AerosSagaCommented:
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
 
rreisterAuthor Commented:
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
 
AerosSagaCommented:
I think so
0
 
AerosSagaCommented:
having to use the e arguments in the datagrid does take some getting used to and is always tedious
0
 
AerosSagaCommented:
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
 
rreisterAuthor Commented:
That worked perfectly!  Thank you so much!  Woo hoo!
0
 
AerosSagaCommented:
Your very welcome.

Aeros
0
All Courses

From novice to tech pro — start learning today.