Valid SQL? Or is there a better way

[mossimo]

My code below works fine but I’m not sure if it’s valid since I’ve never seen it done this way.

I’m checking one field each in three different tables to see if it has data or is null.
I only care if all three fields are null (false) or if any one of them has data (true).

If someone with more experience could let me know also I think this can be done with a single SQL statement but so far I’ve failed.

	SqlOne="SELECT FieldOne FROM tblOne WHERE FieldOne='vToMatch'"
	SqlTwo="SELECT FieldTwo FROM tblTwo WHERE FieldTwo='vToMatch'"
	SqlThree="SELECT FieldThree FROM tblThree WHERE FieldThree='vToMatch'"

	Set Conn=Server.Createobject("ADODB.Connection")
	Conn.Open ConxString
	Set RsOne=Conn.Execute(SqlOne)
	Set RsTwo=Conn.Execute(SqlTwo)
	Set RsThree=Conn.Execute(SqlThree)

	If RsOne.EOF Then xOne=False Else xOne=True End If
	If RsTwo.EOF Then xTwo=False Else xTwo=True End If
	If RsThree.EOF Then xThree=False Else xThree=True End If

	RsOne.Close : RsTwo.Close : RsThree.Close : Conn.Close
	Set RsOne=Nothing : Set RsTwo=Nothing : Set RsThree=Nothing : Set Conn=Nothing

Big Thanks
mossimo

 

[scriverb]

Try this code:

Dim vNullOrData as Boolean
vNullOrData = IIF(IsNull(DLookUp("[FieldOne]","[tblOne]","[FieldOne]='" & vToMatch & "'")) and 
IsNull(DLookUp("[FieldTwo]","[tblTwo]","[FieldTwo]='" & vToMatch & "'")) and 
IsNull(DLookUp("[FieldThree]","[tblThree]","[FieldThree]='" & vToMatch & "'")), True, False)

I don't know what the value of vToMatch as you have left this variable declaration and assignment of value out of your posted code. I am assuming it is a string value. By running this code the variable vNullOrdata will be equal to False(no data in any of the fields) or True(data in at least one of the fields).

Post back if you have any questions.



Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[mossimo]

Thanks scriverb and yes vToMatch is a string value but I really don’t know how to implement your code. The examples I’ve found have been for access forms not asp pages? Could you give an example of how your code would fit into the database call?

mossimo

 

[lespaul]

instead of doing each call separately, you just have to do this one thing.

so you have xOne = True and xTwo = True and xThree = True

bob set it up so that you have

vNullorData

if ANY of them of them have data it's true,
if ALL of them are Null it's False.

All you have to do in your code is change the FieldNames and TableNames in bob's example.

Then anywhere that you are checking your xOne, xTwo, xThree, you would change that to vNullorData.



Leslie

 

[scriverb]

Leslie: Thanks for the help here. My email notification has been down for two days. Now I have a full inbox of emails. You analysis is correct. Thanks again.

Mossimo, You have said. . .

The examples I’ve found have been for access forms not asp pages?

Are you trying to make this call from an ASP page? If so I really am not the guy to talk to. I stumble around a little in that field but someone else would have to help you make that transition. This code could be put into a module in ACCESS database and then a call to the database with a command line parameter to run some particular code can be done. But, that doesn't necessarily return to you the analysis of the situation. Leslie, if you can help here feel free to jump in with both feet so that I may learn how to do this also.

Waiting for some help.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[lespaul]

Glad you got your email working again Bob!

As far as ASP pages go, I'm with you! If I was doing this in ASP (which I don't think support the DLookup), I would write a query that either returns something or returns nothing. If it's nothing then the boolean would be false if there is a record it's true.

I guess if you want to write a SINGLE query to see if any of that data is present, then we will need to see more of your table structure.

It's fairly obvious your tables aren't normalized since you have to check 3 places for the same value. Is there a common key between those three tables? Perhaps a single union query?

SqlOne="SELECT FieldOne FROM tblOne WHERE FieldOne='vToMatch' 
UNION SELECT FieldTwo FROM tblTwo WHERE FieldTwo='vToMatch' 
UNION SELECT FieldThree FROM tblThree WHERE FieldThree='vToMatch'"

Now are you really looking for 'vToMatch' in the field name? Is that the variable that holds what you are looking for? SHouldn't it be:

SqlOne="SELECT FieldOne FROM tblOne WHERE FieldOne='" & vToMatch & "' 
UNION SELECT FieldTwo FROM tblTwo WHERE FieldTwo='" & vToMatch & "'
UNION SELECT FieldThree FROM tblThree WHERE FieldThree='" & vToMatch & "'"

now you at least have a SINGLE recordset to check for values instead of three separate ones.




Leslie

 

[mossimo]

Please allow me to clarify my vagueness,
- Page is ASP VBScript
- vToMatch should have been intValOne,intValTwo,intValThree

instead of doing each call separately, you just have to do this one thing.

Very true lespaul a single SQL call is much better. I used your union query bellow to make a complete working solution. Witch is really the same as scriverb's original code written for pure asp.

sSQL = "SELECT FieldOne FROM tblOne WHERE FieldOne='" & intValOne & "' "
sSQL = sSQL & "UNION SELECT FieldTwo FROM tblTwo WHERE FieldTwo='" & intValTwo & "' "
sSQL = sSQL & "UNION SELECT FieldThree FROM tblThree WHERE FieldThree='" & intValThree & "'"

Set Conn=Server.Createobject("ADODB.Connection")
Conn.Open sConnAVMain
Set Rs=Conn.Execute(sSQL)

If Not Rs.EOF Then
  If (Rs(0)) <>"" Then 
	bNullOrData=True 
	Else 
	bNullOrData=False 
  End If
End If

Rs.Close
Set Rs=Nothing
Conn.Close
Set Conn=Nothing

Response.Write bNullOrData

Big Thanks scriverb and lespaul for all your help it’s very appreciated!

Cheers
mossimo

 

[scriverb]

Glad that we could help you. Good Luck with your project.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]