SQL Queries with commas

[Danzig]

Hello,

I'm writing some code to output the results to a sql db. The problem I'm having is that vbr>functionname,param will have issues with the commas in the sql query and thinks that they are secondary vbr paramaters.

This works:

Code: Select all

vbr>vbs_function_name, insert into sometable (somefield)Values('somevalue')

This will not (seemingly because of the commas in the sql code):

Code: Select all

vbr>vbs_function_name, insert into sometable (somefield,somefield2)Values('somevalue',somevalue2')

Anybody know a way to get around this?

Thanks, Danzig

[Danzig]

Seems as though this was a stumper...

I thought I'd post my work around out here incase anybody else ever needs to do this or would like an example to work from.

Code: Select all

VBSTART

Function UpDateDB(strSQL)
		 
		 Dim objConn
		 	 Set objConn = CreateObject("ADODB.Connection")
			 ConnectSTR = "driver=SQL Server;server=SERVER-NAME-GOES-HERE;uid=USER-NAME-GOES-HERE;pwd=PASSWORD-GOES-HERE;database=DATABASE-NAME-GOES-HERE;"
			 objConn.Open ConnectSTR
			 set oRsNewData = CreateObject("ADODB.Recordset")
			 	 oRsNewData.Open strSQL, objConn
			 	 
End Function

VBEND

\\ Lets create a random number to send to the database as a record ID

RAN>1000000,strRand
VBR>UpdateDB,INSERT INTO MachinePerformance (RecordSeed)Values('%strRand%')

\\ Using the MS Constant COMPUTER_NAME for the variable in the SQL command
\\ and updating the database with our machine name using the SQL conditional clause WHERE
\\ to locate the random number we inserted in the database as our record ID

VBR>UpdateDB,UPDATE MachinePerformance SET MachineName = '%COMPUTER_NAME%' WHERE RecordSeed = '%strRand%'

\\ We'll go to the TimeAndDate subroutine to get the current time and date
\\ and place it in to a variable called Time

GOSUB>TimeAndDate

\\ Now that we have the current time and date Lets update our record
\\ notice that we are now using a two part conditional statement in our WHERE clause
\\ specifying both the random number record ID and the name of our machine

VBR>UpdateDB,UPDATE MachinePerformance SET TStamp = '%Time%' where RecordSeed = '%strRand%' AND MachineName = '%COMPUTER_NAME%

\\ From this point you can continue to UPDATE the record as needed
\\
\\ The rub comes in when you can't get past a , in an SQL command
\\ unless you want to hard code the SQL command in to the Vbscript section
\\ at the top and feed the variables through the function.
\\ Doing it that way will require multiple Vbscript functions for each
\\ variant of SQL command being used.



//// Start of Subs ////
srt>TimeAndDate
			   GDT>DateAndTime
			   GTM>Time
			   let>Time=  %Time%
			   con>DateAndTime,Time
End>TimeAndDate

Please keep in mind that this is just how I am doing it as it works best in my situation. You will want to adapt this to best suit your own needs and coding style.

Thanks, Danzig

[support]

Why not just change the VBS subroutine to a function. Then you would do:

Let>sql=select * from bla where a,b,c,d...
VBEval>UpdateDB("%sql"%),result