Unable to import XLS into SQL database through DataSet and TableAdapter

<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="StudentInfoADataSet" targetNamespace="http://tempuri.org/StudentInfoADataSet.xsd" xmlns:mstns="http://tempuri.org/StudentInfoADataSet.xsd" xmlns="http://tempuri.org/StudentInfoADataSet.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:msprop="urn:schemas-microsoft-com:xml-msprop" attributeFormDefault="qualified" elementFormDefault="qualified">
  <xs:annotation>
    <xs:appinfo source="urn:schemas-microsoft-com:xml-msdatasource">
      <DataSource DefaultConnectionIndex="0" FunctionsComponentName="QueriesTableAdapter" Modifier="AutoLayout, AnsiClass, Class, Public" SchemaSerializationMode="IncludeSchema" xmlns="urn:schemas-microsoft-com:xml-msdatasource">
        <Connections>
          <Connection AppSettingsObjectName="Web.config" AppSettingsPropertyName="FINAOsmsSQL1mdf_92291StudentInfoDataSetConnectionString" ConnectionStringObject="" IsAppSettingsProperty="true" Modifier="Assembly" Name="FINAOsmsSQL1mdf_92291StudentInfoDataSetConnectionString (Web.config)" ParameterPrefix="@" PropertyReference="AppConfig.System.Configuration.ConfigurationManager.0.ConnectionStrings.FINAOsmsSQL1mdf_92291StudentInfoDataSetConnectionString.ConnectionString" Provider="System.Data.SqlClient" />
        </Connections>
        <Tables>
          <TableAdapter BaseClass="System.ComponentModel.Component" DataAccessorModifier="AutoLayout, AnsiClass, Class, Public" DataAccessorName="StudentInfoA_localTableAdapter" GeneratorDataComponentClassName="StudentInfoA_localTableAdapter" Name="StudentInfoA_local" UserDataComponentName="StudentInfoA_localTableAdapter">
            <MainSource>
              <DbSource ConnectionRef="FINAOsmsSQL1mdf_92291StudentInfoDataSetConnectionString (Web.config)" DbObjectName="[FINAOsmsSQL1.mdf_92291].dbo.StudentInfoA_local" DbObjectType="Table" GenerateMethods="Get" GenerateShortCommands="false" GeneratorGetMethodName="GetStudentInfoAData" GetMethodModifier="Public" GetMethodName="GetStudentInfoAData" QueryType="Rowset" ScalarCallRetval="System.Object, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" UseOptimisticConcurrency="false" UserGetMethodName="GetStudentInfoAData" UserSourceName="GetStudentInfoAData">
                <SelectCommand>
                  <DbCommand CommandType="Text" ModifiedByUser="true">
                    <CommandText>SELECT        StudentInfoA_local.*
FROM            StudentInfoA_local</CommandText>
                    <Parameters />
                  </DbCommand>
                </SelectCommand>
              </DbSource>
            </MainSource>
            <Mappings>
              <Mapping SourceColumn="student_id" DataSetColumn="student_id" />
              <Mapping SourceColumn="last_name" DataSetColumn="last_name" />
              <Mapping SourceColumn="first_name" DataSetColumn="first_name" />
              <Mapping SourceColumn="middle_name" DataSetColumn="middle_name" />
              <Mapping SourceColumn="Gender" DataSetColumn="Gender" />
              <Mapping SourceColumn="birthdate" DataSetColumn="birthdate" />
              <Mapping SourceColumn="Grade" DataSetColumn="Grade" />
              <Mapping SourceColumn="ethnicity" DataSetColumn="ethnicity" />
              <Mapping SourceColumn="lep" DataSetColumn="lep" />
              <Mapping SourceColumn="SPED" DataSetColumn="SPED" />
              <Mapping SourceColumn="Sheltered" DataSetColumn="Sheltered" />
              <Mapping SourceColumn="AIP" DataSetColumn="AIP" />
              <Mapping SourceColumn="AVID" DataSetColumn="AVID" />
              <Mapping SourceColumn="ELL" DataSetColumn="ELL" />
              <Mapping SourceColumn="notes" DataSetColumn="notes" />
              <Mapping SourceColumn="StudentARecordNumber" DataSetColumn="StudentARecordNumber" />
              <Mapping SourceColumn="He_She" DataSetColumn="He_She" />
              <Mapping SourceColumn="His_Hers" DataSetColumn="His_Hers" />
              <Mapping SourceColumn="Him_Her" DataSetColumn="Him_Her" />
            </Mappings>
            <Sources>
              <DbSource ConnectionRef="FINAOsmsSQL1mdf_92291StudentInfoDataSetConnectionString (Web.config)" DbObjectName="[FINAOsmsSQL1.mdf_92291].dbo.StudentInfoA_local" DbObjectType="Table" GenerateMethods="Get" GenerateShortCommands="true" GeneratorGetMethodName="GetStudentInfoADataByRecordNumber" GeneratorSourceName="FillBy" GetMethodModifier="Public" GetMethodName="GetStudentInfoADataByRecordNumber" QueryType="Rowset" ScalarCallRetval="System.Object, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" UseOptimisticConcurrency="true" UserGetMethodName="GetStudentInfoADataByRecordNumber" UserSourceName="GetStudentInfoADataByRecordNumber">
                <SelectCommand>
                  <DbCommand CommandType="Text" ModifiedByUser="true">
                    <CommandText>SELECT        student_id, StudentARecordNumber
