StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POASP Update database, insert if does not exist
text
Body
copied!<p>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 <em>exists</em> in the database, it will then update the record with relevant info from the CSV.</p> <p>However, I'm stuck in an endless loop (from what I can tell) and am not sure how to get out of it.</p> <pre class="lang-vb prettyprint-override"><code> 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 </code></pre> <p>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.</p> <h1>//Update</h1> <p>Thanks to <a href="https://stackoverflow.com/questions/7880908/asp-update-database-insert-if-does-not-exist/#7888353">Richard Benson</a>, I've been able to get my code to work properly, for the most part: I'm hung up on this bit of code:</p> <pre class="lang-vb prettyprint-override"><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 </code></pre> <p>The <code>replace()</code> function works on the first & last name, but when I get to the middle name, it won't work. If I keep it as <code>csvMiddle = replace(csv.fields(4), "'", "")</code> by itself it errors out sometimes because the middle name field is sometimes empty. <strong>How can I get this to work properly?</strong> This is most likely the final problem before this code will run smoothly.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload