Selecting

[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

[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

[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

[Simon]

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

Simon

[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

[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

[Simon]

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

Simon

[parsec]

Yes, that is not a problem

[Simon]

Here is the lesson
http://lessons.livecode.com/m/datagrid/ ... -with-data

Simon

[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

[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

[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

[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

[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