FROM            StudentInfoA_local
WHERE        (student_id = @student_id)</CommandText>
                    <Parameters>
                      <Parameter AllowDbNull="false" AutogeneratedName="student_id" ColumnName="student_id" DataSourceName="[FINAOsmsSQL1.mdf_92291].dbo.StudentInfoA_local" DataTypeServer="int" DbType="Int32" Direction="Input" ParameterName="@student_id" Precision="0" ProviderType="Int" Scale="0" Size="4" SourceColumn="student_id" SourceColumnNullMapping="false" SourceVersion="Current" />
                    </Parameters>
                  </DbCommand>
                </SelectCommand>
              </DbSource>
              <DbSource ConnectionRef="FINAOsmsSQL1mdf_92291StudentInfoDataSetConnectionString (Web.config)" DbObjectName="" DbObjectType="Unknown" GenerateShortCommands="true" GeneratorGetMethodName="GetDataBy" GeneratorSourceName="InsertStudentInfoAQuery" Modifier="Public" Name="InsertStudentInfoAQuery" QueryType="NoData" ScalarCallRetval="System.Object, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" UseOptimisticConcurrency="true" UserGetMethodName="GetDataBy" UserSourceName="InsertStudentInfoAQuery">
                <InsertCommand>
                  <DbCommand CommandType="Text" ModifiedByUser="true">
                    <CommandText>INSERT into [StudentInfoA_local] (student_id, last_name, first_name, middle_name, Gender, birthdate, Grade, ethnicity, lep, SPED, Sheltered, AIP, AVID, ELL, notes, He_She, His_Hers, Him_Her)
VALUES (@student_id, @ last_name, @ first_name, @ middle_name, @ Gender, @ birthdate, @ Grade, @ ethnicity, @ lep, @ SPED, @ Sheltered, @ AIP, @ AVID, @ ELL, @ notes, @ He_She, @ His_Hers, @ Him_Her)
 
