These are displayed automatically for eligible foreign keys for MS Access and SQL Server databases. An OLEDB connection is required and there is a max limit of records to display that can be set by adding or modify the following line in Config.asp or FreeConfig.asp, depending on version:
ocdMaxRelatedValues = 1000
1000 related records is the built in default. Normally you would not want to display more than 1000 records in a static HTML drop down box as it will be difficult for users to navigate and also impact performance.
To customize, modify the WriteHeader() function in the PageInit.asp or FreeInit.asp file (depending on version). Search for the word Retrieving to find the exact spot in source code to modify.
1 Click DB connection information can be set in the ocdADOConnection ASP variable found in configuration file. Since this is a regular string variable in a short .ASP file, connections can be set based on any number of criteria. When connection info is hard coded in the configuration file the Connect screen is no longer presented to the user for dynamic logins. The system also works well with IIS/SQL Server integrated login functions.
Access 2000 has an official maximum of 255 simultaneous connections. Unfortunately, in practice, problems appear with much fewer active connections. Excel has no multiuser support and is recommended by MS only for spreadsheet-type analysis and _not_ to support heavy duty database queries.
However, ASP web applications like 1 Click DB can support a much higher number of simultaneous _users_ than desktop MS Access or Excel because the database connection is not held between asp page requests. So if a user is only requesting asp pages every few seconds or so, the "in-between" time can be used to serve the page requests of other users.
Because of this, a lot will depend on your server hardware (which affects how fast the asp page can be served) as well as your query requirements (do all 2000 records have to be returned on every asp page ?) A lot of moderately high traffic sites run on MS Access without no problems, but better scalability and other great features MS SQL Server is recommended for the most demanding applications.
Access reports on the web are not supported by 1 Click DB.
1 Click DB Pro is a web database administrators tool. Although parts of the 1 Click DB Library can work without querystring variables (the info after the ? ) but most of the interesting features will be unavailable.
Session State has been turned off on your web server. Contact your administrator to restore session state or disable dynamic session access by hard coding the value ocdADOConnection in the configuration file. This will suppress the standard 1 Click DB Connect Wizard screen and automatically logon users to the specified connection.
"Out of the box", 1 Click DB displays one level of table relations per object per page. However it is possible to nest the grid object using IFRAMES.
You can issue a backup command directly from the Command screen, the syntax is documented in the TSQL Documentation.
Sometimes. but add/edit/delete from multi-table queries can be tricky. Only certain types of joins are logically updateable and you can not update both sides of a join at the same time. You can work around this with a custom input form, however, with the standard 1 Click DB webform library you have to be sure the join is updateable, has an autonumber from the controlling table included and watch out for updating both sides of a join at once.
For SQL Server only, on a web server with SQLDMO objects installed (these are standard part of SQL Client Tools), you can choose Generate SQL option from the top of the Table properties screen to view DDL commands to reconstruct the table. This can be cut and pasted into a new .sql file for later use. However this SQL for structure of a table only, not data.
The best way to do this is to set the option "Use Query Governor to prevent queries exceeding specified cost" in SQL Enterprise Manager. When you right click and view properties for a server it is under the "Server Setting" tab. This can also be set manually from the Click DB Pro Command screen by using the sp_configure system stored procedure.
Linked tables in MS Access are _not_ directly supported from 1 Click. The main reason for this is that they are very very slow It is almost always much better to link directly to the external datasource with another 1 Click DB connection. This cuts Access out of the loop and usually speeds up processing quite a bit.
Queries work fine in 1 Click DB (even with linked tables most of the time) ... please note though: the Access interface does not distinguish between queries that are Views and queries that are Procedures but ADO does. If your access query contains SQL with a parameter or misspelled field it will show up in the Procedures collection _not_ the Views collection. Also if you edit a query View and misspell something, the View magically turns into a Procedure, which can be very confusing.
ADO cannot differentiate between fields with the same name in different tables except by their numeric index. Usually this results in an "ambiguous field name" reference. To display multiple fields with the same name create a view to perform the join and and alias the field names in the SQL text.
The Import Wizard supports imports from Excel, Access, mySQL, Oracle or SQL Server to SQL Server, Oracle or MS Access with custom field mapping, expressions, and code events that can be saved for later reuse. You can try the demo online but since uploads to this web server is not supported you can only demonstrate SQL Server to SQL Server transfers.
Once you do the mapping, running it again is just a few clicks to upload and execute your saved specification.
Because it uses ADO, this tech works great with lots of different import datasources (including FoxPro and Oracle). However, Microsoft Excel data must be in tabular format or ADO will have a hard time interpreting it.
All file based data like Microsoft Excel must first be uploaded to the web server with appropriate permissions set to view with 1 Click DB. This can be done with ASP, COM or simple ftp code not included with 1 Click DB.
To use 1 Click DB (or any ADO program) for different text file formats you need to define a schema.ini file in the same directory as your text import file (this would generally be the web server directory for 1 Click DB). See the MS docs on schema.ini for more information.
Here is a doc on unicode support in Office XP :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xpreskit/html/intd02.asp
Even if Unicode was disabled, here is a specific bug that can cause doubling of Excel file size :
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q293828
Note the fix for this is to resave the file.
Excel exports use HTML formats which adds a lot of overhead to the data and is most acute when the size of the data in the individual fields is generally less than the size of the formatting commands for display.
To build secure applications, the use of built-in NT/SQL users and groups for ALL applications is strongly recommended. Security is a crucial component of database systems that are often accessed by multiple VB/COM/Office apps. Enforcing an effective application based security model usually requires lots of extra code and adherence to a particular coding standard. All it takes is one problem in your code or one deviation from the standard and security is potentially compromised. Plus there is the overhead of every time you write a new app or a new feature for your db you have to re-implement security.
WARNING! An Unexpected Error Occurred Processing Your Request: 1030 - Identifier too long
This error usually occurs with long keywords or on searches including many fields. 1 Click DB stores navigation and search criteria in the query string which in IE has a limit of 2083 characters. When 1 Click DB detects a redirect from a search to querystring that is longer than 2083 characters it raises this error and aborts the search. Attempts by IE and other browsers to navigate to overly long URL addresses usually fail with non informative errors.
Use media definitions for your stylesheets, here is a good link on the technique :
No and this is for the simple reason that 1 Click DB uses ADO and Query Analyzer works with more low level ODBC commands. ADO is a middleware product that has some built in limitations. Show Plan results for 1 Click DB may be different than what appears from Query Analyzer because of automatic ADO translations of your SQL CommandText. Also when a fatal ADO error is triggered in a batch command using the OLEDB provider, output from the rest of the batch will not be returned.
public sub DisplayFieldAsMemo(strFieldName, strDefault, strAttributes)
Is the the procedure signature to display an HTML TEXTAREA FORM element, so the syntax to set a field named Address to be 5 lines high and 40 characters long is this :
call objForm.DisplayFieldAsMemo("Address","","COLS=40 ROWS=5")
Form and Grid language settings are in ocdForm_Lang.asp and ocdGrid_Lang.asp files respectively
Some language settings can be set in the configuration file as well, however current Pro interface can't be completely translated from a _Lang file, full source code is required.
WARNING! An Unexpected Error Occurred Processing Your Request: 500 - Variable is undefined
A variable in your code has not been defined. All standard 1 Click DB pages have Option Explicit as the first executable line of VBScript code which requires all variables to be declared with a dim statement before using.
To resolve, either remove the Option Explicit statement or add dim statements for all variables.
Storing pictures in a database table is not recommended for most applications. Store the path to your image or an HTML tag instead.
Out of the box standard 1 Click DB only retrieves as many records from a query as is needed to display the current page. This makes it very efficient compared to most alternatives for display of very large recordsets (with millions of records)
Indexing is different; it helps determine how long SQL Server will take to figure out what subset of your millions of records meet your search criteria. SQL Server indexes work like a alphabetical list. So if you have an indexed Address field and are looking for all records that start with "123" or exactly match "123 Main St", then SQL Server can just do an alphabetic lookup on the index to find matching records and will be very fast.
However, if you want to return all records that contain the word "Main" in the Address field then the index is useless because even though you have an alphabetic list, the db still has to check every single record to see if "Main" is found anywhere in it. The alphabetic list is not helpful in this case because you are not doing that type of lookup.
In this second case, the efficiency of 1 Click DB for working with large recordsets still kicks in, however it will probably take much longer to figure out which records you are looking for than in the first case. This is the case even though 1 Click is issuing search statements that do not require all matches to be returned before displaying records.
If you want to make sure indexes are most likely to be used on all your searches, turn off the keyword searching (which tries to match values anywhere in a field) and turn off the "CONTAINS" search for regular filter/search screens. The logic these features (and NOT the particular way they are implemented in 1 Click DB) are pretty much guaranteed to not be able to use an index. It doesn't matter what tool you use, what you are asking for is logically what they call "non sargable"
If you want an indexed keyword search using straight SQL design, then make a separate keyword/linked record table to search for results and return links to matching records. This is a comparatively advanced design opposed to just scanning through a single table looking for keywords, but will usually be much faster. As an alternative, look at FULLTEXT indexing for SQL Server which is a very different technology than working with regular SQL Server tables.
Use of non-western character sets are not currently tested or supported by 1 Click DB. There are a number of significant issues when writing ASP pages for non-Western character sets. The three big ones are the CodePage, LCID, and the Charset specified in the HTTP header. The computer you are running your Web Browser on must also be running in the right international version. If you are an ASP programmer who has successfully been able to write programs for the web server/database combination you are using for 1 Click DB we should be able to plug in your normal settings and be good to go. If this is your first time attempting this, it will probably require some trial and error to see what works.