ASP更新数据库，如果不存在则插入

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.

我有一个需要循环的CSV,获取每一行的ID,然后遍历数据库,将csvID与每个dbID进行比较。如果数据库中存在该ID,则它将使用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.

这甚至可能不是最好的方法,我也愿意接受这方面的建议。但是,首先我需要完成这项工作:循环访问CSV,如果数据库中存在csvID则更新数据库,如果不存在则插入csvID行信息。

//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:

感谢Richard Benson,我能够让我的代码正常工作,大部分时间:我已经挂了这段代码:

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.

replace()函数适用于名字和姓氏,但当我到达中间名时,它将无效。如果我把它保存为csvMiddle = replace(csv.fields(4),“'”,“”)本身就会出错,因为中间名字段有时是空的。我怎样才能让它正常工作?这很可能是此代码顺利运行之前的最终问题。

3 个解决方案

#1

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.

稍后会尝试将其放入更多上下文中,现在指向我尝试实现插入时的操作,如果不是在db中,则更新if。

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

根据你的循环方式和循环次数,这可能过于密集,但从代码的角度看它是最简单的

#2

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.

我注意到的第一件事是你在rs.movenext之前循环。这意味着rs.eof永远不会发生,因为你永远不会使用rs.movenext前进,实际上会导致无限循环。

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

我认为这只是一个错字,因为你正确地做了外部CSV循环。

#3

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