need help with this please. This is driving me crazy.
I have an asp page that loads a CSV file into a table named emplist with the following collumns;
PayGroup varchar(15), LocationCode varchar(15), SupervisorName varchar(55), FileNumber varchar(15), LastName varchar(55), FirstName varchar(55)
This part works fine thus far. Where I run into trouble is; I then need to scan each row for the supervisor name and then insert each UNIQUE supervisorname into another table named jraspin which also has a collumn named supervisor with varchar(55)
so if supervisor names on the emplist table were =
john doe
john doe
john doe
jane smith
jane smith
jim Thomas
It would need to insert into jraspin the names, john doe, jane smith and jim thomas and not the duplicates and if only the name isn't already in the jraspin table
Code:
<%
DIM mySQL, RS
mySQL = "SELECT * FROM emplist " & _
" WHERE supervisorname <> '' " & _
" AND supervisorname <> 'supervisorname' "
Set RS = Server.CreateObject("ADODB.Recordset")
Rs.Open mySQL, DataConn, 0,1
' Count how many records exist
DIM isupcount, countsup,sql_select,strsupervisor, supervisor
strsupervisor = ""
isupcount = 0
DO WHILE NOT RS.EOF
If RS("supervisorname") <> countsup then
isupcount = isupcount + 1
countsup = RS("supervisorname")
End If
RS.MoveNext
loop
' Display result
Response.Write "(" & isupcount & ")" 'this counts how many unique supervisor names
RS.close
Set RS = nothing
Set RS = Server.CreateObject("ADODB.Recordset")
Rs.Open mySQL, DataConn, 0,1
'insert into jraspin new supervisor names
DO WHILE NOT RS.EOF
For supervisor = 0 To isupcount ' this sets the count for supervisor using the number counted
If Rs("paygroup") <> strsupervisor Then ' uses to see if supervisor is a duplicate
supervisor = Rs("paygroup") ' this is the counting variable which should have multiple records
strsupervisor = Rs("paygroup")
End If
'response.write(supervisor)
Rs.MoveNext
next
loop
'response.write(supervisor)
RS.Close
Set RS = Nothing
%>
'You will notice I have not included the insert function yet. I can't get past this part so need for 'that until I can figure out how to record each unique supervisor record.
Okay so there it is. I keep getting a mismatch error on the first name in the list; The count displays the right number of unique supervisors right before the error prints so that part is working obviously. Line 43 is my "next" command
(53)
Microsoft VBScript runtime error '800a000d'
Type mismatch: '[string: "C2A"]'
/hours/supcount2.asp, line 43
PLEEEAASEEE HELLPPPP!!!!
P.S. the dataconn variable is attached with an included file in the header which is defined as my db connection...
If first you don't succeed - Post in a forum.