CODEWITHSUNDEEP
sql.netsql-servervb.net
Lamloumi Afif
Lamloumi Afif

Reputation: 9081

Avoiding spaces in Sql query within Vb.Net application

I have a database table in which i have two these fields : prix min and prix max

When i try to update my table 

 Public Shared Sub updatetraitementprix(ByVal min As Double, ByVal max As Double, ByVal ref As Integer)

        Dim MaConnexion As SqlConnection = Nothing
        Try
            MaConnexion = OuvrirConnection()
            Dim sql As String = "update traitementprix set prix min=@min , prix max=@max  where Ref=@refer"

            Dim Commande As New SqlCommand(sql, MaConnexion)

            Commande.CommandType = CommandType.Text
            Commande.Parameters.Add(New SqlParameter("@refer", ref))
            Commande.Parameters.Add(New SqlParameter("@min", min))
            Commande.Parameters.Add(New SqlParameter("@max", max))
            Commande.ExecuteNonQuery()

        Catch
        Finally
            If MaConnexion.State = ConnectionState.Open Then
                MaConnexion.Close()
            End If
        End Try

    End Sub

i have an error of Sql Syntax because of the space in the names of the fields.

  1. So how can i avoid this problem ?
  2. How can i make the Sql query ignore the space in the names of the fields?

Upvotes: 0

Views: 1001

Answers (2)

jpw
jpw

Reputation: 44921

Use square brackets. Do this:

"update traitementprix set [prix min]=@min, [prix max]=@max where Ref=@refer"

From the documentation:

The name of a result set column is an identifier. If the name is a regular identifier that follows the rules for identifiers, it does not have to be delimited. If the name does not follow the rules for identifiers it must be delimited using either brackets ([]) or double quotation marks (""). Double quotation marks can be used to delimit result set column names, regardless of the setting of the QUOTED_IDENTIFIER option.

And, the answer to the first part of the query is of course to only use identifiers without white spaces (or other characters that break the rules for regular identifiers).

See this TechNet article for more information of the various classes of identifiers.

Upvotes: 6

Dan Bracuk
Dan Bracuk

Reputation: 20804

Put square brackets around the field name containing the space.

Upvotes: 2

Related Questions