Hi Friends,
I am trying to save data in MS Access Table from my vb.net application using OleDbCommand.
It is working very well but here is a problem; when I put it in loop then I give an error that "Cannot open any more tables."
I used "Cmd.Dispose()" method but get no result.
Would you please tell me any other way to refresh the command or clear it or remove all tables from it.
Thanks in Advance
Parveen Rahti
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnParticualrStudentTermGrade.Click
Dim cmdGetMarksT, cmdGetMaxMarksT, cmdGetGrade As New OleDbCommand()
Dim saveNameCodeT As Boolean = True
Dim colIndexT, MaxIDT, MaxMarksT, SubIndexT, GrandMaxMarksT As Integer
Dim TotMarksT, AvgT, GrandTotMarksT, GrandAvgT, gradeMarks As Decimal
Dim ClassNameT As String
conAccess = New OleDbConnection(st)
If conAccess.State = ConnectionState.Closed Then
conAccess.Open()
End If
Dim cmdDeletT As New OleDbCommand()
cmdDeletT.CommandType = CommandType.Text
cmdDeletT.Connection = conAccess
cmdDeletT.CommandText = "delete * from tbTempMarksReportParticularStudentInParticularTest"
cmdDeletT.ExecuteNonQuery()
cmdDeletT.CommandText = "delete * from tbTempSession"
cmdDeletT.ExecuteNonQuery()
Dim cmdGetSession As New OleDbCommand()
cmdGetSession.CommandText = "SELECT startdate,enddate FROM SessionMaster where srno=" & txtSession.Text
cmdGetSession.Connection = conAccess
Dim drGetSession As OleDbDataReader = cmdGetSession.ExecuteReader()
drGetSession.Read()
Dim StartDate = Year(drGetSession("startdate"))
Dim EndDate = Year(drGetSession("enddate"))
Dim Session = StartDate & "-" & EndDate
Dim cmdInsSession As New OleDbCommand()
cmdInsSession.CommandText = "insert into tbTempSession values('" & Session & "')"
cmdInsSession.Connection = conAccess
cmdInsSession.ExecuteNonQuery()
Dim cmdGetClassNameT As New OleDbCommand("SELECT class_name FROM class_master WHERE (class_id =" & txtClass.Text & ")", conAccess)
Dim drGetClassNameT As OleDbDataReader = cmdGetClassNameT.ExecuteReader()
drGetClassNameT.Read()
ClassNameT = drGetClassNameT("class_name")
Dim cmdStudentNameCodeT As New OleDbCommand()
cmdStudentNameCodeT.Connection = conAccess
cmdStudentNameCodeT.CommandType = CommandType.Text
cmdStudentNameCodeT.CommandText = "select firstname+ ' '+lastname as studentnam, class,section from studentmaster where stcode=" & txtStudentCode.Text & " and sessionid=" & txtSession.Text
Dim drStudentNameCodeT As OleDbDataReader = cmdStudentNameCodeT.ExecuteReader()
drStudentNameCodeT.Read()
Dim cmdInsSubjectNameT As New OleDbCommand()
cmdInsSubjectNameT.Connection = conAccess
cmdInsSubjectNameT.CommandType = CommandType.Text
cmdInsSubjectNameT.CommandText = "insert into tbTempMarksReportPrtclrStdSubectWise (tempID,StudentCode,StudentName) values (1,'Class','" & drStudentNameCodeT("class") & " " & drStudentNameCodeT("section") & "')"
cmdInsSubjectNameT.ExecuteNonQuery()
Dim cmdMaxTempIDT As New OleDbCommand()
cmdMaxTempIDT.Connection = conAccess
cmdMaxTempIDT.CommandType = CommandType.Text
cmdMaxTempIDT.CommandText = "select max (tempID)as MaxID from tbTempMarksReportPrtclrStdSubectWise"
Dim drMaxTempIDT As OleDbDataReader = cmdMaxTempIDT.ExecuteReader()
drMaxTempIDT.Read()
Try
MaxIDT = Convert.ToInt32(drMaxTempIDT("MaxID"))
Catch ex As Exception
MaxIDT = 0
End Try
MaxIDT += 1
Dim cmdInsStdNameCode As New OleDbCommand()
cmdInsStdNameCode.Connection = conAccess
cmdInsStdNameCode.CommandType = CommandType.Text
cmdInsStdNameCode.CommandText = "insert into tbTempMarksReportPrtclrStdSubectWise (tempID,studentcode,studentname) values (" & MaxIDT & ",'St. No.','Student Name')"
cmdInsStdNameCode.ExecuteNonQuery()
drStudentNameCodeT.Read()
MaxIDT += 1
Dim qryName = "insert into tbTempMarksReportPrtclrStdSubectWise (tempID,studentcode,studentname) values (" & MaxIDT & ",'" & txtStudentCode.Text & "','" & drStudentNameCodeT("studentname") & "')"
cmdInsStdNameCode.CommandText = qryName
cmdInsStdNameCode.ExecuteNonQuery()
TotMarksT = 0
MaxMarksT = 0
Dim cmdGetSrNoSubNameT As New OleDbCommand("SELECT testmaster.Srno, Subject_master.shortName FROM (Subject_master INNER JOIN testmaster ON Subject_master.srno = testmaster.sub_id) WHERE (testmaster.class_id = " & txtClass.Text & ") AND (testmaster.[section] = '" & txtSection.Text & "') AND (testmaster.test = '" & txtTestName.Text & "') AND (testmaster.[session] = '" & txtSession.Text & "')", conAccess)
Dim drGetSrNoSubNameT As OleDbDataReader = cmdGetSrNoSubNameT.ExecuteReader()
While drGetSrNoSubNameT.Read()
colIndexT += 1
cmdGetMarksT.CommandText = "SELECT marks,max FROM testdetail WHERE (srno =" & drGetSrNoSubNameT("srno") & ") AND (stcode =" & txtStudentCode.Text & ")"
cmdGetMarksT.Connection = conAccess
Dim drGetMarksT As OleDbDataReader = cmdGetMarksT.ExecuteReader()
Dim MaxMarksR As Integer
Dim Marks As Decimal
If drGetMarksT.HasRows Then
drGetMarksT.Read()
Marks = drGetMarksT("marks")
MaxMarksR = drGetMarksT("Max")
Else
Marks = 0
MaxMarksR = 0
End If
MaxMarksT += MaxMarksR
GrandMaxMarksT += MaxMarksR
TotMarksT += Marks
GrandTotMarksT += Marks
cmdGetMarksT.Dispose()
cmdGetMarksT.Cancel()
drGetMarksT.Close()
Dim Grade, MarksIns As String
If Marks <> 0 Then
gradeMarks = Marks * 100 / MaxMarksR
cmdGetGrade.Connection = conAccess
cmdGetGrade.CommandText = "select * from SessionGrade"
Dim drGetGrade = cmdGetGrade.ExecuteReader()
While drGetGrade.Read()
If gradeMarks >= drGetGrade("mf") And gradeMarks <= drGetGrade("mt") Then
Grade = "\" & drGetGrade("grade")
Exit While
End If
End While
drGetGrade.Close()
cmdGetGrade.Dispose()
cmdGetGrade.Cancel()
MarksIns = Marks
Else
MarksIns = "-"
Grade = ""
End If
Dim cmdSetMarksT As New OleDbCommand()
cmdSetMarksT.CommandText = "update tbTempMarksReportParticularStudentInParticularTest set R" & colIndexT & "='" & MarksIns & Grade & "' where studentcode='" & txtStudentCode.Text & "'"
cmdSetMarksT.Connection = conAccess
cmdSetMarksT.ExecuteNonQuery()
cmdSetMarksT.Dispose()
cmdSetMarksT.Connection = conAccess
cmdSetMarksT.CommandText = "update tbTempMarksReportParticularStudentInParticularTest set R" & colIndexT & "='" & drGetSrNoSubNameT("shortName") & "' where tempID=2"
cmdSetMarksT.ExecuteNonQuery()
cmdSetMarksT.Dispose()
End While
colIndexT += 1
Dim cmdSetTotAvgMarks As New OleDbCommand()
cmdSetTotAvgMarks.CommandText = "update tbTempMarksReportParticularStudentInParticularTest set R" & colIndexT & "=" & TotMarksT & " where studentcode='" & txtStudentCode.Text & "'"
cmdSetTotAvgMarks.Connection = conAccess
cmdSetTotAvgMarks.ExecuteNonQuery()
cmdSetTotAvgMarks.Dispose()
cmdSetTotAvgMarks.Cancel()
cmdSetTotAvgMarks.CommandText = "update tbTempMarksReportParticularStudentInParticularTest set R" & colIndexT & "='Total' where tempID=2"
cmdSetTotAvgMarks.Connection = conAccess
cmdSetTotAvgMarks.ExecuteNonQuery()
cmdSetTotAvgMarks.Dispose()
cmdSetTotAvgMarks.Cancel()
AvgT = TotMarksT / MaxMarksT * 100
Dim AvgTR As String = AvgT.ToString("0.00")
colIndexT += 1
cmdSetTotAvgMarks.CommandText = "update tbTempMarksReportParticularStudentInParticularTest set R" & colIndexT & "='Avg.' where tempID=2"
cmdSetTotAvgMarks.Connection = conAccess
cmdSetTotAvgMarks.ExecuteNonQuery()
cmdSetTotAvgMarks.Dispose()
cmdSetTotAvgMarks.Cancel()
cmdSetTotAvgMarks.CommandText = "update tbTempMarksReportParticularStudentInParticularTest set R" & colIndexT & "=" & AvgTR & " where studentcode='" & txtStudentCode.Text & "'"
cmdSetTotAvgMarks.Connection = conAccess
cmdSetTotAvgMarks.ExecuteNonQuery()
cmdSetTotAvgMarks.Dispose()
cmdSetTotAvgMarks.Cancel()
cmdSetTotAvgMarks.CommandText = "update tbTempMarksReportParticularStudentInParticularTest set R" & colIndexT - 3 & "='" & txtTestName.Text & "' where tempID=1"
cmdSetTotAvgMarks.Connection = conAccess
cmdSetTotAvgMarks.ExecuteNonQuery()
cmdSetTotAvgMarks.Dispose()
cmdSetTotAvgMarks.Cancel()
drGetSrNoSubNameT.Close()
MsgBox("Success !")
End Sub