View previous topic :: View next topic |
Author |
Message |
TG
Joined: 05 Apr 2007 Posts: 6
|
Posted: Thu Apr 05, 2007 9:57 am Post subject: Novice user needs help trying to make a SQL table front end |
|
|
I'm new to Synopsis and do not have a programming background, but I get around fairly well in SQL. What I would like to do is make a simple front end for a table in my SQL database, more as a learning experience, and as a base for future code. The table has the following rows - unit number, weight1, weight2, and weight3. The form would show all of this info and would allow a user to search for a particular unit number and edit the weight data, or add a completely new unit number and weights. For simplicity's sake, the database server is DBSERVER, the database is UNITDB, and the table is Unit_Data. I have absolutely no idea how to proceed, other than creating the form. A working example of the above front end would GREATLY enhance my understanding of the product. Any assistance would be greatly appreciated! Thanks!
TG |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
CodeMorphis
Joined: 02 Dec 2003 Posts: 252
|
Posted: Thu Apr 05, 2007 4:07 pm Post subject: |
|
|
The name of the database component “Access Database” is actually a misnomer in that it allows connection to more than just the Microsoft Access database. In fact it is possible to connect to a variety of other databases such as MS SQL Server, Excel, CSV format database files and ODBC. These database connections will be made more explicit in the future with dedicated database components.
In the meantime, we can connect to MS SQL Server databases using the “Access Database” component.
To address your question, let’s see how we can use Synopsis to quickly put together a short program that allows for the basic viewing, editing, insertion and deletion functions.
Our program will have the following user interface:
The program uses a DataGrid component to allow the user to directly edit the values of the rows and also insert new rows at the bottom of the table where the insert row is marked with an asterisk (*). Deletion is also possible by selecting a row and pressing the Delete key. The DataGrid component is connected to the database table with a single Synopsis component.
Our Synopsis program looks like this:
Here is the final program that we are discussing:
http://www.codemorphis.com/articles/ans07/ans_040507/SQL_Example.vpd
The SQL database table that we are connecting is defined as follows:
Note that the table needs a primary key in order to allow for the DataGrid component to be able to implement record updates.
The key components in the program are the “Access Database” component with the “Connect” service and the Service Call components that invoke the “Fill Grid” and “Update grid” services of the “Access Database” component.
The database connection is implemented with the following connection string in input data port 0 of the “Connect” service call (right click the “Access Database” component and select “Input Argument(s)”):
In the connection string:
Provider=sqloledb;Data Source=HOMER;Initial Catalog=UnitDB;User ID=sa;Password=satest
HOMER is our SQL server
UnitDB is the database that contains the “Unit_Data” table
sa and satest are our SQL Server login and password credentials
You will need to substitute your particular server name, login and password here.
Once the database is connected with the “Connect” service, we want to fill the DataGrid with the rows from the “Unit_Data” table. To do this we use the “Fill grid” service. Here are the input data arguments for the “Fill grid” service call:
Here we’ve selected the Windows Form, the DataGrid name and added the table to display (“Unit_Data”). We can then simply display the form and enter the basic Windows event loop. Running the program produces the first screenshot in this article.
After the user is done viewing and editing the data, the program is exited by clicking the “Quit” button. This triggers the “Update grid” service call to the database component which writes back any data to the SQL Server table that was changed.
It is also possible to use Synopsis to create your own customized forms with viewing and editing features controlled directly. For an example of this, compare the sample program “SuperStore database.vpd” to its DataGrid counterpart “SuperStore datagrid.vpd”. You can find these programs in the “Sample Programs” folder of the Synopsis installation. The connection is made to an Access database but as we have shown above, the connection with the “Connect” service can be made to an SQL database. After the connection is made, the logic is identical. This makes it easier to develop database programs as you do not need to take into account what type of database you have connected. Synopsis takes care of that for you so you are free to concentrate on higher level programming issues.
Here are some related forum threads:
Database readers
http://www.codemorphis.com/forums/viewtopic.php?t=27
Printing from a grid
http://www.codemorphis.com/forums/viewtopic.php?t=36
Hope that this helps. Feel free to ask questions. |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
TG
Joined: 05 Apr 2007 Posts: 6
|
Posted: Mon Apr 09, 2007 9:44 am Post subject: Thanks!!! |
|
|
Thanks so much for the example! I will try to assimilate it and learn from it. The support you guys give is unmatched!
TG |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
TG
Joined: 05 Apr 2007 Posts: 6
|
Posted: Tue Apr 10, 2007 9:02 am Post subject: SQL OLE Error |
|
|
I'm getting the following error when trying to populate the data grid...
Serv Call 1: Access Database Error: No database Open
The server is a MS SQL Server 2000. Here is my connection string:
Provider=sqloledb;Data Source=DBSERVER;Initial Catalog=DB1;User ID=sa;Password=sapwd
(Example names - DB1 is the name of the database i am trying to connect to on server DBSERVER)
The Fill Grid service call points to the table I am trying to display.
Should I be using an ODBC call instead of an OLE function???
Thanks!!!![/img] |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
CodeMorphis
Joined: 02 Dec 2003 Posts: 252
|
Posted: Tue Apr 10, 2007 11:45 am Post subject: |
|
|
TG,
Where is DBSERVER located? It is a local host or remote? sqloledb is the proper provider for MS SQL Server. |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
TG
Joined: 05 Apr 2007 Posts: 6
|
Posted: Tue Apr 10, 2007 12:05 pm Post subject: |
|
|
I figured it out. Sorry! It was a typo. However, it takes about 2 1/2 minutes for the data grid to populate, and there are only about 100 rows in the table. (About 27 columns). Why would the data connection be that slow? Are there best practice procedures to speed it up? Thanks again for your excellent support!
TG |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
CodeMorphis
Joined: 02 Dec 2003 Posts: 252
|
Posted: Tue Apr 10, 2007 12:52 pm Post subject: |
|
|
Glad to hear that you found the issue and that it is working now.
Is the 2 1/2 minute startup consistent? Are there a lot of requests going into your server? That is an excessive amount time not observed with even larger databases. Can you try with different databases to see the results? |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
TG
Joined: 05 Apr 2007 Posts: 6
|
Posted: Tue Apr 10, 2007 3:38 pm Post subject: |
|
|
Yes, the 2.5 minutes is consistent. This is a production database for an ERP system, so I'm sure the SQL backend is busy. Could the time delay be due to record locks on tables or rows? BTW, when using Enterprise Manager there's not any significant delay. Also, I just tried using our development database, which is on the same server, but there are no users using it right now. Populating the grid on this database took exactly 2.5 minutes as before. (I guess that eliminates the record-lock theory). The database itself is very large, with over 1000 tables, but like I said, the table I am accessing is rather small. Thanks for the ideas and the time - I appreciate any feedback you have on this.
TG |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
CodeMorphis
Joined: 02 Dec 2003 Posts: 252
|
Posted: Wed Apr 11, 2007 3:47 am Post subject: |
|
|
The issue is likely related to some table schema information that is cached on connection to the database.
Can you please try extracting the following optimization test to your Synopsis installation directory and let us know what you observe?
http://www.codemorphis.com/articles/ans07/ans_040507/DB_opt.zip |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
TG
Joined: 05 Apr 2007 Posts: 6
|
Posted: Wed Apr 11, 2007 8:14 am Post subject: |
|
|
Wow! What a difference! The data grid populates instantly now. What was the difference in the 2 files you sent and the ones they replaced in the Synopsis install directory? |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
CodeMorphis
Joined: 02 Dec 2003 Posts: 252
|
Posted: Wed Apr 11, 2007 1:34 pm Post subject: |
|
|
There was table and field schema data that was being cached on connection. This was moved out of the connection process. We'll try to further optimize this data caching. If possible, please try the "Num tables" service and let us know how long that takes. |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
|