Using UNION operator in asp

[simflex]

I am trying to use the UNION operator to retrieve data from two tables and merge into one result.
When I this code in query analyzer of sql server database, it works great.
It works when I run only the UNION query (top query) and works when I run only the target list (bottom query) and it runs when I merge the two.
But for the life of me, I cannot get it to work when embedded in an asp code.
What is wrong here?
The key trackingNumber is of nvarchar datatype and so does have some values like:
24848-A, 4540-B etc.
Can someone please take a look and let me know, please.
Here is real code I am using.
Thanks in advance!

trackingNumber = request.querystring("trackingNumber")
strsql="SELECT tblAccidentEvent.trackingNumber," & _
"CountyDriver.fname," & _
"CountyDriver.lname," & _
"CountyDriver.middleInitial," & _
"CountyDriver.driverLicNumber," & _
"theEmp.Org," & _
"theEmp.division, " & _
"CountyDriver.driverGender," & _
"CountyDriver.address, " & _
"CountyDriver.City," & _
"CountyDriver.State, " & _
"CountyDriver.zipcode, " & _
"CountyDriver.phoneWork, " & _
"tblAccidentEvent.injuries, " & _
"tblAccidentEvent.fatalities, " & _
"tblAccidentEvent.vehicles, " & _
"tblvehicle.carmake, " & _
"tblvehicle.carmodel, " & _
"tblvehicle.vehicletype, " & _
"tbldamage.damageDesc, " & _
"tblvehicle.vin, " & _
"tblvehicle.insuranceCo, " & _
"tblvehicle.policyNumber, " & _
"tblvehicle.tagNumber, " & _
"CountyDriver.drugTest, " & _
"CountyDriver.DrugTestType, " & _
"CountyDriver.DrugResult, " & _
"tblDriverCondition.ConditionDesc, " & _
"tblLocation.locationDesc, " & _
"tblCollision.CollisionDesc, " & _
"tblFactors.FactorsDesc, " & _
"tblWeather.weatherDesc, " & _
"tblAccidentEvent.AccidentDate, " & _
"tblAccidentEvent.AccidentTime, " & _
"tblAccidentEvent.ReportBy, " & _
"tblAccidentEvent.ReportDate, " & _
"tblAccidentEvent.Department " & _
"FROM tblaccidentevent INNER JOIN theEmp " & _
"ON tblAccidentevent.empid = theEmp.empid " & _
"INNER JOIN CountyDriver " & _
"ON CountyDriver.accidenteventid = tblaccidentevent.accidenteventid " & _
"INNER JOIN tblDriverCondition " & _
"ON CountyDriver.DriverCondition=tblDriverCondition.conditionID " & _
"INNER JOIN tblvehicle " & _
"ON tblaccidentevent.accidenteventid = tblvehicle.accidenteventid " & _
"INNER JOIN tblcollision " & _
"ON tblaccidentevent.collisionid = tblcollision.collisionid " & _
"INNER JOIN tbldamage " & _
"ON tblvehicle.damageid = tbldamage.damageid " & _
"INNER JOIN tblfactors " & _
"ON CountyDriver.contributingFactors = tblfactors.factorsid " & _
"INNER JOIN tbllocation " & _
"ON tblaccidentevent.LocationOfImpact = tbllocation.locationid " & _
"INNER JOIN tblweather " & _
"ON tblaccidentevent.weathercondition = tblweather.weatherid " & _
"WHERE CountyFlag = 1 AND tblAccidentevent.trackingNumber ='"&trackingNumber&"'"
strsql=strsql & "UNION"
strsql="SELECT tblAccidentEvent.accidentEventID," & _
"OtherDriver.fname, " & _
"OtherDriver.lname, " & _
"OtherDriver.middleInitial, " & _
"OtherDriver.driverLicNumber, " & _
"theEmp.Org, " & _
"theEmp.division, " & _
"OtherDriver.driverGender, " & _
"OtherDriver.address, " & _
"OtherDriver.City, " & _
"OtherDriver.State, " & _
"OtherDriver.zipcode, " & _
"OtherDriver.phoneWork, " & _
"tblAccidentEvent.injuries, " & _
"tblAccidentEvent.fatalities, " & _
"tblAccidentEvent.vehicles, " & _
"OtherVehicle.carmake, " & _
"OtherVehicle.carmodel, " & _
"OtherVehicle.vehicletype, " & _
"tbldamage.damageDesc, " & _
"OtherVehicle.vin, " & _
"OtherVehicle.insuranceCo, " & _
"OtherVehicle.policyNumber, " & _
"OtherVehicle.tagNumber, " & _
"OtherDriver.drugTest, " & _
"OtherDriver.DrugTestType, " & _
"OtherDriver.DrugResult, " & _
"tblDriverCondition.ConditionDesc, " & _
"tblLocation.locationDesc, " & _
"tblCollision.CollisionDesc, " & _
"tblFactors.FactorsDesc, " & _
"tblWeather.weatherDesc, " & _
"tblAccidentEvent.AccidentDate, " & _
"tblAccidentEvent.AccidentTime, " & _
"tblAccidentEvent.ReportBy, " & _
"tblAccidentEvent.ReportDate, " & _
"tblAccidentEvent.Department " & _
"FROM tblaccidentevent INNER JOIN theEmp " & _
"ON tblAccidentevent.empid = theEmp.empid " & _
"INNER JOIN OtherDriver " & _
"ON OtherDriver.accidenteventid = tblaccidentevent.trackingNumber " & _
"INNER JOIN tblDriverCondition " & _
"ON OtherDriver.DriverCondition=tblDriverCondition.conditionID " & _
"INNER JOIN OtherVehicle " & _
"ON tblaccidentevent.accidenteventid = OtherVehicle.accidenteventid " & _
"INNER JOIN tblcollision " & _
"ON tblaccidentevent.collisionid = tblcollision.collisionid " & _
"INNER JOIN tbldamage " & _
"ON OtherVehicle.damageid = tbldamage.damageid " & _
"INNER JOIN tblfactors " & _
"ON OtherDriver.contributingFactors = tblfactors.factorsid " & _
"INNER JOIN tbllocation " & _
"ON tblaccidentevent.LocationOfImpact = tbllocation.locationid " & _
"INNER JOIN tblweather " & _
"ON tblaccidentevent.weathercondition = tblweather.weatherid " & _
"WHERE CountyFlag = 1 AND tblAccidentevent.trackingNumber ='"&trackingNumber&"'"

set rs = my_conn.Execute (StrSql)

[magus]

If the SQL statement running well in Query Analyzer, it should be running in ASP too. May be the bug are in these lines:

strsql=strsql & "UNION"
strsql="SELECT tblAccidentEvent.accidentEventID," & _

try this:
strsql=strsql & " UNION "
strsql= strsql & "SELECT tblAccidentEvent.accidentEventID," & _

[rdoekes]

If you are using SQL server, why not create a stored procedure for this massive piece of code. At least the stored procedure compiles the execution path of the query, making it faster to run, easier to maintain etc..

You create a parameter for trackingnumber and you execute the stored procedure in ASP.

just my two cents.

-Rogier Doekes

[simflex]

thanks for all the inputs.
I have resolved it.