Screen Shots
...and How To Use SQL VB ASP Code Generator
SQL VB ASP Code Generator is a powerful collection of tools with many different modes of usage:
As an SQL Generator...
Main Screen:
When you run the program, the main screen will appear (shown below). The first list box on the main screen displays your ODBC databases first, and then any user-added Microsoft Access databases. Double-click on a database and it will display all the tables for that database in the second list box (or use the "Get Tables" button). Double-click on a table and it will display all of the fields in that table (or use the "Get Fields" button). SQL commands and operators are in the remaining list boxes.
Manual Point-And-Click SQL
Simpy point-and-click to manually build the SQL statement you need. If you click on the wrong item, you can right-click to undo.
Automatic SQL Generation
To automatically generate some of the most common types of SQL queries, simply select the desired table (and sometimes field), and then click on one of the four hot buttons ("List Records", "Count", "Count By", or "Count By Desc"). This is very quick and handy when you need to learn and analyze a database quickly, like many consultants have to do routinely.
The query above was generated using the "Count By Desc" button. You first select the field in which you are counting by, and then just click the "Count By Desc" button to generate a descending count statement on the selected field. Then click the "Execute SQL" button to execute the SQL and return the resulting recordset to a grid.
You can also automatically generate more complex SQL statements by clicking on "SQL" menu item. It includes the same four hot button SQL statements and other functions. But it's most powerful query is the "Multi-Table Select".
Below is an example of the Multi-Table Joined Select query using the "Employee" table. Note that it picks up the lookup fields from the "EmployeeType" and "Department" tables using the "EmployeeTypeID" and "DepartmentID" fields from the "Employee" table.
Also note in the right side of the screen above, in the middle, the "Query #" has now advanced to "2". The Spin Control button next to it allows you to page back and forth through the all the queries very quickly.
As a VB Generator...
Generate an entire VB application (derived from selected database tables) with a few clicks.
Imagine generating every VB form you need for every table in your database with one click. All you have to do is point to a database and click. Then just kick back while the generator produces a new VB Project with all of necessary VB forms and VB code that you need to SELECT, LIST, ADD, EDIT, DELETE, and PRINT records for each table in your database. It produces easy-to-read, error-free VB code that is easy to modify, even for a VB beginner. It generates all of the necessary embedded SQL statements and VB code, with error trapping on each subroutine. The error trapping in the generated code is conditional so it can be globally turned off to make future debugging easier when modifying the generated code. The generator can design all of your forms based on your data model and data without you having to do a thing. However, you can over-ride many of the generator's defaults to tailor your forms to your situation.
It has many luxurious features that you might not expect, but that can make your life much easier...
It handles relational lookup fields and displays them in dropdown combo boxes. Boolean fields default to check boxes. It analyzes your data to determine the necessary text box and combo box widths which can be overridden to a fixed width. All the objects on the form are perfectly centered. It even looks for "line feeds" in your data in order to determine if a text box's multiline property should be set to "True". If a field name contains the word "password" it assumes it is a password type of field and will set the PasswordChar property of the text box to "*".
To Generate a VB application, from the Main screen, click the "VB" Menu, then click "Generate VB Application", as shown on the menus in the picture below.
The following screen will appear. It has four tabs. Simply set the desired parameters on each of the tabs, and on the forth tab, click the "Generate VB Application" button. The first tab is "Select Tables".
Select the tables you want to include on the first tab. Then click on the "Form Design" tab.
The "Form Design" tab lets you change the default form design for a given table. The list box on the left displays the selected tables from the "Select Tables" tab.
You can preview a form before you actually generate it in order to make sure default design is what you want. If it is not, you can edit the form design and preview it again until it is designed the way you want.
To preview a form, select the desired table and click "Preview VB Form".
Below is a preview of what the generated VB form for the selected "Employee" table will look like. Note that the first two fields are drop down combo boxes, that are filled with the relational lookup fields from the corresponding lookup tables.
In the VB form preview of the "Employee" table example below, you can see that the "Employee Type" drop down combo box is filled with data from the relational lookup field "EmployeeType" in the corresponding "EmployeeType" lookup table, using the "EmployeeTypeID" field in the "Employee" table.
Change the Form Design
The form design below for the "Employee" table was generated automatically. To change the default form design for a given table, select the desired table and click the "Edit Form Design" button.
Below is a list of the fields in the "Employee" table. Note that the first three fields are ID fields. The first one is of course the "ID field" for the selected "Employee" table. The second two "ID fields" refer to data in corresponding lookup tables. Since they are lookup fields, they default to combo boxes, but you can changed them to text boxes. You can also make them locked and/or visible. The form design is generated automatically
To change the form design for any field, double click on it.
Below is the form field settings for "EmployeeTypeID" field in the "Employee" table. Note that the "Object Type" is set to Combo Box.
Click on the "Object Type" combo box and you can see that the choices are "Combo Box" or "Text Box" as shown below.
In many (if not most) cases, you don't need to make any modifications to the Form Design defaults. But if you do need to make any changes to the form design as outlayed above, your changes will be stored in the product's internal database.
After you have done any neccessary modifications to your forms, you can then move to the next step which is on the "Set Directory" tab.
The Set Directory tab lets you set the VB project name and the directory in which the generated VB files will written to. Do not use your real development directory as files can be overwritten. Use a temporary directory that is only used for the purpose of written generated files to. When you generate a VB application, it will generate all the project and form files so you can test the code in this temporary location. When your forms are designed and working the way you like, then copy them by hand to your real VB development directory. We are not responsible for any data you may lose from the use of this product, so be careful.
The Set Directory tab also lets you set it to "Delete All Existing Files in VB Project Directory (excepty .mdb files) Before Generating Application". Sometimes you just want to start with a clean slate and have no files in your VB Project Generation directory except any .mdb files. Checking this box deletes everything in the directory except Microsoft Access database (.mdb) files.
Sometimes you may only be adding a few tables and therefore only generating a few, but you are adding to code already generated, but not moved to your permanent VB file locations. In that case you can select "Overwrite Files in VB Project Directory".
If you are generating a full vb project, you may want to add an "About" box (VB form). You "About" box defaults are under the "Preferences" menu and then "Setup" menu item, where you set your name, copyright info, and website link.
Generate Application tab
The final step is to generate your VB application. To do so, click the "Generate VB App Source Code" button. Now sit back and relax until all the steps are complete. When completed, the generated app will automatically run.
If you are using the Free Version of the program, the "VB Generation Mode" will be "Compiled ProtoType EXE" and not "VB Source Code" In this case, it will still generate a vb app, but then compile it and delete the source code. Also, the Free Version makes the generated vb source code unreadable before it compiles which can take quite a while (several minutes depending on the number tables and fields) so please be patient. The activated version skips this step and is much faster.
To cancel the generation process before it is complete, click the cancel button and wait until the generation process is cancelled. After it cancels, the "Close" button will become enabled again.
Database Rules
Note the "Database Rules" button on the above screen. If the program fails to "Verify Database", then your database does not follow the "Database Rules" required for proper VB code generation.
There are only a couple of rules to follow when creating your database layout. To read about the rules, click "Database Rules".
Generate VB Code Functions
Sometimes you don't want to generate an entire VB app, or even VB files for a single table. Sometimes you just want a small chunk of VB code to do a specific function, like a Select or Insert function.
Generate VB Code Functions lets you generate small pieces of VB and SQL code and then puts that code in the SQL text box on the main screen (for a given query number). Usually, you will not be able to view it all in the small SQL text box on the main screen, but will be able to if you click the View button.
The screen below shows how to generate a "Select" VB code function for the "Employee" table by clicking the "VB" menu, "Generate VB Code Functions", then the "Insert" menu item.
Click the View button to see a full screen of the generated vb code. In the example below, a "Select" VB code function for the Employee table was generated.
You can adjust the font slider to change the font to a smaller one so that the SQL does not wrap. You can go from Arial 8 point to a normal fixed-width Courier font 12 pitch as pictured below.
As an ASP Generator...
Generate an ASP Application
To generate an ASP Application for all the tables in your database, click the "ASP" menu, then click "Generate ASP App".
The ASP Project Directory screen will come up and lets you enter an ASP project name, and the locations of your root web directory and where you want to save the generated ASP files.
In the "Database Locations" box at the bottom of the screen, the program displays where the (Access) database is located.
Currently, the ASP generation part of the program works seamlessly with MS Access databases. But for non-Access (ODBC) databases, you may need to change a couple of lines of code.
For generating ASP code for ODBC databases that are not in MS Access, you may need to change the ASP database files spec (as displayed in the last box) and/or you may need to also change the way the database is opened, but once you correct those few lines of code, all of the other genearted ASP code should work fine.
As a Text Parser Tool
The Text Parser Tool is very powerful and incredibly handy. It has a lot of different features and can be used in a lot of different situations. It is mainly made to parse data out of a text file. That is, to pull a list of substrings out of a list of strings. It can also add data or delete data to each line of text. It can do a "Search and Replace". It can add line numbers to your text. It can even "Flip Sides of Equal Sign" which can sometimes be handy with VB code.
The Text Parser Tool is particularly handy for parsing out HTML source code from a web page. For example, say you wanted to get a list of all the URL links out of a web page. You would just have it remove everything to the left of "http://" and then from "> to the right of the line (or to the left or right, inclusive or exclusive, for whatever text you enter). Parsing out HTML source code from a web page is a very common use. Another use would be getting a list of objects, say jpg files, from a website's HTML source code.
The next three screens below are an example of getting a list of jpg file names out of a web page's html code.
First we do a Search and Replace on the string <IMG SRC= " and replace it withnothing.
The results are as follows.
Next, we do a Search and Replace on "> which leaves only the list of jpg file names as desired in this example and shown below.
The size of text is limited to 32K by the limits of the VB text box control.
This tool eliminates the inhumane repetitive manual tasks needed to be done when parsing text by hand.
Additional Features...
Saving Your Queries
Once you have generated a bunch of queries, you can save them all in this product's internal database for later recall. It stores all of your queries for that session including all of the recordsets.
To Save Queries, click on the "File menu, and then "My Queries" and then "Save Queries" as shown below.
Then type in the name you want to save the queries under....
Load Queries that you saved previously.
To recall the queries, click the "File" menu and then "My Queries" and then "Load Queries ". The screen below will appear. Simply select the set of queries that you want to work with again.
Add Access Database
The first list box on the main screen contains ODBC databases and any user-added Microsoft Access databases, for which you may not feel like setting up an ODBC DSN, but still want to use.
To add an Access database to the first list box, click the "File" menu and then click "Add Access Database". If any are already entered into the program's internal database, they will appear in a grid as shown in the example below. If not, the grid will be empty, and you can just click the "Add" button.
Clicking the "Add" button will give you the dialog below for which you can browse for the Access database you want to add to the system, and give it a name that will be displayed in the first list box on the Main screen.
DSN Passwords for Automatic Login
In low security situations, you can even store your UserID and Password for a given DataSource Name so you do not have to re-enter your User ID and Password each time you connect to a database.To enter and store a DSN Password, click the "File" menu, and then "DSN Passwords" . A grid list will dialog box will appear for which you then click the "Add" button. Then the following dialogue will appear:
Select the database from the drop down combo box and then enter the user ID and password and click the "OK" button.
Table Filters
Sometimes you may have a very large number of tables, but only need to work with a few. Table Filters allow you to see just the tables you are interested in so you do not have to scroll through perhaps hundreds of tables that you do not need.
To enter Table Filters, click the "File" menu, and then "Table Filters".
The Table Filters screen will then appear which allows you to enter filters that decrease the number of tables in the list box.
There are Exclude Filters and Include Filters.
Exclude Filters exclude tables with names that match any of the filters added to the "Exclude Filters" list box.
Include Filters include tables with names that match any of the filters added to the "Include Filters" list box.
To Add a Filter, simply type it into the "Add Filter" textbox and press "Enter".
To Delete a Filter, select the filter you want to delete, and the hit the "Delete" key. Tables are refeshed upon each entry or deletion so you may have to wait a few seconds.
In the example on the screen above, an Include Filter of "emp" was entered to just see Employee tables (or tables that contain "emp") in the SampleDb1 database. You can see the result below in that now only two tables are displayed. Although, you would not actually use this feature on such a small database, it does come in very handy when you have about 40 or more tables and are only working with a few of them.
Setup
System Tables are hidden on the Main screen by default, but that default can be changed in the Setup screen..
To access the program's Setup screen, click the "Preferences" menu, and then "Setup".
To display a database's system tables check the "Display System Tables" check box.
The system table also includes a place to enter your name or company's name and web page and specific copyright notice to be displayed in an autmatically generated "About" dialog box for the VB programs you generate.
Setting Case
Sometimes you may be working with existing SQL code that is written in all caps (upper case), and you want to add to it or modify it, while retaining the same all caps convention.
The program lets you set the case for all of the databases, tables, and fields to upper or lower for the SQL you generate manually via point-and-click.
To change the case to ALL CAPS, click the "Preferences" menu, then "Case", then "Upper Case", as shown below.
Note how all the SQL is in UPPER CASE in the example above.
