?
Solved

SQL LOOP

Posted on 2007-10-14
8
Medium Priority
?
1,041 Views
Last Modified: 2012-05-05
I have this code in which I got help in by other experts in the system. This  code gose to a txt file which I download everyday and for now only prints what is in the text file. What I want to accomplish is a delete all record of the table and then begin by uploading the new records one by one. Is there a way to do this. How do I structure the sql with the loop every record is split by "miamimls". Can someone please help.


<%
      Const ForReading = 1
      Set objFSO = CreateObject("Scripting.FileSystemObject")
      Set objTextFile = objFSO.OpenTextFile("c:\sefl_data.txt", ForReading)
     
      Do Until objTextFile.AtEndOfStream
            strNextLine = objTextFile.Readline
            arrServiceList = Split(strNextLine , ",")
           ' Response.Write "Server name:" & arrServiceList(0)

            For i = 1 to Ubound(arrServiceList)
                  Response.write " " & arrServiceList(i)
            response.flush
            Next
      Loop
%>
"miamimls","FL","M1172181","1025193","Clarte Int'l Properties LLC","3055322226","1274527","Penuela,Pablo","","","","","","M","CONDO","FULL SERVICE 5 STAR HOTEL. UNIT WITH OCEAN VIEW. FULLY FURNISHED BY DESIGNER, TOP OF THE LINE APPLIANCES, LUXURIOUS LOBBY, PRIVATE BEACH WITH CABANAS","A","379900","","FL","18683","COLLINS AV","","","2309","-80.12060909860296","25.94903129272457","SUNNYISL","000016","33160","","22","DADE","12025","M Resort Le Meridien","M Resort Le Meridien","2005","","","0","","750","1","1","1","0","","","","","","","","","","","","823","","M RESORT RESIDENCES CONDOUNIT 2309 INDIV 0.338131% INT IN COMMON ELEMENTS OFF","31-22-02-045-2309","4150","2006","2","","22","","","","09/12/2007","","","","","","","","","","","","","","","D09,E05,J02,J04,M01,M05,M17,N10,N11,N13,N19,Q08,T01,T28,U01,U16,W28,X07,X09,X12,Y07","CIPL01","3127720","","0","",""
---------- NEW RECORDS---------------------------------------------------------------------
"miamimls","FL","M1172187","1024855","Astor International Realty","3054876265","2120357","Vargas,Limaris","","","","","","M","CONDO","O","A","324900","","FL","6001","70 ST","","NW","439","-80.29101221387516","25.706129817534272","SMIAMI","Y22922","33143","","40","DADE","12025","","VALENCIA SOUTH MIAMI","2004","","","1010","","1010","2","2","2","0","","","","","","","","","","","","428","","TOWNSITE","","0","2006","25","","40","","","","09/12/2007","","","","","","","","","","","","","","","C02,C05,C13,C23,D09,E05,J02,J07,M01,M05,M17,N02,N10,N11,N19,O14,O17,Q01,T02,T11,T12,T28,U01,W28","AITR01","3160259","","0","",""
0
Comment
Question by:eteran
  • 4
  • 4
8 Comments
 
LVL 3

Accepted Solution

by:
yourbudweiser earned 2000 total points
ID: 20078043
First delete the records from the table.

Next inside the For Next loop, run an Insert statement inserting each of the records from the text file:

set objConn = Createobject("ADODB.Connection")
objConn.Provider = "Microsoft.Jet.OLEDB.4.0"
objConn.Properties("Data Source") = "//path_to_your_database/database.mdb"
objConn.open

objConn.Execute "Delete From Table Where...."

For i = 1 to Ubound(arrServiceList)
      Response.write " " & arrServiceList(i)
      strSQL = "INSERT INTO TABLE FIELDNAME VALUES ('" &arrServiceList(i)& "')"
      objConn.Execute strSQL
      response.flush
Next

objConn.Close
set objConn = nothing
0
 

Author Comment

by:eteran
ID: 20078637
Just this would delete all the records in that table.

objConn.Execute "Delete From Table Where...."
0
 
LVL 3

Expert Comment

by:yourbudweiser
ID: 20085673
didn't you want to delete all records in that table?

your questions stated:

"What I want to accomplish is a delete all record of the table..."
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:eteran
ID: 20090622
yeah, so that sql you worte dose that. I don't have to fo somehting like this

objConn.Execute "Delete * From Table Where MLSCONTAINER"
0
 
LVL 3

Expert Comment

by:yourbudweiser
ID: 20093048
Have you been able to test the code?
0
 

Author Comment

by:eteran
ID: 20093149
no canget get it to mix wityh my code i postedl. can you please help.
0
 

Author Comment

by:eteran
ID: 20115609
now the problem is that I only need to insert 3 colums of the data. The 3rd, 89th, 90th colum of the array in the data base to the three fields in my database posted below. How do I go about doing this.
<%
      Const ForReading = 1
      Set objFSO = CreateObject("Scripting.FileSystemObject")
      Set objTextFile = objFSO.OpenTextFile("c:\sefl_data.txt", ForReading)
     
      objConn.Execute "Delete From Table MLS_Picture_Control"
     
      Do Until objTextFile.AtEndOfStream
            strNextLine = objTextFile.Readline
            arrServiceList = Split(strNextLine , ",")
           ' Response.Write "Server name:" & arrServiceList(0)
           
      For i = 1 to Ubound(arrServiceList)
        Response.write " " & arrServiceList(i)
        strSQL = "INSERT INTO MLS_Picture_Control MLS_LISTING_ID, PHOTO_QUANTITY, PHOTO_URL ('" &arrServiceList(i)& "')"
        objConn.Execute strSQL
        response.flush
      Next

objConn.Close
set objConn = nothing          
%>
0
 
LVL 3

Expert Comment

by:yourbudweiser
ID: 20134213
     For i = 1 to Ubound(arrServiceList)
        Response.write " " & arrServiceList(i)
          If i = 3 or i = 89 or i = 90 Then

strSQL = "INSERT INTO MLS_Picture_Control MLS_LISTING_ID, PHOTO_QUANTITY, PHOTO_URL ('" &arrServiceList(i)& "')"
        objConn.Execute strSQL
        response.flush

        End If
      Next
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question