[RESOLVED] vbs: Connect SQL Server

[mel_flynn]

Error on highlighted line: Arguments are of the wrong type, are out of acceptable range, or are in conflict with one other

iconnTrans = CreateObject("ADODB.Connection")
Set iconnPromo = CreateObject("ADODB.Connection")

Set rsTrans = CreateObject("ADODB.Recordset")
Set rsPromoTrans = CreateObject("ADODB.Recordset")

iconnTrans.open _
	"Provider=SQLOLEDB.1;Data Source = localhost\sqlexpress;Integrated Security = SSPI;Initial Catalog=dw2"

iconnPromo.open _
	"Provider=SQLOLEDB.1;Data Source = localhost\sqlexpress;Integrated Security = SSPI;Initial Catalog=dw"

rsTrans.open "SELECT TransactionID, StockID, Promotion FROM Transactions WHERE OnPromotion='yes'", iconnTrans, adOpenStatic, adLockOptimistic 

Do Until rsTrans.EOF 
	
	wscript.echo rstrans.TransactionID
        rsTrans.Movenext
Loop

If I modify my recordset string to

sTrans.open "SELECT TransactionID, StockID, Promotion FROM Transactions WHERE OnPromotion='yes'", iconnTrans

No records are being returned. rsTrans.RecordCount=-1

[si_the_geek]

In terms of the error, did you declare the constants? (on that line, they are adOpenStatic and adLockOptimistic)

Regarding .RecordCount=-1 , that does not mean that there are no records - it means that the exact amount of records is currently unknown (and there is probably at least one record). .EOF and .BOF will still work as expected.

[mel_flynn]

Hi Si,
Thanks for the reply. I don't get your question? What constants need to be declared?
Also, how do I determine the recordcount? When I run the code without adOpenStatic and adLockOptimistic I get an error at line wscript.echo rstrans.TransactionID Error: Object does't support this property or method: 'rsTrans.transactionID'

[si_the_geek]

Hi Si,
Thanks for the reply. I don't get your question? What constants need to be declared?

adOpenStatic and adLockOptimistic

If you use VB6 (or similar), when you add a Reference, the constants are included... in VBScript you need to declare them yourself. You can use the Object Browser in VB6 etc to find out what the declarations should be.

Also, how do I determine the recordcount?

Do you need to?

For the kind of code you showed, there is absolutely no reason to know it.

When I run the code without adOpenStatic and adLockOptimistic I get an error at line wscript.echo rstrans.TransactionID Error: Object does't support this property or method: 'rsTrans.transactionID'

That is to be expected, because that is not the syntax to refer to a field.

As with VB6 etc, a valid way is: rstrans.Fields("TransactionID").Value

[mel_flynn]

Thanks a mill Si. Do I need adOpenStatic and adLockOptimistic? I need to update the recordset.

[si_the_geek]

You need to use something other than the defaults, but perhaps not those specific ones.

For more information, see the article What do the parameters of the recordset.Open method mean? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)

[mel_flynn]

Thanks Si.

[mel_flynn]

Still an error on highlighted line: Arguments are of the wrong type, are out of acceptable range, or are in conflict with one other

Set iconnTrans = CreateObject("ADODB.Connection")

iconnTrans.open _
	"Provider=SQLOLEDB.1;Data Source = localhost\sqlexpress;Integrated Security = SSPI;Initial Catalog=dw2"

Set rsTrans = CreateObject("ADODB.Recordset")
Set rsTrans.ActiveConnection=iconnTrans
rsTrans.CursorLocation = adUseClient
rsTrans.CursorType = adOpenDynamic
rsTrans.open "SELECT TransactionID, StockID, Promotion FROM Transactions WHERE OnPromotion='yes'"

[si_the_geek]

Have you declared adUseClient and adOpenDynamic? (you need to)

[mel_flynn]

Yes

dim adUseClient
dim adOpenDynamic

Set iconnTrans = CreateObject("ADODB.Connection")

iconnTrans.open _
	"Provider=SQLOLEDB.1;Data Source = localhost\sqlexpress;Integrated Security = SSPI;Initial Catalog=dw2"

Set rsTrans = CreateObject("ADODB.Recordset")
Set rsTrans.ActiveConnection=iconnTrans
rsTrans.CursorLocation = adUseClient
rsTrans.CursorType = adOpenDynamic
rsTrans.open "SELECT TransactionID, StockID, Promotion FROM Transactions WHERE OnPromotion='yes'"

[si_the_geek]

Actually no.

You have declared variables, and not assigned them a value.

They are supposed to be constants with particular values, which you can find using the method I described above (or a web search, etc).

[mel_flynn]

Thanks a mill for your help.

Const adLockBatchOptimistic = 4
Const adUseClient = 3 

Set iconnTrans = CreateObject("ADODB.Connection")

iconnTrans.open _
	"Provider=SQLOLEDB.1;Data Source = localhost\sqlexpress;Integrated Security = SSPI;Initial Catalog=dw2"

Set rsTrans = CreateObject("ADODB.Recordset")

rsTrans.CursorLocation = adUseClient
rsTrans.LockType = adLockBatchOptimistic
rsTrans.open "SELECT * FROM ir_Transactions", iconnTrans