Must declare a scalar variable
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 6 of 6

Thread: Must declare a scalar variable

  1. #1
    Join Date
    Feb 2009
    Posts
    190

    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

  2. #2
    DataMiser is online now Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,830

    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.

  3. #3
    Join Date
    Feb 2009
    Posts
    190

    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

  4. #4
    DataMiser is online now Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,830

    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.

  5. #5
    Join Date
    Feb 2009
    Posts
    190

    Re: Must declare a scalar variable

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


    Many thanks

  6. #6
    Join Date
    Jul 2001
    Location
    Sunny South Africa
    Posts
    11,091

    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
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center