CreamStat
CreamStat

Reputation: 2185

Invalid column name exception in SQL Query - VB.Net

I made a button to receive the texts from two combo boxes. The connection is OK and the names in the combo box too, but when I run the application for any two valid names I receive the message Invalid column name. I'm sure that the column names are valid according to my data base.

Here's my code:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles btnProcesar.Click

  sql.sqlcon.Open()
  sql.sqlsentence = New SqlCommand("SELECT * FROM dbo.Univ$ WHERE Sector =  " & ingSector.Text & "  AND Ciudad =  " & ingCiudad.Text, sql.sqlcon)

  Dim adaptador As SqlDataAdapter
  adaptador = New SqlDataAdapter(sql.sqlsentence)

  Dim ds As New DataSet
  adaptador.Fill(ds)
  sql.sqlcon.Close()
  dgrid.DataSource = ds.Tables(0)

End Sub

So, how can I fix that?

Upvotes: 0

Views: 5605

Answers (2)

Guffa
Guffa

Reputation: 700332

You don't have apostrophes around the string literals in the query, so the database thinks that the values are supposed to be column names.

Adding apostrophes around the values would make it work:

sql.sqlsentence = New SqlCommand("SELECT * FROM dbo.Univ$ WHERE Sector =  '" & ingSector.Text & "'  AND Ciudad =  '" & ingCiudad.Text & "'", sql.sqlcon)

However, you should really look into using a parameterised query to use the values, instead of concatenating the values into the query.

Upvotes: 3

HengChin
HengChin

Reputation: 593

Do this instead of your current one to avoid SQL injection.

  Private Sub Button1_Click(sender As Object, e As EventArgs) Handles btnProcesar.Click
    sql.sqlcon.Open()
    sql.sqlsentence = New SqlCommand("SELECT * FROM dbo.Univ$ WHERE Sector=@sector AND Ciudad=@ciudad" , sql.sqlcon)

    Dim adaptador As SqlDataAdapter
    adaptador = New SqlDataAdapter(sql.sqlsentence)
    adaptador.SelectCommand.Parameters.AddWithValue("@sector", ingSector.Text)
    adaptador.SelectCommand.Parameters.AddWithValue("@ciudad", ingCiudad.Text)

    Dim ds As New DataSet
    adaptador.Fill(ds)
    Sql.sqlcon.Close()
    dgrid.DataSource = ds.Tables(0)
  End Sub

Upvotes: 4

Related Questions