Dim tempFileName As String
tempFileName = ""
Dim varFullPath As String
Dim strExtensionName As String
Try
If FileUploader.HasFile Then
tempFileName = FileUploader.PostedFile.FileName
strExtensionName = System.IO.Path.GetExtension(FileUploader.FileName)
If tempFileName = "" Then
lblmsg.Text = "Please browse excel file."
Exit Sub
End If
If strExtensionName <> ".xls" And strExtensionName <> ".xlsx" And strExtensionName <> ".XLS" And strExtensionName <> ".XLSX" Then
lblmsg.Text = "Invalid file extension"
Exit Sub
End If
varFullPath = Server.MapPath("AddRecords\") & Session("IDNo") & "_" & ddlcollegeupload.SelectedItem.Text & "_" & ddlcourseupload.SelectedItem.Text & "_" & ddlbatchupload.SelectedItem.Text & "_" & FileUploader.FileName
FileUploader.SaveAs(varFullPath)
Dim Con As SqlConnection
Dim varClass1 As New DataHelper
Con = New SqlConnection(varClass1.GetConnectionString(varIp))
Con.Open()
Dim con3 As New OleDbConnection
If strExtensionName = ".xls" Or strExtensionName = ".XLS" Then
con3 = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & varFullPath & ";Extended Properties=Excel 8.0;")
ElseIf strExtensionName = ".xlsx" Or strExtensionName = ".XLSX" Then
con3 = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & varFullPath & ";Extended Properties=Excel 12.0 Xml;")
End If
Dim ds As New DataSet
Dim da As New OleDbDataAdapter("SELECT * FROM [Sheet1$]", con3)
Try
da.Fill(ds, "Sheet1")
Catch ex As Exception
lblmsg.Text = "Select excel file in correct data format."
Exit Sub
End Try
lblmsg.Text = ""
Dim chkds As New DataSet
Dim chkda As New OleDbDataAdapter("SELECT * FROM [Sheet1$] WHERE SemesterID >0", con3)
chkda.Fill(chkds, "Sheet1")
Dim cmdIns As New SqlCommand
Dim counter As Integer
counter = 0
While counter < chkds.Tables("Sheet1").Rows.Count
cmdIns = New SqlCommand("INSERT INTO Tablename(CollegeName,CollegeID,Course,CourseID,Batch,SemesterID,Semester,SrNo ,SubjectName ,SubjectType,SubjectCode,SubjectShortName,Elective,IntMaxMarks,ExtMaxMarks,Lecture,Tutorial,Practical,Isverified,SGroup,Islocked) VALUES(@CollegeName,@CollegeID, @Course, @CourseID, @Batch, @SemesterID, @Semester, @SrNo, @SubjectName, @SubjectType, @SubjectCode, @SubjectShortName, @Elective, @MaxMarks, @ExtMaxMarks, @Lecture, @Tutorial, @Practical, @Isverified, @SGroup,@Islocked)", Con)
cmdIns.Parameters.AddWithValue("@CollegeName", ddlcollegeupload.SelectedItem.Text)
cmdIns.Parameters.AddWithValue("@CollegeID", ddlcollegeupload.SelectedItem.Value)
cmdIns.Parameters.AddWithValue("@Course", ddlcourseupload.SelectedItem.Text)
cmdIns.Parameters.AddWithValue("@CourseID", ddlcourseupload.SelectedItem.Value)
cmdIns.Parameters.AddWithValue("@Batch", ddlbatchupload.SelectedItem.Text)
cmdIns.Parameters.AddWithValue("@SrNo", String.Empty)
If IsDBNull(ds.Tables("Sheet1").Rows(counter).Item("SemesterID")) = True Then
cmdIns.Parameters.AddWithValue("@SemesterID", DBNull.Value)
Else
cmdIns.Parameters.AddWithValue("@SemesterID", ds.Tables("Sheet1").Rows(counter).Item("SemesterID"))
End If
If IsDBNull(ds.Tables("Sheet1").Rows(counter).Item("Semester")) = True Then
cmdIns.Parameters.AddWithValue("@Semester", DBNull.Value)
Else
cmdIns.Parameters.AddWithValue("@Semester", ds.Tables("Sheet1").Rows(counter).Item("Semester"))
End If
If IsDBNull(ds.Tables("Sheet1").Rows(counter).Item("SubjectName")) = True Then
cmdIns.Parameters.AddWithValue("@SubjectName", DBNull.Value)
Else
cmdIns.Parameters.AddWithValue("@SubjectName", ds.Tables("Sheet1").Rows(counter).Item("SubjectName"))
End If
If IsDBNull(ds.Tables("Sheet1").Rows(counter).Item("SubjectType")) = True Then
cmdIns.Parameters.AddWithValue("@SubjectType", DBNull.Value)
Else
cmdIns.Parameters.AddWithValue("@SubjectType", ds.Tables("Sheet1").Rows(counter).Item("SubjectType"))
End If
If IsDBNull(ds.Tables("Sheet1").Rows(counter).Item("SubjectCode")) = True Then
cmdIns.Parameters.AddWithValue("@SubjectCode", DBNull.Value)
Else
cmdIns.Parameters.AddWithValue("@SubjectCode", ds.Tables("Sheet1").Rows(counter).Item("SubjectCode"))
End If
If IsDBNull(ds.Tables("Sheet1").Rows(counter).Item("SubjectShortName")) = True Then
cmdIns.Parameters.AddWithValue("@SubjectShortName", DBNull.Value)
Else
cmdIns.Parameters.AddWithValue("@SubjectShortName", ds.Tables("Sheet1").Rows(counter).Item("SubjectShortName"))
End If
If IsDBNull(ds.Tables("Sheet1").Rows(counter).Item("Elective")) = True Then
cmdIns.Parameters.AddWithValue("@Elective", DBNull.Value)
Else
cmdIns.Parameters.AddWithValue("@Elective", ds.Tables("Sheet1").Rows(counter).Item("Elective"))
End If
If IsDBNull(ds.Tables("Sheet1").Rows(counter).Item("IntMaxMarks")) = True Then
cmdIns.Parameters.AddWithValue("@MaxMarks", DBNull.Value)
Else
cmdIns.Parameters.AddWithValue("@MaxMarks", ds.Tables("Sheet1").Rows(counter).Item("IntMaxMarks"))
End If
If IsDBNull(ds.Tables("Sheet1").Rows(counter).Item("ExtMaxMarks")) = True Then
cmdIns.Parameters.AddWithValue("@ExtMaxMarks", DBNull.Value)
Else
cmdIns.Parameters.AddWithValue("@ExtMaxMarks", ds.Tables("Sheet1").Rows(counter).Item("ExtMaxMarks"))
End If
If IsDBNull(ds.Tables("Sheet1").Rows(counter).Item("Lecture")) = True Then
cmdIns.Parameters.AddWithValue("@Lecture", DBNull.Value)
Else
cmdIns.Parameters.AddWithValue("@Lecture", ds.Tables("Sheet1").Rows(counter).Item("Lecture"))
End If
If IsDBNull(ds.Tables("Sheet1").Rows(counter).Item("Tutorial")) = True Then
cmdIns.Parameters.AddWithValue("@Tutorial", DBNull.Value)
Else
cmdIns.Parameters.AddWithValue("@Tutorial", ds.Tables("Sheet1").Rows(counter).Item("Tutorial"))
End If
If IsDBNull(ds.Tables("Sheet1").Rows(counter).Item("Practical")) = True Then
cmdIns.Parameters.AddWithValue("@Practical", DBNull.Value)
Else
cmdIns.Parameters.AddWithValue("@Practical", ds.Tables("Sheet1").Rows(counter).Item("Practical"))
End If
cmdIns.Parameters.AddWithValue("@Isverified", "0")
cmdIns.Parameters.AddWithValue("@SGroup", String.Empty)
cmdIns.Parameters.AddWithValue("@Islocked", String.Empty)
If Con.State = ConnectionState.Closed Then
Con.Open()
End If
cmdIns.Connection = Con
cmdIns.ExecuteNonQuery()
cmdIns.Dispose()
counter = counter + 1
End While
Con.Close()
con3.Close()
DataHelper.ShowMessageSuccess(Me, "Successfully inserted")
Else
DataHelper.ShowMessageError(Me, "Please browse excel file.")
Exit Sub
End If
Catch ex As Exception
DataHelper.ShowMessageError(Me, "Excel File Format Not Correct, All Data Not Uploaded")
Exit Sub
End Try
End Sub
0 Comments