SQL fejl med Ambiguous

mener at det betyder din kolonne CivilRegistrationNumber er defineret 2 steder

prøv og erstat * med en liste over de felter du vil have ud

CivilRegistrationNumber findes i begge tabeller. Jeg har forsøgt at sætte de værdier ind jeg skal bruge, men det virker heller ikke. Burde den ikke være ok når jeg skriver:
dbo.tblInjury.CivilRegistrationNumber = dbo.tblEmployee.CivilRegistrationNumber


tsk_sql = "SELECT dbo.tblInjury.DateOfInjury,dbo.tblInjury.injurynatureID,dbo.tblInjury.injurypartID,dbo.tblEmployee.jobpositionID,dbo.tblEmployee.nationalityID,dbo.tblEmployee.jobtypeID,dbo.tblEmployee.vesselterminalID,dbo.tblEmployee.CivilRegistrationNumber  FROM dbo.tblInjury, dbo.tblEmployee  WHERE dbo.tblInjury.CivilRegistrationNumber = dbo.tblEmployee.CivilRegistrationNumber AND  (DateOfInjury between '" & ConvertToSqlDate(Request.Querystring("startdate")) & "' AND '" & ConvertToSqlDate(Request.Querystring("enddate")) & "') AND (SickDays between '" & (Request.Querystring("min")) & "' AND '" & (Request.Querystring("max")) & "') "

jo din WHERE ser OK ud og det var derfor jeg tænkte på SELECT listen selvom
jeg faktisk ikke mener at det plejer at give problemer

det var underligt. betyder det så at jeg skal omstrukturere databasen eller er der nogen anden løsning

mit recordsæt:

tsk_sql = "SELECT *  WHERE dbo.tblInjury.CivilRegistrationNumber = dbo.tblEmployee.CivilRegistrationNumber AND  (DateOfInjury between '" & ConvertToSqlDate(Request.Querystring("startdate")) & "' AND '" & ConvertToSqlDate(Request.Querystring("enddate")) & "') AND (SickDays between '" & (Request.Querystring("min")) & "' AND '" & (Request.Querystring("max")) & "') "

If Request.QueryString("vesselterminal")<>"" Then
tsk_sql = tsk_sql & "AND vesselterminalID = " & Request.QueryString("vesselterminal") & " "
End if
If Request.QueryString("jobposition")<>"" Then
tsk_sql = tsk_sql & "AND jobpositionID = " & Request.QueryString("jobposition") & " "
End if
If Request.QueryString("injuryplace")<>"" Then
tsk_sql = tsk_sql & "AND injuryplaceID = " & Request.QueryString("injuryplace") & " "
End if
If Request.QueryString("injurynature")<>"" Then
tsk_sql = tsk_sql & "AND injurynatureID = " & Request.QueryString("injurynature") & " "
End if
If Request.QueryString("injurypart")<>"" Then
tsk_sql = tsk_sql & "AND injurypartID = " & Request.QueryString("injurypart") & " "
End if
tsk_sql = tsk_sql & "ORDER BY " & Request.QueryString("orderby") & " ASC"

'response.write tsk_sql
rsResults.Source = tsk_sql

så skriver den pludselig:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'tblInjury' does not match with a table name or alias name used in the query.

/reports/include/injury_occupationalinjury.asp, line 50

jeg fik bikset mig frem til en løsning:

<%
Dim rsResults
Dim rsResults_numRows

Set rsResults = Server.CreateObject("ADODB.Recordset")
rsResults.ActiveConnection = MM_claims_conn_STRING
tsk_sql = "SELECT *  FROM dbo.tblInjury LEFT JOIN dbo.tblEmployee ON dbo.tblInjury.CivilRegistrationNumber = dbo.tblEmployee.CivilRegistrationNumberE "

If Request.QueryString("vesselterminal")<>"" Then
tsk_sql = tsk_sql & "AND vesselterminalID = " & Request.QueryString("vesselterminal") & " "
End if
If Request.QueryString("jobposition")<>"" Then
tsk_sql = tsk_sql & "AND jobpositionID = " & Request.QueryString("jobposition") & " "
End if

tsk_sql = tsk_sql & "WHERE (DateOfInjury between '" & ConvertToSqlDate(Request.Querystring("startdate")) & "' AND '" & ConvertToSqlDate(Request.Querystring("enddate")) & "') "


If Request.QueryString("min")<>"0" Then
tsk_sql = tsk_sql & "AND (SickDays between '" & (Request.Querystring("min")) & "' AND '" & (Request.Querystring("max")) & "') "
Else
If Request.QueryString("max")<>"999" Then
tsk_sql = tsk_sql & "AND (SickDays between '" & (Request.Querystring("min")) & "' AND '" & (Request.Querystring("max")) & "') "
End if
End if





If Request.QueryString("injuryplace")<>"" Then
tsk_sql = tsk_sql & "AND injuryplaceID = " & Request.QueryString("injuryplace") & " "
End if
If Request.QueryString("injurynature")<>"" Then
tsk_sql = tsk_sql & "AND injurynatureID = " & Request.QueryString("injurynature") & " "
End if
If Request.QueryString("injurypart")<>"" Then
tsk_sql = tsk_sql & "AND injurypartID = " & Request.QueryString("injurypart") & " "
End if
tsk_sql = tsk_sql & "ORDER BY " & Request.QueryString("orderby") & " ASC"

'response.write tsk_sql
rsResults.Source = tsk_sql

rsResults.CursorType = 0
rsResults.CursorLocation = 2
rsResults.LockType = 1
rsResults.Open()

rsResults_numRows = 0
%>