-
Sep 13th, 2021, 11:21 AM
#1
Thread Starter
Lively Member
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
-
Sep 13th, 2021, 11:40 AM
#2
Re: Must Declare a Variable
Originally Posted by dr225
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
-
Sep 13th, 2021, 12:51 PM
#3
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
-
Sep 13th, 2021, 07:49 PM
#4
Re: Must Declare a Variable
Originally Posted by dday9
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:
.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:
.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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|