INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

porblem with quotes in building an ADO SELECT with multiple AND's

porblem with quotes in building an ADO SELECT with multiple AND's

(OP)
In an asp file I am opening an ADO recordset and want to search for a unique record in the set that matches name, date and time in the database against the entries by a user.  All three db fields are defined in the db as varchars.

A form is used to enter the three values at the client side and then the form is submitted to this asp file which then does the SELECT on the recordset.  The resultant row of values then populate the GUI form in the client's browser.

Here's the SELECT that I'm having trouble with.

.Source = "SELECT * FROM CareMinistry WHERE care_Contact_Last_Name = '" & field4 & "'" AND entry_Date = "'" & field2 & "'" AND entry_Time = "'" & field3  & "'" & ";"

Thanks for your help.

 

RE: porblem with quotes in building an ADO SELECT with multiple AND's

You had some quotes in the wrong place.  Try this:

CODE

.Source = "SELECT * FROM CareMinistry WHERE care_Contact_Last_Name = '" & field4 & "' AND entry_Date = '" & field2 & "' AND entry_Time = '" & field3  & "';"
 

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: porblem with quotes in building an ADO SELECT with multiple AND's

(OP)
Spot on, George!  Thank you for taking the time to look into this.  I have spent a ton of time trying to get the quotes right and now it is set up correctly.

Darold

RE: porblem with quotes in building an ADO SELECT with multiple AND's

Just a note about a method I used in the past to handle compound quoted string segments :
I created a variable for the constant strings something like
(excuse my syntax errors , its been a couple of years since I wrote much code)

StringVar QueryStart := "Select * from ";
StringVar TableName := "CareMinistry";
StringVar WhereStart := "Where care_Contact_Last_Name =";
StringVar AndClause1 "AND Entry_date = ";
StringVar AndClause2 "And Entry_Time = ";
Then you can build the actual statement more easily:

.Source = QueryStart & TableName & WhereStart & field4 & AndClause1 & field2 & AndClause2 & field3;



You can replace the variable contents to build different queries and I found that I had fewer problems with balancing quotes .
 

profile

To Paraphrase:"The Help you get is proportional to the Help you give.."

RE: porblem with quotes in building an ADO SELECT with multiple AND's

(OP)
Thanks for the tip.  I can see where that approach can virtually eliminate the "single/double quote" query-building mistakes. Very helpful suggestion.

 

RE: porblem with quotes in building an ADO SELECT with multiple AND's

I tend to code it differently - a lot more work but it means I can comment out whole lines more easily

CODE

sql = "SELECT *"
sql = sql & " FROM CareMinistry"
sql = sql & " WHERE"
sql = sql & " care_Contact_Last_Name = '" & field4 & "'"
sql = sql & " AND"
sql = sql & " entry_Date = '" & field2 & "'"
sql = sql & " AND"
sql = sql & " entry_Time = '" & field3  & "'"
sql = sql & ";"
If I don't want the care_contact, I'd just comment out that line and the following AND.  I also found that starting with a leading space is a lot easier to remember than ending with a trailing space.

RE: porblem with quotes in building an ADO SELECT with multiple AND's

I usually do something similar to xwb's method, but I format it differently, like this:

CODE

sql = ""
sql = sql & " SELECT *"
sql = sql & " FROM   CareMinistry"
sql = sql & " WHERE  care_Contact_Last_Name = '" & field4 & "'"
sql = sql & "        AND entry_Date = '" & field2 & "'"
sql = sql & "        AND entry_Time = '" & field3  & "'"
sql = sql & ";"

I find that it's easier to read the SQL query without the syntax getting in the way.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: porblem with quotes in building an ADO SELECT with multiple AND's

(OP)
Thanks to all who contributed all of the great ideas.  As for my initial problem, George (gmmastros) is the solution I used which resolved the issue.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close