HI,

i need to read a single value from an SQL DB, this is my code:

Dim TableName, Uname, DBPassword, DataBaseName, SQLServer, rst

Dim SQLConnection

Dim SQLConnectionString

Dim SQLFields,SQLValues

Dim SQLQueryString

Dim SQLCommand

DataBaseName = "xxx"

TableName = "xxx"

Uname = "xxx"

DBPassword = "xxx"

'If you are using WinCC Instance, otherwise put your own instance name here

SQLServer = "xxx"

SQLConnectionString = "Provider=MSDASQL;Persist Security Info=False;User ID=" & Uname & ";Pwd=" & DBPassword & ";Initial Catalog=" & DataBaseName & ";Data Source=" & SQLServer & ""

Set rst = CreateObject("ADODB.Recordset")

Set SQLConnection = CreateObject("ADODB.Connection")

SQLConnection.ConnectionString = SQLConnectionString

SQLConnection.Open

Set SQLCommand = CreateObject("ADODB.Command")

SQLCommand.ActiveConnection = SQLConnection

SQLQueryString = "SELECT * FROM [CORDIVARI].[dbo].[Produzione_Giornaliere] ( '2018-05-03' , 108)"

Set rst = SQLConnection.Execute(SQLQueryString)

rst.MoveFirst

SmartTags("VALORE_SQL") = rst.Fields(0).Value

rst.close

SQLCommand.CommandText = SQLQueryString

SQLCommand.Execute

Set SQLCommand = Nothing

SQLConnection.Close

Set SQLConnection = Nothing

Set rst = Nothing

 Why the actual code does not work? Where is the error?

The query was created to return only one INT value, so i expect a single value but nothing appears.

Thank you

Put a "MsgBox" in the middle of the code to see how far your code goes.
To locate the error.

Have you set up ODBC?

Dim TableName, Uname, DBPassword, DataBaseName, SQLServer, rst

Dim SQLConnection

Dim SQLConnectionString

Dim SQLFields,SQLValues

Dim SQLQueryString

Dim SQLCommand

MsgBox "_____________________________ 1"

DataBaseName = "xxx"

TableName = "xxx"

Uname = "xxx"

DBPassword = "xxx"

MsgBox "_____________________________ 2"

'If you are using WinCC Instance, otherwise put your own instance name here

SQLServer = "xxx"

SQLConnectionString = "Provider=MSDASQL;Persist Security Info=False;User ID=" & Uname & ";Pwd=" & DBPassword & ";Initial Catalog=" & DataBaseName & ";Data Source=" & SQLServer & ""

MsgBox "_____________________________ 3"
Set rst = CreateObject("ADODB.Recordset")

Set SQLConnection = CreateObject("ADODB.Connection")

MsgBox "_____________________________ 4"
SQLConnection.ConnectionString = SQLConnectionString

MsgBox "_____________________________ 5"

SQLConnection.Open

Set SQLCommand = CreateObject("ADODB.Command")

MsgBox "_____________________________ 6"

SQLCommand.ActiveConnection = SQLConnection

MsgBox "_____________________________ 7"

SQLQueryString = "SELECT * FROM [CORDIVARI].[dbo].[Produzione_Giornaliere] ( '2018-05-03' , 108)"

MsgBox "_____________________________ 8"
Set rst = SQLConnection.Execute(SQLQueryString)

rst.MoveFirst

MsgBox "_____________________________ 9"

SmartTags("VALORE_SQL") = rst.Fields(0).Value

MsgBox "_____________________________ Value" & rst.Fields(0).Value

MsgBox "_____________________________ 10"

rst.close

MsgBox "_____________________________ end"
SQLCommand.CommandText = SQLQueryString

SQLCommand.Execute

Set SQLCommand = Nothing

SQLConnection.Close

Set SQLConnection = Nothing

Set rst = Nothing

Hi,

i haven't configured the ODBC, how to do this? and what must i set?

Tomorrow i'll do the MsgBox implementation and i'll give you a feedback.

Thank you

cordivari

Hi,

i haven't configured the ODBC, how to do this? and what must i set?

Tomorrow i'll do the MsgBox implementation and i'll give you a feedback.

Thank you

 Look at Chapter 2.3 for ODBC.

LINK

Ok, i configured the ODBC following the guide.

i restart the script changing MSDASQL TO SQLOLEDB and it magically works :)

Thank you very much for support!