Restart: How to combine db and webrecorder

[ZeitenWanderer]

Ok, I take it that my planning was no good.

Now I access the db directly and have parts of the mosaik, where I want to go to. That is meant for price comparison (oh, what a brand new idea ...)

I already have another working script to extract data from the db, thus I just copied myself the first parts needed and included the working part of grabbing data out of a csv and loop through it to send it into web forms.

To tackle problems take them one by one ... - how do I convert this reading out of a csv into reading from out of a db, starting from line 24?

Code: Select all

VBSTART
Function GetData(DataSelected)
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Dim SQLString
    Dim Value
    Dim TextLine
    Dim strSafeTime
    Dim strSafeDate
    Dim ProjectFolder

    ProjectFolder="C:\darp2\Projects\Kooperation Rosenke\Data"

    set MyDB = CreateObject("ADODB.Connection")
    MyDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DARP2\Projects\Kooperation Rosenke\Data\DARP_DB.mdb"

    strSafeTime = Right("0" & Hour(Now), 2) & Right("0" & Minute(Now), 2) & Right("0" & Second(Now), 2)
    strSafeDate = replace(FormatDateTime(now,2),"/","")

    SQLString = "select * from tblBuchdaten where Data>Selected >=0"

    Set fs = CreateObject("Scripting.FileSystemObject")

    //if the excel-file is not in the same folder as the script, modify the path in this line:
    Let>XLSPath=%SCRIPT_DIR%\excellist.xls

    //Start IE
    IESetTimeout>5,ie_res
    IECreate>IE[0]

    //get to the eurobuch page
    IENavigate>%IE[0]%,http://www.eurobuch.com/search_results.php?updatePresets=1&proSearch=0&js=1&noBids=0&land=0&usedState=0&professionalState=0&coverState=&newReleases=0&fromDateDays=7&first_edition=0&signed=0&easyOrder=0&minPrice=&maxPrice=&minJahr=&maxJahr=&doLuisterboek=1&doAbeDe=1&doAchtungBuecher=1&doAlibris=1&doAmazonUk=1&doAmazonCom=1&doAmazon=1&doAmazonEs=1&doAmazonFr=1&doAmazonIt=1&doAntbo=1&doProlibri=1&doAudibile=1&doBn=1&doBbBuch=1&doBiblio=1&doBides=1&doBookdepository=1&doBooklooker=1&doBruna=1&doBUCHCH=1&doBUCH=1&doBuch24=1&doBuchfreund=1&doBuecherDe=1&doCasaDelLibro=1&doCiando=1&doCosmox=1&doEBay=1&doEci=1&doEBS=1&doGoogle=1&doITunes=1&doJokers=1&doKobo=1&doLaFeltrinelli=1&doLehmanns=1&doLibreka=1&doLibriEB=1&doLibri=1&doNotenbuch=1&doPrintsasiaCom=1&doRegalfrei=1&doRheinberg=1&doThaliaAt=1&doThaliaCh=1&doThaliaDe=1&doAum=1&doZVAB=1&doFnac=&doFnacEs=&doHoepli=&doSkyo=&doWebster=&search=&author=Ferkins&doEbooks=1&mediatype=0&title=ZeitenWanderer&doAudiobooks=1&isbn=&noReprint=0&publisher=&search_header_submit=suchen,ie_res
    IEWaitDocumentComplete>%IE[0]%,ie_res

    //Open Excel - change path of XLS file if needed ... 
    XLOpen>XLSPath,1,xlBook
    //Get number of rows
    XLGetSheetDims>xlBook,Sheet1,numRows,numCols
    If>numRows>1
    Let>row=1
    //loop through rows (start row 2, row 1 has headers)
    Repeat>row
    //get each item (column)
    Let>row=row+1
    XLGetCell>xlBook,Sheet1,row,1,strTitel
    XLGetCell>xlBook,Sheet1,row,2,strAutor
    //fill the form (calls FillForm subroutine)
    GoSub>FillForm
    
    //here we click on "Search"
    IEClickTag>%IE[0]%,{""},{"search_header"},{"INPUT"},{"ID"},{"search_header_submit"},ie_res
    IEFormSubmit>%IE[0]%,{""},{"search_header"},ie_res

    IEWaitDocumentComplete>%IE[0]%,ie_res
    
  Until>row=numRows
Endif

    
    MyDB.Close
    GetData="Done."
End Function
VBEND

VBEval>GetData("%DataSelected%"),Message
MessageModal>%Message%

Next, I would like to hand over data

[Marcus Tettmar]

