Sign In Help
Schneider Electric
HelpSign In
Schneider Electric Exchange
  • Home
  • Collaborate
  • Develop
  • Shop
Home Collaborate Develop Shop Log in or Register Help

Invite a Co-worker

Send a co-worker an invite to the Exchange portal.Just enter their email address and we’ll connect them to register. After joining, they will belong to the same company.
You have entered an invalid email address. Please re-enter the email address.
This co-worker has already been invited to the Exchange portal. Please invite another co-worker.
Please enter email address
Send Invite Cancel

Invitation Sent

Your invitation was sent.Thanks for sharing Exchange with your co-worker.
Send New Invite Close
  • Home
  • Collaborate
  • Exchange Community
  • :
  • SCADA & Telemetry Solutions
  • :
  • Geo SCADA Expert Forum
  • :
  • GeoSCADA VB SQL
Community Menu
  • Forums
    • By Topic
        • EcoStruxure IT
          • EcoStruxure IT forum
        • Industrial Automation
          • Industry Automation and Control Forum
          • Alliance System Integrators Forum
          • Machine Solutions in the Digital Transformation
          • EcoStruxure Automation Expert / IEC 61499 Forum
          • Industrial Edge Computing Forum
          • Level and Pressure Instrumentation Forum
          • Modicon User Group
          • PLC Club Indonesia
          • SEE Automation Club Forum
          • Fabrika ve Makina Otomasyonu Çözümleri
          • Форум по промышленной автоматизации СНГ
        • SCADA & Telemetry Solutions
          • Geo SCADA Expert Forum
          • SCADA and Telemetry Devices Forum
        • Power Distribution IEC
          • Power Distribution and Digital
          • Power Standards & Regulations
          • Paneelbouw & Energie Distributie
          • Eldistribution & Fastighetsautomation
        • Power Distribution Softwares
          • EcoStruxure Power Design Forum
          • SEE Electrical Building+ Forum
          • LayoutFAST User Group Forum
        • Wireless Information Network Solutions
          • Instrument Area Network
          • Remote Monitoring
          • Tank Level Monitoring
          • Remote Data Collection
        • Solutions for your Business
          • Solutions for Food & Beverage Forum
          • Solutions for Healthcare Forum
    • By Segment
        • Food & Beverage
          • Solutions for Food & Beverage Forum
        • Healthcare
          • Solutions for Healthcare Forum
      • EcoStruxure IT
        • EcoStruxure IT forum
      • Industrial Automation
        • Industry Automation and Control Forum
        • Alliance System Integrators Forum
        • Machine Solutions in the Digital Transformation
        • EcoStruxure Automation Expert / IEC 61499 Forum
        • Industrial Edge Computing Forum
        • Level and Pressure Instrumentation Forum
        • Modicon User Group
        • PLC Club Indonesia
        • SEE Automation Club Forum
        • Fabrika ve Makina Otomasyonu Çözümleri
        • Форум по промышленной автоматизации СНГ
      • SCADA & Telemetry Solutions
        • Geo SCADA Expert Forum
        • SCADA and Telemetry Devices Forum
      • Power Distribution IEC
        • Power Distribution and Digital
        • Power Standards & Regulations
        • Paneelbouw & Energie Distributie
        • Eldistribution & Fastighetsautomation
      • Power Distribution Softwares
        • EcoStruxure Power Design Forum
        • SEE Electrical Building+ Forum
        • LayoutFAST User Group Forum
      • Wireless Information Network Solutions
        • Instrument Area Network
        • Remote Monitoring
        • Tank Level Monitoring
        • Remote Data Collection
      • Solutions for your Business
        • Solutions for Food & Beverage Forum
        • Solutions for Healthcare Forum
      • Food & Beverage
        • Solutions for Food & Beverage Forum
      • Healthcare
        • Solutions for Healthcare Forum
  • Blogs
    • By Topic
        • Industrial Automation
          • Industrial Edge Computing Blog
          • Industry 4.0 Blog
          • Industrie du Futur France
        • SCADA & Telemetry Solutions
          • SCADA and Telemetry Blog
        • Power Distribution IEC
          • Power Events & Webinars
          • Power Foundations Blog
        • Power Distribution NEMA
          • NEMA Power Foundations Blog
        • Power Distribution Softwares
          • EcoStruxure Power Design Blog
          • SEE Electrical Building+ Blog
        • Solutions for your Business
          • Solutions for Food & Beverage Blog
          • Solutions for Healthcare Blog
          • Solutions for Retail Blog
        • Community experts & publishers
          • Publishers Community
    • By Segment
        • Food & Beverage
          • Solutions for Food & Beverage Blog
        • Healthcare
          • Solutions for Healthcare Blog
        • Retail
          • Solutions for Retail Blog
      • Industrial Automation
        • Industrial Edge Computing Blog
        • Industry 4.0 Blog
        • Industrie du Futur France
      • SCADA & Telemetry Solutions
        • SCADA and Telemetry Blog
      • Power Distribution IEC
        • Power Events & Webinars
        • Power Foundations Blog
      • Power Distribution NEMA
        • NEMA Power Foundations Blog
      • Power Distribution Softwares
        • EcoStruxure Power Design Blog
        • SEE Electrical Building+ Blog
      • Solutions for your Business
        • Solutions for Food & Beverage Blog
        • Solutions for Healthcare Blog
        • Solutions for Retail Blog
      • Community experts & publishers
        • Publishers Community
      • Food & Beverage
        • Solutions for Food & Beverage Blog
      • Healthcare
        • Solutions for Healthcare Blog
      • Retail
        • Solutions for Retail Blog
  • Ideas
        • Industrial Automation
          • Modicon Ideas & new features
        • SCADA & Telemetry Solutions
          • Geo SCADA Expert Ideas
          • SCADA and Telemetry Devices Ideas
  • Knowledge Center
    • Building Automation Knowledge Base
    • Industrial Automation Knowledge Base
    • Industrial Automation How-to videos
    • SCADA & Telemetry Solutions Knowledge Base
    • Digital E-books
    • Success Stories Corner
    • Power Talks
  • Events & Webinars
    • Innovation Talks
    • Innovation Summit
    • Let's Exchange Series
    • Technology Partners
  • Support
    • Ask Exchange
    • Leaderboard
    • Our Community Guidelines
    • Community User Guide
    • How-To & Best Practices
    • More
