Results 1 to 4 of 4

Thread: Must Declare a Variable

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jul 2021
    Posts
    71

    Must Declare a Variable

    Hi,

    I have the error:

    Must declare the scalar variable "@propertyID".

    Please help!

    Code:
    Private Sub UpdatedPropertyDetails()
    
            Dim conn As SqlConnection = GetDbConnection()
            Dim conn1 As SqlConnection = GetDbConnection()
    
            Dim query As String
            Dim cmd As New SqlCommand
    
            If cmbPTypeUp.SelectedIndex = 0 Then
                MessageBox.Show("Select Property Type", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Information)
                cmbPType.Focus()
                Return
            End If
    
            If cmbLandlordnameUp.SelectedIndex = 0 Then
                MessageBox.Show("Select Property Owner", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Information)
                cmbLandlordname.Focus()
                Return
            End If
    
            If TxtBNameUp.Text = "" Then
                MessageBox.Show("Enter Building Name", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Information)
                TxtBName.Focus()
                Return
            End If
    
            If TxtPlotNoUp.Text = "" Then
                MessageBox.Show("Enter Plot No", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Information)
                TxtPlotNo.Focus()
                Return
            End If
    
            If TxtLocUp.Text = "" Then
                MessageBox.Show("Enter Location", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Information)
                TxtLoc.Focus()
                Return
            End If
    
            If TxtTUnitsUp.Text = "" Or IsNumeric(TxtTUnitsUp.Text) = False Then
                MessageBox.Show("Enter total number of Units or Value entered is not valid", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Information)
                TxtTUnitsUp.Focus()
                Return
            End If
    
            Dim Property_Read As New System.Data.SqlClient.SqlCommand(("Select propertyID from tblproperties where PropertyID ='" &
                                        Me.TxtPropertyIDClick.Text & "' "), conn)
    
            Try
    
                Using Autoreader As System.Data.SqlClient.SqlDataReader = Property_Read.ExecuteReader()
    
                    While Autoreader.Read()
    
                        Dim PropertyID As String = Autoreader.GetValue(0)
    
                        If PropertyID = TxtPropertyIDClick.Text Then
    
                            Try
                                query = "UPDATE dbo.tblproperties SET propertytypeid=@propertytypeid, landlordregID=@landlordregID, buildingname=@buildingname, plotno=@plotno, location=@location, totalunits=@totalunits OUTPUT DELETED.propertytypeid,DELETED.landlordregID,DELETED.buildingname,DELETED.plotno,DELETED.location,DELETED.totalunits INTO dbo.TblPropertiesHistory (propertytypeid, landlordregID, buildingname, plotno, location, totalunits) WHERE propertyID=@propertyID"
                                cmd = New SqlCommand(query, conn1)
    
                                cmd.Parameters.AddWithValue("@propertytypeid", cmbPTypeUp.SelectedValue)
                                cmd.Parameters.AddWithValue("@landlordregID", cmbLandlordnameUp.SelectedValue)
                                cmd.Parameters.AddWithValue("@buildingname", TxtBNameUp.Text)
                                cmd.Parameters.AddWithValue("@plotno", TxtPlotNoUp.Text)
                                cmd.Parameters.AddWithValue("@location", TxtLocUp.Text)
                                cmd.Parameters.AddWithValue("@totalunits", TxtTUnitsUp.Text)
    
                                cmd.ExecuteNonQuery()
    
                                MessageBox.Show("Record has been updated", "SUCCESS", MessageBoxButtons.OK, MessageBoxIcon.Information)
    
                            Catch ex As Exception
                                MsgBox(ex.Message, MsgBoxStyle.Information, "RCMS")
                            End Try
    
                        Else
                        End If
    
                    End While
                End Using
    
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Information, "RCMS")
            End Try
    
        End Sub
    Thanks

  2. #2
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,458

    Re: Must Declare a Variable

    Quote Originally Posted by dr225 View Post
    Hi,

    I have the error:

    Must declare the scalar variable "@propertyID".

    Please help!

    Code:
    Private Sub UpdatedPropertyDetails()
    
            Dim conn As SqlConnection = GetDbConnection()
            Dim conn1 As SqlConnection = GetDbConnection()
    
            Dim query As String
            Dim cmd As New SqlCommand
    
            If cmbPTypeUp.SelectedIndex = 0 Then
                MessageBox.Show("Select Property Type", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Information)
                cmbPType.Focus()
                Return
            End If
    
            If cmbLandlordnameUp.SelectedIndex = 0 Then
                MessageBox.Show("Select Property Owner", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Information)
                cmbLandlordname.Focus()
                Return
            End If
    
            If TxtBNameUp.Text = "" Then
                MessageBox.Show("Enter Building Name", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Information)
                TxtBName.Focus()
                Return
            End If
    
            If TxtPlotNoUp.Text = "" Then
                MessageBox.Show("Enter Plot No", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Information)
                TxtPlotNo.Focus()
                Return
            End If
    
            If TxtLocUp.Text = "" Then
                MessageBox.Show("Enter Location", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Information)
                TxtLoc.Focus()
                Return
            End If
    
            If TxtTUnitsUp.Text = "" Or IsNumeric(TxtTUnitsUp.Text) = False Then
                MessageBox.Show("Enter total number of Units or Value entered is not valid", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Information)
                TxtTUnitsUp.Focus()
                Return
            End If
    
            Dim Property_Read As New System.Data.SqlClient.SqlCommand(("Select propertyID from tblproperties where PropertyID ='" &
                                        Me.TxtPropertyIDClick.Text & "' "), conn)
    
            Try
    
                Using Autoreader As System.Data.SqlClient.SqlDataReader = Property_Read.ExecuteReader()
    
                    While Autoreader.Read()
    
                        Dim PropertyID As String = Autoreader.GetValue(0)
    
                        If PropertyID = TxtPropertyIDClick.Text Then
    
                            Try
                                query = "UPDATE dbo.tblproperties SET propertytypeid=@propertytypeid, landlordregID=@landlordregID, buildingname=@buildingname, plotno=@plotno, location=@location, totalunits=@totalunits OUTPUT DELETED.propertytypeid,DELETED.landlordregID,DELETED.buildingname,DELETED.plotno,DELETED.location,DELETED.totalunits INTO dbo.TblPropertiesHistory (propertytypeid, landlordregID, buildingname, plotno, location, totalunits) WHERE propertyID=@propertyID"
                                cmd = New SqlCommand(query, conn1)
    
                                cmd.Parameters.AddWithValue("@propertytypeid", cmbPTypeUp.SelectedValue)
                                cmd.Parameters.AddWithValue("@landlordregID", cmbLandlordnameUp.SelectedValue)
                                cmd.Parameters.AddWithValue("@buildingname", TxtBNameUp.Text)
                                cmd.Parameters.AddWithValue("@plotno", TxtPlotNoUp.Text)
                                cmd.Parameters.AddWithValue("@location", TxtLocUp.Text)
                                cmd.Parameters.AddWithValue("@totalunits", TxtTUnitsUp.Text)
    
                                cmd.ExecuteNonQuery()
    
                                MessageBox.Show("Record has been updated", "SUCCESS", MessageBoxButtons.OK, MessageBoxIcon.Information)
    
                            Catch ex As Exception
                                MsgBox(ex.Message, MsgBoxStyle.Information, "RCMS")
                            End Try
    
                        Else
                        End If
    
                    End While
                End Using
    
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Information, "RCMS")
            End Try
    
        End Sub
    Thanks
    Your SQL command has a parameter called PropertyId and you aren't providing one. You need to add PropertyId into you list of Parameters

  3. #3
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: Must Declare a Variable

    FYI - It's best practice to use Parameters.Add and then specify the value. You can also shorten the code a bit by using the With keyword:
    Code:
    With cmd.Parameters
        .Add("@propertytypeid", SqlTypes.SqlInt32).Value = cmbPTypeUp.SelectedValue
        .Add("@landlordregID", SqlTypes.SqlInt32).Value = cmbLandlordnameUp.SelectedValue
        .Add("@buildingname", SqlTypes.SqlString).Value = TxtBNameUp.Text
        .Add("@plotno", SqlTypes.SqlString).Value = TxtPlotNoUp.Text
        .Add("@location", SqlTypes.SqlString).Value = TxtLocUp.Text
        .Add("@totalunits", SqlTypes.SqlString).Value = TxtTUnitsUp.Text
        .Add("@propertyID", SqlTypes.SqlInt32).Value = MyPropertyIdValueHere ' change this value
    End With
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: Must Declare a Variable

    Quote Originally Posted by dday9 View Post
    FYI - It's best practice to use Parameters.Add and then specify the value. You can also shorten the code a bit by using the With keyword:
    Code:
    With cmd.Parameters
        .Add("@propertytypeid", SqlTypes.SqlInt32).Value = cmbPTypeUp.SelectedValue
        .Add("@landlordregID", SqlTypes.SqlInt32).Value = cmbLandlordnameUp.SelectedValue
        .Add("@buildingname", SqlTypes.SqlString).Value = TxtBNameUp.Text
        .Add("@plotno", SqlTypes.SqlString).Value = TxtPlotNoUp.Text
        .Add("@location", SqlTypes.SqlString).Value = TxtLocUp.Text
        .Add("@totalunits", SqlTypes.SqlString).Value = TxtTUnitsUp.Text
        .Add("@propertyID", SqlTypes.SqlInt32).Value = MyPropertyIdValueHere ' change this value
    End With
    I'm fairly sure that there is no such overload of Add. The enumeration of interest is SqlDbType rather than SqlTypes. For data types with a variable size, you should specify the size, e.g.
    vb.net Code:
    1. .Add("@propertytypeid", SqlDbType.VarChar, 50).Value = cmbPTypeUp.SelectedValue
    for those with a fixed-size, you specify the actual size or zero, but you can just omit it, e.g.
    vb.net Code:
    1. .Add("@propertyID", SqlDbType.Int).Value = MyPropertyIdValueHere

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width