Wednesday, March 7, 2012

Can anyone tell me why this code wont update my SQL Server database?

I am trying to perform a simple update to a SQL Server database table and I can't figure out why this simple UPDATE command doesn't work. I am performing the same thing in another page for owner information and it works just fine. It seems that some of my code won't work even though the syntax is correct and there is no reason for it not to work.

I have hardcoded text for some of the fields and the UPDATE code below works but when I try to use the information that may be in the Textbox, the code won't do the UPDATE. Please help me figure out what is going on.

Thanks

Sub btnUpdate_Click_1(sender As Object, e As EventArgs)
Dim UserCode as String
Dim PropertyCode as String
Dim UnitCode as String
Dim address1 as String
Dim address2 as String
Dim address3 as String
Dim city as String
Dim zip_Code as String
Dim description as String
Dim price_Range as String
Dim state as String
Dim type_Property as String
Dim property_Status as String

if chkChangeState.Checked = True then
state = ddl_State.SelectedItem.Value
else
state = lblState.Text
end if

if chkPropertyType.Checked = True then
type_Property = ddlPropertyType.SelectedItem.Value
else
type_Property = lblPropertyType.Text
end if

if chkPropertyStatus.Checked = True then
property_Status = ddlPropertyStatus.SelectedItem.Value
else
property_Status = lblPropertyStatus.Text
end if

txtPropertyCode.ReadOnly = False
txtUnitCode.ReadOnly = False

'address1 = txtAddress1.Text
'address2 = txtAddress2.Text
'address3 = txtAddress3.Text
'city = txtCity.Text
'zip_Code = txtZipCode.Text
'description = txtDescription.Text
'price_Range = txtPriceRange.Text

UserCode = Server.HtmlEncode(Request.Cookies("UCodeCookie")("Code"))
PropertyCode = txtPropertyCode.Text
UnitCode = txtUnitCode.Text

Dim connectionString As String = "server='(local)'; trusted_connection=true; database='master'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "UPDATE [Property_db] SET [Address1]=@.Address1, [Address2]=@.Address2, [Address3]=@.Address3, [City]=@.City, [State]=@.State, [Zip_Code]=@.Zip_Code, [Type_Property]=@.Type_Property, [Description]=@.Description, [Property_Status]=@.Property_Status, [Price_Range]=@.Price_Range WHERE ([Property_db].[Code] = @.Code) AND ([Property_db].[Prop_Code] = @.Prop_Code) AND ([Property_db].[Unit_Code] = @.Unit_Code)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_code.ParameterName = "@.Code"
dbParam_code.Value = UserCode
dbParam_code.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_code)
Dim dbParam_prop_Code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_prop_Code.ParameterName = "@.Prop_Code"
dbParam_prop_Code.Value = PropertyCode
dbParam_prop_Code.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_prop_Code)
Dim dbParam_unit_Code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_unit_Code.ParameterName = "@.Unit_Code"
dbParam_unit_Code.Value = UnitCode
dbParam_unit_Code.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_unit_Code)
Dim dbParam_address1 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_address1.ParameterName = "@.Address1"
dbParam_address1.Value = txtAddress1.Text
dbParam_address1.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_address1)
Dim dbParam_address2 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_address2.ParameterName = "@.Address2"
dbParam_address2.Value = txtAddress2.Text
dbParam_address2.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_address2)
Dim dbParam_address3 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_address3.ParameterName = "@.Address3"
dbParam_address3.Value = txtAddress3.Text
dbParam_address3.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_address3)
Dim dbParam_city As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_city.ParameterName = "@.City"
dbParam_city.Value = txtCity.Text
dbParam_city.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_city)
Dim dbParam_state As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_state.ParameterName = "@.State"
dbParam_state.Value = state
dbParam_state.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_state)
Dim dbParam_zip_Code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_zip_Code.ParameterName = "@.Zip_Code"
dbParam_zip_Code.Value = txtZipCode.Text
dbParam_zip_Code.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_zip_Code)
Dim dbParam_type_Property As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_type_Property.ParameterName = "@.Type_Property"
dbParam_type_Property.Value = type_Property
dbParam_type_Property.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_type_Property)
Dim dbParam_description As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_description.ParameterName = "@.Description"
dbParam_description.Value = txtDescription.Text
dbParam_description.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_description)
Dim dbParam_property_Status As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_property_Status.ParameterName = "@.Property_Status"
dbParam_property_Status.Value = property_Status
dbParam_property_Status.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_property_Status)
Dim dbParam_price_Range As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_price_Range.ParameterName = "@.Price_Range"
dbParam_price_Range.Value = txtPriceRange.Text
dbParam_price_Range.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_price_Range)

Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try

'if Not rowsAffected then
Response.Redirect("./editproperty.aspx")
'end if
End Sub

Be sure to indicate parameter lengths for character-type data.

Terri

No comments:

Post a Comment