Join Now
How can we help?
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
51132members
Join Now
245262posts
Join Now

GeoSCADA VB SQL

Options
  • Subscribe to RSS Feed
  • Mark Topic as New
  • Mark Topic as Read
  • Float this Topic for Current User
  • Bookmark
  • Subscribe
  • Mute
  • Printer Friendly Page
Solved Go to Solution
Back to Geo SCADA Expert Forum
Solved
DavidSkilbeck
Lt. Commander DavidSkilbeck
Lt. Commander
‎2020-06-25 11:49 PM
0 Likes
11
1066
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-06-25 11:49 PM

GeoSCADA VB SQL

Hi, I am trying to use the below SQL statement to retrieve the lowest number in a column of a table.

 

SELECT MIN(column_name) FROM table_name

 

Is this SQL statement usable in GeoSCADA or does it require different syntax?

Thanks,

Solved! Go to Solution.

Labels
  • SCADA
Tags (1)
  • Tags:
  • english
Share
Reply

Accepted Solutions
DavidSkilbeck
Lt. Commander DavidSkilbeck
Lt. Commander
‎2020-06-29 05:35 PM
0 Likes
2
1004
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-06-29 05:35 PM

Re: GeoSCADA VB SQL

Hi, thanks Stephen and Bevan.

 

I appreciate your answers.

The solution to keep it simple, is to use the syntax as below.
This is suffice for what I need in my particular scenario.


Set DeleteMinRow= Server.Query("DELETE FROM WorkOrderData WHERE WorkOrderNo = (SELECT MIN(WorkOrderNo) FROM WorkOrderData) ")

Thanks,

See Answer In Context

Tags (1)
  • Tags:
  • english
Share
Reply
  • All forum topics
  • Previous Topic
  • Next Topic
