Indusoft (Aveva Edge) VbScript to find a string in a column and return line values

[lunenburger]

Thank you for the help... I have been looking through the Db help in Indusoft

It is just frustrating it took no time to do it using the ID number, but using a string value is proving extremely difficult.

Really, I just want to know what row number a matching string is in.....

 

[lunenburger]

Norm,
What does the "ITEMNUM LIKE " mean?

What is it referring to?

and what is the $Str

thank you

 

[ndzied1]

In my table named Item, there is a column named ITEMNUM.

After I scanned the barcode I was left with a tag of type number. But the WHERE clause must be a string so the $Str() converts the number to a string. We already talked about the ticks or $Chr(39)

In human speak the general idea is to open the table Item and search for a record where the value in the column ITEMNUM is the same as the value in the tag ItemNum converted to a string.

 

[lunenburger]

So I can't get it to work using DBCursorOPenSql

So I am trying to use the DBSelect function......

Everytime I type in the sCriteria I get a syntax error.

this is how I am typing it in according to post#9

sCriteria = "scan_code "   & ' & ($RecipeScan) & '

scan_code is the column in the database and $RecipeScan is the string value in Indusoft.

Am I missing something in the sCriteria?????

 

[ndzied1]

I would make my sCriteria (note there is nothing magic about this name, it's just a string variable defined in Indusoft). This assumes that your Tag RecipeScan is a string type.

sCriteria = "scan_code LIKE " & $Chr(39) & $RecipeScan & $Chr(39)

So, for instance if your RecipeScan was ChocolateChip, this should result in sCriteria being set to the string

scan_code LIKE 'ChocolateChip'

 

[ndzied1]

also, watch the case of the things you enter. Best if they match exactly

 

[lunenburger]

Thanks again Norm....

I am sorry to be dragging this on

But the goal is to return the line number that the recipe name is on (see Pic)
It would be an integer value

Once I have the integer value, I can load the different recipe parameters into the PLC program.

 

[drbitboy]

So I can't get it to work using DBCursorOPenSql

So I am trying to use the DBSelect function......

Everytime I type in the sCriteria I get a syntax error.

this is how I am typing it in according to post#9

sCriteria = "scan_code "   & ' & ($RecipeScan) & '

scan_code is the column in the database and $RecipeScan is the string value in Indusoft.

Am I missing something in the sCriteria?????

yes.


1)


it should be "scan_code=" & ...



2)


type in Chr(39), literally*, instead of those ticks. If there is a tick character in a statement and not within quotes, I think it makes VBScript ignore both that tick character and everything that follows on that line.



* or tick, where the variable tick = Chr(39)

 

[drbitboy]

Thanks again Norm....

I am sorry to be dragging this on

you appear to be using navigator. I assume you typed in that SELECT statement "Action" in the image above.


Try typing this instead:

SELECT * FROM meerer12.recipe_list WHERE scan_code=[COLOR=Blue][I][B]'67101M Ver:1'[/B][/I][/COLOR];

Note the ticks, which would be inside the (double) quotes in the VBScript statement.


And what do you see?

 

[ndzied1]

Sorry I’m actually off today working mostly on my phone. But once you have the row number don’t you have to look up the information in that row to get the recipe parameters? DMSelect() does that

 

[drbitboy]

When you did this:

 sql="Select * FROM machine12.`recipe_list`Where ID="&$RecipeValue&""

in Post #1, if $RecipeValue was e.g. the integer 5, then the string sql, that was passed to the SQL, was this:

Select * from machine12.`recipe_list`Where ID=5

Then SQL parsed that [5] at the end as the immediate integer value 5.


When you did this:

 sql="Select * FROM machine12.`recipe_list`Where RecipeName="&$RecipeName&""

in Post #1, if the string $RecipeName is e.g. "George", then the string sql is

Select * FROM machine12.`recipe_list`Where RecipeName=George

and SQL will parse that [George] as a column name, the same as it does for that [RecipeName], and throw an error because that table does not have a column named [George].


If $RecipeName is e.g. "67101M Ver:1" then the string sql is

Select * FROM machine12.`recipe_list`Where RecipeName=67101M Ver:1

and SQL will again throw an error because of at least one of the following

• 67101M starts with a number but the string [67101M] is not a valid number
• the token [Ver:1] after the space is not a valid keyword
• the colon [:] in Ver:1 is invalid outside enclosing ticks in an SQL statement

SQL is a language, just as VBScript is a language. SQL has a syntax, and trying to express an SQL statement as a string in VBScript is going to be messy (witness the &s). So it might be worth learning SQL syntax (instead of bouncing pillar to post like a kajillion programmers randomly banging on keyboards trying to write Shakesepeare ).

 

[drbitboy]

Oh dear, I just read this: http://webstudio.helpmax.net/en/app...nguage/databaseerp-functions/dbcursoropensql/


Try either this:

 sql = "SELECT * FROM meerer12.recipe_list WHERE scancode={RecipeName}"

$numCur=$DBCursorOpenSQL("MySqlDB",sql)

OR

 sql = "SELECT * FROM meerer12.recipe_list WHERE scancode='{RecipeName}'"

$numCur=$DBCursorOpenSQL("MySqlDB",sql)

One of those might work; the curly braces might be the equivalent of the query/?/question mark.


Note that RecipeName does not have the preceding $ in the sql string assignment.

 

[parky]

