• Replies 10 replies
  • Subscribers 1 subscriber
  • Views 93 views
  • Users 0 members are here
Options
Related

retrieving data from MS-SQL database to GRID

Admin
HI

i have a MS SQL database with below columns and their data types as below, meanwhile in indusoft i configured a GRID and using the same name as for the grid field column, and the type is as below :-

[Column Name] [MSSQL-TYPE] [INDUSOFT GRID-TYPE]

ID -------------------------- INT (primary key)------- NUMERIC
CamID---------------------INT ------------------------- NUMERIC
CamStartTime-----------TIME------------------------TIME
CamStartDate-----------DATE-----------------------DATE
CamEntryNbr------------VARCHAR(50)-----------TEXT
CamExitNbr--------------VARCHAR(50)-----------TEXT
CamStatus---------------BIT (true/false)-----------TEXT
CreateDATE-------------DATE-----------------------DATE

A single row in SQL Database is as below:-

1 209 09:05:19 2020-04-24 16 11 True 2020-06-02

i have configured Database and when i just put the table name in the Data Source setting (image attached) options in indusoft and execute it,
i put the table name as below:

Name: [cameraidtag]

The GRID can retrieved the MS SQL database according to the table name (cameraidtag). so database reading connection all ok.
and the data is loaded into the grid as below:-

1 209 09:05:19 24-04-2020 16 11 True 02-06-2020

my problem when i create a SQL command to select any data using some conditions, im not sure how to syntactically create a SQL query command in indusoft and load the grid accordingly.

for e.g in the Microsoft SQL query function i can execute this query successfully :-

SELECT * FROM [dbo].[aztitechCrowdCam209] WHERE [ID] BETWEEN 8 AND 20, and i get all the rows from 8th until 20th

So i try the same from indusoft scripts

in the DataSource setting Table tab i wrote this:-

Name: [cameraidtag] WHERE [gridcondition]

So when i click a button, in the button script i update the conditions as below:

$gridCondition = "ID BETWEEN 5 AND 20"

i get invalid object error. it doesnt work.

Database: Error: Invalid object name 'aztitechcrowdcam209 WHERE ID BETWEEN 5 AND 20'........... and its long error message,i just paste some here.

So the grid only gets loaded when i put only the table name in the [cameraidtag] without any condition, how to use with conditions??

i even try to used integer tags as below, i preload the tags with 8 and 20 but i get same error:-

$gridCondition = "ID BETWEEN "&$RowIDmin&" And "&$RowIDmax&""

Can anybody help for above, if i can successfully do above first, then i need to do this which is my ultimate aim:-

i should be able to compare two dates and get the data i want in between, in MS SQL query window i can successfully execute this:-

SELECT * FROM [dbo].[aztitechCrowdCam209] WHERE [CamStartDate] BETWEEN '2020-04-25' AND '2020-06-02' and get the results.

but the same as above , when i try executing similar query from indusoft SQL i get the same invalid object error:-

e.g, i used :-

$gridCondition = "CamstartDate BETWEEN '"&$DateFrom&"' AND '"$DateTo"'"

above script syntax is already error, and i not sure how to write it....

and my question for date comparison is , the indusoft tag only have string tag (both above are strings tags), i can get date-from and date-to using a datetimepicker but it returns the date in strings, can i just direct use strings dates for comparison or i need to convert it??

i can pick the dates as below and load it into a strings tags and plan to use in my condition based SQL command:-

$DateFrom =FormatDateTime($XGet("DateTimePicker1_from","Value.date"),0)
$DateTo =FormatDateTime($XGet("DateTimePicker2_to","Value.date"),0)

