Selecting

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller, robinmiller

Post Reply
parsec
Posts: 27
Joined: Fri Jan 09, 2015 5:24 pm

Selecting

Post by parsec » Wed Jan 20, 2016 3:34 pm

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

Klaus
Posts: 12204
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Selecting

Post by Klaus » Wed Jan 20, 2016 7:43 pm

Hi parsec,

looks like its time to learn some SQL! :D

This page got me started and I still have to visit it, whenever I have to do some database stuff 8)
http://www.w3schools.com/sql/default.asp


Best

Klaus

parsec
Posts: 27
Joined: Fri Jan 09, 2015 5:24 pm

Re: Selecting

Post by parsec » Thu Jan 21, 2016 2:52 am

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

Simon
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 3901
Joined: Sat Mar 24, 2007 2:54 am
Location: Palo Alto

Re: Selecting

Post by Simon » Thu Jan 21, 2016 3:33 am

Hi John,
Is it that you don't know how to put returned info into the grid?

Simon
I used to be a newbie but then I learned how to spell teh correctly and now I'm a noob!

Klaus
Posts: 12204
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Selecting

Post by Klaus » Thu Jan 21, 2016 10:26 am

Hi parsec,

well, this is the "Database" forum, so I thought you were having problems with SQL! 8)

Can you please post a screenshot with some comments?
Like SImon, I am not sure what you really mean.

Best

Klaus

parsec
Posts: 27
Joined: Fri Jan 09, 2015 5:24 pm

Re: Selecting

Post by parsec » Thu Jan 21, 2016 11:54 am

Image

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

Simon
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 3901
Joined: Sat Mar 24, 2007 2:54 am
Location: Palo Alto

Re: Selecting

Post by Simon » Thu Jan 21, 2016 4:46 pm

Hi John,
Are you getting data returned from your SQL query?

Simon
I used to be a newbie but then I learned how to spell teh correctly and now I'm a noob!

parsec
Posts: 27
Joined: Fri Jan 09, 2015 5:24 pm

Re: Selecting

Post by parsec » Thu Jan 21, 2016 4:51 pm

Yes, that is not a problem

Simon
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 3901
Joined: Sat Mar 24, 2007 2:54 am
Location: Palo Alto

Re: Selecting

Post by Simon » Thu Jan 21, 2016 11:55 pm

I used to be a newbie but then I learned how to spell teh correctly and now I'm a noob!

parsec
Posts: 27
Joined: Fri Jan 09, 2015 5:24 pm

Re: Selecting

Post by parsec » Sat Jan 23, 2016 12:06 am

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

Simon
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 3901
Joined: Sat Mar 24, 2007 2:54 am
Location: Palo Alto

Re: Selecting

Post by Simon » Sat Jan 23, 2016 12:14 am

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
I used to be a newbie but then I learned how to spell teh correctly and now I'm a noob!

parsec
Posts: 27
Joined: Fri Jan 09, 2015 5:24 pm

Re: Selecting

Post by parsec » Sun Jan 24, 2016 8:23 pm

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

Klaus
Posts: 12204
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Selecting

Post by Klaus » Mon Jan 25, 2016 3:09 pm

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

parsec
Posts: 27
Joined: Fri Jan 09, 2015 5:24 pm

Re: Selecting

Post by parsec » Sat Jan 30, 2016 8:52 am

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

Post Reply

Return to “Databases”