在vb.net中刷新OleDbCommand [英] Refresh OleDbCommand in vb.net
本文介绍了在vb.net中刷新OleDbCommand的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
朋友，
我正在尝试使用OleDbCommand将vb.net应用程序中的数据保存在MS Access Table中.
它工作得很好，但这是一个问题.当我将其放入循环中时，会出现错误无法再打开任何表".
我使用了"Cmd.Dispose()"方法，但没有结果.
您能否以其他任何方式告诉我刷新命令或清除命令或从中删除所有表.
在此先感谢
Parveen Rahti
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
'===================== 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
推荐答案
您应该提供您的代码，以便我们可以了解问题所在.如果您需要一次性处理大量查询，则应考虑使用OleDbTransaction，如下所示:-
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:-
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
这篇关于在vb.net中刷新OleDbCommand的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
