469,917 Members | 1,957 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,917 developers. It's quick & easy.

Not register double record in the DB MySQL

263 100+
Not register double record in the DB MySQL

I have developed a simple procedure asp to upload files csv and record data in the table mysql database.

This procedure not register double record because I have one control existing records in the table mysql database.

But if you reinsert the same file csv the rows first excluded are now registered.

For example I have this rows in the file csv:

row 1 = PIPPO, 25/07/2008, 11:50, XXXZZZ, YOU
row 2 = PIPPO, 26/07/2008, 01:50, XXXZZZ, YOU
row 3 = CAIO, 25/07/2008, 17:50, XXXYYY, NEWBIE
row 4 = TIZIO, 26/07/2008, 10:05, YYYZZZ, ADMIN

In first upload file csv in the db register the rows 1-3 and 4.

In second upload same file csv in the db register the row 2, eliminated in the first upload because similar to row 1.

This is my code ASP, can you help me?

Expand|Select|Wrap|Line Numbers
  1.  
  2. PercorsoCSV = "D:\Inetpub\wwwroot\CSV\file1.csv "
  3.  
  4.   Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
  5.   strURL = percorsoCSV 
  6.  
  7.   Set objFile = objFSO.OpenTextFile(strURL)  
  8.   objFile.SkipLine()  
  9.  
  10.   strVariabile = ""
  11.   Do While Not objFile.AtEndOfStream
  12.  
  13.    strText = objFile.readLine   
  14.    arrText =  split(strText, ",", 17)
  15.  
  16. 'CHECK EXISTING ROWS IN THE DB MYSQL
  17. SQL = " SELECT * " 
  18. SQL = SQL & " FROM "  
  19. SQL = SQL & " tabellaCSV " 
  20. SQL = SQL & " WHERE "
  21. SQL = SQL & " NOME = '" & replace(arrText(0), """", "") & "' " 
  22. SQL = SQL & " AND "  
  23. SQL = SQL & " DATA = " & formatDBDate(replace(arrText(2), """", ""), "mysql") & " " 
  24. SQL = SQL & " AND "  
  25. SQL = SQL & " ORA = '" & replace(arrText(3), """", "") & "' "
  26. SQL = SQL & " AND " 
  27. SQL = SQL & " CODICE_PERSONALE = '" & replace(arrText(7), """", "") & "' "
  28. SQL = SQL & " AND " 
  29. SQL = SQL & " DESCRIZIONE = '" & replace(arrText(8), """", "") & "' "
  30. Set objRS = Server.CreateObject("ADODB.Recordset")
  31. objRS.open SQL, cn
  32.  
  33. 'CHECK objRS
  34. if objRS.eof then
  35.  
  36. 'CHECK THE SIMILAR ROWS
  37. if objRS("CODICE_PERSONALE") <> strVariabile  then
  38.  
  39. 'INSERT IN THE DB MYSQL
  40. strSql = "INSERT INTO tabellaCSV ... " 
  41. cn.execute(strSql)
  42.  
  43.   strVariabile = objRS("CODICE_PERSONALE")
  44.  
  45.  end if
  46.  
  47. else
  48.  
  49.   response.write "This record is already in the db!<br>"
  50.  
  51. end if
  52.   Loop
  53.  
  54.   objRS.Close()
  55.   Set objRS = Nothing 
  56.  
  57.   cn.Close()
  58.   Set cn = nothing
  59.  
  60. Set fsoMyFile = CreateObject("Scripting.FileSystemObject")
  61.  
  62. if fsoMyFile.FileExists (PercorsoCSV) then
  63.    fsoMyFile.DeleteFile PercorsoCSV
  64. end if
  65.  
  66. Set fsoMyFile = Nothing 
  67. Set objFSO = Nothing
  68.  
Aug 21 '08 #1
6 1975
omerbutt
638 512MB
hi viki1967
what i havent got so far is that if any record is inserted before, if that same record is inserted again you want to skip it or want it as an another separate entry , lets say i have a file in which i have these records
CSV file which hold ,Name,Dob,Address and Age
row1=Omer,19/10/1982,house No 23-b,25
row1=Omer,19/10/1982,house No 24-b,25
.................if i want that each these records should have a separate entry and the second row should not overwrite the first one then
Assume your connection is already running and you have set the recordset object and the the rows from the CSV file is save in the arraytext() array
Expand|Select|Wrap|Line Numbers
  1. <%
  2.     strsql="SELECT * from table_name;"
  3.     recObj.open strsql, conn, 3
  4.         if rs.recordcount=0 then
  5.     response.Write("NO RECORDS TO MATCH TO , THIS IS THE FIRST REC")
  6.     else
  7.         Do until rs.EOF OR foundit
  8.             if(Strcomp(rs.fields("NAME"),arraytext(0),vbtextcompare)=0 && Strcomp(rs.fields("Dob"),arraytext(1),vbtextcompare)=0 && Strcomp(rs.fields("Address"),arraytext(2),vbtextcompare)=0 && Strcomp(rs.fields("Age"),arraytext(3),vbtextcompare)=0) then
  9.                 foundit=true
  10.             else
  11.                 rs.movenext
  12.             end if
  13.         Loop
  14.     end if    
  15. rs.close
  16. if if foundit=false then
  17.  strinert="insert ito table ......................"
  18. else
  19. endif
  20. %>
in this case you are checking all the fields in the row if all of them are the same then it will not enter record
hope you can do it your way from here
Aug 22 '08 #2
DrBunchman
979 Expert 512MB
Omerbutt, you should know to use the code tags by now! :-)

Dr B
Aug 22 '08 #3
omerbutt
638 512MB
Omerbutt, you should know to use the code tags by now! :-)

Dr B
:( SOWIE OR THAT JUST FORGOT :d
REGARDS,
OMER
Aug 22 '08 #4
DrBunchman
979 Expert 512MB
No problem Omer, that's what I thought!
Aug 22 '08 #5
viki1967
263 100+
many thanks omerbutt x your help; this script working. :)
regards
Aug 22 '08 #6
omerbutt
638 512MB
many thanks omerbutt x your help; this script working. :)
regards
:)
Good to hear that Viki,
regards,
omer
Sep 1 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

12 posts views Thread by birdy | last post: by
6 posts views Thread by sa_wahab | last post: by
George Lft
1 post views Thread by George Lft | last post: by
1 post views Thread by aajayaprakash | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.