12. oktober 2005 - 01:55Der er
5 kommentarer og 1 løsning
SQL fejl i inner join
er der nogen som kan se fejlen i nedenstående sql:
SELECT * FROM dbo.tblCars, dbo.tblMake INNER JOIN dbo.tblCarUser ON dbo.tblCars.caruserID = dbo.tblCarUser.IDcaruser INNER JOIN dbo.tblCostcenter ON dbo.tblCarUser.costcenterID = dbo.tblCostcenter.IDcostcenter AND Geography = 'Jorcks Passage' WHERE makeID = IDmake ORDER BY RegistrationNumber ASC Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'dbo.tblCars' does not match with a table name or alias name used in the query.
Mener da ikke man skal angive den "dbo." del? Altså:
SELECT * FROM tblCars, tblMake INNER JOIN tblCarUser ON tblCars.caruserID = tblCarUser.IDcaruser INNER JOIN tblCostcenter ON tblCarUser.costcenterID = tblCostcenter.IDcostcenter AND Geography = 'Jorcks Passage' WHERE makeID = IDmake ORDER BY RegistrationNumber ASC
samme fejl: SELECT * FROM tblCars, tblMake INNER JOIN tblCarUser ON tblCars.caruserID = tblCarUser.IDcaruser INNER JOIN tblCostcenter ON tblCarUser.costcenterID = tblCostcenter.IDcostcenter AND Geography = 'Jorcks Passage' WHERE makeID = IDmake ORDER BY RegistrationNumber ASC Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'tblCars' does not match with a table name or alias name used in the query.
SELECT * FROM ((dbo.tblCars INNER JOIN dbo.tblCarUser ON dbo.tblCars.caruserID = dbo.tblCarUser.IDcaruser) INNER JOIN dbo.tblMake ON dbo.?.? = dbo.tblMake.?) INNER JOIN dbo.tblCostcenter ON dbo.tblCarUser.costcenterID = dbo.tblCostcenter.IDcostcenter AND Geography = 'Jorcks Passage' WHERE makeID = IDmake ORDER BY RegistrationNumber ASC
fedt det virker: tsk_sql = tsk_sql & "SELECT * FROM ((tblCars INNER JOIN tblCarUser ON tblCars.caruserID = tblCarUser.IDcaruser) INNER JOIN tblMake ON tblCars.makeID = tblMake.IDmake) INNER JOIN tblCostcenter ON tblCarUser.costcenterID = tblCostcenter.IDcostcenter AND Geography = 'Jorcks Passage' WHERE makeID = IDmake ORDER BY RegistrationNumber ASC"
MEN jeg kan ikke overskue at implementere den i min hoved sql kan du ?
SQL: <% Dim rsResults Dim rsResults_numRows
Set rsResults = Server.CreateObject("ADODB.Recordset") rsResults.ActiveConnection = MM_claims_conn_STRING tsk_sql = "SELECT * FROM tblCars, tblMake "
If Request.QueryString("geography")<>"" Then tsk_sql = tsk_sql & "INNER JOIN tblCarUser ON tblCars.caruserID = tblCarUser.IDcaruser INNER JOIN tblCostcenter ON tblCarUser.costcenterID = tblCostcenter.IDcostcenter AND Geography = '" & Request.QueryString("geography") & "' " End if
tsk_sql = tsk_sql & "WHERE makeID = IDmake "
If Request.QueryString("user")<>"" Then tsk_sql = tsk_sql & "AND caruserID = " & Request.QueryString("user") & " " End if If Request.QueryString("make")<>"" Then tsk_sql = tsk_sql & "AND makeID = " & Request.QueryString("make") & " " End if If Request.QueryString("nationality")<>"" Then tsk_sql = tsk_sql & "AND Nationality = " & Request.QueryString("nationality") & " " End if If Request.QueryString("typeofuse")<>"" Then tsk_sql = tsk_sql & "AND TypeOfUse = '" & Request.QueryString("typeofuse") & "' " End if
If Request.QueryString("weight")="0-1000" Then tsk_sql = tsk_sql & "AND (Weight BETWEEN '0' AND '1000') " End if If Request.QueryString("weight")="1001-2000" Then tsk_sql = tsk_sql & "AND (Weight BETWEEN '1001' AND '2000') " End if If Request.QueryString("weight")="2001-3000" Then tsk_sql = tsk_sql & "AND (Weight BETWEEN '2001' AND '3000') " End if If Request.QueryString("weight")="3001-5000" Then tsk_sql = tsk_sql & "AND (Weight BETWEEN '3001' AND '5000') " End if If Request.QueryString("weight")="5001-10000" Then tsk_sql = tsk_sql & "AND (Weight BETWEEN '5001' AND '10000') " End if tsk_sql = tsk_sql & "ORDER BY RegistrationNumber ASC "
den skal på en eller anden måde implementeres i If Request.QueryString("geography")<>"" Then tsk_sql = tsk_sql & "INNER JOIN tblCarUser ON tblCars.caruserID = tblCarUser.IDcaruser INNER JOIN tblCostcenter ON tblCarUser.costcenterID = tblCostcenter.IDcostcenter AND Geography = '" & Request.QueryString("geography") & "' " End if
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.