Solved

SQL question

Posted on 2002-05-03
11
163 Views
Last Modified: 2010-05-02
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
0
Comment
Question by:hilltop
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +1
11 Comments
 
LVL 2

Author Comment

by:hilltop
ID: 6989522
This is being executed from a webclass
0
 
LVL 12

Expert Comment

by:roverm
ID: 6989641
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 6989671
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 2

Author Comment

by:hilltop
ID: 6989882
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
 
LVL 2

Author Comment

by:hilltop
ID: 6989886
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
 
LVL 2

Author Comment

by:hilltop
ID: 6989998
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
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 50 total points
ID: 6990453
Domain is a reserved word.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6990460
You could also have surrounded with square brackets [Domain]
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 6992705
You're right emoreau.  Seems like we overlook the obvious.

mlmcc
0
 
LVL 12

Expert Comment

by:roverm
ID: 6993088
Yeppers....Good one emoreau!
0
 
LVL 2

Author Comment

by:hilltop
ID: 6994098
Thank you very much
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sending a email via excel using vba 6 109
vb6, excel Run-time error 1004 method 'close' of object 'workbooks' failed 7 70
vbModal 12 66
Excel Automation VBA 19 104
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question