Must declare a scalar variable

[dr223]

Hi,

I have the following 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

[DataMiser]

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()

[dr223]

Ok Changed it to;

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

[DataMiser]

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.

[dr223]

please tell how to do it in one SQL update statement..


Many thanks

[HanneSThEGreaT]

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