how to write correct sql command to get the data inbtwn the picked dates??














  • I think you need square brackets around your column names - [ID] BETWEEN 8 AND 20
  • [quote]
    Posted By Norman E on 11 Jun 2020 03:14 PM
    I think you need square brackets around your column names - [ID] BETWEEN 8 AND 20
    [/quote]


    i try as this, at the Data Source setting table, name: {cameraidtag} WHERE {gridCondition}


    so i already preloaded cameraidtag with the table name, and for the gridcondition is a below:

    $gridCondition = "[ID] BETWEEN "&$RowIDmin&" And "&$RowIDmax&" "

    i get syntax error when i put the bracket, and i just did it for another column using simpler command as below:-

    $gridCondition = "[CamEntryNbr] BETWEEN '5' AND '20'"

    same i get syntax error, but if i remove the brackets and construct it as below:-

    $gridCondition = "CamEntryNbr BETWEEN '5' AND '20'"

    i get below error:

    Database: Error: Invalid object name 'aztitechcrowdcam209 WHERE CamEntryNbr BETWEEN '5' AND '20''.<ConStr: Provider=SQLOLEDB.1; Integrated Security=SSPI; Initial Catalog=AztiCrowdcamDB; Data Source=SHOGHI-J5DB2BD\SQLEXPRESS><Table: aztitechcrowdcam209 WHERE CamEntryNbr BETWEEN '5' AND '20'><SQL: SELECT [ID], [CamID], [CamStartTime], [CamStartDate], [CamEntryNbr], [CamExitNbr], [CamStatus], [CreateDATE] FROM [aztitechcrowdcam209 WHERE CamEntryNbr BETWEEN '5' AND '20']>[CMD_QUERY] [Connection String: 'Provider=SQLOLEDB.1; Integrated Security=SSPI; Initial Catalog=AztiCrowdcamDB; Data Source=SHOGHI-J5DB2BD\SQLEXPRESS', Table: 'aztitechcrowdcam209 WHERE CamEntryNbr BETWEEN '5' AND '20'']

    if i breakdown the error messages above i get as below:-

    Database: Error: Invalid object name 'aztitechcrowdcam209 WHERE CamEntryNbr BETWEEN '5' AND '20''.

    <ConStr: Provider=SQLOLEDB.1; Integrated Security=SSPI; Initial Catalog=AztiCrowdcamDB; Data Source=SHOGHI-J5DB2BD\SQLEXPRESS>

    <Table: aztitechcrowdcam209 WHERE CamEntryNbr BETWEEN '5' AND '20'>

    <SQL: SELECT [ID], [CamID], [CamStartTime], [CamStartDate], [CamEntryNbr], [CamExitNbr], [CamStatus], [CreateDATE] FROM [aztitechcrowdcam209 WHERE CamEntryNbr BETWEEN '5' AND '20']>

    [CMD_QUERY] [Connection String: 'Provider=SQLOLEDB.1; Integrated Security=SSPI; Initial Catalog=AztiCrowdcamDB; Data Source=SHOGHI-J5DB2BD\SQLEXPRESS', Table: 'aztitechcrowdcam209 WHERE CamEntryNbr BETWEEN '5' AND '20'']









  • Hi there. You need to disable the delimiters from the database advanced settings, so that IWS cannot modify your query by inserting the brackets [] after the FROM keyword. Right now IWS assumes that all this string that you placed on the table field is a table and not a complex query.
  • HI

    i have a MS SQL database with below columns and their data types as below, meanwhile in indusoft i configured a GRID and using the same name as for the grid field column, and the type is as below :-

    [Column Name] [MSSQL-TYPE] [INDUSOFT GRID-TYPE]

    ID -------------------------- INT (primary key)------- NUMERIC
    CamID---------------------INT ------------------------- NUMERIC
    CamStartTime-----------TIME------------------------TIME
    CamStartDate-----------DATE-----------------------DATE
    CamEntryNbr------------VARCHAR(50)-----------TEXT
    CamExitNbr--------------VARCHAR(50)-----------TEXT
    CamStatus---------------BIT (true/false)-----------TEXT
    CreateDATE-------------DATE-----------------------DATE

    A single row in SQL Database is as below:-

    1 209 09:05:19 2020-04-24 16 11 True 2020-06-02

    i have configured Database and when i just put the table name in the Data Source setting (image attached) options in indusoft and execute it,
    i put the table name as below:

    Name: [cameraidtag]

    The GRID can retrieved the MS SQL database according to the table name (cameraidtag). so database reading connection all ok.
    and the data is loaded into the grid as below:-

    1 209 09:05:19 24-04-2020 16 11 True 02-06-2020

    my problem when i create a SQL command to select any data using some conditions, im not sure how to syntactically create a SQL query command in indusoft and load the grid accordingly.

    for e.g in the Microsoft SQL query function i can execute this query successfully :-

    SELECT * FROM [dbo].[aztitechCrowdCam209] WHERE [ID] BETWEEN 8 AND 20, and i get all the rows from 8th until 20th

    So i try the same from indusoft scripts

    in the DataSource setting Table tab i wrote this:-

    Name: [cameraidtag] WHERE [gridcondition]

    So when i click a button, in the button script i update the conditions as below:

    $gridCondition = "ID BETWEEN 5 AND 20"

    i get invalid object error. it doesnt work.

    Database: Error: Invalid object name 'aztitechcrowdcam209 WHERE ID BETWEEN 5 AND 20'........... and its long error message,i just paste some here.

    So the grid only gets loaded when i put only the table name in the [cameraidtag] without any condition, how to use with conditions??

    i even try to used integer tags as below, i preload the tags with 8 and 20 but i get same error:-

    $gridCondition = "ID BETWEEN "&$RowIDmin&" And "&$RowIDmax&""

    Can anybody help for above, if i can successfully do above first, then i need to do this which is my ultimate aim:-

    i should be able to compare two dates and get the data i want in between, in MS SQL query window i can successfully execute this:-

    SELECT * FROM [dbo].[aztitechCrowdCam209] WHERE [CamStartDate] BETWEEN '2020-04-25' AND '2020-06-02' and get the results.

    but the same as above , when i try executing similar query from indusoft SQL i get the same invalid object error:-

    e.g, i used :-

    $gridCondition = "CamstartDate BETWEEN '"&$DateFrom&"' AND '"$DateTo"'"

    above script syntax is already error, and i not sure how to write it....

    and my question for date comparison is , the indusoft tag only have string tag (both above are strings tags), i can get date-from and date-to using a datetimepicker but it returns the date in strings, can i just direct use strings dates for comparison or i need to convert it??

    i can pick the dates as below and load it into a strings tags and plan to use in my condition based SQL command:-

    $DateFrom =FormatDateTime($XGet("DateTimePicker1_from","Value.date"),0)
    $DateTo =FormatDateTime($XGet("DateTimePicker2_to","Value.date"),0)

    how to write correct sql command to get the data inbtwn the picked dates??














  • Hi.

    You can set the over-riding Indusoft date format within the Indusoft 'Program Settings.ini' file.

    [International]
    Order=DMY

    The default is MDY.

    You can also set this within the project 'Startup Script', for example:

    $SetDateFormat("/", "DMY")

    Hope this is of use.

    Regards,
    Greg Shearer
  • [quote]
    Posted By G Shearer on 14 Jun 2020 09:14 PM
    Hi.

    You can set the over-riding Indusoft date format within the Indusoft 'Program Settings.ini' file.

    [International]
    Order=DMY

    The default is MDY.

    You can also set this within the project 'Startup Script', for example:

    $SetDateFormat("/", "DMY")

    Hope this is of use.

    Regards,
    Greg Shearer

    [/quote]

    hi i did quick test, if i do the above is the start-up script, it doesn't effect anything,im not sure as maybe something else is blocking it....

    'Procedures available for all Script groups from the Script task can be implemented here.
    $SetDateFormat("-","YMD")

    above no effect....nothing changes

    but i try the same function in my 'screen script' of the 'calendar screen' as below:-

    'This procedure is executed just once when this screen is open.
    Sub Screen_OnOpen()
    $SetDateFormat("-","YMD")

    the results are:-

    the GRID dates now has changed according to above format.

    But still the "strings tag" in textbox which i used to read the date value from the .net date-time-picker is still on default format as e.g: 6/5/2020

    i read the date value from date time picker as below:-

    $DateFromNet =FormatDateTime($XGet("DateTimePicker1_from","Value.date"),0)
    $DateToNet =FormatDateTime($XGet("DateTimePicker2_to","Value.date"),0)

    and as per that the strings tags loaded with date as e.g 6/5/2020 and not following the format i set using SetDateformat, but as mentioned the GRID date is now displaying following the format, and i just tested reading $Date (current pc date) ,and the format is also according to what i set, only the textbox which loaded the date value from the .NET calender is still on its default date.

    anyway i just wonder why does not work if put it in the start up script as u mentioned,anything else i need do before writing into the startup script.....???






  • Actually … you are correct.
    I had forgotten … but it is meant to go in the Graphics Script.

    Sub Graphics_OnStart()

    'Set Australian date-format for thin clients.
    $SetDateFormat( "/", "DMY" )

    End Sub

    I have it in both places, as I originally misunderstood where it needed to be placed.
    Try placing it in as shown above in the application Graphics Script.
    I expect it will then be in effect for all screens.

    Greg
  • HI

    i have a MS SQL database with below columns and their data types as below, meanwhile in indusoft i configured a GRID and using the same name as for the grid field column, and the type is as below :-

    [Column Name] [MSSQL-TYPE] [INDUSOFT GRID-TYPE]

    ID -------------------------- INT (primary key)------- NUMERIC
    CamID---------------------INT ------------------------- NUMERIC
    CamStartTime-----------TIME------------------------TIME
    CamStartDate-----------DATE-----------------------DATE
    CamEntryNbr------------VARCHAR(50)-----------TEXT
    CamExitNbr--------------VARCHAR(50)-----------TEXT
    CamStatus---------------BIT (true/false)-----------TEXT
    CreateDATE-------------DATE-----------------------DATE

    A single row in SQL Database is as below:-

    1 209 09:05:19 2020-04-24 16 11 True 2020-06-02

    i have configured Database and when i just put the table name in the Data Source setting (image attached) options in indusoft and execute it,
    i put the table name as below:

    Name: [cameraidtag]

    The GRID can retrieved the MS SQL database according to the table name (cameraidtag). so database reading connection all ok.
    and the data is loaded into the grid as below:-

    1 209 09:05:19 24-04-2020 16 11 True 02-06-2020

    my problem when i create a SQL command to select any data using some conditions, im not sure how to syntactically create a SQL query command in indusoft and load the grid accordingly.

    for e.g in the Microsoft SQL query function i can execute this query successfully :-

    SELECT * FROM [dbo].[aztitechCrowdCam209] WHERE [ID] BETWEEN 8 AND 20, and i get all the rows from 8th until 20th

    So i try the same from indusoft scripts

    in the DataSource setting Table tab i wrote this:-

    Name: [cameraidtag] WHERE [gridcondition]

    So when i click a button, in the button script i update the conditions as below:

    $gridCondition = "ID BETWEEN 5 AND 20"

    i get invalid object error. it doesnt work.

    Database: Error: Invalid object name 'aztitechcrowdcam209 WHERE ID BETWEEN 5 AND 20'........... and its long error message,i just paste some here.

    So the grid only gets loaded when i put only the table name in the [cameraidtag] without any condition, how to use with conditions??

    i even try to used integer tags as below, i preload the tags with 8 and 20 but i get same error:-

    $gridCondition = "ID BETWEEN "&$RowIDmin&" And "&$RowIDmax&""

    Can anybody help for above, if i can successfully do above first, then i need to do this which is my ultimate aim:-

    i should be able to compare two dates and get the data i want in between, in MS SQL query window i can successfully execute this:-

    SELECT * FROM [dbo].[aztitechCrowdCam209] WHERE [CamStartDate] BETWEEN '2020-04-25' AND '2020-06-02' and get the results.

    but the same as above , when i try executing similar query from indusoft SQL i get the same invalid object error:-

    e.g, i used :-

    $gridCondition = "CamstartDate BETWEEN '"&$DateFrom&"' AND '"$DateTo"'"

    above script syntax is already error, and i not sure how to write it....

    and my question for date comparison is , the indusoft tag only have string tag (both above are strings tags), i can get date-from and date-to using a datetimepicker but it returns the date in strings, can i just direct use strings dates for comparison or i need to convert it??

    i can pick the dates as below and load it into a strings tags and plan to use in my condition based SQL command:-

    $DateFrom =FormatDateTime($XGet("DateTimePicker1_from","Value.date"),0)
    $DateTo =FormatDateTime($XGet("DateTimePicker2_to","Value.date"),0)

    how to write correct sql command to get the data inbtwn the picked dates??














  • I would also expect to find a similar configuration property within the .NET calendar … but unfortunately I have no experience in that area.
  • 'Script Created By Stavros Dimoudis
    'Function DateToSQL(sDate, sDateFormat)
    'sDate --> Date of format according to sDateFormat
    'sDateFormat --> Valid values DMY, DYM, MDY, MYD, YMD, YDM
    'example --> if sDateFormat=DMY -->sDate should be 15-5-2020 or 15/5/2020 or with any separator you want
    'first the function will try to extract the separator
    'then it will try to extract the Day, Month and Year and assign them according to the defined date format
    'Finally it will produce the SQL Date (YYYY-MM-DD)

    'Function Example-->DateToSQL("1/10/2020", "DMY") --> Result: "2020-10-01"

    Function DateToSQL(sDate, sDateFormat)
    Dim i, sDay, sMonth, sYear, seperator, parts

    'Find the seperator string from the defined date
    For i = 1 To Len(sDate)
    If Not IsNumeric(Mid(sDate,i,1)) Then
    seperator = Mid(sDate,i,1)
    Exit For
    End If
    Next

    'split the date parts as they are appear
    parts=Split(sDate,seperator)

    'assign the date parts according to the date format
    Select Case sDateFormat

    Case "DMY":
    sDay=parts(0)
    sMonth=parts(1)
    sYear=parts(2)

    Case "DYM":
    sDay=parts(0)
    sYear=parts(1)
    sMonth=parts(2)

    Case "MDY":
    sMonth=parts(0)
    sDay=parts(1)
    sYear=parts(2)

    Case "MYD":
    sMonth=parts(0)
    sYear=parts(1)
    sDay=parts(2)

    Case "YMD":
    sYear=parts(0)
    sMonth=parts(1)
    sDay=parts(2)

    Case "YDM":
    sYear=parts(0)
    sDay=parts(1)
    sMonth=parts(2)

    Case Else: 'invalid date format, DMY used
    sDay=parts(0)
    sMonth=parts(1)
    sYear=parts(2)

    End Select

    'add zeros to the 1-digit days and months
    If Len(sDay)=1 Then
    sDay= "0" & sDay
    End If

    If Len(sMonth)=1 Then
    sMonth= "0" & sMonth
    End If

    'Build the SQL date
    DateToSQL=sYear & "-" & sMonth & "-" & sDay
    End Function