ASP Update database, insert if does not exist

Question

I have a CSV I need to loop through, get the ID of each row, then loop through the database, comparing the csvID to each dbID. If the ID exists in the database, it will then update the record with relevant info from the CSV.

However, I'm stuck in an endless loop (from what I can tell) and am not sure how to get out of it.

    Option Explicit
    Server.ScriptTimeout = 2147483647

    dim conn, rs, updatedUser, updatedDate, filePath
    dim deactivateSQL, csvConn, connCSV, csv, sql
    dim dbID, dbSSN, dbLast, dbFirst, dbMiddle, dbGender, dbScl, dbCls
    dim csvID, csvSSN, csvLast, csvFirst, csvMiddle, csvGender
    dim csvScl, csvCls, csvGrd, csvHrm

    updatedUser = Request.Cookies("UserN")
    updatedDate = date() & " " & time()
    filePath    = "\path\to\file"


' Connect to Students.CSV
    csvConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &_
               Server.MapPath(filePath) &_
              ";Extended Properties='text;HDR=no;FMT=Delimited';"

    Set connCSV = Server.CreateObject("ADODB.Connection")
    connCSV.Open csvConn
    Set csv = Server.CreateObject("ADODB.recordset")
    csv.open "SELECT * FROM Students.csv", connCSV

        temp = csv.RecordCount
        redim toAdd(temp)


    ' Begin looping through Students.csv 
        do until csv.eof

        ' Get Students.csv Column Values 
        ' please disregard the "replace" stuff for now 
            csvID     = replace(replace(csv.fields(0), " ", ""), "'", "")
            csvSSN    = replace(replace(csv.fields(1), " ", ""), "'", "")
            csvLast   = replace(replace(csv.fields(2), " ", ""), "'", "")
            csvFirst  = replace(replace(csv.fields(3), " ", ""), "'", "")
            csvMiddle = replace(replace(csv.fields(4), " ", ""), "'", "")
            csvGender = replace(replace(csv.fields(5), " ", ""), "'", "")
            csvScl    = replace(replace(csv.fields(6), " ", ""), "'", "")
            csvGrd    = replace(replace(csv.fields(7), " ", ""), "'", "")
            csvHrm    = replace(replace(csv.fields(8), " ", ""), "'", "")


        ' Connect to database 
            set conn=Server.CreateObject("ADODB.Connection")
            conn.Provider="Microsoft.Jet.OLEDB.4.0"
            conn.Open "E:/path/to/file/database.mdb"
            set rs=Server.CreateObject("ADODB.Recordset")
            rs.open "SELECT * FROM tblStudent", conn

        ' Begin looping through tblStudents 
            do until rs.eof

            ' Get tblStudents.StudentID 
                dbID     = rs.fields("StudentID")
                dbSSN    = rs.fields("SSN")
                dbLast   = rs.fields("LastName")
                dbFirst  = rs.fields("FirstName")
                dbMiddle = rs.fields("MiddleName")
                dbGender = rs.fields("Gender")
                dbScl    = rs.fields("School")
                dbCls    = rs.fields("Class")

                if dbID = csvID then

                    ' if dbID matches csvID, 
                    ' update tblStudents with the new CSV data 
                    sql = "UPDATE tblStudent SET " &_
                      "Active='Yes' AND " &_
                      "SSN='" & csvSSN & "' AND " &_
                      "LastName='" & csvlast & "' AND " &_
                      "FirstName='" & csvFirst & "' AND " &_
                      "MiddleName='" & csvMiddle & "' AND " &_
                      "Gender='" & csvGender & "' AND " &_
                      "School='" & csvScl & "' AND " &_
                      "GradeLvl='" & csvGrd & "' AND " &_
                      "HomeRoomID='" & csvHrm & "' AND " &_
                      "PrevClass1='" & dbCls & "' AND" &_
                      "lastUpdatedUser='" & updatedUser & "' AND" &_
                      "lastUpdatedDate='" & updatedDate & "'" &_
                     "WHERE StudentID=" & dbID & ";"

                on error resume next
                    conn.execute(sql)

                else
                    ' I am not sure what to do here...
                    ' I thought about creating a dynamic array:
                    ' adding to the array for each ID not found
                    ' however, I am not THAT skilled.
                    ' If someone could help me with that, 
                    ' I would be grateful
                end if

            rs.movenext
            loop

        csv.movenext
        loop



    ' This is the INSERT SQL I need to execute, 
    ' but do not exactly know where it needs to be placed either 
        sql = "INSERT INTO tblStudent (" &_
          "Active, StudentID, SSN, LastName, FirstName, MiddleName, Gender, "&_
          "School, GradeLvl, HomeRoomID, lastUpdatedUser, LastUpdatedDate" &_
        ") VALUES (" &_
          "'Yes', '" & csvID & "', '" & csvSSN & "', '" & csvLast & "', '" &_
              csvFirst & "', '" & csvMiddle & "', '" & csvGender & "', '" &_
              csvScl & "', '" & csvGrd & "', '" & csvHrm & "', '" &_
      updatedUser & "', '" & updatedDate & _
        "');"

        on error resume next
        conn.execute(sql)


    if error<>0 then
        response.cookies("updated") = "no"
        response.cookies("updated").Expires = dateadd("s", 2, now())
        response.redirect("step-5.asp")
    else
        response.cookies("updated") = "yes"
        response.cookies("updated").Expires = dateadd("s", 2, now())
        response.redirect("step-6.asp")
    end if