11 Replies 11
sbeadle
Sisko sbeadle Sisko
Sisko
‎2020-06-26 01:07 AM
0 Likes
9
1062
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-06-26 01:07 AM

Re: GeoSCADA VB SQL

That will work. The column needs to be numeric.

If you want to show more columns you would need a GROUP BY

e.g.

SELECT
MAX( ID ), NAME
FROM
CDBOBJECT
GROUP BY
NAME

 

F1 help contains a syntax guide to the SQL engine.

Tags (1)
  • Tags:
  • english
Share
Reply
BevanWeiss
Sisko BevanWeiss
Sisko
‎2020-06-26 08:59 PM
0 Likes
0
1048
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-06-26 08:59 PM

Re: GeoSCADA VB SQL

As Steve said, that's a valid SQL query.

But... given the subject of your question is 'GeoSCADA VB SQL', your question appears to leaving out the VB part (Visual Basic?.. important to note that GeoSCADA allows the use of VBscript in mimics... but NOT Visual Basic)... so I suspect there's more to it than you asked about.


Lead Control Systems Engineer for Alliance Automation (VIC).
All opinions are my own and do not represent the opinions or policies of my employer, or of my cat..
Tags (1)
  • Tags:
  • english
Share
Reply
DavidSkilbeck
Lt. Commander DavidSkilbeck
Lt. Commander
‎2020-06-28 04:28 PM
0 Likes
8
1040
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-06-28 04:28 PM

Re: GeoSCADA VB SQL

Hi, the code runs untill MsgBox.
I am trying to get the value of "FirstRowTable" to display in a Msg Box.

Sub DeleteFirstRow()

Dim objectTable
Dim Name
Dim SelectedRow
Dim SQLStatement
Dim FirstRowTable

 

Set objectTable = Server.FindObject("PoC.Web Service.Work Order.Work Order Table.XXXXX")

SQLStatement = "SELECT MIN(ColumnA) FROM XXXXX"

Set FirstRowTable= Server.Query(SQLStatement)

'Mimic.Layers(0).Item("Lst_1").Sql = Mimic.Layers(0).Item("Lst_1").Sql

MsgBox (FirstRowTable)

End Sub

 

 

 

Tags (1)
  • Tags:
  • english
Share
Reply
BevanWeiss
Sisko BevanWeiss
Sisko
‎2020-06-28 08:21 PM
0 Likes
7
1030
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-06-28 08:21 PM

Re: GeoSCADA VB SQL

When you say 'the value of "FirstRowTable" to display in a Msg Box'.. what do you think the data type of "FirstRowTable" will actually be?  and how would that 'look' in a MsgBox?

 

I'll cut to the chase, it won't work.  FirstRowTable is not a datatype that will EVER work as a parameter to a MsgBox.  MsgBox is expecting a string.  FirstRowTable is NOT a string.  It is a ServerQuery object.

 

So.. what you should have, is something closer to the Server.Query example code in the help (below)

 

Set DGRS = Server.Query("SELECT MIN(ColumnA) FROM XXXXX")

If Not(DGRS.Error) Then

  DGRows = DGRS.Rows

  DGColNames = DGRS.ColumnNames

  For c = 0 To (DGRS.ColumnCount - 1)

    MsgBox DGColNames(c)

  Next

  For r = 0 To (DGRS.RowCount - 1)

    string = ""

    For c = 0 to (DGRS.ColumnCount - 1)

      string = string & DGRows(r,c) & " , "

    Next

    Msgbox Cstr(r) & string

  Next

Else

  Msgbox DGRS.ErrorMessage, vbCritical + vbOKOnly, "Error in SQL Command"

End If

 

You'll also want to take care around your use of XXXXX, the way that you've used them makes me suspect that you might think the SQL TableName for the DataTable IS the Name of the object in the ClearSCADA Database.

This is NOT the case.

The SQL TableName is the 'TableName' OPC property field of the DataTable / DataGrid, so you'd need to open up the Properties of the Table object to find it.


Lead Control Systems Engineer for Alliance Automation (VIC).
All opinions are my own and do not represent the opinions or policies of my employer, or of my cat..
Tags (1)
  • Tags:
  • english