-- return the StudentARecordNumber
SELECT SCOPE_IDENTITY()</CommandText>
                    <Parameters />
                  </DbCommand>
                </InsertCommand>
              </DbSource>
            </Sources>
          </TableAdapter>
        </Tables>
        <Sources />
      </DataSource>
    </xs:appinfo>
  </xs:annotation>
  <xs:element name="StudentInfoADataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true" msprop:Generator_UserDSName="StudentInfoADataSet" msprop:Generator_DataSetName="StudentInfoADataSet">
    <xs:complexType>
      <xs:choice minOccurs="0" maxOccurs="unbounded">
        <xs:element name="StudentInfoA_local" msprop:Generator_UserTableName="StudentInfoA_local" msprop:Generator_RowDeletedName="StudentInfoA_localRowDeleted" msprop:Generator_RowChangedName="StudentInfoA_localRowChanged" msprop:Generator_RowClassName="StudentInfoA_localRow" msprop:Generator_RowChangingName="StudentInfoA_localRowChanging" msprop:Generator_RowEvArgName="StudentInfoA_localRowChangeEvent" msprop:Generator_RowEvHandlerName="StudentInfoA_localRowChangeEventHandler" msprop:Generator_TableClassName="StudentInfoA_localDataTable" msprop:Generator_TableVarName="tableStudentInfoA_local" msprop:Generator_RowDeletingName="StudentInfoA_localRowDeleting" msprop:Generator_TablePropName="StudentInfoA_local">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="student_id" msprop:Generator_UserColumnName="student_id" msprop:Generator_ColumnPropNameInRow="student_id" msprop:Generator_ColumnVarNameInTable="columnstudent_id" msprop:Generator_ColumnPropNameInTable="student_idColumn" type="xs:int" />
              <xs:element name="last_name" msprop:Generator_UserColumnName="last_name" msprop:Generator_ColumnPropNameInRow="last_name" msprop:Generator_ColumnVarNameInTable="columnlast_name" msprop:Generator_ColumnPropNameInTable="last_nameColumn" minOccurs="0">
                <xs:simpleType>
                  <xs:restriction base="xs:string">
                    <xs:maxLength value="255" />
                  </xs:restriction>
                </xs:simpleType>
              </xs:element>
              <xs:element name="first_name" msprop:Generator_UserColumnName="first_name" msprop:Generator_ColumnPropNameInRow="first_name" msprop:Generator_ColumnVarNameInTable="columnfirst_name" msprop:Generator_ColumnPropNameInTable="first_nameColumn" minOccurs="0">
                <xs:simpleType>
                  <xs:restriction base="xs:string">
                    <xs:maxLength value="255" />
                  </xs:restriction>
                </xs:simpleType>
              </xs:element>
              <xs:element name="middle_name" msprop:Generator_UserColumnName="middle_name" msprop:Generator_ColumnPropNameInRow="middle_name" msprop:Generator_ColumnVarNameInTable="columnmiddle_name" msprop:Generator_ColumnPropNameInTable="middle_nameColumn" minOccurs="0">
                <xs:simpleType>
                  <xs:restriction base="xs:string">
                    <xs:maxLength value="255" />
                  </xs:restriction>
                </xs:simpleType>
              </xs:element>
              <xs:element name="Gender" msprop:Generator_UserColumnName="Gender" msprop:Generator_ColumnPropNameInRow="Gender" msprop:Generator_ColumnVarNameInTable="columnGender" msprop:Generator_ColumnPropNameInTable="GenderColumn" minOccurs="0">
                <xs:simpleType>
                  <xs:restriction base="xs:string">
                    <xs:maxLength value="255" />
                  </xs:restriction>
                </xs:simpleType>
              </xs:element>
              <xs:element name="birthdate" msprop:Generator_UserColumnName="birthdate" msprop:Generator_ColumnPropNameInRow="birthdate" msprop:Generator_ColumnVarNameInTable="columnbirthdate" msprop:Generator_ColumnPropNameInTable="birthdateColumn" type="xs:dateTime" minOccurs="0" />
              <xs:element name="Grade" msprop:Generator_UserColumnName="Grade" msprop:Generator_ColumnPropNameInRow="Grade" msprop:Generator_ColumnVarNameInTable="columnGrade" msprop:Generator_ColumnPropNameInTable="GradeColumn" type="xs:double" minOccurs="0" />
              <xs:element name="ethnicity" msprop:Generator_UserColumnName="ethnicity" msprop:Generator_ColumnPropNameInRow="ethnicity" msprop:Generator_ColumnVarNameInTable="columnethnicity" msprop:Generator_ColumnPropNameInTable="ethnicityColumn" minOccurs="0">
                <xs:simpleType>
                  <xs:restriction base="xs:string">
                    <xs:maxLength value="10" />
                  </xs:restriction>
                </xs:simpleType>
              </xs:element>
              <xs:element name="lep" msprop:Generator_UserColumnName="lep" msprop:Generator_ColumnPropNameInRow="lep" msprop:Generator_ColumnVarNameInTable="columnlep" msprop:Generator_ColumnPropNameInTable="lepColumn" type="xs:boolean" minOccurs="0" />
              <xs:element name="SPED" msprop:Generator_UserColumnName="SPED" msprop:Generator_ColumnPropNameInRow="SPED" msprop:Generator_ColumnVarNameInTable="columnSPED" msprop:Generator_ColumnPropNameInTable="SPEDColumn" type="xs:boolean" minOccurs="0" />
              <xs:element name="Sheltered" msprop:Generator_UserColumnName="Sheltered" msprop:Generator_ColumnPropNameInRow="Sheltered" msprop:Generator_ColumnVarNameInTable="columnSheltered" msprop:Generator_ColumnPropNameInTable="ShelteredColumn" type="xs:boolean" minOccurs="0" />
              <xs:element name="AIP" msprop:Generator_UserColumnName="AIP" msprop:Generator_ColumnPropNameInRow="AIP" msprop:Generator_ColumnVarNameInTable="columnAIP" msprop:Generator_ColumnPropNameInTable="AIPColumn" type="xs:boolean" minOccurs="0" />
              <xs:element name="AVID" msprop:Generator_UserColumnName="AVID" msprop:Generator_ColumnPropNameInRow="AVID" msprop:Generator_ColumnVarNameInTable="columnAVID" msprop:Generator_ColumnPropNameInTable="AVIDColumn" type="xs:boolean" minOccurs="0" />
              <xs:element name="ELL" msprop:Generator_UserColumnName="ELL" msprop:Generator_ColumnPropNameInRow="ELL" msprop:Generator_ColumnVarNameInTable="columnELL" msprop:Generator_ColumnPropNameInTable="ELLColumn" type="xs:boolean" minOccurs="0" />
              <xs:element name="notes" msprop:Generator_UserColumnName="notes" msprop:Generator_ColumnPropNameInRow="notes" msprop:Generator_ColumnVarNameInTable="columnnotes" msprop:Generator_ColumnPropNameInTable="notesColumn" minOccurs="0">
                <xs:simpleType>
                  <xs:restriction base="xs:string">
                    <xs:maxLength value="2147483647" />
                  </xs:restriction>
                </xs:simpleType>
              </xs:element>
              <xs:element name="StudentARecordNumber" msdata:ReadOnly="true" msdata:AutoIncrement="true" msdata:AutoIncrementSeed="-1" msdata:AutoIncrementStep="-1" msprop:Generator_UserColumnName="StudentARecordNumber" msprop:Generator_ColumnPropNameInRow="StudentARecordNumber" msprop:Generator_ColumnVarNameInTable="columnStudentARecordNumber" msprop:Generator_ColumnPropNameInTable="StudentARecordNumberColumn" type="xs:int" />
              <xs:element name="He_She" msprop:Generator_UserColumnName="He_She" msprop:Generator_ColumnPropNameInRow="He_She" msprop:Generator_ColumnVarNameInTable="columnHe_She" msprop:Generator_ColumnPropNameInTable="He_SheColumn" minOccurs="0">
                <xs:simpleType>
                  <xs:restriction base="xs:string">
                    <xs:maxLength value="10" />
                  </xs:restriction>
                </xs:simpleType>
              </xs:element>
              <xs:element name="His_Hers" msprop:Generator_UserColumnName="His_Hers" msprop:Generator_ColumnPropNameInRow="His_Hers" msprop:Generator_ColumnVarNameInTable="columnHis_Hers" msprop:Generator_ColumnPropNameInTable="His_HersColumn" minOccurs="0">
                <xs:simpleType>
                  <xs:restriction base="xs:string">
                    <xs:maxLength value="10" />
                  </xs:restriction>
                </xs:simpleType>
              </xs:element>
              <xs:element name="Him_Her" msprop:Generator_UserColumnName="Him_Her" msprop:Generator_ColumnPropNameInRow="Him_Her" msprop:Generator_ColumnVarNameInTable="columnHim_Her" msprop:Generator_ColumnPropNameInTable="Him_HerColumn" minOccurs="0">
                <xs:simpleType>
                  <xs:restriction base="xs:string">
                    <xs:maxLength value="10" />
                  </xs:restriction>
                </xs:simpleType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:choice>
    </xs:complexType>
    <xs:unique name="Constraint1" msdata:PrimaryKey="true">
      <xs:selector xpath=".//mstns:StudentInfoA_local" />
      <xs:field xpath="mstns:student_id" />
    </xs:unique>
  </xs:element>
