locked
VBScript query one database insert result in SQL database RRS feed

  • Question

  • I'm trying to query one database for 3 fields and update an SQL database with the results.  Below is the code I'm using I get the message Expected end of statement.  If I remove the Do While to Loop section (6 lines) the script runs like I expect it.

    
    Dim Table1ConnectionString
     Table1ConnectionString = "provider=MSDASQL.1;" & _
      "Extended Properties=DRIVER={xxxxxxxxxxxxxxxxxx};" & _
      "Server=xxxxxxxxxxx; Port=xxxxxx;Database=xxxxxx;UID=xxxxxx;PWD=xxxxxxxxxx"
    
    Dim Table2ConnectionString
     Table2ConnectionString = "PROVIDER=MSDASQL;DRIVER={SQL Server};" & _
      "SERVER=xxxxxxxxxxxxx;DATABASE=xxxxxxxxxx;" & _
      "UID=xxxxxxx;PWD=xxxxxxxxxx;"
    
    
    Dim cnTable1: Set cnTable1 = WScript.CreateObject("ADODB.Connection")
    Dim cnTable2: Set cnTable2 = WScript.CreateObject("ADODB.Connection")
    Dim oRs: Set oRs = WScript.CreateObject("ADODB.Recordset")
    Dim oRsTable2: Set oRsTable2 = WScript.CreateObject("ADODB.Recordset")
    Dim sSQL
    Dim sTable2
    Dim strStartDate
    Dim strEndDate
    Dim strYYYY
    Dim strMM
    Dim strDD
    Dim strHH
    Dim strNN
    Dim strSS
    Dim StartDate
    Dim EndDate
    
    
    StartDate = Now()
    EndDate = DateAdd("d",2,Now())
    
    strYYYY = Trim(CStr(DatePart("yyyy", StartDate)))
    strMM = Trim(CStr(DatePart("m", StartDate)))
    If Len(strMM) < 2 Then strMM = "0" & strMM
    strDD = Trim(CStr(DatePart("d", StartDate)))
    If Len(strDD) < 2 Then strDD = "0" & strDD
    strHH = Trim(CStr(DatePart("h", StartDate)))
    If Len(strHH) < 2 Then strHH = "0" & strHH
    strNN = Trim(CStr(DatePart("n", StartDate)))
    If Len(strNN) < 2 Then strNN = "0" & strNN
    strSS = Trim(CStr(DatePart("s", StartDate)))
    If Len(strSS) < 2 Then strSS = "0" & strSS
    strStartDate = strYYYY & "-" & strMM & "-" & strDD & " " & strHH & ":" & strNN & ":" & strSS
    
    strYYYY = Trim(CStr(DatePart("yyyy", DateAdd("d", 1, EndDate))))
    strMM = Trim(CStr(DatePart("m", DateAdd("d", 1, EndDate))))
    If Len(strMM) < 2 Then strMM = "0" & strMM
    strDD = Trim(CStr(DatePart("d", DateAdd("d", 1, EndDate))))
    If Len(strDD) < 2 Then strDD = "0" & strDD
    strEndDate = strYYYY & "-" & strMM & "-" & strDD & " 23:59:59"
    
    
    sSQL = "SELECT sdt.DATE_TIME, se.ENTITY_NAME, sas.CLINIC_NAME " & _
           "from xxxxxxxxxxxxxxxxxxx, xxxxxxxxxxxxxxxxxxx, xxxxxxxxxxxxxxxxxxx " & _
           "where xxxxxx = xxxxxxx " & _
           "and xxxxxx = xxxxxxx " & _
           "and xxxxxxxx >= '" & strStartDate & "' " & _
           "and xxxxxxxx <= '" & strEndDate & "' " & _
           "and xxxxxx = xxxxxxx " & _
           "Order By xxxxxxxxxxxxxxxxxxx, xxxxxxxxxxxxxxxxxxx, xxxxxxxxxxxxxxxxxxx"
    
     cnTable1.Open CacheConnectionString
     cnTable2.Open Table2ConnectionString
     sSQLTable2 = "SELECT * FROM xxxxxxxxx"
     Set oRsTable2 = cnTable2.Execute(sSQLTable2)
     
      If Not oRsTable2.EOF Then
      If oRsTable2("DataBeingRefreshed").value Then
       If DateAdd("h",1,oRsTable2("DateLastModified").value)>Now Then
        oRsTable2.Close
        cnTable2.Close
        cnTable1.Close
        Set chTable1 = Nothing
        Set cnTable2 = Nothing
        Set oRsTable2 = Nothing
        WScript.Quit
       End If
      End If
     oRsTable2.Close
     sSQLTable2 = "UPDATE xxxxxxxxxxx Set DataBeingRefreshed = 1, DateLastModified = '" & Trim(CStr(Now)) & "'"
     cnTable2.Execute sSQLTable2
     End If
     
    
    Set oRsTable2 = cnTable2.Execute("DELETE FROM OpenApptUpdateForDisplay WHERE fld_complete = 'NotComplete'; ")
    Set oRs = cnTable1.Execute(sSQL)
    
    Do While Not oRs.EOF
       sSQLTable2 = "INSERT INTO OpenApptUpdateForDisplay (DATE_TIME, ENTITY_NAME, CLINIC_NAME, fld_TimeStamp)" &  _
         "VALUES ('" & oRs("DATE_TIME").value & ",'" & oRs("ENTITY_NAME").value & "', '" & oRs("CLINIC_NAME").value & "', '" & StartDate & "')"
       cnTable2.Execute sSQLTable2
       oRs.MoveNext
    Loop
    
        
    sSQLTable2 = "UPDATE PCMControl Set DataBeingRefreshed = 0, DateLastModified = '" & Trim(CStr(Now)) & "'"
    cnTable2.Execute sSQLTable2
    On Error Resume Next
    oRsTable2.Close
    cnTable2.Close
    cnTable1.Close
    Set chTable1 = Nothing
    Set cnTable2 = Nothing
    Set oRs = Nothing
    Set oRsTable2 = Nothing
       
      
    

    Thanks for any help you can provide

    • Moved by Bill_Stewart Tuesday, July 21, 2015 9:06 PM This is not "replicate my environment, debug and test for me" forum
    Thursday, June 18, 2015 3:17 PM