This may not even be the best way to go about doing this and I'm open to suggestions here, too. But, first I need to have this work: loop through the CSV, update the DB if the csvID exists in the DB and insert the csvID row info if it doesn't exist.

//Update

Thanks to Richard Benson, I've been able to get my code to work properly, for the most part: I'm hung up on this bit of code:

csvLast = replace(csv.fields(2), "'", "")
csvFirst = replace(csv.fields(3), "'", "")
if csv.fields(4) <> NULL then
   csvMiddle = replace(csv.fields(4), "'", "")
else
   csvMiddle = csv.fields(4)
end if

The replace() function works on the first & last name, but when I get to the middle name, it won't work. If I keep it as csvMiddle = replace(csv.fields(4), "'", "") by itself it errors out sometimes because the middle name field is sometimes empty. How can I get this to work properly? This is most likely the final problem before this code will run smoothly.

`if csv.fields(4) <> NULL then` has been replaced with `if IsNull(csv.fields(4)) then`. See my wiki-answer below. — joshmax, Oct 27 '11 at 15:55

Richard Benson · Accepted Answer · 2011-10-26T09:44:50.373

2

Will try and put this into more context later, for now a pointer on what I do when trying to achieve the insert if not in db, update if is.

Set rs = Server.CreateObject("ADODB.Recordset")
rs.ActiveConnection = "whateveryourconnectionstringis"
rs.Source = "SELECT * FROM Table WHERE ID = '" & intValue & ";"
rs.CursorType = 2
rs.CursorLocation = 3
rs.LockType = 3
rs.Open()

'If at this point we have no records, it doesnt exist so add it and any data all new records need'
If rs.BOF AND rs.EOF Then
    rs.AddNew
    rs("ID") = intValue
End If

    'Update the rest of the fields
rs("Field1") = Value1
rs("Field2") = Value2
rs("Field3") = Value3
rs.Update()

rs.Close()
Set rs = Nothing

Depending on how you are looping and how many loops you will go through, this may be too intensive, but it's the simplest from a code point-of-view

edited Oct 26 '11 at 09:44

answered Oct 25 '11 at 11:09

Richard Benson

1,477
12
20

Could you explain to me how that block of code at the top (before the rs.AddNew) is able to tell that the intValue is not in the table? – joshmax Oct 25 '11 at 20:26
@joshmax there was an indent issue which I have just fixed. The first block is a long winded way of opening a recordset. If you check for BOTH `.BOF` and `.EOF` then you have no records returned, so the ID you are looking for doesn't exist. – Richard Benson Oct 26 '11 at 09:48
Thanks for your help! My full result is below. – joshmax Oct 27 '11 at 15:49
@joshmax If you haven't moved the current position in the `ADODB.Recordset` using `IF Not rs.EOF Then` is sufficient. – user692942 May 29 '14 at 09:45

score 0 · Answer 2 · answered Oct 24 '11 at 19:47

0

The first thing I notice is that you do loop before rs.movenext. This means that rs.eof will never occur because you never move forward using rs.movenext and indeed would result in an infinite loop.

I assume this is just a typo, because you do it correctly for the outer CSV loop.

answered Oct 24 '11 at 19:47

Logan Serman

29,447
27
102
141

In the post. I still need some way to loop through the DB to be sure the csvID is **not** in the DB before I attempt to `INSERT` the info. – joshmax Oct 24 '11 at 19:56
So the part where you are updating the database if the csvID is in the database works? – Logan Serman Oct 24 '11 at 20:02
No, I tried again and it won't successfully complete that either. – joshmax Oct 24 '11 at 20:18
I can't see any other problems with that part. Are you sure you are getting the correct values for csvID and dbID? Does your program ever enter the `if dbID = csvID then` block? – Logan Serman Oct 24 '11 at 20:31