Please repost your code. For some reason snippets are not working in your posts. I just tested that they work for me so I'm not sure why. Your code is mangled and has native code inside vbscript code.

Try using the "Code" button when you paste your code instead of Snippets.

When you read from a data source using SQL using DBQuery you end up with an array of all the data.

[ZeitenWanderer]

I reworked the code for a different website, that ought to work now.
This is my first attempt to transfer data from a list into web forms and it works all right so far.

Code: Select all

//if the xlsx file is not in the same folder as the script, modify the path in this line:
Let>XLSPath=%SCRIPT_DIR%\test.xls

//Start IE
IESetTimeout>5,ie_res
IECreate>IE[0]

//get to the form page - in a real system this might require more steps - a login, some links etc ...
IENavigate>%IE[0]%,http://www.booklooker.de/app/search.php,ie_res
IEWaitDocumentComplete>%IE[0]%,ie_res

//Open Excel - change path of XLS file if needed ... 
XLOpen>XLSPath,1,xlBook
//Get number of rows
XLGetSheetDims>xlBook,Sheet1,numRows,numCols
If>numRows>1
  Let>row=1
  //loop through rows (start row 2, row 1 has headers)
  Repeat>row
    //get each item (column)
    Let>row=row+1
    XLGetCell>xlBook,Sheet1,row,1,strTitel
    XLGetCell>xlBook,Sheet1,row,2,strAutor

    //fill the form (calls FillForm subroutine)
    GoSub>FillForm
    
    //now we need to get back to the form page for the next record ..... 
    //a navigate may be all that is required, or click some links or whatever ... 
    //here we can click on "Submit another response"
    IEClickTag>%IE[0]%,{""},{"eingabe"},{"INPUT"},{"CLASSNAME"},{" bl_button bl_button_submit"},ie_res
    IEWaitDocumentComplete>%IE[0]%,ie_res
    IENavigate>%IE[0]%,http://www.booklooker.de/app/search.php,ie_res
    IEWaitDocumentComplete>%IE[0]%,ie_res
    
  Until>row=numRows
Endif

//finally close Excel
XLQuit>xlBook

//close IE (if you want)
IEQuit>IE[0],ie_res

//This fills the form in with our data
SRT>FillForm
  //fill Author field  
  IEFormFill>%IE[0]%,{""},{"eingabe"},{"autor"},strAutor,0,ie_res

  //fill Title field  
  IEFormFill>%IE[0]%,{""},{"eingabe"},{"titel"},strTitel,0,ie_res

  //submit the form
  IEClickTag>%IE[0]%,{""},{""},{"INPUT"},{"ID"},{"ss-submit"},ie_res
  IEWaitDocumentComplete>%IE[0]%,ie_res
End>FillForm

Now the first suggestion was to use a db instead of csv, that exists and would shortcut the originally intended process, but I am unable to transfer the code working for an Excel list into reading from a db.

This is the working code to access the db.

Code: Select all

VBSTART
Function GetData(DataSelected)
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Dim SQLString
    Dim Value
    Dim TextLine
    Dim strSafeTime
    Dim strSafeDate
    Dim ProjectFolder

    ProjectFolder="C:\darp2\Projects\Kooperation Rosenke\Data"

    set MyDB = CreateObject("ADODB.Connection")
    MyDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DARP2\Projects\Kooperation Rosenke\Data\DARP_DB.mdb"

    strSafeTime = Right("0" & Hour(Now), 2) & Right("0" & Minute(Now), 2) & Right("0" & Second(Now), 2)
    strSafeDate = replace(FormatDateTime(now,2),"/","")



    SQLString = "select * from tblBuchdaten where DataSelected >=1"

    Set fs = CreateObject("Scripting.FileSystemObject")

    
    MyDB.Close

    GetData="Done."
End Function
VBEND

VBEval>GetData("%DataSelected%"),Message
MessageModal>%Message%

How do I bring these two together, that I pick the data out of the db table instead of the Excel list?

Many fat apprecitiations!!!

[Marcus Tettmar]

Can I ask why you are using VBScript? That will make it more complicated. You can do it natively:

Code: Select all

Let>constr=provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DARP2\Projects\Kooperation Rosenke\Data\DARP_DB.mdb
DBConnect>constr,dbH
Let>SQL=select * from tblBuchdaten where DataSelected >=1,
DBQuery>dbH,SQL,arrRecs,numRecs,numFields,1

// You now have an array called arrRecs full of your data.  Look in the watch list.
// you can now easily loop through it with a Repeat/Until

DBClose>dbH

PLEASE read this:
http://help.mjtnet.com/article/20-using ... -functions