how to make condition to update table ?

Question

hi..i want to make condition to update my table if there's already same data (in the same column) inserted in the table.

im using

 If String.ReferenceEquals(hotel, hotel) = False Then

        insertDatabase()
    Else
        updateDatabase()

    End If

this is the updateDatabase() code...

 Dim sql2 As String = "update infoHotel set nameHotel = N" & FormatSqlParam(hotel) & _
                                                      ", streetAddress = N" & FormatSqlParam(StreetAddress) & _
                                                      ", locality = N" & FormatSqlParam(Locality) & _
                                                      ", postalCode = N" & FormatSqlParam(PostalCode) & _
                                                      ", country = N" & FormatSqlParam(Country) & _
                                                      ", addressFull = N" & FormatSqlParam(address) & _
                                                      ", tel = N" & FormatSqlParam(contact) & _
                                                      "where hotel = '" & hotel & "')"

this is the formatSqlParam() code:

Function FormatSqlParam(ByVal strParam As String) As String

    Dim newParamFormat As String

    If strParam = String.Empty Then
        newParamFormat = "'" & "NA" & "'"
    Else
        newParamFormat = strParam.Trim()
        newParamFormat = "'" & newParamFormat.Replace("'", "''") & "'"
    End If

    Return newParamFormat

End Function

the function manage to go into updateDatabase() but has some error saying

"Incorrect syntax near 'Oriental'."

Oriental is the data inserted in the table. is it suitable to use ReferenceEquals?

im using vb.net and sql database..tq

Answer 1

You have a single quote in your data, as in:

Oriental's

Show us the code for FormatSqlParam() -- the bug is probably in there.

Or else you left out the single quotes around the hotel name:

where hotel = '" & hotel & "')"

I hope that's not it, because it would mean you're using the name as a key, a very bad idea.

Answer 2

You should be using parameterized queries instead of concatenating strings like so:

Dim sql2 As String = "Update InfoHotel" _ 
    & "Set nameHotel = @nameHotel" _ 
    & ", knownAs1 = @knownAs1" _ 
    & ", knownAs2 = @knownAs2" _    
    & ", knownAs3 = @knownAs3" _    
    & ", knownAs4 = @knownAs4" _ 
    & " Where hotel = @hotel"

If you used parameterized queries, you wouldn't have to worry about trying do quote replacement and potentially introducing a SQL Injection vulnerability.