score 0 · Answer 3 · answered Oct 27 '11 at 15:44

Here's what I ended up using (in case someone else runs into an issue like this):
Thanks everyone for your help.

Server.ScriptTimeout = 2147483647
Response.Buffer = False
on error resume next


dim conn1, conn, rs, updatedUser, updatedDate, filePath, 
dim deactivateSQL, csvConn, connCSV, csv, sql
dim csvID, csvSSN, csvLast, csvFirst, csvMiddle
dim csvGender, csvScl, csvGrd, csvCls, dbCls

    updatedUser = Request.Cookies("UserN")
    updatedDate = date() & " " & time()
    filePath    = "\path\to\file"


' --- Connect to DZ database
    set conn1=Server.CreateObject("ADODB.Connection")
    conn1.Provider="Microsoft.Jet.OLEDB.4.0"
    conn1.Open "E:/path/to/database.mdb"    

' --- Deactivate ALL students
    deactivateSQL = "UPDATE tblStudent SET Active=False " &_
                    "AND lastUpdatedUser='" & updatedUser & "' "&_
                    "AND lastUpdatedDate='" & updatedDate & "';"
    conn1.execute(deactivateSQL)
    conn1.close


    ' --- Connect to Students.CSV exported by iNOW
        csvConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &_
                       Server.MapPath(filePath) &_
                   ";Extended Properties='text;HDR=no;FMT=Delimited';"

        Set connCSV = Server.CreateObject("ADODB.Connection")
            connCSV.Open csvConn
        Set csv = Server.CreateObject("ADODB.recordset")
            csv.open "SELECT * FROM Students.csv", connCSV


    ' --- Begin looping through Students.csv
        do until csv.eof


        ' --- Get Students.csv Column Values
            csvID     = csv.fields(0)

            if isnull(csv.fields(1)) then
                csvSSN = NULL
            else
                csvSSN = csv.fields(1)
            end if

            csvLast   = replace(csv.fields(2), "'", "")
            csvFirst  = replace(csv.fields(3), "'", "")

            ' --- Using IsNull() fixed the 2nd problem 
            ' --- I was having after updating the question
            if isnull(csv.fields(4)) then
                csvMiddle = csv.fields(4)
            else
                csvMiddle = replace(csv.fields(4), "'", "")
            end if

            csvGender = csv.fields(5)
            csvScl    = csv.fields(6)
            csvGrd    = csv.fields(7)
            csvCls    = csv.fields(8)


        ' --- Connect to database
            set conn=Server.CreateObject("ADODB.Connection")
            conn.Provider="Microsoft.Jet.OLEDB.4.0"
            conn.Open "E:/path/to/database.mdb"
            set rs=Server.CreateObject("ADODB.Recordset")
            rs.open "SELECT * FROM tblStudent " &_
                    "WHERE StudentID='" & csvID & "';", conn

                if rs.bof and rs.eof then

                    ' --- if rs.bof & rs.eof, the csvID is NOT in the table
                    ' --- Add the new csvID to DB, we'll add the rest in 
                    ' --- the UPDATE statement below
                    conn.execute("INSERT INTO tblStudent (StudentID) " &_
                                 "VALUES ('" & csvID & "');")

                    ' --- Set to 0 since it's supposed to be a number
                    dbCls = 0
                else
                    ' --- Get tblStudents.Class from existing record
                    dbCls = rs.fields("Class")
                end if

            ' --- UPDATE the table with the appropriate info
                sql = "UPDATE tblStudent SET " &_
                        "Active=True, " &_
                        "SSN=" & csvSSN & ", " &_
                        "LastName='" & csvlast & "', " &_
                        "FirstName='" & csvFirst & "', " &_
                        "MiddleName='" & csvMiddle & "', " &_
                        "Gender='" & csvGender & "', " &_
                        "School=" & csvScl & ", " &_
                        "GradeLvl=" & csvGrd & ", " &_
                        "Class=" & csvCls & ", " &_
                        "PrevClass1='" & dbCls & "', " &_
                        "lastUpdatedUser='" & updatedUser & "', " &_
                        "lastUpdatedDate='" & updatedDate & "' " &_
                      "WHERE StudentID='" & csvID & "';"

                conn.execute(sql)

            csv.movenext
            loop


    if error<>0 then
        response.cookies("updated") = "no"
        response.cookies("updated").Expires = dateadd("s", 2, now())
        response.redirect("step-5.asp")
    else
        response.cookies("updated") = "yes"
        response.cookies("updated").Expires = dateadd("s", 2, now())
        response.redirect("step-6.asp")
    end if

ASP Update database, insert if does not exist

//Update

3 Answers3