How to easily extract MSSQL info and display formatted ?

[[email protected]]

I am a complete newbie (just got it today). I have a very simple goal. I want to get text from a screen (my ERP application, the customer number) , using that text I want to retrieve information from a MS SQL DB and then I want to display that to my users in a new window. Some of the data is one for one from a customer master. Some (notes) is many to one (i. e. there could be any number of notes for a given customer). From reading the documentation I think i see the correct approach to 2/3 of my desires

1. I Use the text capture functions to get the initial text (customer number)
2. I use the DB functions to connect and get the information I want which can be put into variables as desired

---(this is where I am confused)---

At this point what is the best method for creating a window of formatted (headings and such) information which I then display to the user? Dialog boxes seem to be a possibility but I do not understand them well enough to see how to place the 'variables' in the box as it is designed so that on execution the data is shown. Another possibility would be to create a Web Page and then display that in IE but that seems like a lot of trouble.

I am sure there is an easier way to do this. Please point me in the correct direction where I can find examples and similar scripts.


Thanks

[adroege]

Another possibility would be to create a Web Page and then display that in IE

Without knowing more details about your data and how it is formatted/displayed, the HTML approach is the one I would use. It is a much more flexible approach, and could end up looking more aesthetically pleasing (if you are good at design that is)

I have posted an example on this forum of displaying the results of a SQL query in HTML format. I'll try to search and post the link, or you may beat me to it by doing a search.

**Edit**
Here is the link
http://www.mjtnet.com/forum/viewtopic.p ... light=html



.

[[email protected]]

Thanks for your help. I can see how that could work. My situation is simply than the one you link to . I will be retrieving perhaps 6 individual pieces of data and then from another table approximate 20 records each with 2 fields so I am not dealing with the large size of tables from your example.

I will likely end up using html as you suggest but for my education is there a way to do it with dialog boxes? I was looking at the file browser sample and see there that a dialog box can be created with a MsMemo control and that control filled with the value of a variable. I could then create one control for my longer data and individual ones for each of the other pieces of data. Is this a practical approach?

[adroege]

Here is a simple example built with version 10 of Macro Scheduler.

Version 12 has MUCH greater control over Dialogs and so you have many more creative options with that version. Version 12 should run this code also since it is backward compatible.

Run it, then fill in a zipcode and click the go button.

Code: Select all

// Show the results of a parsed webpage inside a Dialog

Dialog>Dialog1
   Caption=Dialog1
   Width=403
   Height=301
   Top=150
   Left=31
   Edit=msEdit1,80,40,81,
   Label=ZipCode,24,40,true
   Button=Go,232,40,75,25,3
   Memo=msMemo1,40,96,273,113,
   Label=Get Weather Info,64,8,true
EndDialog>Dialog1


Show>Dialog1

Label>MainLoop
   GetDialogAction>Dialog1,result
   If>result=2,End
   If>result=3,GetWeather
   Wait>0.01
Goto>MainLoop

SRT>GetWeather
   Let>zip_code=Dialog1.msEdit1
   Let>URL=http://www.briansutton.com/wx/weather.html?id=%zip_code%&length=10&unit=s&#current
   HTTPRequest>URL,,GET,,HTMLResponse

   Let>String1=<td width="41%" height="24" align="center" valign="top">
   Let>String2=</td>

   Length>String1,String1_length

   Position>String1,HTMLResponse,1,StartAt
   Add>StartAt,String1_length
   Position>String2,HTMLResponse,StartAt,EndAt,FALSE

   Let>myLen=EndAt-StartAt

   MidSTr>HTMLResponse,StartAt,myLen,myWeather
   Let>Dialog1.msMemo1=The current weather for zipcode %zip_code% is %CRLF%%CRLF%%myWeather%
   ResetDialogAction>Dialog1
END>GetWeather

Label>End