February 16, 2006 at 6:56 am
Hi,
I am currently creating a web application with VB.NET and SQL Server 2000,
which displays a record of glossaries from an SQL Server Database.
I have completed the adding of individual glossaries from manual user input using web froms and such.
However, I am trying to implement a function whereby the user is able to upload an
excel datasheet to my application or server(not sure how this works).
My application will then be able to extract the data from the datasheet and write it into one of the tables in my SQL Server Database.
Any help in this matter will be greatly appreciated as I do not have much of an idea how do I go about to start coding this function.
For reference, my glossary.aspx
<%@ Page CodeBehind="glossary.aspx.vb" Language="vb" AutoEventWireup="false" Inherits="mLS.glossary" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<HTML>
<HEAD>
<title>Mobile Learning System - Glossary Editor</title>
<!-- saved from url=(0014)about:internet -->
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<style type="text/css">TD IMG { DISPLAY: block }
.searchBackground { BACKGROUND-IMAGE: url(images/glossary_r2_c8.jpg) }
</style>
<!--Fireworks 8 Dreamweaver 8 target. Created Thu Feb 02 14:54:36 GMT+0800 (Malay Peninsula Standard Time) 2006-->
<script language="JavaScript">
function confirm_delete()
{
if (confirm("Are you sure you want to delete this item?")==true)
return true;
else
return false;
}
</script>
</HEAD>
<body bgColor="#ffffff" onload="MM_preloadImages('images/glossary_r3_c1_f2.jpg','images/glossary_r3_c1_f3.jpg','images/glossary_r3_c1.jpg','images/glossary_r3_c3_f2.jpg','images/glossary_r3_c3_f3.jpg','images/glossary_r3_c4_f2.jpg','images/glossary_r3_c4_f3.jpg','images/glossary_r3_c5_f2.jpg','images/glossary_r3_c5_f3.jpg');">
<form runat="server">
<table cellSpacing="0" cellPadding="0" width="900" align="center" border="0">
<!-- fwtable fwsrc="glossary.png" fwbase="glossary.jpg" fwstyle="Dreamweaver" fwdocid = "1461620840" fwnested="0" -->
<tr>
<td><IMG height="1" alt="" src="images/spacer.gif" width="56" border="0"></td>
<td><IMG height="1" alt="" src="images/spacer.gif" width="72" border="0"></td>
<td><IMG height="1" alt="" src="images/spacer.gif" width="135" border="0"></td>
<td><IMG height="1" alt="" src="images/spacer.gif" width="100" border="0"></td>
<td><IMG height="1" alt="" src="images/spacer.gif" width="58" border="0"></td>
<td><IMG height="1" alt="" src="images/spacer.gif" width="51" border="0"></td>
<td><IMG height="1" alt="" src="images/spacer.gif" width="24" border="0"></td>
<td><IMG height="1" alt="" src="images/spacer.gif" width="404" border="0"></td>
<td><IMG height="1" alt="" src="images/spacer.gif" width="1" border="0"></td>
</tr>
<tr>
<td rowSpan="2"><IMG id="glossary_r1_c1" height="77" alt="" src="images/glossary_r1_c1.jpg" width="56"
border="0" name="glossary_r1_c1"></td>
<td colSpan="4"><IMG id="glossary_r1_c2" height="58" alt="" src="images/glossary_r1_c2.jpg" width="365"
border="0" name="glossary_r1_c2"></td>
<td colSpan="3"><IMG id="glossary_r1_c6" height="58" alt="" src="images/glossary_r1_c6.jpg" width="479"
border="0" name="glossary_r1_c6"></td>
<td><IMG height="58" alt="" src="images/spacer.gif" width="1" border="0"></td>
</tr>
<tr>
<td colSpan="6"><IMG id="glossary_r2_c2" height="19" alt="" src="images/glossary_r2_c2.jpg" width="440"
border="0" name="glossary_r2_c2"></td>
<td class="searchBackground" rowSpan="2"> </td>
<td><IMG height="19" alt="" src="images/spacer.gif" width="1" border="0"></td>
</tr>
<tr>
<td colSpan="2" rowSpan="2"><A onmouseover="MM_nbGroup('over','glossary_r3_c1','images/glossary_r3_c1_f2.jpg','images/glossary_r3_c1_f3.jpg',1);"
onclick="MM_nbGroup('down','navbar1','glossary_r3_c1','images/glossary_r3_c1_f3.jpg',1);" onmouseout="MM_nbGroup('out');"
href="snippet.aspx"><IMG id="glossary_r3_c1" height="29" alt="" src="images/glossary_r3_c1_f3.jpg" width="128"
onload="MM_nbGroup('init','navbar1', 'glossary_r3_c1','images/glossary_r3_c1.jpg',1)" border="0" name="glossary_r3_c1"></A></td>
<td rowSpan="2"><A onmouseover="MM_nbGroup('over','glossary_r3_c3','images/glossary_r3_c3_f2.jpg','images/glossary_r3_c3_f3.jpg',1);"
onclick="MM_nbGroup('down','navbar1','glossary_r3_c3','images/glossary_r3_c3_f3.jpg',1);" onmouseout="MM_nbGroup('out');"
href="javascript:;"><IMG id="glossary_r3_c3" height="29" alt="" src="images/glossary_r3_c3.jpg" width="135"
border="0" name="glossary_r3_c3"></A></td>
<td rowSpan="2"><A onmouseover="MM_nbGroup('over','glossary_r3_c4','images/glossary_r3_c4_f2.jpg','images/glossary_r3_c4_f3.jpg',1);"
onclick="MM_nbGroup('down','navbar1','glossary_r3_c4','images/glossary_r3_c4_f3.jpg',1);" onmouseout="MM_nbGroup('out');"
href="javascript:;"><IMG id="glossary_r3_c4" height="29" alt="" src="images/glossary_r3_c4.jpg" width="100"
border="0" name="glossary_r3_c4"></A></td>
<td colSpan="2" rowSpan="2"><A onmouseover="MM_nbGroup('over','glossary_r3_c5','images/glossary_r3_c5_f2.jpg','images/glossary_r3_c5_f3.jpg',1);"
onclick="MM_nbGroup('down','navbar1','glossary_r3_c5','images/glossary_r3_c5_f3.jpg',1);" onmouseout="MM_nbGroup('out');"
href="javascript:;"><IMG id="glossary_r3_c5" height="29" alt="" src="images/glossary_r3_c5.jpg" width="109"
border="0" name="glossary_r3_c5"></A></td>
<td rowSpan="3"><IMG id="glossary_r3_c7" height="40" alt="" src="images/glossary_r3_c7.jpg" width="24"
border="0" name="glossary_r3_c7"></td>
<td><IMG height="23" alt="" src="images/spacer.gif" width="1" border="0"></td>
</tr>
<tr>
<td rowSpan="2"><IMG id="glossary_r4_c8" height="17" alt="" src="images/glossary_r4_c8.jpg" width="404"
border="0" name="glossary_r4_c8"></td>
<td><IMG height="6" alt="" src="images/spacer.gif" width="1" border="0"></td>
</tr>
<tr>
<td colSpan="6"><IMG id="glossary_r5_c1" height="11" alt="" src="images/glossary_r5_c1.jpg" width="472"
border="0" name="glossary_r5_c1"></td>
<td><IMG height="11" alt="" src="images/spacer.gif" width="1" border="0"></td>
</tr>
<tr>
<td vAlign="top" colSpan="8"><asp:label id="welcome" runat="server" ForeColor="#6666A3"></asp:label><asp:label id="check" runat="server"></asp:label><br>
<asp:datagrid id="glossData" runat="server" DataKeyField="gID" OnUpdateCommand="UpdateRecord"
OnCancelCommand="CancelEdit" OnEditCommand="EditRecord" AutoGenerateColumns="False" Width="898px"
OnDeleteCommand="DeleteRecord" PageSize="5" AllowPaging="True" OnPageIndexChanged="PageChange"
AllowSorting="True" OnSortCommand="SortColumn">
<Columns>
<asp:BoundColumn DataField="gTerm" SortExpression="gTerm" HeaderText="Term"></asp:BoundColumn>
<asp:BoundColumn DataField="gDef" HeaderText="Definition"></asp:BoundColumn>
<asp:BoundColumn DataField="Contributor" SortExpression="Contributor" ReadOnly="True" HeaderText="Contributor"></asp:BoundColumn>
<asp:EditCommandColumn ButtonType="LinkButton" UpdateText="Update" CancelText="Cancel" EditText="Edit"></asp:EditCommandColumn>
<asp:TemplateColumn>
<ItemTemplate>
<asp:LinkButton id="DelBut" runat="server" Text="Delete" CommandName="Delete" />
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
<PagerStyle Mode="NumericPages"></PagerStyle>
</asp:datagrid>
<table border="0">
<tr>
<td width="296" colSpan="2"><asp:button id="addGloss" runat="server" Width="328px" BackColor="#6666A3" BorderColor="Black"
BorderStyle="Double" BorderWidth="3px" Font-Bold="True" Text="Add a New Glossary"></asp:button></td>
<td><asp:label id="lblError" runat="server" ForeColor="Red" Visible="False">Please ensure both fields are not empty</asp:label></td>
</tr>
<tr>
<td><asp:label id="Term" runat="server" Font-Bold="True" Visible="False">Term:</asp:label></td>
<td width="219"><asp:textbox id="glossTerm" runat="server" Width="255px" Visible="False"></asp:textbox></td>
<td><asp:requiredfieldvalidator id="termVal" runat="server" ErrorMessage="Please enter a term" ControlToValidate="glossTerm"
Visible="False"></asp:requiredfieldvalidator></td>
</tr>
<tr>
<td><asp:label id="Definition" runat="server" Font-Bold="True" Visible="False">Definition:</asp:label></td>
<td width="219"><asp:textbox id="glossDef" runat="server" Width="255px" Visible="False"></asp:textbox></td>
<td><asp:requiredfieldvalidator id="defVal" runat="server" ErrorMessage="Please enter a definition" ControlToValidate="glossDef"
Visible="False"></asp:requiredfieldvalidator></td>
</tr>
<tr>
<td width="296" colSpan="2"><asp:button id="addGlossBut" runat="server" Width="104px" BackColor="#FFFFC0" BorderColor="Black"
Text="Add Glossary" Visible="False"></asp:button><asp:button id="cancelBut" runat="server" BackColor="#FFFFC0" BorderColor="Black" Text="Cancel"
CausesValidation="False" Visible="False"></asp:button></td>
</tr>
</table>
</td>
<td><IMG height="433" alt="" src="images/spacer.gif" width="1" border="0"></td>
</tr>
</table>
</form>
</body>
</HTML>
And my glossary.vb
Imports System.Data.SqlClient
Imports System.Data
Imports System.Text
Imports System.Web.Security
Public Class glossary
Inherits System.Web.UI.Page
'Open connection to database
Dim connect_Str As String = "Data Source=localhost;Initial Catalog=mLS;Integrated Security=True;"
Dim conn As New SqlConnection(connect_Str)
Protected WithEvents Term As System.Web.UI.WebControls.Label
Protected WithEvents glossTerm As System.Web.UI.WebControls.TextBox
Protected WithEvents Definition As System.Web.UI.WebControls.Label
Protected WithEvents glossDef As System.Web.UI.WebControls.TextBox
Protected WithEvents addGlossBut As System.Web.UI.WebControls.Button
Protected WithEvents termVal As System.Web.UI.WebControls.RequiredFieldValidator
Protected WithEvents defVal As System.Web.UI.WebControls.RequiredFieldValidator
Protected WithEvents addGloss As System.Web.UI.WebControls.Button
Protected WithEvents lblError As System.Web.UI.WebControls.Label
Protected WithEvents cancelBut As System.Web.UI.WebControls.Button
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
End Sub
Protected WithEvents welcome As System.Web.UI.WebControls.Label
Protected WithEvents check As System.Web.UI.WebControls.Label
Protected WithEvents glossData As System.Web.UI.WebControls.DataGrid
'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
If Session("userID") Is Nothing Then
Response.Redirect("index.aspx")
End If
Dim userID, userRole
userID = Session("userID")
userRole = Session("userRole")
welcome.Visible = True
welcome.Text = "You are logged in as: <b>" & userID & "</b>"
viewstate("sortField") = "gTerm"
viewstate("sortDirection") = "ASC"
DisplayData(userID, userRole)
End If
End Sub
Private Sub glossData_ItemDataBound(ByVal sender As System.Object, ByVal e As DataGridItemEventArgs) Handles glossData.ItemDataBound
Dim btn As LinkButton
If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
btn = CType(e.Item.Cells(0).FindControl("DelBut"), LinkButton)
btn.Attributes.Add("onclick", "return confirm_delete();")
End If
End Sub
Public Sub PageChange(ByVal sender As Object, ByVal E As DataGridPageChangedEventArgs)
glossData.EditItemIndex = -1
lblError.Visible = False
glossData.CurrentPageIndex = E.NewPageIndex
DisplayData(Session("userID"), Session("userRole"))
End Sub
Public Sub SortColumn(ByVal Source As Object, ByVal E As DataGridSortCommandEventArgs)
If E.SortExpression.ToString() = viewstate("sortField").ToString() Then
Select Case viewstate("sortDirection").ToString()
Case "ASC"
viewstate("sortDirection") = "DESC"
Case "DESC"
viewstate("sortDirection") = "ASC"
End Select
Else
viewstate("sortField") = E.SortExpression
viewstate("sortDirection") = "ASC"
End If
DisplayData(Session("userID"), Session("userRole"))
End Sub
Private Sub DataGrid1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
End Sub
Public Function DisplayData(ByVal userID As String, ByVal userRole As String)
Try
conn.Open()
Dim strSQL As String
'Checking for User Role
If userRole = 1 Then
strSQL = "SELECT gID, gTerm, gDef, Contributor FROM Glossary ORDER BY " & viewstate("sortField").ToString() & " " & viewstate("sortDirection").ToString()
ElseIf userRole = 2 Then
strSQL = "SELECT gID, gTerm, gDef, Contributor FROM Glossary WHERE Contributor = '" & userID & "'"
End If
Dim sqlCmd As New SqlDataAdapter(strSQL, conn)
Dim rs As New DataSet
sqlCmd.Fill(rs, "Glossary")
'Dim dataTbl As DataTable = rs.Tables("Glossary")
'Dim dvView As New DataView(dataTbl)
'dvView.Sort = viewstate("sortField")
check.Visible = True
check.Text = "Loaded"
glossData.DataSource = rs
glossData.DataBind()
conn.Close()
Catch exc As Exception
check.Visible = True
check.Text = exc.Message
End Try
End Function
Public Sub EditRecord(ByVal sender As System.Object, ByVal e As DataGridCommandEventArgs)
Term.Visible = False
Definition.Visible = False
glossTerm.Visible = False
glossTerm.Text = ""
glossDef.Visible = False
glossDef.Text = ""
addGlossBut.Visible = False
cancelBut.Visible = False
glossData.EditItemIndex = e.Item.ItemIndex
DisplayData(Session("userID"), Session("userRole"))
End Sub
Public Sub CancelEdit(ByVal sender As System.Object, ByVal e As DataGridCommandEventArgs)
glossData.EditItemIndex = -1
DisplayData(Session("userID"), Session("userRole"))
lblError.Visible = False
End Sub
Public Sub UpdateRecord(ByVal sender As System.Object, ByVal e As DataGridCommandEventArgs)
Dim PrimaryKey As Int32 = Convert.ToInt32(glossData.DataKeys(e.Item.ItemIndex))
Dim strTermTB As TextBox = CType(e.Item.Cells(0).Controls(0), TextBox)
Dim strDefTB As TextBox = CType(e.Item.Cells(1).Controls(0), TextBox)
Dim strTerm As String = Convert.ToString(strTermTB.Text)
Dim strDef As String = Convert.ToString(strDefTB.Text)
If strTerm = "" Or strDef = "" Then
lblError.Visible = True
termVal.Visible = False
defVal.Visible = False
Else
glossData.EditItemIndex = -1
check.Visible = True
check.Text = "UpdateRec:" & strTerm
UpdateData(PrimaryKey, strTerm, strDef)
lblError.Visible = False
End If
End Sub
Private Sub UpdateData(ByVal PrimaryKey As Long, ByVal strTerm As String, ByVal strDef As String)
'Create and load a DataSet with records from Glossary Table
conn.Open()
Dim strSelect As String = "SELECT gID, gTerm, gDef, Contributor FROM Glossary"
Dim adapter As New SqlDataAdapter(strSelect, conn)
Dim ds As New DataSet
adapter.Fill(ds, "Glossary")
conn.Close()
'Modify the in-memory records in the Dataset
Dim tbl As DataTable = ds.Tables("Glossary")
tbl.PrimaryKey = New DataColumn() {tbl.Columns("gID")}
Dim row As DataRow = tbl.Rows.Find(PrimaryKey)
row.Item("gTerm") = strTerm
row.Item("gDef") = strDef
'Re-connect the DataSet and update the database
Dim sqlCmd As New SqlCommandBuilder(adapter)
conn.Open()
adapter.Update(ds, "Glossary")
conn.Close()
' Reset & Bind Datagrid
glossData.DataSource = ds.Tables("Glossary")
glossData.DataBind()
termVal.Visible = False
defVal.Visible = False
End Sub
Public Sub DeleteRecord(ByVal sender As System.Object, ByVal e As DataGridCommandEventArgs)
Dim PrimaryKey As Int32 = Convert.ToInt32(glossData.DataKeys(e.Item.ItemIndex))
glossData.EditItemIndex = -1
DeleteData(PrimaryKey)
End Sub
Private Sub DeleteData(ByVal PrimaryKey As Long)
conn.Open()
Dim strSelect As String = "SELECT gID, gTerm, gDef, Contributor FROM Glossary"
Dim adapter As New SqlDataAdapter(strSelect, conn)
Dim ds As New DataSet
adapter.Fill(ds, "Glossary")
conn.Close()
Dim tbl As DataTable = ds.Tables("Glossary")
tbl.PrimaryKey = New DataColumn() {tbl.Columns("gID")}
Dim row As DataRow = tbl.Rows.Find(PrimaryKey)
row.Delete()
Dim sqlCmd As New SqlCommandBuilder(adapter)
conn.Open()
adapter.Update(ds, "Glossary")
conn.Close()
glossData.DataSource = ds.Tables("Glossary")
glossData.DataBind()
termVal.Visible = False
defVal.Visible = False
End Sub
Private Sub glossData_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles glossData.SelectedIndexChanged
End Sub
Private Sub addGloss_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles addGloss.Click
glossData.EditItemIndex = -1
DisplayData(Session("userID"), Session("userRole"))
lblError.Visible = False
Term.Visible = True
Definition.Visible = True
glossTerm.Visible = True
glossDef.Visible = True
addGlossBut.Visible = True
cancelBut.Visible = True
termVal.Visible = True
defVal.Visible = True
End Sub
Private Sub cancelBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cancelBut.Click
Term.Visible = False
Definition.Visible = False
glossTerm.Visible = False
glossTerm.Text = ""
glossDef.Visible = False
glossDef.Text = ""
addGlossBut.Visible = False
cancelBut.Visible = False
End Sub
Private Sub addGlossBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles addGlossBut.Click
If Not Page.IsValid Then
termVal.Visible = True
defVal.Visible = True
Else
conn.Open()
Dim strSelect As String = "SELECT gID, gTerm, gDef, Contributor FROM Glossary"
Dim adapter As New SqlDataAdapter(strSelect, conn)
Dim ds As New DataSet
adapter.Fill(ds)
conn.Close()
'Create a new row
Dim r As DataRow = ds.Tables(0).NewRow()
'Add gTerm, gDef and COntributor
r("gTerm") = glossTerm.Text
r("gDef") = glossDef.Text
r("Contributor") = Session("userID")
'Add the new row into the dataset
ds.Tables(0).Rows.Add(r)
'Update the database
Dim sqlCmd As New SqlCommandBuilder(adapter)
conn.Open()
adapter.Update(ds)
conn.Close()
Term.Visible = False
Definition.Visible = False
glossTerm.Visible = False
glossTerm.Text = ""
glossDef.Visible = False
glossDef.Text = ""
addGlossBut.Visible = False
cancelBut.Visible = False
DisplayData(Session("userID"), Session("userRole"))
End If
End Sub
End Class
February 16, 2006 at 8:43 pm
bump
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply