[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel to DataGrid  to Database

Posted on 2009-02-22
4
Medium Priority
?
489 Views
Last Modified: 2012-05-06
Hello Experts,
   I am trying to take an excel sheet that has some numeric tables, export them to a datagrid and then on a submit click, I want to insert or update each of the rows in the datagrid to the database.

I know how to go from the excel sheet to a datagrid, but I am having trouble with the following:

1) Validating the data coming from the excel sheet to the datagrid.
2) Taking a valid datagrid and updating or inserting each row into a database.

Any help and code samples would be appreciated.
0
Comment
Question by:aatif84
  • 2
  • 2
4 Comments
 
LVL 2

Expert Comment

by:kirihimete
ID: 23707905
For this code example I created a simple dataset with some mock data to populate the datagrid. I then perform a foreach on all items (rows if you're using a gridview) and created a reference to the controls in the datagrid (lblCustomerId, lblFirstName, lblLastName) with 3 labels as shown.  I then send those values to the function that will save the data to the database.
ASPX CODE:
 
<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>
 
<%@ Register assembly="System.Web.Entity, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" namespace="System.Web.UI.WebControls" tagprefix="asp" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:DataGrid ID="dgTest" runat="server" AutoGenerateColumns="false">
            <Columns>
                <asp:TemplateColumn HeaderText="Customer ID">
                    <ItemTemplate>
                        <asp:Label ID="lblCustomerId" runat="server" Text='<%#Eval("CustomerId") %>' />
                    </ItemTemplate>
                </asp:TemplateColumn>
                <asp:TemplateColumn HeaderText="First Name">
                    <ItemTemplate>
                        <asp:Label ID="lblFirstName" runat="server" Text='<%#Eval("FirstName") %>' />
                    </ItemTemplate>
                </asp:TemplateColumn>
                <asp:TemplateColumn HeaderText="Last Name">
                    <ItemTemplate>
                        <asp:Label ID="lblLastName" runat="server" Text='<%#Eval("LastName") %>' />
                    </ItemTemplate>
                </asp:TemplateColumn>
            </Columns>
        </asp:DataGrid>
    </div>
    </form>
</body>
</html>
 
 
 
 
 
C# CODE BEHIND:
 
using System;
using System.Web.UI.WebControls;
 
public partial class _Default : System.Web.UI.Page 
{
    /// <summary>
    /// Handles the Load event of the Page control.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void Page_Load(object sender, EventArgs e)
    {
        DataSet1TableAdapters.CustomerTableAdapter tableAdapter = new DataSet1TableAdapters.CustomerTableAdapter();
 
        dgTest.DataSource = tableAdapter.GetData();
        dgTest.DataBind();
 
        foreach (DataGridItem item in dgTest.Items)
        {
            Label customerId = (Label)item.FindControl("lblCustomerId");
            Label firstName = (Label)item.FindControl("lblFirstName");
            Label lastName = (Label)item.FindControl("lblLastName");
 
            SaveToDatabase(customerId.Text.Trim(), firstName.Text.Trim(), lastName.Text.Trim());
        }
    }
 
    /// <summary>
    /// Saves to database.
    /// </summary>
    /// <param name="customerId">The customer id.</param>
    /// <param name="firstName">The first name.</param>
    /// <param name="lastName">The last name.</param>
    protected void SaveToDatabase(string customerId, string firstName, string lastName)
    {
        // CODE HERE TO DAL ONLY SHOWING HERE FOR DEMONSTRATION PURPOSES :)
    }
}

Open in new window

0
 
LVL 1

Author Comment

by:aatif84
ID: 23708058
could you post that same code in VB.Net?
0
 
LVL 2

Accepted Solution

by:
kirihimete earned 2000 total points
ID: 23708366
Here you go.

Partial Class Default2
    Inherits System.Web.UI.Page
 
    ''' <summary> 
    ''' Handles the Load event of the Page control. 
    ''' </summary> 
    ''' <param name="sender">The source of the event.</param> 
    ''' <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param> 
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
        Dim tableAdapter As New DataSet1TableAdapters.CustomerTableAdapter()
 
        dgTest.DataSource = tableAdapter.GetData()
        dgTest.DataBind()
 
        For Each item As DataGridItem In dgTest.Items
            Dim customerId As Label = DirectCast(item.FindControl("lblCustomerId"), Label)
            Dim firstName As Label = DirectCast(item.FindControl("lblFirstName"), Label)
            Dim lastName As Label = DirectCast(item.FindControl("lblLastName"), Label)
 
            SaveToDatabase(customerId.Text.Trim(), firstName.Text.Trim(), lastName.Text.Trim())
        Next
    End Sub
 
    ''' <summary> 
    ''' Saves to database. 
    ''' </summary> 
    ''' <param name="customerId">The customer id.</param> 
    ''' <param name="firstName">The first name.</param> 
    ''' <param name="lastName">The last name.</param> 
    Protected Sub SaveToDatabase(ByVal customerId As String, ByVal firstName As String, ByVal lastName As String)
        ' CODE HERE TO DAL ONLY SHOWING HERE FOR DEMONSTRATION PURPOSES :) 
    End Sub
 
End Class

Open in new window

0
 
LVL 1

Author Closing Comment

by:aatif84
ID: 31549914
Thanks!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this Article, I will provide a few tips in problem and solution manner. Opening an ASPX page in Visual studio 2003 is very slow. To make it fast, please do follow below steps:   Open the Solution/Project. Right click the ASPX file to b…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses
Course of the Month19 days, 12 hours left to enroll

873 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