SQL Query

[Larry Dodd]

I am trying to write an application and I need to get the SQL query syntax
right. I have a form that new user information will be filled in. I want
to execute a query that will take information from the two text boxes and
perform a query on the database to see if this user exists. The query should
look something like this:

SELECT * FROM Table where UserFirstName = txtFirstName.Text and UserLastName
= txtLastName.Text

This is just what I think it should look something like. I just can't seem
to get the single quotation and the double quotations correct. Any help would
be appreciated.

[G. Allen]

It sounds like you're trying to create the SQL query in VB so you can pass
it to a recordset object. If this is the case then you need something like
this:

Dim strSQL as String
strSQL = "SELECT * FROM Table where UserFirstName = '" & txtFirstName.Text
& '" and UserLastName '" & txtLastName.Text & "'"

Actually I wouldn't use the *. If all you want to do is see if the record
exists, say by testing for EOF, then don't waste bandwidth pulling all the
fields over. Just select one field, e.g. an id field which you may need
for other things anyway.

As an alternative if you are using SQL Server you may want to create a stored
procedure that takes the names as parameters, uses the Exists function on
the SQL statement and returns True/False based on the results. You can improve
efficiency by using this with the adExecuteNoRecords option on the recordset.




"Larry Dodd" <Larry.Dodd@fmr.com> wrote:
>
>I am trying to write an application and I need to get the SQL query syntax
>right. I have a form that new user information will be filled in. I want
>to execute a query that will take information from the two text boxes and
>perform a query on the database to see if this user exists. The query should
>look something like this:
>
>SELECT * FROM Table where UserFirstName = txtFirstName.Text and UserLastName
>= txtLastName.Text
>
>This is just what I think it should look something like. I just can't seem
>to get the single quotation and the double quotations correct. Any help
would
>be appreciated.

[G. Allen]

You ask a question about quotation marks and I turn around in mix two up in
my response! The line that sets the variable should read:

strSQL = "SELECT * FROM Table where UserFirstName = '" & txtFirstName.Text
& "' and UserLastName = '" & txtLastName.Text & "'"

Sorry for the confusion.

"G. Allen" <gallenspam@worldnet.att.net> wrote:
>
>It sounds like you're trying to create the SQL query in VB so you can pass
>it to a recordset object. If this is the case then you need something like
>this:
>
>Dim strSQL as String
>strSQL = "SELECT * FROM Table where UserFirstName = '" & txtFirstName.Text
>& '" and UserLastName '" & txtLastName.Text & "'"
>
>Actually I wouldn't use the *. If all you want to do is see if the record
>exists, say by testing for EOF, then don't waste bandwidth pulling all the
>fields over. Just select one field, e.g. an id field which you may need
>for other things anyway.
>
>As an alternative if you are using SQL Server you may want to create a stored
>procedure that takes the names as parameters, uses the Exists function on
>the SQL statement and returns True/False based on the results. You can
improve
>efficiency by using this with the adExecuteNoRecords option on the recordset.
>
>
>
>

[Steve Berzins]

> strSQL = "SELECT Count(*) As FoundCount FROM Table where UserFirstName =
'" & txtFirstName.Text
> & "' and UserLastName = '" & txtLastName.Text & "'"

if count is 0 not found, greater than 0 found at least one...I like this
better that testing for eof, code is cleaner...FWTW.

If cn.Execute(strSQL).Fields("FoundCount").Value > 0 Then
'found
Else
'not found
End If

"G. Allen" <gallenspam@worldnet.att.net> wrote in message
news:3ad5df24$1@news.devx.com...
>
> You ask a question about quotation marks and I turn around in mix two up
in
> my response! The line that sets the variable should read:
>
> strSQL = "SELECT * FROM Table where UserFirstName = '" & txtFirstName.Text
> & "' and UserLastName = '" & txtLastName.Text & "'"
>
> Sorry for the confusion.
>
> "G. Allen" <gallenspam@worldnet.att.net> wrote:
> >
> >It sounds like you're trying to create the SQL query in VB so you can
pass
> >it to a recordset object. If this is the case then you need something
like
> >this:
> >
> >Dim strSQL as String
> >strSQL = "SELECT * FROM Table where UserFirstName = '" &
txtFirstName.Text
> >& '" and UserLastName '" & txtLastName.Text & "'"
> >
> >Actually I wouldn't use the *. If all you want to do is see if the
record
> >exists, say by testing for EOF, then don't waste bandwidth pulling all
the
> >fields over. Just select one field, e.g. an id field which you may need
> >for other things anyway.
> >
> >As an alternative if you are using SQL Server you may want to create a
stored
> >procedure that takes the names as parameters, uses the Exists function on
> >the SQL statement and returns True/False based on the results. You can
> improve
> >efficiency by using this with the adExecuteNoRecords option on the
recordset.
> >
> >
> >
> >
>
>