SQL query in vb.net

[vbnetrookie]

Here's what I've got:
*************** **************
Dim postalcode As String
postalcode = txtpostalcode.T ext
Dim title As String
title = ddltitle.Select edItem.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]

Re: SQL query in vb.net

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

"vbnetrooki e" <bigjmt@hotmail .com> wrote in message
news:1117646629 .259343.123460@ g44g2000cwa.goo glegroups.com.. .[color=blue]
> Here's what I've got:
> *************** **************
> Dim postalcode As String
> postalcode = txtpostalcode.T ext
> Dim title As String
> title = ddltitle.Select edItem.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
>[/color]

[Erland Sommarskog]

Re: SQL query in vb.net

vbnetrookie (bigjmt@hotmail .com) writes:[color=blue]
> Here's what I've got:
> *************** **************
> Dim postalcode As String
> postalcode = txtpostalcode.T ext
> Dim title As String
> title = ddltitle.Select edItem.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.[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp