Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-10-26
26
Medium Priority
?
1,274 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
Comment
Question by:rreister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 10
26 Comments
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12411224
<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
ID: 12411296
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
ID: 12411314
You want to loop through your datagrid and insert each row as a new record?  
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 17

Expert Comment

by:AerosSaga
ID: 12411374
<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
ID: 12411756
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
ID: 12411786
those were for demonstration only
0
 

Author Comment

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

Expert Comment

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

Author Comment

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

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

Author Comment

by:rreister
ID: 12411849
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
ID: 12411858
ok try a quick watch for
 item
0
 

Author Comment

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

Expert Comment

by:AerosSaga
ID: 12411887
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
ID: 12411891
ouch
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12411913
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
ID: 12412112
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
ID: 12412162
now where you have your response.write perform your insert.
0
 

Author Comment

by:rreister
ID: 12412296
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
ID: 12413022
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
ID: 12414025
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
ID: 12414060
I think so
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12414064
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:
AerosSaga earned 1000 total points
ID: 12414103
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
ID: 12414109
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
ID: 12414316
That worked perfectly!  Thank you so much!  Woo hoo!
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12414329
Your very welcome.

Aeros
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

609 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