SQL query in vb.net

[vbnetrookie]

Here's what I've got:
*****************************
Dim postalcode As String
postalcode = txtpostalcode.Text
Dim title As String
title = ddltitle.SelectedItem.Text
Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM " & PubName & "
WHERE PostalCode=" & postalcode And " Title=" & title ORDER BY
Last_Name"

***********************
Last_Name, PostalCode and Title are columns in my table.
My table is referenced as PubName from a drop dow list.
I just want to know were the error is in this sqlStr since it always
gives me an error in that line. I'm pretty sure it has to do with the
symbols (& " = ). I just can't seem to get it right.
Any clues ??
Thanks
JMT

[John Bell]

Hi

WHERE PostalCode=" & postalcode And " Title=" & title ORDER BY
Last_Name"

should be:

WHERE PostalCode=" & postalcode & " And Title = " & title & " ORDER BY
Last_Name"

You may want to enquote postalcode and title
WHERE PostalCode = '" & postalcode & "' And Title = '" & title & "' ORDER BY
Last_Name"

John

"vbnetrookie" <bi****@hotmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

Here's what I've got:
*****************************
Dim postalcode As String
postalcode = txtpostalcode.Text
Dim title As String
title = ddltitle.SelectedItem.Text
Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM " & PubName & "
WHERE PostalCode=" & postalcode And " Title=" & title ORDER BY
Last_Name"

***********************
Last_Name, PostalCode and Title are columns in my table.
My table is referenced as PubName from a drop dow list.
I just want to know were the error is in this sqlStr since it always
gives me an error in that line. I'm pretty sure it has to do with the
symbols (& " = ). I just can't seem to get it right.
Any clues ??
Thanks
JMT

[Erland Sommarskog]

vbnetrookie (bi****@hotmail.com) writes:

Here's what I've got:
*****************************
Dim postalcode As String
postalcode = txtpostalcode.Text
Dim title As String
title = ddltitle.SelectedItem.Text
Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM " & PubName & "
WHERE PostalCode=" & postalcode And " Title=" & title ORDER BY
Last_Name"

***********************
Last_Name, PostalCode and Title are columns in my table.
My table is referenced as PubName from a drop dow list.
I just want to know were the error is in this sqlStr since it always
gives me an error in that line. I'm pretty sure it has to do with the
symbols (& " = ). I just can't seem to get it right.

Don't build complete SQL strings like this. Use the parameter object
to supply your parameters:

Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM " & PubName & "
WHERE PostalCode= @postalcode And Title = @title ORDER BY LastName

Then use .AddParameter to defined @postalcode and @title. What you
are trying to do above, is open for a security problem known as SQL
injection.

Also, I don't know why PubBane is a variable - dynamic selection of
table names usually indicates poor database design.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp