CODEWITHSUNDEEP
vb.netvisual-studio-2008sql-server-2008
Arash
Arash

Reputation: 3051

How To Find Input String In Database

I want to find the text in textbox in my database. I wrote the code below. It finds numbers well, but when I want to find strings it gives a runtime error: invalid column name for example aaa, but aaa exists in column1 in my table.

What do you think about the problem?

    cmd = New SqlCommand("select * from tbl where column1=" + TextBox1.Text)
    cmd.CommandType = CommandType.Text
    cmd.Connection = cnn
    dad.SelectCommand = cmd
    cmd.ExecuteNonQuery()
    dad.Fill(ds)
    dgv.DataSource = ds.Tables(0)

Upvotes: 1

Views: 682

Answers (2)

Didier Levy
Didier Levy

Reputation: 3453

Adritt is right, you must enclose the text to find within single quotes and, provided that very text doesn't contains single quotes, all is well - Apart for the risk of SQL attacks.

That being said, you are using an outdated, obsolete way of coding your app against the database.

You should definitively have a deep look at LINQ technology where:

  • You have no connection to open or close: its automatic
  • You don't have to cope with quotes, crlf and commas
  • You do not risk SQL attacks: queries are parameterised by the LINQ engine
  • You will benefit from intellisense againts the database objects: tables, views, columns
  • You get a straightforward result to use as the datasource!
  • For INSERT and UPDATE statements transactions are automatically enabled.

Example:

using ctx as new dataContext1
  dim result = from r in ctx.tbl
    where r.column1 = textBox1.text

    dgv.datasource = result.tolist
end using

Intellisense:

  • When you type "ctx.", you are presented with the list of available tables!
  • When you type "r." you are presented with the list of column the table (or vieww) contains!

LINQ is not difficult to learn and you'll find tons of examples to help you, here and there on the web.

Last but not least, you can use the LINQ SQL-like syntax to query XML data, CSV files, Excel spreadsheets and even the controls in your form, or the HTML DOM document in ASP.NET!

Upvotes: 1

Jeff Ogata
Jeff Ogata

Reputation: 57783

That's because the sql statement you send is not delimiting the TextBox1.Text value so you end up with this sql:

select * from tbl where column1 = aaa

and SQL Server treats aaa as a column name.

Regardless of that, you should be using a SqlParameter to avoid sql injection attacks:

cmd = New SqlCommand("select * from tbl where column1=@value") 
cmd.CommandType = CommandType.Text 
cmd.Paramaters.AddWithValue("@value", TextBox1.Text)

VB is not my primary language, so the syntax might be a little off, but you should be able to make it work.

Upvotes: 3

Related Questions