error "There is already an open DataReader associated with this Command which must be closed 1st."
-
Hi ladies and gentlmen, I got this error while doing the transactions insert command in a do loop select command. What I'm trying to do is, 1st to run select command to query filename and filesize (it consists of few results). Then with the retrieved results, rename the filename and insert into another table with the new filename. After that delete it from the original table. But i got this error "There is already an open DataReader associated with this Command which must be closed 1st". How can i overcome this?can anyone help me on this? I'm out of ideas. Thanks and appreciate... Here is my code:- cmd.Connection = cn cn.Open() tn = cn.BeginTransaction cmd.Transaction = tn strQuery2 = "SELECT FileName,FileSize FROM udtTempAttach where UniqueID = '" & Me.lblUniqueID.Text & "'" cmd.CommandText = strQuery2 dr2 = cmd.ExecuteReader Try Do While dr2.Read filename = Replace(dr2(0).ToString, ".", "_" & DistID & ".") filesize = dr2(1).ToString FileIO.FileSystem.CopyFile(ServerPath + "TempAttach\" + dr2(0).ToString, ServerPath + "Attachment\" + filename, True) If FileIO.FileSystem.FileExists(ServerPath + "Attachment\" + filename) Then FileIO.FileSystem.DeleteFile(ServerPath + "TempAttach\" + dr2(0).ToString) End If strQuery3 = "INSERT udtAttachment (DistID,FileName,FileSize,EnterDate) " strQuery3 = strQuery3 & " values('" & DistID.ToString & "','" & filename.ToString & "','" & filesize.ToString & "',getdate()) " cmd.CommandText = strQuery3 ra = cmd.ExecuteNonQuery() strQuery3 = "Delete from udtTempAttach where UniqueID='" & Me.lblUniqueID.Text & "' and filename = '" & dr2(0).ToString & "'" cmd.CommandText = strQuery3 ra = cmd.ExecuteNonQuery() Loop dr2.Close() Catch ex As Exception MsgBox(ex.Message) dr2.Close() tn.Rollback() Finally If cn.State = ConnectionState.Open Then cn.Close() End Try .......Thanks.......
-
Hi ladies and gentlmen, I got this error while doing the transactions insert command in a do loop select command. What I'm trying to do is, 1st to run select command to query filename and filesize (it consists of few results). Then with the retrieved results, rename the filename and insert into another table with the new filename. After that delete it from the original table. But i got this error "There is already an open DataReader associated with this Command which must be closed 1st". How can i overcome this?can anyone help me on this? I'm out of ideas. Thanks and appreciate... Here is my code:- cmd.Connection = cn cn.Open() tn = cn.BeginTransaction cmd.Transaction = tn strQuery2 = "SELECT FileName,FileSize FROM udtTempAttach where UniqueID = '" & Me.lblUniqueID.Text & "'" cmd.CommandText = strQuery2 dr2 = cmd.ExecuteReader Try Do While dr2.Read filename = Replace(dr2(0).ToString, ".", "_" & DistID & ".") filesize = dr2(1).ToString FileIO.FileSystem.CopyFile(ServerPath + "TempAttach\" + dr2(0).ToString, ServerPath + "Attachment\" + filename, True) If FileIO.FileSystem.FileExists(ServerPath + "Attachment\" + filename) Then FileIO.FileSystem.DeleteFile(ServerPath + "TempAttach\" + dr2(0).ToString) End If strQuery3 = "INSERT udtAttachment (DistID,FileName,FileSize,EnterDate) " strQuery3 = strQuery3 & " values('" & DistID.ToString & "','" & filename.ToString & "','" & filesize.ToString & "',getdate()) " cmd.CommandText = strQuery3 ra = cmd.ExecuteNonQuery() strQuery3 = "Delete from udtTempAttach where UniqueID='" & Me.lblUniqueID.Text & "' and filename = '" & dr2(0).ToString & "'" cmd.CommandText = strQuery3 ra = cmd.ExecuteNonQuery() Loop dr2.Close() Catch ex As Exception MsgBox(ex.Message) dr2.Close() tn.Rollback() Finally If cn.State = ConnectionState.Open Then cn.Close() End Try .......Thanks.......
Eunice (VB junior) wrote:
There is already an open DataReader associated with this Command which must be closed 1st".
Use a different command object to do insertion and deletion. This error is coming because DataReader is still active and you can't use the command for other queries until you close reader. Your queries are open to SQL Injection attacks. Read about it and change to parametrized queries.
Navaneeth How to use google | Ask smart questions
-
Hi ladies and gentlmen, I got this error while doing the transactions insert command in a do loop select command. What I'm trying to do is, 1st to run select command to query filename and filesize (it consists of few results). Then with the retrieved results, rename the filename and insert into another table with the new filename. After that delete it from the original table. But i got this error "There is already an open DataReader associated with this Command which must be closed 1st". How can i overcome this?can anyone help me on this? I'm out of ideas. Thanks and appreciate... Here is my code:- cmd.Connection = cn cn.Open() tn = cn.BeginTransaction cmd.Transaction = tn strQuery2 = "SELECT FileName,FileSize FROM udtTempAttach where UniqueID = '" & Me.lblUniqueID.Text & "'" cmd.CommandText = strQuery2 dr2 = cmd.ExecuteReader Try Do While dr2.Read filename = Replace(dr2(0).ToString, ".", "_" & DistID & ".") filesize = dr2(1).ToString FileIO.FileSystem.CopyFile(ServerPath + "TempAttach\" + dr2(0).ToString, ServerPath + "Attachment\" + filename, True) If FileIO.FileSystem.FileExists(ServerPath + "Attachment\" + filename) Then FileIO.FileSystem.DeleteFile(ServerPath + "TempAttach\" + dr2(0).ToString) End If strQuery3 = "INSERT udtAttachment (DistID,FileName,FileSize,EnterDate) " strQuery3 = strQuery3 & " values('" & DistID.ToString & "','" & filename.ToString & "','" & filesize.ToString & "',getdate()) " cmd.CommandText = strQuery3 ra = cmd.ExecuteNonQuery() strQuery3 = "Delete from udtTempAttach where UniqueID='" & Me.lblUniqueID.Text & "' and filename = '" & dr2(0).ToString & "'" cmd.CommandText = strQuery3 ra = cmd.ExecuteNonQuery() Loop dr2.Close() Catch ex As Exception MsgBox(ex.Message) dr2.Close() tn.Rollback() Finally If cn.State = ConnectionState.Open Then cn.Close() End Try .......Thanks.......
The error message means what it says. Also, your code looks to be very insecure. Do you know what a SQL injection attack is ?
Christian Graus Driven to the arms of OSX by Vista.
-
The error message means what it says. Also, your code looks to be very insecure. Do you know what a SQL injection attack is ?
Christian Graus Driven to the arms of OSX by Vista.
Any hint to overcome the SQL injection attack? Thanks in advanced.....
-
Any hint to overcome the SQL injection attack? Thanks in advanced.....
Eunice (VB junior) wrote:
Any hint to overcome the SQL injection attack
First hint is to use Google. This[^] was my first hit when I searched. :)
Navaneeth How to use google | Ask smart questions
-
Eunice (VB junior) wrote:
Any hint to overcome the SQL injection attack
First hint is to use Google. This[^] was my first hit when I searched. :)
Navaneeth How to use google | Ask smart questions
thanks..... I'm doing it now. :)