</xs:schema>

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:

Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Partial Class SiteAdministrator_ImportExcel
    Inherits System.Web.UI.Page
    Protected Sub ButtonUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ButtonUpload.Click
        PanelUpload.Visible = True
        PanelView.Visible = False
        PanelImport.Visible = False
    End Sub
    Protected Function ExcelConnection() As OleDbCommand
 
        ' Connect to the Excel Spreadsheet
        Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & (Server.MapPath("~/ExcelImport.xls")) & ";" & "Extended Properties=Excel 8.0;"
 
        ' create your excel connection object using the connection string
        Dim objXConn As New OleDbConnection(xConnStr)
        objXConn.Open()
 
        ' use a SQL Select command to retrieve the data from the Excel Spreadsheet
        ' the "table name" is the name of the worksheet within the spreadsheet
        ' in this case, the worksheet name is "Bellows3" and is coded as: [Bellows3$]
        Dim objCommand As New OleDbCommand("SELECT * FROM [Bellows3$]", objXConn)
        Return objCommand
 
    End Function
 
    Protected Sub ButtonUploadFile_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles ButtonUploadFile.Click
        If FileUploadExcel.HasFile Then
            Try
                ' alter path for your project
                FileUploadExcel.SaveAs(Server.MapPath("~/ExcelImport.xls"))
                LabelUpload.Text = "Upload File Name: " & _
                FileUploadExcel.PostedFile.FileName & "<br>" & _
                "Type: " & _
                FileUploadExcel.PostedFile.ContentType & _
                " File Size: " & _
                FileUploadExcel.PostedFile.ContentLength & " kb<br>"
            Catch ex As Exception
                LabelUpload.Text = "Error: " & ex.Message.ToString
            End Try
        Else
            LabelUpload.Text = "Please select a file to upload."
        End If
    End Sub
 
    Protected Sub ButtonView_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ButtonView.Click
        PanelUpload.Visible = False
        Panelview.Visible = True
        Panelimport.Visible = False
 
        ' Create a new Adapter
        Dim objDataAdapter As New OleDbDataAdapter()
 
        ' retrieve the Select command for the Spreadsheet
        objDataAdapter.SelectCommand = ExcelConnection()
 
        ' Create a DataSet
        Dim objDataSet As New DataSet()
 
        ' Populate the DataSet with the spreadsheet worksheet data
        objDataAdapter.Fill(objDataSet)
 
        ' Bind the data to the GridView
        GridViewExcel.DataSource = objDataSet.Tables(0).DefaultView
        GridViewExcel.DataBind()
 
 
 
    End Sub
    Protected Sub ButtonImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
          Handles ButtonImport.Click
        PanelUpload.Visible = False
        PanelView.Visible = False
        PanelImport.Visible = True
        LabelImport.Text = "" ' reset to blank
 
        ' retrieve the Select Command for the worksheet data
        Dim objCommand As New OleDbCommand()
        objCommand = ExcelConnection()
 
        '  create a DataReader
        Dim reader As OleDbDataReader
        reader = objCommand.ExecuteReader()
 
 
        Dim counter As Integer = 0 ' used for testing your import in smaller increments
 
        While reader.Read()
 
            counter = counter + 1 ' counter to exit early for testing...
 
            Dim student_id As String = Convert.ToString(reader("SID"))
            Dim last_name As String = Convert.ToString(reader("LN"))
            Dim first_name As String = Convert.ToString(reader("FN"))
            Dim middle_name As String = Convert.ToString(reader("MN"))
            Dim Gender As String = Convert.ToString(reader("GENDER"))
            Dim birthdate As Date = Convert.ToString(reader("DOB"))
            Dim Grade As String = Convert.ToString(reader("GRADE"))
            Dim ethnicity As String = Convert.ToString(reader("ETHNICITY"))
            Dim lep As String = Convert.ToString(reader("LEP"))
            Dim SPED As String = Convert.ToString(reader("SPED"))
            Dim Sheltered As String = Convert.ToString(reader("SHELTERED"))
            Dim AIP As String = Convert.ToString(reader("AIP"))
            Dim AVID As String = Convert.ToString(reader("AVID"))
            Dim ELL As String = Convert.ToString(reader("ELL"))
            Dim notes As String = Convert.ToString(reader("NOTES"))
            Dim StudentARecordNumber As Integer = 0
            Dim He_She As String = Convert.ToString(reader("HE"))
            Dim His_Hers As String = Convert.ToString(reader("HIS"))
            Dim Him_Her As String = Convert.ToString(reader("HIM"))
 
            'TODO: Insert any required validtions here...
 
            'TODO: Insert a record into StudentInfoA table (if it's not a duplicate)
 
            StudentARecordNumber = ImportIntoStudentInfoA(student_id, last_name, first_name, middle_name, Gender, birthdate, Grade, ethnicity, lep, SPED, Sheltered, AIP, AVID, ELL, notes, He_She, His_Hers, Him_Her)
 
            'Display information in LabelImport
            LabelImport.Text = student_id & " " & last_name & ", " & first_name & " " & middle_name & " " & Gender & " " & birthdate & " " & Grade & " " & ethnicity & " " & lep & " " & SPED & " " & Sheltered & " " & AIP & " " & AVID & " " & ELL & " " & notes & " " & StudentARecordNumber & " " & He_She & " " & His_Hers & " " & Him_Her & " " & "<br>"
 
            'If counter > 2 Then ' exit early for testing, comment later...
            'Exit While
            ' End If
 
        End While
        reader.Close()
    End Sub
 
    Protected Function ImportIntoStudentInfoA(ByVal student_id As String, ByVal last_name As String, ByVal first_name As String, ByVal middle_name As String, ByVal Gender As String, ByVal birthdate As String, ByVal Grade As String, ByVal ethnicity As String, ByVal lep As String, ByVal SPED As String, ByVal Sheltered As String, ByVal AIP As String, ByVal AVID As String, ByVal ELL As String, ByVal notes As String, ByVal He_She As String, ByVal His_Hers As String, ByVal Him_Her As String) As Integer
 
 
        ' make sure values don't exceed column limits
        student_id = Left(student_id, 10)
        last_name = Left(last_name, 50)
        first_name = Left(first_name, 50)
        middle_name = Left(middle_name, 50)
        Gender = Left(Gender, 10)
        birthdate = Left(birthdate, 50)
        Grade = Left(Grade, 10)
        ethnicity = Left(ethnicity, 10)
        lep = Left(lep, 5)
        SPED = Left(SPED, 5)
        Sheltered = Left(Sheltered, 5)
        AIP = Left(AIP, 5)
        AVID = Left(AVID, 5)
        ELL = Left(ELL, 5)
        notes = Left(notes, 5)
        He_She = Left(He_She, 10)
        His_Hers = Left(His_Hers, 10)
        Him_Her = Left(Him_Her, 10)
 
        Dim StudentARecordNumber As Integer = 0
        Try
            Dim FinaoAdapter As New StudentInfoADataSetTableAdapters.StudentInfoA_localTableAdapter
            Dim FinaoDataTable As StudentInfoADataSet.StudentInfoA_localDataTable = Nothing
            FinaoDataTable = FinaoAdapter.GetStudentInfoADataByRecordNumber(StudentARecordNumber)
 
            ' see if the student already exists in the table, if not insert it
            If Not FinaoDataTable Is Nothing Then
                If FinaoDataTable.Rows.Count > 0 Then
                    If Not FinaoDataTable(0).StudentARecordNumber = Nothing Then
                        StudentARecordNumber = FinaoDataTable(0).StudentARecordNumber
                        LabelImport.Text = LabelImport.Text & "<fontcolor=lime>Member Found, Not Imported: " & "ID: " & student_id & " " & last_name & ", " & first_name & ".</Font><br />"
 
                    End If
                End If
            End If
            If StudentARecordNumber = 0 Then
                ' retrieve the identity key StudentARecordNumber from the insert
                StudentARecordNumber = Convert.ToInt32(FinaoAdapter.InsertStudentInfoAQuery(student_id, last_name, first_name, middle_name, Gender, birthdate, Grade, ethnicity, lep, SPED, Sheltered, AIP, AVID, ELL, notes, He_She, His_Hers, Him_Her))
 
                LabelImport.Text = LabelImport.Text & "<fontcolor=lime>Member Imported: " & "ID: " & student_id & " " & last_name & ", " & first_name & " Grade: " & Grade & " Gender: " & Gender & ".</font><br />"
 
            End If
 
            Return StudentARecordNumber
        Catch ex As Exception
            LabelImport.Text &= "<fontcolor=red>" & ex.ToString & "</font><br />"
            Return 0
        End Try
 
 
 
    End Function
