-
October 23rd, 2012, 07:13 AM
#1
Must declare a scalar variable
Hi,
I have the following code;
Code:
Dim query As String
Dim RefID As New System.Data.SqlClient.SqlCommand(("Select RefNo From dbo.QryRefNo WHERE ChangeRaisedDate = @ChangeRaisedDate"), nextconn)
cmd.Parameters.AddWithValue("@ChangeRaisedDate", DateTime.Now.ToString("dd\/MM\/yyyy HH:mm:ss"))
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"
cmd = New SqlCommand(query, nextconn)
cmd.Parameters.AddWithValue("@RefNo", RefNo)
cmd.ExecuteNonQuery()
End While
End Using
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information, "Spare Elements")
End Try
At run time when the button is clicked I receive the error;
Must declare the scalar variable "@ChangeRaiseddate" - No Update takes place
Why?
Thanks
-
October 23rd, 2012, 08:02 AM
#2
Re: Must declare a scalar variable
Code:
Dim RefID As New System.Data.SqlClient.SqlCommand(("Select RefNo From dbo.QryRefNo WHERE ChangeRaisedDate = @ChangeRaisedDate"), nextconn)
cmd.Parameters.AddWithValue("@ChangeRaisedDate", DateTime.Now.ToString("dd\/MM\/yyyy HH:mm:ss"))
Try
Using Autoreader As System.Data.SqlClient.SqlDataReader = RefID.ExecuteReader()
Always use [code][/code] tags when posting code.
-
October 23rd, 2012, 08:36 AM
#3
Re: Must declare a scalar variable
Ok Changed it to;
Code:
nextconn.Open()
Dim query As String
Dim cmdB As New System.Data.SqlClient.SqlCommand(("Select RefNo From dbo.QryRefNo WHERE ChangeRaisedDate = @ChangeRaisedDate"), nextconn)
cmdB.Parameters.AddWithValue("@ChangeRaisedDate", DateTime.Now.ToString("dd\/MM\/yyyy HH:mm:ss"))
Using Autoreader As System.Data.SqlClient.SqlDataReader = cmdB.ExecuteReader()
While Autoreader.Read()
Dim RefNo As String = (Autoreader.GetValue(0))
query = "UPDATE dbo.TblChangeControlDet SET RefNo = @RefNo"
cmdB = New SqlCommand(query, nextconn)
cmdB.Parameters.AddWithValue("@RefNo", RefNo)
cmdB.ExecuteNonQuery()
End While
End Using
nextconn.Close()
Now I receive the error - There is already an open DataReader associated with this command which must be close first..
Why.. Many thanks
-
October 23rd, 2012, 09:16 AM
#4
Re: Must declare a scalar variable
Because you are trying to do an update query on a connection with an open datareader.
You also should not be creating a new instance of the cmd object within your loop.
Not sure why you are even using a data reader and a loop. You could do this all in one Sql Update query.
Always use [code][/code] tags when posting code.
-
October 23rd, 2012, 09:25 AM
#5
Re: Must declare a scalar variable
please tell how to do it in one SQL update statement..
Many thanks
-
October 24th, 2012, 05:18 AM
#6
Re: Must declare a scalar variable
As mentioned in your previous thread :
http://forums.codeguru.com/showthrea...ted-at-RunTime
You could use a transaction :
Here is an example :
http://msdn.microsoft.com/en-us/libr...ansaction.aspx
You should also look into Store Procedures with VB.NET and SQL
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
|