Not register double record in the DB MySQL

[viki1967]

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?

 
PercorsoCSV = "D:\Inetpub\wwwroot\CSV\file1.csv "
 
  Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
  strURL = percorsoCSV 
 
  Set objFile = objFSO.OpenTextFile(strURL)  
  objFile.SkipLine()  
 
  strVariabile = ""
  Do While Not objFile.AtEndOfStream
 
   strText = objFile.readLine   
   arrText =  split(strText, ",", 17)
 
'CHECK EXISTING ROWS IN THE DB MYSQL
SQL = " SELECT * " 
SQL = SQL & " FROM "  
SQL = SQL & " tabellaCSV " 
SQL = SQL & " WHERE "
SQL = SQL & " NOME = '" & replace(arrText(0), """", "") & "' " 
SQL = SQL & " AND "  
SQL = SQL & " DATA = " & formatDBDate(replace(arrText(2), """", ""), "mysql") & " " 
SQL = SQL & " AND "  
SQL = SQL & " ORA = '" & replace(arrText(3), """", "") & "' "
SQL = SQL & " AND " 
SQL = SQL & " CODICE_PERSONALE = '" & replace(arrText(7), """", "") & "' "
SQL = SQL & " AND " 
SQL = SQL & " DESCRIZIONE = '" & replace(arrText(8), """", "") & "' "
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.open SQL, cn
 
'CHECK objRS
if objRS.eof then
 
'CHECK THE SIMILAR ROWS
if objRS("CODICE_PERSONALE") <> strVariabile  then
 
'INSERT IN THE DB MYSQL
strSql = "INSERT INTO tabellaCSV ... " 
cn.execute(strSql)
 
  strVariabile = objRS("CODICE_PERSONALE")
 
 end if
 
else
 
  response.write "This record is already in the db!<br>"
 
end if
  Loop
 
  objRS.Close()
  Set objRS = Nothing 
 
  cn.Close()
  Set cn = nothing
 
Set fsoMyFile = CreateObject("Scripting.FileSystemObject")
 
if fsoMyFile.FileExists (PercorsoCSV) then
   fsoMyFile.DeleteFile PercorsoCSV
end if
 
Set fsoMyFile = Nothing 
Set objFSO = Nothing
 

[omerbutt]

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

<%
    strsql="SELECT * from table_name;"
    recObj.open strsql, conn, 3
        if rs.recordcount=0 then
    response.Write("NO RECORDS TO MATCH TO , THIS IS THE FIRST REC")
    else
        Do until rs.EOF OR foundit
            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
                foundit=true
            else
                rs.movenext
            end if
        Loop
    end if    
rs.close
if if foundit=false then
 strinert="insert ito table ......................"
else
endif
%>

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

[DrBunchman]

Omerbutt, you should know to use the code tags by now! :-)

Dr B

[omerbutt]

Omerbutt, you should know to use the code tags by now! :-)

Dr B

:( SOWIE OR THAT JUST FORGOT :d
REGARDS,
OMER

[DrBunchman]

No problem Omer, that's what I thought!

[viki1967]

many thanks omerbutt x your help; this script working. :)
regards

[omerbutt]

many thanks omerbutt x your help; this script working. :)
regards

:)
Good to hear that Viki,
regards,
omer