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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
AerosSagaCommented:
You want to loop through your datagrid and insert each row as a new record?  
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.