Refresh OleDbCommand in vb.net

Question

0.00/5 (No votes)

See more:

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

VB

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

        '===================== Empty Temprory table =================================
        Dim cmdDeletT As New OleDbCommand()
        cmdDeletT.CommandType = CommandType.Text
        cmdDeletT.Connection = conAccess
        cmdDeletT.CommandText = "delete * from tbTempMarksReportParticularStudentInParticularTest"
        cmdDeletT.ExecuteNonQuery()
        '=============================================================================

        '====================Insert Session to display=======================
        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()

        '=====================GetClassName=====================
        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")



        '====================Get Student Name & Code ========================
        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()

        '=============== insert heading first time only ==========================
        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()

        '===========================Insert Student Name and Code==========================
        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

        '====================== insert student name & code ======================
        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
        '====================Get Sr.No. and Subject Short Name===========================
        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

            '===============Get Marks of Particualr Student in Particular Test and All Subject==============================

            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

            '==============Set Marks of Particualr Student in Particular Test and All Subject==============================
            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()
            '=============Set Subject Names ===============================
            cmdSetMarksT.Connection = conAccess
            cmdSetMarksT.CommandText = "update tbTempMarksReportParticularStudentInParticularTest set R" & colIndexT & "='" & drGetSrNoSubNameT("shortName") & "' where tempID=2"
            cmdSetMarksT.ExecuteNonQuery()
            cmdSetMarksT.Dispose()
        End While 'Sr No & Subjet Short name While end

        '================Set Test Name======================
        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()

        '==========Set Avg.==============================

        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()

        '======================== Set Test name===================================

        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

Posted 9-Nov-11 20:30pm

Parveen Rathi

Updated 21-Nov-11 3:01am

v3

Add a Solution

Comments

Dalek Dave 10-Nov-11 3:38am

Edited for Grammar, Spelling and Readability.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wayne Gaylard · Answer 1 · 2011-11-09T21:07:00

You should provide your code so we can see what the problem is. If you need to process aa lot of queries in one go, you should consider using an OleDbTransaction, something like this:-

C#

void SaveTransactions()
        {
            using (OleDbConnection con = new OleDbConnection(connectionString))
            {
                con.Open();
                OleDbTransaction trans = con.BeginTransaction();
                try
                {
                    foreach (Item item in items)
                    {
                        OleDbCommand cmd = new OleDbCommand("yourUpdateStatement", con);
                        cmd.Parameters.AddWithValue("@par1!", parameter1);
                        cmd.Parameters.AddWithValue("@par2", parameter2);
                        cmd.ExecuteNonQuery();
                    }
                    trans.Commit();
                }
                catch (OleDbException)
                {
                    trans.Rollback();
                }
            }
        }

This should ensure that all your updates do not interfere with each other, and they will also be much faster.

Hope this helps