Share
Reply
DavidSkilbeck
Lt. Commander DavidSkilbeck
Lt. Commander
‎2020-06-28 10:47 PM
0 Likes
6
1028
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-06-28 10:47 PM

Re: GeoSCADA VB SQL

Hi, I replaced the string to TestResult to get the code to run. When the code is run a message box appears with "min(WorkOrderNo)". Press OK.

Then another message box with "Variable uses an automation type not supported in VBScript".

Referring to this line of code - TestResult = TestResult & DGRows(r,c) & " , ".
Thanks,

 

 

 

Tags (1)
  • Tags:
  • english
Share
Reply
sbeadle
Sisko sbeadle Sisko
Sisko
‎2020-06-29 12:56 AM
0 Likes
5
1020
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-06-29 12:56 AM

Re: GeoSCADA VB SQL

These lines are important: DGRows = DGRS.Rows DGColNames = DGRS.ColumnNames (Particularly the first). Accessing the data in secondary variables this way seems to avoid a VB issue where the structure is not interpreted correctly.
Share
Reply
sbeadle
Sisko sbeadle Sisko
Sisko
‎2020-06-29 02:12 AM
0 Likes
4
1019
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-06-29 02:12 AM

Re: GeoSCADA VB SQL

Better still, why not wrap the query into a class to give Record handling functions?

Add the following into a Script Library and reference that library from your script:

