Get data from SQL Db trought script

  • 285 Views
  • Last Post 11 June 2018
  • Topic Is Solved
dusan.popovic posted this 07 June 2018

Hello Again,

I have some example script where i can query data from DB and put that data in variable (example script bellow) tested and it works.

But i need to get next available number from specific table with fallowing SQL query:

select IDENT_CURRENT('dbo.dn_delovodna') + IDENT_INCR('dbo.dn_delovodna')

but i don't know how to get returned value from SQL in this case in metadata?

If i put SQL = "select IDENT_CURRENT('dbo.dn_delovodna') + IDENT_INCR('dbo.dn_delovodna')" in the process log i have error:

Error | An error occurred at  | line 16, column 0: 3265 | Item cannot be found in the collection corresponding to the requested name or ordinal.

I guess that i need to change line 16 docnumber = Recordset("USERNAME") to something else but what?

 

##### My Sample Script ######

Dim Connection
Dim SQL

SQL = "SELECT [username] FROM [DN_Test].[dbo].[dn_users] WHERE [ID] = 100"


Set Connection = CreateObject("ADODB.Connection")
Set Recordset = CreateObject("ADODB.Recordset")

Connection.Open "Provider=SQLOLEDB;Data Source=demo-sqlsrv\sqlexpress;Persist Security Info=True;OLE DB Services=-13;Password=*********;User ID=sa;Initial Catalog=DN_Test;Locale Identifier=1033;Connect Timeout=0;General Timeout=0;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WIN-CVF839O6QKM;Use Encryption for Data=False;Tag with column collation when possible=False"
Recordset.Open SQL,Connection
docnumber = Recordset("USERNAME")
Connection.Close
Set Connection=nothing

'Wscript.Echo docnumber
CALL Metadata.SetValues("USERNAME",docnumber)

 

Many Thanks!

Dusan

Attached Files

Order By: Standard | Newest | Votes
luigi.zurolo posted this 08 June 2018

Hi Dusan,

yes you are correct in the terms that when using such SQL Server constructors the resultset will have actually no name by default, hence you get that error message since you will be using a column name to refer to it.

The best approach is to transform the resultset column by giving a custom name inside the query itself and use that name inside the script to refer to the right column you would like to access.

The query will become like this:

select IDENT_CURRENT('dbo.dn_delovodna') + IDENT_INCR('dbo.dn_delovodna') as 'RESULT'

 where AS is the transformation parameter and RESULT is the name I'm using in my example (you can give any name you prefer).

In this way you can use in the script:

docnumber = Recordset("RESULT")

to access the query result column.

Please refer to the attached screenshots.

 

Have a nice day,

Luigi

Attached Files

  • Liked by
  • dusan.popovic
dusan.popovic posted this 11 June 2018

Hi Luigi,

Yes, it's working perfectly!

Thanks!

  • Liked by
  • luca.scarpati
Close