Visual interdev 6 and ado version

When you say "create a DSN connection via global.asa" do you mean you're storing the connection string in a application variable, like:

Application("DB_CONN") = "DSN=dsn_conn;UID=user;PWD=pasword"

Or do you mean you're actually storing the whole connection/command in a session or application variable? If you're doing this then stop, you're defeating the whole purpose of connection pooling and multi-threading. Storing the connection string in an application or session variable is fine, but storing the actual database connection is not.

Or do you mean you're creating a DSN-less connection?

Maybe you can elaborate as to exactly what you're doing.

In regards to the version of mdac that VI needs, originally I think it was 2.1. Though with later service packs that might have changed.

Ok from the begginning - i have a customer who wants to use visual interdev to develop a front end accessing our SQL server ( wich is on a seperate server), and we only support DSN connections to it.

So my customer is using a DSN connection within the global.asa ( wich I never thought was possible - or at least I always thought the global.asa was used to enable a connectionless connection). It doesn't work and we can't work out why not.

Code from the global.asa :

Application("Connection1_ConnectionString") = "DSN=databook1;User Id=userid;PASSWORD=password;UID=databook_admin;APP=Microsoft Development Environment;DATABASE=databookconz"

http://205.178.180.35/databook.co.nz/DSNTest1.asp
http://205.178.180.35/databook.co.nz/DSNTest.asp

The page (DSNTest1.asp),uses command line to set up data connection, and it works fine. The other page(DSNTest.asp, very simple page) uses global.asa to create data connection, and uses Recordset Object from tool box (visually dragged and dropped in Visual Interdev) to create SQL command, however it doesn't work.

We can't work out why not - our Admin simply says :
The ADO connection is not supported for DSN connections to the SQL servers, only for access databases.

and my reasoning is - well NT has ADO loaded in by default, why can' you use it - unless it's been disabled somehow on the sql server or it's the incorrect version - why couldn't it work?

Please please let me know you thoughts on this one ?

Thanks a million so far

regards

[Mod Edit: best not to post userid and passwords]

The first thing I would check before anything is to make sure you have a system DSN installed on the web server. So go into the Data Sources area in the control panel and select System Data Sources and make sure there is one called "Databook1". From what your Admin says it sounds like the DSN he setup is for an Access database, you need one for a SQL database.

If that all checks out ok then I'm not sure what the problem is. The ADO version shouldn't matter since the web app runs on the server (if working in master mode).

Did you use the built in "Add Data Connection" in Interdev or did you code the connection yourself?

HI there

this is what I got from our Snr admin :
I have spoken with a senior admin regarding this issue and I believe the
version does matter. Version 2.5 is not backwards compatible and that is
most likely why Visual Interdev doesn't work properly. The reason we do not
use version 2.5 ADO is because it breaks perl support on the server and
there is a security problem with 2.5 that is not present in 2.1.

let me know your thoughts please

cheers

Unless you're referencing the ADO library specifically (by version), then the ADO version does not matter. Having said that, there are some features that are only available in the newer releases.

Maybe if you could post the code from your asp that is not working (remember to take out the username & password)

This page(DSNTest.asp, very simple page) uses global.asa to create data connection, and uses Recordset Object from tool box (visually dragged and dropped in Visual Interdev) to create SQL command, however it doesn't work.

Here's the code:

<script id=DebugDirectives runat=server language=javascript>
// Set these to true to enable debugging or tracing
@set @debug=false
@set @trace=false
</script>

DSN Testing

-->
<script LANGUAGE="JavaScript" RUNAT="server">
function _initRecordset1()
{
var DBConn = Server.CreateObject('ADODB.Connection');
DBConn.ConnectionTimeout = Application('Connection1_ConnectionTimeout');
DBConn.CommandTimeout = Application('Connection1_CommandTimeout');
DBConn.CursorLocation = Application('Connection1_CursorLocation');
DBConn.Open(Application('Connection1_ConnectionString'), Application('Connection1_RuntimeUserName'), Application('Connection1_RuntimePassword'));
var cmdTmp = Server.CreateObject('ADODB.Command');
var rsTmp = Server.CreateObject('ADODB.Recordset');
cmdTmp.ActiveConnection = DBConn;
rsTmp.Source = cmdTmp;
cmdTmp.CommandType = 2;
cmdTmp.CommandTimeout = 10;
cmdTmp.CommandText = '"dbo"."WebSite"';
rsTmp.CacheSize = 10;
rsTmp.CursorType = 3;
rsTmp.CursorLocation = 3;
rsTmp.LockType = 3;
Recordset1.setRecordSource(rsTmp);
Recordset1.open();
if (thisPage.getState('pb_Recordset1') != null)
Recordset1.setBookmark(thisPage.getState('pb_Recordset1'));
}
function _Recordset1_ctor()
{
CreateRecordset('Recordset1', _initRecordset1, null);
}
function _Recordset1_dtor()
{
Recordset1._preserveState();
thisPage.setState('pb_Recordset1', Recordset1.getBookmark());
}
</script>

This page uses command line to set up data connection, and it works fine.

here's the code :

DSN Testing

Please let me know your thoughts - or do you know where I can get a downloadable trial version of visual interdev so that I can play with this code?

thanks again for your persevering help.

I didn't realize you were using the design-time controls, yuck! When I first was learning Interdev I tried these controls but stopped using them after the lack of flexibility and the amount of crap they put into my pages. I don't even bother installing the script library anymore on any projects I work on.

My first recommendation would be, don't use the design-time controls, stick to using your own code. When you say it doesn't work, what error message are you getting, or is it just not returning any data?

I'm not sure where you can download a trial version of Interdev, but you can order a trial here:

http://developerstore.com/devstore/product.asp?productID=7448

mmm yip I'm going to have to take that option - command line coding...

thanks for all your help

regards