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

[dekesc]

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.

 

[gmmastros]

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

.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

 

[dekesc]

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

 

[Turkbear]

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 .




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

 

[dekesc]

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

 

[xwb]

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

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.

 

[gmmastros]

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

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

 

[dekesc]

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.