I don't understand why you need the line number (ID), if you have the recipe name (I assume you mean bar code) then you have already found it.
Here is a snippet of one I did some years ago.
There is a combo box in the code before that I populate with the recipe name this would be the same as your bar code in effect, so the operator is given a list box of all recipes stored, the operator selects the recipe and the code attached (well this is part of it, as it is far more complicated with linked fields).
So it loads all the values in the row, no need for the id providing the bar code name is unique which I assume would have to be or how would you select which one.
Here we select the recipe stages based on the recipe name and load them into internal tags (thisis required on my system as there is some processing to do before putting the data into the PLC Tags.
Note: the underscore is a way of splitting the string and ignoring Carriage return that the editor puts in, it makes reading the string easier but could be just one long string without the _.

SQLStr = "SELECT * FROM Recipe_Stages " _
& "WHERE Rec_Name='" & Format$(RequestedRecName) & "' " _
& "ORDER BY Stage_Num"
Rs1.Open SQLStr,Conn1,,,
While Not Rs1.EOF
For i = 1 To uRecipe.StagesUsed
With uRecipe.Stages(i)
.IngType = Rs1!Ingredient_Type
.NumIngs = Rs1!Num_Ingredients
.WeightTotal = Rs1!Weight_Total
.WeightTol = Rs1!Weight_Tol
.TempTarg = Rs1!Temperature
.BldStatus = Rs1!Blender_Status
.BldSpeed = Rs1!Blender_Speed
.AgStatus = Rs1!Agitator_Status
.AgSpeed = Rs1!Agitator_Speed
.Duration = Rs1!Duration
.CookOn = Rs1!Cook_On
.Reduction = Rs1!Reduction
.BulkYield = Rs1!Bulk_Yield
End With
Rs1.MoveNext
Next
Wend
Rs1.Close

This is of course far more complex than what you are doing but the principle is the same.

Here is a snippet of where we log the batch details to the database, although this populates it you can see the way we format the query string using quotes which is the same as reading the recipe

Dim GroupStr1,SQLStr, BatchIDStr,Pass,UpTime,UpWeight,UpReqWeight,UpTemp,UpReqTemp,Stage As String
Dim Rs1 As New ADODB.Recordset
Dim Conn1 As New ADODB.Connection
Dim Times,r As Integer
Dim Weight, ReqWeight,Temp,ReqTemp As Double
Sub Main()
On Error GoTo Err
Pass = This.GetParameter(0)
Select Case Pass
Case "1"
GroupStr1 = "CK1_CK1_"
Case "2"
GroupStr1 = "CK2_CK2_"
Case "3"
GroupStr1 = "CK2_CK2_"
End Select
Stage = CStr(GetVariableValue(GroupStr1 & "Scada_Store_Stage_Num"))
BatchIDStr = GetVariableValue(GroupStr1 & "Cooker_Batch_Code")
UpTime = "S" & Stage & "Time"
UpWeight = "S" & Stage & "Weight"
UpReqWeight = "S" & Stage & "ReqWeight"
UpTemp = "S" & Stage & "Temp"
UpReqTemp = "S" & Stage & "ReqTemp"

Times = GetVariableValue(GroupStr1 & "Scada_Store_Stage_Duration")
Weight = GetVariableValue(GroupStr1 & "Scada_Store_Stage_Weight")
ReqWeight = GetVariableValue(GroupStr1 & "Scada_Store_Stage_ReqWeight")
Temp = GetVariableValue(GroupStr1 & "Scada_Store_Stage_Temp")
ReqTemp = GetVariableValue(GroupStr1 & "Scada_Store_Stage_ReqTemp")
Conn1.ConnectionString = "dsn=SP3DB;uid='sa';pwd='';"
SQLStr = "UPDATE Cooker_Batch SET " & UpTime & " =" & Times & ", " & UpWeight & " =" & Weight & ", " & UpReqWeight & " = " & ReqWeight & ", " & UpTemp & " = " & Temp & ", " & UpReqTemp & " = " & ReqTemp & " Where BatchID =" & BatchIDStr & "
Conn1.Open
Conn1.Execute SQLStr
Conn1.Close
Set Conn1 =Nothing
SetVariableValue(GroupStr1 & "Scada_Stage_Complete_Trig",False)
Debug.Print Str(Error) + " Batch Stage " & Stage & " Added For Cooker " & Pass

Exit Sub
Err:

Debug.Print Str(Error) + " Batch Stage Log Failed For Cooker " & Pass
SetVariableValue(GroupStr1 & "Scada_Stage_Complete_Trig",False)
Exit Sub
End Sub

 

[drbitboy]

SQLStr = "SELECT * FROM Recipe_Stages " _
& "WHERE Rec_Name='" & Format$(RequestedRecName) & "' " _
& "ORDER BY Stage_Num"

^^^THIS


@parky's example would result in VBScript/IWS variable (tag) SQLStr, which will be passed to SQL, having this value:

[COLOR=Green]SELECT * FROM Recipe_Stages WHERE Rec_Name=[SIZE=5][B][COLOR=Red]'[/COLOR][/B][/SIZE]The requested record name[/COLOR][COLOR=Green][SIZE=5][COLOR=Green][B][COLOR=Red]'[/COLOR][/B][/COLOR][/SIZE] [/COLOR][COLOR=Green]ORDER BY Stage_Num[/COLOR]

assuming e.g. the string variable RequestedRecName had the string value "The requested record name"


N.B. the ticks are oversized and highlighted in red

 

[ndzied1]

This whole thread is getting pretty squirrely. I made a demo project with SQLExpress and VisDes that I hope will help you. I think it does exactly what you are trying to do. Here is a link to a video of me going through the demo.

https://youtu.be/gLspi3UscUk