Invalid column name exception in SQL Query - VB.Net

Question

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?

If I were to key in (' or 1=1--) in your ingSector.Text. It will return all result from the table. — HengChin
– HengChin, Commented Jul 3, 2014 at 0:57

HengChin · Accepted Answer · 2014-07-03 01:05:11Z

4

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

answered Jul 3, 2014 at 1:05

HengChin

5715 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Guffa · Accepted Answer · 2014-07-03 10:41:02Z

3

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.

edited Jul 3, 2014 at 10:41

answered Jul 2, 2014 at 23:53

Guffa

703k111 gold badges760 silver badges1k bronze badges

2 Comments

CreamStat Over a year ago

Thanks for making that clear !. Why Should I look into using a parameterised query? Would you mind recommending a goord resource about that topic?

Dan Bracuk Over a year ago

This will get you started. software-security.sans.org/developer-how-to/…. While the focus is on security, I think the biggest values are escaping special characters, like apostrophes, and better performance.

Collectives™ on Stack Overflow

Invalid column name exception in SQL Query - VB.Net

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related