Answers

  • Since I don't have your database, write a short sample script that just echoes values to the screen or something. Put in the loop. Observe the behavior of your script. In this way you should be able to readily spot your error.

    -- Bill Stewart [Bill_Stewart]

    • Proposed as answer by Bill_Stewart Monday, July 6, 2015 10:15 PM
    • Marked as answer by Bill_Stewart Tuesday, July 21, 2015 9:06 PM
    Thursday, June 18, 2015 4:17 PM

All replies

  • So you want for someone to try to reproduce your database schema, set up an environment, and then test and debug your script for you?

    That's not really the purpose of this forum. (Remember that we are volunteers, and there is no SLA.)

    I would recommend that you start small. Begin your script with a single, simple database update. Once you are certain that it is working correctly, then you can begin to introduce looping constructs. The principle is this: http://sscce.org/

    I would also recommend PowerShell rather than VBScript, in any case.


    -- Bill Stewart [Bill_Stewart]

    Thursday, June 18, 2015 3:28 PM
  • Sorry no that wasn't my intention.  I'm thinking I have the syntax wrong in the do while loop and someone could spot what I did wrong just in that section.  If I remove those 6 lines the script does work as expected and if I simply put in an insert statement with one line of fields I type in for the values it does update the database.

    Thursday, June 18, 2015 4:15 PM
  • Since I don't have your database, write a short sample script that just echoes values to the screen or something. Put in the loop. Observe the behavior of your script. In this way you should be able to readily spot your error.

    -- Bill Stewart [Bill_Stewart]

    • Proposed as answer by Bill_Stewart Monday, July 6, 2015 10:15 PM
    • Marked as answer by Bill_Stewart Tuesday, July 21, 2015 9:06 PM
    Thursday, June 18, 2015 4:17 PM
  • You have many errors in that script beyond a loop issue.  I suggest that you want to execute an select into query and drop all of the variables.  You can get the dates as database functions.

    Here is one line that makes no sense:

    cnTable1.Open CacheConnectionString

    I recommend doing this as a stored procedure on the database.  Use SSMS to build and test the stored procedure then call it from VBScript of from PowerShell.

    You would also find it easier to find you errors if you would correctly format the script.

    Here is an example.

    strStartDate = Replace(FormatDateTime(Now(),vbGeneralDate),"/.","-")
    strEndDate = Replace(FormatDateTime(DateAdd("d",2,Now()),vbGeneralDate),"/.","-")
    
    sSQL = "SELECT sdt.DATE_TIME, se.ENTITY_NAME, sas.CLINIC_NAME " & _
           "from xxxxxxxxxxxxxxxxxxx, xxxxxxxxxxxxxxxxxxx, xxxxxxxxxxxxxxxxxxx " & _
           "where xxxxxx = xxxxxxx " & _
           "and xxxxxx = xxxxxxx " & _
           "and xxxxxxxx >= '" & strStartDate & "' " & _
           "and xxxxxxxx <= '" & strEndDate & "' " & _
           "and xxxxxx = xxxxxxx " & _
           "Order By xxxxxxxxxxxxxxxxxxx, xxxxxxxxxxxxxxxxxxx, xxxxxxxxxxxxxxxxxxx"
    
    sSQLTable2 = "SELECT * FROM xxxxxxxxx"
    
    
    Table1ConnectionString = "provider=MSDASQL.1;Extended Properties=DRIVER={xxxxxxxxxxxxxxxxxx};Server=xxxxxxxxxxx; Port=xxxxxx;Database=xxxxxx;UID=xxxxxx;PWD=xxxxxxxxxx"
    Table2ConnectionString = "PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=xxxxxxxxxxxxx;DATABASE=xxxxxxxxxx;UID=xxxxxxx;PWD=xxxxxxxxxx;"
    
    Set cnTable1 = CreateObject("ADODB.Connection")
    Set cnTable2 = CreateObject("ADODB.Connection")
    Set oRs = CreateObject("ADODB.Recordset")
    Set oRsTable2 = CreateObject("ADODB.Recordset")
    
    cnTable1.Open CacheConnectionString
    cnTable2.Open Table2ConnectionString
    
    Set oRsTable2 = cnTable2.Execute(sSQLTable2)
     
    If Not oRsTable2.EOF Then
    	If oRsTable2("DataBeingRefreshed").value Then
    		If DateAdd("h",1,oRsTable2("DateLastModified").value)>Now Then
    		    WScript.Quit
    		End If
    	End If
    	
    	oRsTable2.Close
    	sSQLTable2 = "UPDATE xxxxxxxxxxx Set DataBeingRefreshed = 1, DateLastModified = '" & Trim(CStr(Now)) & "'"
    	cnTable2.Execute sSQLTab
    End If
     
    
    Set oRsTable2 = cnTable2.Execute("DELETE FROM OpenApptUpdateForDisplay WHERE fld_complete = 'NotComplete'; ")
    Set oRs = cnTable1.Execute(sSQL)
    
    Do While Not oRs.EOF
       sSQLTable2 = "INSERT INTO OpenApptUpdateForDisplay (DATE_TIME, ENTITY_NAME, CLINIC_NAME, fld_TimeStamp)" &  _
         			"VALUES ('" & oRs("DATE_TIME").value & ",'" & oRs("ENTITY_NAME").value & "', '" & oRs("CLINIC_NAME").value & "', '" & StartDate & "')"
       cnTable2.Execute sSQLTable2
       oRs.MoveNext
    Loop
      
    sSQLTable2 = "UPDATE PCMControl Set DataBeingRefreshed = 0, DateLastModified = '" & Trim(CStr(Now)) & "'"
    cnTable2.Execute sSQLTable2
    

    This is easier to read and follow and eliminates dozens of useless lines.  NOw you can more easily debug this.

    For result set queries and non esult set queries you can skip the recordset object and just use the execute method of the connection object.


    \_(ツ)_/

    Thursday, June 18, 2015 5:27 PM
  • Here you are using StartDate and not strStartDate.  Did you mean to do that?

    & oRs("CLINIC_NAME").value & "', '" & StartDate & "')"


    \_(ツ)_/

    Thursday, June 18, 2015 5:29 PM
  • This line is wrong:

    Set oRsTable2 = cnTable2.Execute("DELETE FROM OpenApptUpdateForDisplay WHERE fld_complete = 'NotComplete'; ")
    Should be:

    cnTable2.Execute "DELETE FROM OpenApptUpdateForDisplay WHERE fld_complete = 'NotComplete'; "
    It will return an integer of the number of rows affected.  It does not return an object.


    \_(ツ)_/


    • Edited by jrv Thursday, June 18, 2015 5:32 PM
    Thursday, June 18, 2015 5:31 PM
  • If you are looking for zero times in your dates then use vbShortDate and 00:00

    Note that you cannot compare times as strings.  In SQS you can supply a short date to the field and SQS will use a zero time.  DO not use 23:59.  Use the default.

    Example:

    strStartDate='11-10-2014'  Start at midnight (00:00)

    strEndDate='12-01-2014' end at midnight

           "and xxxxxxxx >= '" & strStartDate & "' " &
          
    "and xxxxxxxx < '" & strEndDate & "' " & _

    Includes all dates for 11-10-12014 upt to but not including 12-01-2014

    You may have to add on day to the end date since you were use <=. With zero time you wil not get correct results with <= so add a day and use only <.

    TO use short date in SQS we can just do this

    strStartDate=FormatDateTIme(Now,vbShortDate)

    SQS date fields all understand this string format.



    \_(ツ)_/


    • Edited by jrv Thursday, June 18, 2015 5:42 PM
    Thursday, June 18, 2015 5:40 PM