Query not working after migrating from Access to SQL Server 7 
Author Message
 Query not working after migrating from Access to SQL Server 7

Hi there, I have a search form on an ASP page.  The query works fine when it
points to the Access database but it doesn't like it with a SQL Server 7
database.  I have found out that DistinctRow in Access becomes Distinct in
SQL Server; however, this does not allow me to "select distinct
personnel.*...."   Any advice to get around this would be greatly
appreciated.  I have included the code from the ASP page so you can get an
idea of what I'm trying to do.  It works great in Access 97 but I would
really likely to upgrade the database to SQL Server.  Thanks a lot!

The code:

sqlmain = "select distinctrow [Personnel].* from [Personnel] where "

sql1 = "exists (select * from [Personnel_Language] where " _
    & "[Personnel_Language].language_id = " & name1 & " " _
    & "and [Personnel_Language].[personnel_id] = [personnel].[ID])"

sql2 = "exists (select * from [Personnel_Language] where " _
    & "[Personnel_Language].language_id = " & name2 & " " _
    & "and [Personnel_Language].[personnel_id] = [personnel].[ID])"

sql3 = "exists (select * from [Personnel_db] where " _
    & "[Personnel_db].db_id = " & name3 & " " _
    & "and [Personnel_db].[personnel_id] = [personnel].[ID])"

sql4 = "exists (select * from [Personnel_db] where " _
    & "[Personnel_db].db_id = " & name4 & " " _
    & "and [Personnel_db].[personnel_id] = [personnel].[ID])"

sql5 = "exists (select * from [Personnel_os] where " _
    & "[Personnel_os].os_id = " & name5 & " " _
    & "and [Personnel_os].[personnel_id] = [personnel].[ID])"

sql6 = "exists (select * from [Personnel_os] where " _
    & "[Personnel_os].os_id = " & name6 & " " _
    & "and [Personnel_os].[personnel_id] = [personnel].[ID])"

sql7 = "exists (select * from [Personnel_Industry] where " _
    & "[Personnel_Industry].Industry_id = " & name7 & " " _
    & "and [Personnel_Industry].[personnel_id] = [personnel].[ID])"

sql8 = "exists (select * from [Personnel_education] where " _
    & "[Personnel_education].education_id = " & name8 & " " _
    & "and [Personnel_education].[personnel_id] = [personnel].[ID])"

sql9 = "exists (select * from [Personnel_Languages_Spoken] where " _
    & "[Personnel_Languages_Spoken].Languages_spoken_id = " & name9 & " " _
    & "and [Personnel_Languages_Spoken].[personnel_id] = [personnel].[ID])"

sql10 = "exists (select * from [Personnel_financials] where " _
    & "[Personnel_financials].financial_id = " & name10 & " " _
    & "and [Personnel_financials].[personnel_id] = [personnel].[ID])"

sql11 = "exists (select * from [Personnel_methodologies] where " _
    & "[Personnel_methodologies].method_id = " & name11 & " " _
    & "and [Personnel_methodologies].[personnel_id] = [personnel].[ID])"

sql12 = "exists (select * from [Personnel_otherdevapps] where " _
    & "[Personnel_otherdevapps].otherdevapps_id = " & name12 & " " _
    & "and [Personnel_otherdevapps].[personnel_id] = [personnel].[ID])"

strSQL = sqlmain

If request.form("cbo1") <> "blank" then
    If strSQL = sqlmain Then
        strSQL = strSQL & sql1
    Else
        strSQL = strSQL & "  and  " & sql1
    End If
End If

If request.form("cbo2") <> "blank" then
    If strSQL = sqlmain Then
        strSQL = strSQL & sql2
    Else
        strSQL = strSQL & "  and  " & sql2
    End If
End If

If request.form("cbo3") <> "blank" then
    If strSQL = sqlmain Then
        strSQL = strSQL & sql3
    Else
        strSQL = strSQL & "  and  " & sql3
    End If
End If

If request.form("cbo4") <> "blank" then
    If strSQL = sqlmain Then
        strSQL = strSQL & sql4
    Else
        strSQL = strSQL & "  and  " & sql4
    End If
End If

If request.form("cbo5") <> "blank" then
    If strSQL = sqlmain Then
        strSQL = strSQL & sql5
    Else
        strSQL = strSQL & "  and  " & sql5
    End If
End If

If request.form("cbo6") <> "blank" then
    If strSQL = sqlmain Then
        strSQL = strSQL & sql6
    Else
        strSQL = strSQL & "  and  " & sql6
    End If
End If

If request.form("cbo7") <> "blank" then
    If strSQL = sqlmain Then
        strSQL = strSQL & sql7
    Else
        strSQL = strSQL & "  and  " & sql7
    End If
End If

If request.form("cbo8") <> "blank" then
    If strSQL = sqlmain Then
        strSQL = strSQL & sql8
    Else
        strSQL = strSQL & "  and  " & sql8
    End If
End If

If request.form("cbo9") <> "blank" then
    If strSQL = sqlmain Then
        strSQL = strSQL & sql9
    Else
        strSQL = strSQL & "  and  " & sql9
    End If
End If

If request.form("cbo10") <> "blank" then
    If strSQL = sqlmain Then
        strSQL = strSQL & sql10
    Else
        strSQL = strSQL & "  and  " & sql10
    End If
End If

If request.form("cbo11") <> "blank" then
    If strSQL = sqlmain Then
        strSQL = strSQL & sql11
    Else
        strSQL = strSQL & "  and  " & sql11
    End If
End If

If request.form("cbo12") <> "blank" then
    If strSQL = sqlmain Then
        strSQL = strSQL & sql12
    Else
        strSQL = strSQL & "  and  " & sql12
    End If
 End If

rst.Close
rst.Open  strSQL, con , adOpenkeyset, adLockoptimistic, adCmdText



Mon, 05 Nov 2001 03:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Query not working after Migrating from Access to SQL Server 7

2. Migrate VB/Access to VB/Access/SQL Server?

3. VB6 application using SQL Server 2000 migrating db to SQL Server 2

4. Help with Query (works in Access, not in SQL 7)

5. Update and Insert queries work in SQL but not in Access

6. ODBC .addnew works from Access to SQL Server, but not from Excel

7. Crystal works fine with Access, SQL Server, but not Oracle

8. Migrating from Access to Oracle/SQL-Server/Sybase

9. Migrating from Access to SQL Server

10. Migrating reports from Access to SQL Server

11. Migrating from MS ACCESS to SQL SERVER

12. Help w/ migrating from access to sql server

 

 
Powered by phpBB® Forum Software