Page 1 of 1
Selecting
Posted: Wed Jan 20, 2016 3:34 pm
by parsec
Can some one please let me know or point me to an example to do this
I have two tables Vendors, and the other products. Also I like to have two grids, That when I select a Vendor in one I will see all the Products for the selected Vendor
Thank you
Re: Selecting
Posted: Wed Jan 20, 2016 7:43 pm
by Klaus
Hi parsec,
looks like its time to learn some SQL!
This page got me started and I still have to visit it, whenever I have to do some database stuff
http://www.w3schools.com/sql/default.asp
Best
Klaus
Re: Selecting
Posted: Thu Jan 21, 2016 2:52 am
by parsec
Thank you
I do not have and issue with the SQL query. I try do understand how to tell LC when I select a row with Vendor ID "1" to show all data with Vendor ID"1" from table Products do you know of any examples for me to see
thank you
John
Re: Selecting
Posted: Thu Jan 21, 2016 3:33 am
by Simon
Hi John,
Is it that you don't know how to put returned info into the grid?
Simon
Re: Selecting
Posted: Thu Jan 21, 2016 10:26 am
by Klaus
Hi parsec,
well, this is the "Database" forum, so I thought you were having problems with SQL!
Can you please post a screenshot with some comments?
Like SImon, I am not sure what you really mean.
Best
Klaus
Re: Selecting
Posted: Thu Jan 21, 2016 11:54 am
by parsec
Thank you
I like to learn how to tell LC when I check line one with Id (1) to pass the order detail in the second grid
Here is the SQL Query
Select
StoreTenderOnline.dbo.PO_Master.PO_Number,
StoreTenderOnline.dbo.PO_Detail.PO_Number As PO_Number1,
StoreTenderOnline.dbo.PO_Detail.Description,
StoreTenderOnline.dbo.PO_Detail.Qty,
StoreTenderOnline.dbo.PO_Detail.Cost,
StoreTenderOnline.dbo.PO_Master.PO_Vendor_Name,
StoreTenderOnline.dbo.PO_Master.PO_Vendor_Number,
StoreTenderOnline.dbo.PO_Master.PO_Status,
StoreTenderOnline.dbo.PO_Detail.UPC
From
StoreTenderOnline.dbo.PO_Detail Inner Join
StoreTenderOnline.dbo.PO_Master
On StoreTenderOnline.dbo.PO_Master.PO_Number =
StoreTenderOnline.dbo.PO_Detail.PO_Number
Re: Selecting
Posted: Thu Jan 21, 2016 4:46 pm
by Simon
Hi John,
Are you getting data returned from your SQL query?
Simon
Re: Selecting
Posted: Thu Jan 21, 2016 4:51 pm
by parsec
Yes, that is not a problem
Re: Selecting
Posted: Thu Jan 21, 2016 11:55 pm
by Simon
Re: Selecting
Posted: Sat Jan 23, 2016 12:06 am
by parsec
Thank you Simon
I can place the SQl queries into the grid, how ever how I can run the query below when I select a line in grid 1 via the checkbox and in the Where statement will change to the number of the selected row based of the field dbo.PO_Master.PO_Number
Select
StoreTenderOnline.dbo.PO_Master.*,
StoreTenderOnline.dbo.PO_Master.PO_Status As PO_Status1,
StoreTenderOnline.dbo.PO_Master.PO_Number As PO_Number1
From
StoreTenderOnline.dbo.PO_Master
Where
StoreTenderOnline.dbo.PO_Master.PO_Number = 2
Thank you
Re: Selecting
Posted: Sat Jan 23, 2016 12:14 am
by Simon
Hi John,
I'm not sure if this is what you are asking but in the script of the checkbox something like this?
Code: Select all
on mouseUp
if the highlite of me then
--do something
beep
end if
end mouseUp
Simon
Re: Selecting
Posted: Sun Jan 24, 2016 8:23 pm
by parsec
I have read all the need it and understand the concept a bit better I have manage to do this however I do not get any errors and no results as well other than theDataA in to DataGrid 2
where I have gone wrong any help is much apreciated
Thank you
on mouseUp pMouseBtnNum
if pMouseBtnNum is 1 then
put the dgHilitedLines of group "PO Orders" into theLine
put the dgDataOfLine[theLine] of group "PO Orders" into theDataA
--put theDataA["vendor_number"] into the theVendor of me
set the thePO_Number of me to theDataA["PO_Number"]
put " theDataA " into tRecords
set the dgText of group "DataGrid 2" to tRecords
end if
end mouseUp
Re: Selecting
Posted: Mon Jan 25, 2016 3:09 pm
by Klaus
Hi parsec,
sorry for hte late reply, I am currently moving...
OK, I though you want to start a new SQL query depending on the "PO Number" of the checked line?
I don't see any in your "mouseup" script?
What you are doing in your script is to put some info from DG 1 into DG 2, is that what you want?
If yes, then do NOT quote the used variables!
Code: Select all
on mouseUp pMouseBtnNum
if pMouseBtnNum is 1 then
put the dgHilitedLines of group "PO Orders" into theLine
put the dgDataOfLine[theLine] of group "PO Orders" into theDataA
## Wrong syntax for setting a custom property!
## put theDataA["vendor_number"] into the theVendor of me
## Correct syntax for setting a custom property! :-)
set the thePO_Number of me to theDataA["PO_Number"]
## Setting the DGTEXT requires TAB and CR delimited text as the paramter.
## When supplying an ARRAY like theDataA, set the DGDATA:
set the dgdata of group "DataGrid 2" to theDataA
end if
end mouseUp
If thsi is not what you want, please explain more.
And please use the "Code" tags after pasting scripts, that will keep the formatting!
Best
Klaus
Re: Selecting
Posted: Sat Jan 30, 2016 8:52 am
by parsec
Good luck with the moving no problem and thank you for the help I appreciated
what I like to understand and learn is, when i select a line from the grid PO ORDERS to pass the PO NUMBER to this
SQL statement, but I have to be able to change the
Code: Select all
put "SELECT * FROM PO_Detail WHERE PO_Number =1" into tQuery
to the number that was selected from the
grid line and execute the SQL statement
Code: Select all
global gConID
on mouseUp
--connectToDB
# get all of the records in your database
put "SELECT * FROM PO_Detail WHERE PO_Number =1" into tQuery
--put "SELECT * FROM po_Detail" into tQuery
--put "Select StoreTenderOnline.dbo.PO_Detail.*, StoreTenderOnline.dbo.PO_Master.PO_Number As PO_Number1,StoreTenderOnline.dbo.PO_Master.PO_Date,StoreTenderOnline.dbo.PO_Master.PO_Vendor_Number,StoreTenderOnline.dbo.PO_Master.PO_Vendor_Name, StoreTenderOnline.dbo.PO_Master.PO_Status,StoreTenderOnline.dbo.PO_Master.Store_NumberFrom StoreTenderOnline.dbo.PO_Detail Inner Join StoreTenderOnline.dbo.PO_Master On StoreTenderOnline.dbo.PO_Detail.Row_ID = StoreTenderOnline.dbo.PO_Master.Row_ID" into tQuery
put revDataFromQuery(,,gConID,tQuery) into tRecords
# this part's not required, but a good idea to check for errors
if tRecords begins with "revdberr" then
answer error "There was a problem accessing the database: " & tRecords
closeDB gConID
exit to top
end if
# end error check
--closeDB gConID
set the dgText of group "DataGrid 2" to tRecords
end mouseUp
thank you