-
October 19th, 2012, 05:59 AM
#1
Error Prompted at RunTime
Hi,
I have the following code;
Code:
Protected Sub btnsubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsubmit.Click
Dim strConn As String
strConn = ConfigurationManager.ConnectionStrings("CCMSConnectionString").ConnectionString
Dim conn As New SqlConnection(strConn)
'Dim nextconn As New SqlConnection(strConn)
conn.Open()
Dim strSql As String
strSql = "INSERT INTO dbo.TblChangeControlDet (RequestBy, CreatedBy, ChangeType, TechnicalPerson, Req, BusImp,ChangeRaisedDate, PeerRvw) VALUES (@RequestBy, @CreatedBy, @ChangeType, @TechnicalPerson, @Req, @BusImp,@ChangeRaisedDate, @PeerRvw )"
Dim cmd As New SqlCommand(strSql, conn)
With cmd.Parameters
.AddWithValue("@RequestBy", Me.cmbchngreq.SelectedItem.ToString)
.AddWithValue("@CreatedBy", Me.cmbchngcrt.SelectedItem.ToString)
.AddWithValue("@ChangeType", Me.cmbchngtyp.SelectedItem.ToString)
.AddWithValue("@TechnicalPerson", Me.cmbTechname.SelectedItem.ToString)
.AddWithValue("@Req", txtreq.Text)
.AddWithValue("@BusImp", txtbusimp.Text)
.AddWithValue("@ChangeRaisedDate", DateTime.Now.ToString("dd\/MM\/yyyy HH:mm:ss"))
.AddWithValue("@PeerRvw", Me.cmbpeername.SelectedItem.ToString)
End With
cmd.ExecuteNonQuery()
Dim query As String
Dim RefID As New System.Data.SqlClient.SqlCommand(("Select RefNo From dbo.QryRefNo"), conn)
Try
Using Autoreader As System.Data.SqlClient.SqlDataReader = RefID.ExecuteReader()
While Autoreader.Read()
Dim RefNo As String = (Autoreader.GetValue(0))
query = "UPDATE dbo.TblChangeControlDet SET RefNo = @RefNo WHERE ChangeRaisedDate = @ChangeRaisedDate"
cmd = New SqlCommand(query, conn)
cmd.Parameters.AddWithValue("@RefNo", RefNo)
cmd.Parameters.AddWithValue("@ChangeRaisedDate", DateTime.Now.ToString("dd\/MM\/yyyy HH:mm:ss"))
cmd.ExecuteNonQuery()
End While
End Using
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information, "Spare Elements")
End Try
conn.Close()
End Sub
At run time when I click the button I receive the error -
'There is already an open DataReader associated with this Command which must be closed first".
The INSERT works fine the problem us with the UPDATE statement.
Thanks
Last edited by dr223; October 19th, 2012 at 06:02 AM.
-
October 19th, 2012, 06:27 AM
#2
Re: Error Prompted at RunTime
Have you tried closing and re-opening the connection after your INSERT statement?
Otherwise, you could have made a SQL Transaction that could do both these things for you, but that might be a bit tricky
-
October 19th, 2012, 06:36 AM
#3
Re: Error Prompted at RunTime
How can you do the first one please
-
October 19th, 2012, 06:44 AM
#4
Re: Error Prompted at RunTime
Originally Posted by dr223
How can you do the first one please
Seriously?? Huh?
OK, well, seeing the fact that opening and closing a connection is too complicated for you to do in your code ( eventhough you have used the very same codes in your codes ), I'll take time off my busy schedule - just for you and spoonfeed you :
Code:
Protected Sub btnsubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsubmit.Click
Dim strConn As String
strConn = ConfigurationManager.ConnectionStrings("CCMSConnectionString").ConnectionString
Dim conn As New SqlConnection(strConn)
'Dim nextconn As New SqlConnection(strConn)
conn.Open()
Dim strSql As String
strSql = "INSERT INTO dbo.TblChangeControlDet (RequestBy, CreatedBy, ChangeType, TechnicalPerson, Req, BusImp,ChangeRaisedDate, PeerRvw) VALUES (@RequestBy, @CreatedBy, @ChangeType, @TechnicalPerson, @Req, @BusImp,@ChangeRaisedDate, @PeerRvw )"
Dim cmd As New SqlCommand(strSql, conn)
With cmd.Parameters
.AddWithValue("@RequestBy", Me.cmbchngreq.SelectedItem.ToString)
.AddWithValue("@CreatedBy", Me.cmbchngcrt.SelectedItem.ToString)
.AddWithValue("@ChangeType", Me.cmbchngtyp.SelectedItem.ToString)
.AddWithValue("@TechnicalPerson", Me.cmbTechname.SelectedItem.ToString)
.AddWithValue("@Req", txtreq.Text)
.AddWithValue("@BusImp", txtbusimp.Text)
.AddWithValue("@ChangeRaisedDate", DateTime.Now.ToString("dd\/MM\/yyyy HH:mm:ss"))
.AddWithValue("@PeerRvw", Me.cmbpeername.SelectedItem.ToString)
End With
cmd.ExecuteNonQuery()
conn.Close() 'Here I close the connection
conn.Open() 'Here I open the connection
Dim query As String
Dim RefID As New System.Data.SqlClient.SqlCommand(("Select RefNo From dbo.QryRefNo"), conn)
Try
Using Autoreader As System.Data.SqlClient.SqlDataReader = RefID.ExecuteReader()
While Autoreader.Read()
Dim RefNo As String = (Autoreader.GetValue(0))
query = "UPDATE dbo.TblChangeControlDet SET RefNo = @RefNo WHERE ChangeRaisedDate = @ChangeRaisedDate"
cmd = New SqlCommand(query, conn)
cmd.Parameters.AddWithValue("@RefNo", RefNo)
cmd.Parameters.AddWithValue("@ChangeRaisedDate", DateTime.Now.ToString("dd\/MM\/yyyy HH:mm:ss"))
cmd.ExecuteNonQuery()
End While
End Using
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information, "Spare Elements")
End Try
conn.Close()
End Sub
Happy now? Look at the bolded words with comments
-
October 19th, 2012, 06:59 AM
#5
Re: Error Prompted at RunTime
Tried that it gave same error message.
Even tried to create another string;
Dim nextconn As New SqlConnection(strConn)
Then I opened and closed same location as you did in bold.
Same error message....
Thats when I asked...
Last edited by dr223; October 19th, 2012 at 08:59 AM.
-
October 19th, 2012, 09:03 AM
#6
Re: Error Prompted at RunTime
Tried;
Code:
Protected Sub btnsubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsubmit.Click
Dim strConn As String
strConn = ConfigurationManager.ConnectionStrings("CCMSConnectionString").ConnectionString
Dim conn As New SqlConnection(strConn)
Dim nextconn As New SqlConnection(strConn)
conn.Open()
Dim strSql As String
strSql = "INSERT INTO dbo.TblChangeControlDet (RequestBy, CreatedBy, ChangeType, TechnicalPerson, Req, BusImp,ChangeRaisedDate, PeerRvw) VALUES (@RequestBy, @CreatedBy, @ChangeType, @TechnicalPerson, @Req, @BusImp,@ChangeRaisedDate, @PeerRvw )"
Dim cmd As New SqlCommand(strSql, conn)
With cmd.Parameters
.AddWithValue("@RequestBy", Me.cmbchngreq.SelectedItem.ToString)
.AddWithValue("@CreatedBy", Me.cmbchngcrt.SelectedItem.ToString)
.AddWithValue("@ChangeType", Me.cmbchngtyp.SelectedItem.ToString)
.AddWithValue("@TechnicalPerson", Me.cmbTechname.SelectedItem.ToString)
.AddWithValue("@Req", txtreq.Text)
.AddWithValue("@BusImp", txtbusimp.Text)
.AddWithValue("@ChangeRaisedDate", DateTime.Now.ToString("dd\/MM\/yyyy HH:mm:ss"))
.AddWithValue("@PeerRvw", Me.cmbpeername.SelectedItem.ToString)
End With
cmd.ExecuteNonQuery()
conn.Close()
nextconn.Open()
Dim query As String
Dim RefID As New System.Data.SqlClient.SqlCommand(("Select RefNo From dbo.QryRefNo"), conn)
Try
Using Autoreader As System.Data.SqlClient.SqlDataReader = RefID.ExecuteReader()
While Autoreader.Read()
Dim RefNo As String = (Autoreader.GetValue(0))
query = "UPDATE dbo.TblChangeControlDet SET RefNo = @RefNo WHERE ChangeRaisedDate = @ChangeRaisedDate"
cmd = New SqlCommand(query, conn)
cmd.Parameters.AddWithValue("@RefNo", RefNo)
cmd.Parameters.AddWithValue("@ChangeRaisedDate", DateTime.Now.ToString("dd\/MM\/yyyy HH:mm:ss"))
cmd.ExecuteNonQuery()
End While
End Using
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information, "Spare Elements")
End Try
nextconn.Close()
End Sub
Error Message;
ExecuteReader requires an open and available connection. The connection's current status is closed.
-
October 19th, 2012, 12:37 PM
#7
Re: Error Prompted at RunTime
Might be failing at the TRY, and bubbling down. STEP THRU THE CODE to see the values as they are generated.
-
October 20th, 2012, 01:24 AM
#8
Re: Error Prompted at RunTime
Originally Posted by dr223
Tried that it gave same error message.
Even tried to create another string;
Dim nextconn As New SqlConnection(strConn)
Then I opened and closed same location as you did in bold.
Same error message....
Thats when I asked...
Oh, OK, I'm sorry for my little unwarranted outburst yesterday, please forgive me.
Anyways, a silly suggestion from my side would be to have your INSERT operations and UPDATE operations in two different subs. Or, a trigger
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|