Re: What's wrong with my SQL query ?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

"NS" <n@s> wrote in message news:48b41cf1$1$851$426a74cc@xxxxxxxxxxxxxxx
Hello,

I have a SQL query that perfectly works using *SQL Entreprise Manager*.
But when i try to use the very same in a VBS script i got an error
80040E14
telling me something like :
Tables or functions 'dbo.T_Missions' and 'dbo.T_Missions' have the same
name. (error message here in french :
http://img515.imageshack.us/my.php?image=vbssqlqueryerrorpu1.jpg)


You've include a joing to T_Missions twice. You need therefore to alias the
table name so that each join has a different name. See change in line.

Please help me to solve my pb.
Code is here :
-------------------------------------------------------------------------
Dim Mission
Mission = InputBox("Enter your mission number : " , ".: Mission Browser
:." )
If IsEmpty(Mission) Then
MsgBox "You pressed Cancel!", 48, "Canceling"
Wscript.Quit
End If

Set adoRecordset = CreateObject("ADODB.Recordset")
adoRecordset.ActiveConnection = "DRIVER=SQL Server;" _
& "Trusted_Connection=Yes;" _
& "DATABASE=Xpertease;SERVER=EXPERTEASE"

adoRecordset.Source = "SELECT DISTINCT " _
& "T_Missions.Libellé AS Libellé_Mission, " _

Change to M1.Libellé AS Libellé_Mission,

& "T_Missions.Numéro AS Numéro_Mission, " _

Change to M2.Numéro AS Numéro_Mission,

& "T_Ingénieurs.Initiales AS Initiales_Expert, " _
& "T_TypesMissions.Libellé AS AouJ " _
& "FROM dbo.T_Ingénieurs INNER JOIN " _
& "dbo.T_Missions ON " _

change to dbo.T_Missions M1 ON

& "dbo.T_Ingénieurs.ID = dbo.T_Missions.ID_Responsable INNER JOIN " _
& "dbo.T_Missions ON " _

change to dbo.T_Missions M2 ON

& "dbo.T_Missions.ID_NatureOrigine = dbo.T_TypesMissions.ID " _
& "WHERE (dbo.T_Missions.Numéro = " & Mission & ")"
adoRecordset.Open
Do Until adoRecordset.EOF
str1st = adoRecordset.Fields("Libellé").Value
str2nd = adoRecordset.Fields("No_Mission").Value
str3rd = adoRecordset.Fields("Titulaire").Value
str4th = adoRecordset.Fields("AouJ").Value
Wscript.Echo str1st & " " & str2nd & " " & str3rd & " " & str4th
adoRecordset.MoveNext
Loop
adoRecordset.Close

I don't read french so I've only guessed at which of the field comes from
which instance of T_Missions but you get the general idea.


--
Anthony Jones - MVP ASP/ASP.NET


.

Relevant Pages

  • Re: Access Query - DateTime
    ... > I have a DateTime field in my Access table named AuditTime. ... > I'm having trouble creating a SQL query that will return all records ... > "No value given for one or more parameters" is the error message. ...
    (microsoft.public.access.gettingstarted)
  • Re: Lost data source
    ... If the "error message" that you mention concerns running an SQL query, ... I've had to choose the data source over again for more ... is there any way to find out which table these letters were previously ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Headers already sent problem online, but not offline
    ... That's a side effect of your primary problem, namely that your SQL query is ... That results in an error message when you try to use the result ... means that you can't send out any futher headers. ... All three preceding statements need error checking; ...
    (comp.lang.php)
  • Re: INSERT statement falied: Column count doesnt match value count at row 1
    ... > I am getting this wierd error message when running the following ... > When I check the sql query in the mysql monitor it works fine. ... > value appears in a select * statement on the database. ...
    (php.general)