read file and query database

[larsgs]

I am trying to read a file - get the value from field1 and query the db. This part works. But some records are not found in db, and some records in csv are blank.

Problem is: instead of skipping the blanks or records not found, script uses last found relProductID when inserting in db.

Please help.




Option Explicit

dim db, iim1, sql, rs, res, sql2
dim myname, mypath, connstring
dim iret, result
Dim objFSO, objFile, strNewText, strDescr, strProdID, strImg, strSpec, strRel, arrFields, relProductID, strModel

' database connection
set db = CreateObject("ADODB.Connection")
db.Open("DRIVER={MySQL ODBC 5.1 Driver}; SERVER=localhost;PORT=3306;DATABASE=dbname; UID=root; PASSWORD=; OPTION=3")

Const ForReading = 1
Const ForWriting = 2

'======================
' save file to DB icecat_product_related
'======================
Dim objFile4
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile4 = objFSO.OpenTextFile("D:\related.csv", ForReading)

'Output to sql file
Dim objFile3
Set objFile3 = objFSO.OpenTextFile("D:\related.sql", ForWriting, true)

do until objFile4.AtEndOfStream
arrFields = Split(objFile4.ReadLine,vbTab,2)
if Ubound(arrFields)>=1 then
strProdID = Replace(arrFields(0),Chr(34), "")
strModel = Replace(arrFields(1),Chr(34), "")

sql = "SELECT products_id FROM products WHERE products_model='" & strModel & "'"
set strRel = db.Execute(sql)
if isNull(strRel("products_id"))=false then
while not strRel.eof
relProductID = strRel("products_id")
sql2 = "INSERT INTO icecat_product_related (product_related_id,product_id) VALUES(" & relProductID & "," & strProdID & ")"
strRel.Movenext
wend
end if
'set rsrel = db.Execute(sql2)
objFile3.WriteLine sql2
end if
loop

objFile3.Close 'output sql
objFile4.Close

 

[kermit01de]

For debugging purposes you may add a MsgBox like below.

if isNull(strRel("products_id"))=false then
MsgBox strRel("products_id") & " --- " & Len(strRel("products_id"))        
while not strRel.eof

So with each record you see the content of products_id and the length. It must not necessarily be NULL, but maybe zero-length in the cases you want to skip.


Kind regards
Mirko
--------------------------------------
>>>>>> ... I am sure, some supportissues are a matter of PEBKAC ... <<<<<