Forum Discussion

VishakhaB's avatar
VishakhaB
Visitor
8 years ago

DB connection query via notepad

Hello all,

I have a very big query to be fetched from a notepad/sql file.

Instead of passing an inline DB query like:

strSQLQuery = "INSERT INTO " &strTblNM& " (" &strCols& ") VALUES (" &strVals& ")"

 

I want the query to be passed from a notepad. I fetched it like:

Dim F, s
Set F = aqFile.OpenTextFile("D:/wellinsert.txt", aqFile.faRead, aqFile.ctANSI)
F.Cursor = 0
While Not F.IsEndOfFile()
s = F.ReadLine()
WEnd
F.Close()

 

I could fetch the query successfully. I am not getting any error, when I perform:

objCon.Execute s

but, the DB is not getting updated.

If the same query, I pass inline, I can see the DB getting updated.

Can someone tell me how to deal with accepting DB queries via external files?

 

Thanks,

Vishakha

2 Replies

Sort By
  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    8 years ago

    Is the query in your notepad file a single line?  I mean, it may seem that it is when viewing it, but I'd be curious as to what the value of "s" is at the point in your script when you exit your while loop.  Do you have the full query or only part?

    Additionally, if your SQL query contains special characters like quotes, slashes, etc, those could be misinterpreted when passing to the query.

    Suggestion: Rather than reading the stuff from a text file into a variable and executing it, have you considered opening a shell and then executing the sqlcmd command line syntax?  Something on the lines of

     

    Sys.OleObject('WScript.Shell').Exec('sqlcmd -S MyServer -i C:\MySQLScript.sql')

    Seems that this would be a lot easier than trying to read the file into memory.

  • Marsha_R's avatar
    Marsha_R
    Champion Level 3
    8 years ago

    Maybe there some implicit conversion happening when you do it inline.  

     

    Try objCon.Execute eval(s)