SQL question

hi can someone please tell me why this statement will not work it works fine when just searching for subdom but will not when using AND Please advise...

Subdom = Request.QueryString("Subdom")
Domain = Request.QueryString("Domain")
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

   ' Open the connection
   cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & App.Path & "\DDNSHCP.mdb;"
 
   ' Open the recordset
   rst.Open "SELECT * FROM Users WHERE Subdom = '" & Subdom & "' AND Domain = "' & Domain & "'", _
      cnn, adOpenKeyset, adLockOptimistic
LVL 2
hilltopAsked:
Who is Participating?
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
Domain is a reserved word.
0
 
hilltopAuthor Commented:
This is being executed from a webclass
0
 
rovermCommented:
Couple of possibilities:
1. Make sure the query isn't case sensitive:
SELECT * FROM Users WHERE UCase(Subdom) = '" & UCase(Subdom) & "' AND UCase(Domain) = "' & UCase(Domain) & "'" (I am not sure about the UCase in the SQL statement, could be Upper as well).
2. Use the LIKE statement instead of the '=':
SELECT * FROM Users WHERE Subdom LIKE '" & Subdom & "%' AND Domain LIKE "' & Domain & "%'" (make sure to add the percent sign '%')
3. Combine the first 2 options.

D'Mzzl!
RoverM  
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
mlmccCommented:
rst.Open "SELECT * FROM Users WHERE Subdom = '" & Subdom & "' AND Domain = "' & Domain & "'", _
     cnn, adOpenKeyset, adLockOptimistic

Unless it is a typo you have the quotes backwards between Domain = and & Domain

Should be
rst.Open "SELECT * FROM Users WHERE Subdom = '" & Subdom & "' AND Domain = '" & Domain & "'", _
     cnn, adOpenKeyset, adLockOptimistic

If that is not the problem what error are you getting?  

If you are just not getting any records are you sure records exist for the criteria?

good luck
mlmcc
0
 
hilltopAuthor Commented:
No dice when using
rst.Open "SELECT * FROM Users WHERE Subdom = '" & Subdom & "' AND Domain = '" & Domain & "'", _
    cnn, adOpenKeyset, adLockOptimistic

It just pops up the microsoft script debugger no error other than that.

when using
rst.Open "SELECT * FROM Users WHERE Subdom = '" & Subdom & "'", _
      'cnn, adOpenKeyset, adLockOptimistic

it works fine I am at a serios loss here
0
 
hilltopAuthor Commented:
new development when using

rst.Open "SELECT * FROM Users WHERE Domain = '" & Domain & "'", _
      cnn, adOpenKeyset, adLockOptimistic

it errors as well this is a field in the Users table any ideas
0
 
hilltopAuthor Commented:
I renamed the field Domains and updated my app to reflect the new field name were applicable the AND statment now works fine. Do you have any idea why this happened
0
 
Éric MoreauSenior .Net ConsultantCommented:
You could also have surrounded with square brackets [Domain]
0
 
mlmccCommented:
You're right emoreau.  Seems like we overlook the obvious.

mlmcc
0
 
rovermCommented:
Yeppers....Good one emoreau!
0
 
hilltopAuthor Commented:
Thank you very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.