'******************************************************************************* 
Class ResultSet
	Public SQL
	Public QueryObj
	Private Dic
	Public CurRow
	Public Rows
	
	Sub Class_Initialize()
		Set Dic = CreateObject("Scripting.Dictionary")		
	End Sub
	
	Public Sub Execute(SQL)
		Set QueryObj = Server.Query(SQL)
		ColNames = QueryObj.ColumnNames
		For i = 0 To QueryObj.ColumnCount - 1
			Dic.Add LCase(ColNames(i)), i
		Next
		Rows = QueryObj.Rows
		CurRow = 0
	End Sub
	
	Public Sub ExecuteS( myServer, SQL)
		Set QueryObj = myServer.Query(SQL)
		ColNames = QueryObj.ColumnNames
		For i = 0 To QueryObj.ColumnCount - 1
			Dic.Add LCase(ColNames(i)), i
		Next
		Rows = QueryObj.Rows
		CurRow = 0
	End Sub

	Public Property Get ColumnIndex(Name)
		'If the column has a space, it is surrounded by quote marks. 
		ColumnIndex = Dic.Item(Replace(LCase(Name),"""",""))
	End Property
	
	Public Property Get Fields(Name)				
		If Not EOF Then
			ColIndex = ColumnIndex(Name)
			Fields = Rows(CurRow, ColIndex)
		Else
			Err.Raise vbObjectError + 1, "RecordSet", "EOF"
		End If	
	End Property
	
	Public Property Get EOF()
		If CurRow = QueryObj.RowCount Then
			EOF = True
		Else
			EOF = False
		End If
	End Property
	
	Public Property Get BOF()
		If CurRow = 0 Then 
			BOF = True
		Else
			BOF = False
		End If
	End Property
	
	Public Sub MoveNext()
		If Not EOF Then
			CurRow = CurRow + 1
		Else
			Err.Raise vbObjectError + 1, "RecordSet", "EOF"
		End If
	End Sub
	
	Public Sub MoveFirst()
		CurRow = 0 
	End Sub
	
	Public Sub MoveLast()
		CurRow = q.RowCount
	End Sub
	
	Public Sub MovePrev()
		CurRow = CurRow - 1
		If CurRow < 0 Then CurRow = 0
	End Sub
End Class

 

Now you can write code like this:

Set rs = New ResultSet
sql = "SELECT ID, FULLNAME AS FNAME FROM CMimic"
rs.Execute sql
If rs.QueryObj.RowCount = 0 Then
	Msgbox "No Mimics found"
	Exit Sub
End If
Do While Not rs.EOF
	m = rs.Fields("FNAME")
	'Do something
	rs.MoveNext
Loop

 

 

Tags (1)
  • Tags:
  • english
Share
Reply
DavidSkilbeck
Lt. Commander DavidSkilbeck
Lt. Commander
‎2020-06-29 01:47 PM
0 Likes
0
1005
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-06-29 01:47 PM

Re: GeoSCADA VB SQL

Hi, if the SQL statement is changed to "SELECT * FROM WorkOrderData", then the code steps through the names of the columns.
Until it crashes. Thanks,

Tags (1)
  • Tags:
  • english
Share
Reply
DavidSkilbeck
Lt. Commander DavidSkilbeck
Lt. Commander
‎2020-06-29 05:35 PM
0 Likes
2
1005
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-06-29 05:35 PM

Re: GeoSCADA VB SQL

Hi, thanks Stephen and Bevan.

 

I appreciate your answers.

The solution to keep it simple, is to use the syntax as below.
This is suffice for what I need in my particular scenario.


Set DeleteMinRow= Server.Query("DELETE FROM WorkOrderData WHERE WorkOrderNo = (SELECT MIN(WorkOrderNo) FROM WorkOrderData) ")

Thanks,

See Answer In Context

Tags (1)
  • Tags:
  • english
Share
Reply
BevanWeiss
Sisko BevanWeiss
Sisko
‎2020-06-29 05:58 PM
0 Likes
1
996
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-06-29 05:58 PM

Re: GeoSCADA VB SQL

You clearly need to work on how you ask questions.

 

Your problem was

"I am trying to use the below SQL statement to retrieve the lowest number in a column of a table."

"I am trying to get the value of "FirstRowTable" to display in a Msg Box."

 

and now you mark your own answer as 'The Solution' when it doesn't display a thing, doesn't retrieve the lowest number in a column of a table, doesn't display a MsgBox... so it doesn't solve the problem that you asked people for help with.


Lead Control Systems Engineer for Alliance Automation (VIC).
All opinions are my own and do not represent the opinions or policies of my employer, or of my cat..
Tags (1)
  • Tags:
  • english
Share
Reply
DavidSkilbeck
Lt. Commander DavidSkilbeck
Lt. Commander
‎2020-06-29 07:16 PM
0 Likes
0
992
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2020-06-29 07:16 PM

Re: GeoSCADA VB SQL

Hi Bevan, yes I will try to declare the ultimate goal next time in the first post.

 

I jumped a step thinking it would be give me the first step to resolving the ultimate goal.

A number of syntax combinations had been tested prior to the first post and unfortunately I had not found the correct syntax.

 

I couldn't get the code to work and give me the result I required.

If I get chance to return to it I will do so. However, at the moment I have to move on to my next task.


I will bear this in mind and give my ultimate goal in the next post, I post.

Thanks,

 

 

Tags (1)
  • Tags:
  • english
Share
Reply
Top Experts
User Count
sbeadle
Sisko sbeadle Sisko
188
BevanWeiss
Sisko BevanWeiss
52
AdamWoodland
Lt. Commander AdamWoodland Lt. Commander
16
JChamberlain
Lieutenant JChamberlain Lieutenant
16
AndrewScott
Lieutenant AndrewScott
15
See More Top Experts
Find a Service Provider
Find a certified partner to help you address your integration, installation, maintenance and project needs.
View all Providers
Support

Have a question? Please contact us with details, and we will respond.

Contact Us
FAQ

Look through existing questions to find popular answers.

Learn More
About

Want to know more about Exchange and its possibilities?

Learn More

Full access is just steps away!

Join Exchange for FREE and get unlimited access to our global community of experts.

Connect with Peers & Experts

Discuss challenges in energy and automation with 30,000+ experts and peers.

Get Support in Our Knowledge Base

Find answers in 10,000+ support articles to help solve your product and business challenges.

Ask Questions. Give Solutions

Find peer based solutions to your questions. Provide answers for fellow community members!

Register today for FREE

Register Now

Already have an account?Log in

About Us FAQ Terms & Conditions Privacy Notice Change your cookie settings
©2020, Schneider Electric