End Class

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:

CREATE PROC dbo.load_students
AS BEGIN
 
-- define a temp table with proper data types for each column
-- in the spreadsheet
CREATE TABLE [#loaded_data] (
             [student_id]    int,
             [last_name]     varchar(200),
             [first_name]    varchar(200),
             [date_of_birth] smalldatetime
)
-- Make sure that you delete the header row in the spreadsheet if any
-- Save the spreadsheet as a Tab delimited text file in the shared folder below
 
-- This command reads the spreadsheet and inserts into a temp table
    EXECUTE( 'BULK INSERT #loaded_data ' +
             'FROM ''\\mycomputer\myfolder\myfile.txt''' )
 
-- Now, delete from the temp table, records that already exist
-- in the student table
    DELETE FROM #loaded_data 
          WHERE EXISTS (SELECT students.student_id 
                          FROM students, #loaded_data
                         WHERE #loaded_data.student_id =  
                               students.student_id)
 
-- Now, go ahead and insert the new student records
    INSERT INTO students
    SELECT student_id, last_name, first_name, date_of_birth
      FROM #loaded_data
 
-- Now, return a recordset of all the identity columns to the program
-- Or, don't do this, and simply refresh your original dataset in the 
-- program
    SELECT S.student_id, S.student_record_number
      FROM students S
      JOIN #loaded_data 
        ON #loaded_data.student_id = S.student_id
 
