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??
