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
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
Re: Error Prompted at RunTime
How can you do the first one please
Re: Error Prompted at RunTime
Quote:
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
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...
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.
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.
Re: Error Prompted at RunTime
Quote:
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