-- This should happen automatically, but I do it just to be safe
    DROP TABLE [#loaded_data]
 
END
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:

USE [FINAOsmsSQL1_94132]
GO
/****** Object:  StoredProcedure [dbo].[load_students]    Script Date: 11/01/2009 06:21:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROC [dbo].[load_students]
	-- Add the parameters for the stored procedure here
	--<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
	--<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	--SET NOCOUNT ON;
 
    -- Insert statements for procedure here
	--SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
 
-- define a temp table with proper data types for each column
-- in the spreadsheet
CREATE TABLE [#loaded_data] (
             [student_id]    int,
             [last_name]     varchar(200),
             [first_name]    varchar(200),
             [date_of_birth] smalldatetime
)
-- Make sure that you delete the header row in the spreadsheet if any
-- Save the spreadsheet as a Tab delimited text file in the shared folder below
 
-- This command reads the spreadsheet and inserts into a temp table
    EXECUTE( 'BULK INSERT #loaded_data ' +
             'FROM ''c:\db\rbellows\ExcelImportSmall.txt''' )
 
-- Now, delete from the temp table, records that already exist
-- in the student table
    DELETE FROM #loaded_data 
          WHERE EXISTS (SELECT ExcelImportSmall.student_id 
                          FROM ExcelImportSmall, #loaded_data
                         WHERE #loaded_data.student_id =  
                               ExcelImportSmall.student_id)
 
-- Now, go ahead and insert the new student records
    INSERT INTO ExcelImportSmall
    SELECT student_id, last_name, first_name, date_of_birth
      FROM #loaded_data
 
-- Now, return a recordset of all the identity columns to the program
-- Or, don't do this, and simply refresh your original dataset in the 
-- program
    --SELECT S.student_id, S.student_record_number
      --FROM students S
      --JOIN #loaded_data 
        --ON #loaded_data.student_id = S.student_id
 
-- This should happen automatically, but I do it just to be safe
    DROP TABLE [#loaded_data]
 
END
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65: