«

I have an ASP page that was done in VBScript
It is setup to read an Access database and I need to change it to read
a Sql 2005 Database.

The code that is used to open the Access Database:

Set adoConnection = server.CreateObject("ADODB.Connection")
Set adoRecordset = server.CreateObject("ADODB.Recordset")
adoConnection.Provider = "Microsoft.Jet.OLEDB.4.0"
Dim strLocation, iLength
strLocation = Request.ServerVariables("PATH_TRANSLATED")
iLength = Len(strLocation)
iLength = iLength - 11
strLocation = Left(strLocation, iLength)
strLocation = strLocation & "../Database.mdb"
adoConnection.Open ("Data Source=" & strLocation)
adoRecordset.ActiveConnection = AdoConnection


In my VB 6.0 app I use the following to open the SQL Database"

Set DataBaseTS_1 = New ADODB.Connection
DataBaseTS_1.ConnectionString ="Provider=MSDASQL.1;Persist Security
Info=False;Extended
Properties=Description=Large Pump Data Source;DRIVER=SQL
Server;SERVER=LPDATASYSTEM\PL3LP;APP=Microsoft Data Access
Components;WSID=LPDATASYSTEM;DATABASE=LargePump;Tr usted_Connection=Yes;Initi
al Catalog=LargePump"

DataBaseTS_1.Open

How can I get the VBScript to open the SQL Database?

Thanks,
Bob Hiller
Lifts for the Disabled LLC

Bob and Sharon Hiller wrote:
> I have an ASP page that was done in VBScript
> It is setup to read an Access database and I need to change it
> to read a Sql 2005 Database.
<snip>
> How can I get the VBScript to open the SQL Database?
>
http://www.aspfaq.com/show.asp?id=2126
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Here is what I have tried: No records are returned(This table has 2094 rows,
28 columns)

Set adoConnection = server.CreateObject("ADODB.Connection")
Set adoRecordset = server.CreateObject("ADODB.Recordset")
adoConnection.ConnectionString = "Provider=MSDASQL.1;" _
& "Persist Security Info=False;" _
& "Extended Properties=Description=Large Pump Data Source;" _
& "DRIVER=SQL Server;SERVER=LPDATASYSTEM\PL3LP;" _
& "APP=Microsoft Data Access Components;" _
& "WSID=LPDATASYSTEM;DATABASE=LargePump;" _
& "Trusted_Connection=Yes;Initial Catalog=LargePump"
adoConnection.Open

adoRecordset.ActiveConnection = adoConnection
Dim SqlSelect
SqlSelect = "select * from [LargePumpFloor_TS1] order by Row_num"
adoRecordset.CursorLocation = 3
adoRecordset.CursorType = 3
call adoRecordset.Open(SQLSelect)
adoRecordset.PageSize = 12
adoRecordset.CacheSize = adoRecordset.PageSize
intPageCount = adoRecordset.PageCount
intRecordCount = adoRecordset.RecordCount

Do you see anything ? I am running IIS on Windows XP Pro with MS
SQL Server 2005. I can access and see any MS Access Database but no SQL
databases.

"Bob Barrows [MVP]" <> wrote in message
news:...
> Bob and Sharon Hiller wrote:
>> I have an ASP page that was done in VBScript
>> It is setup to read an Access database and I need to change it
>> to read a Sql 2005 Database.
> <snip>
>> How can I get the VBScript to open the SQL Database?
>>
> http://www.aspfaq.com/show.asp?id=2126
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Bob and Sharon Hiller wrote:
> Here is what I have tried: No records are returned(This table has
> 2094 rows, 28 columns)
>
> Set adoConnection = server.CreateObject("ADODB.Connection")
> Set adoRecordset = server.CreateObject("ADODB.Recordset")
> adoConnection.ConnectionString = "Provider=MSDASQL.1;" _
> & "Persist Security Info=False;" _
> & "Extended Properties=Description=Large Pump Data Source;" _
> & "DRIVER=SQL Server;SERVER=LPDATASYSTEM\PL3LP;" _
> & "APP=Microsoft Data Access Components;" _
> & "WSID=LPDATASYSTEM;DATABASE=LargePump;" _
> & "Trusted_Connection=Yes;Initial Catalog=LargePump"

Nothing to do with your problem, but you should avoid odbc. See the link in
my original reply.

> adoConnection.Open
>
> adoRecordset.ActiveConnection = adoConnection
> Dim SqlSelect
> SqlSelect = "select * from [LargePumpFloor_TS1] order by Row_num"
> adoRecordset.CursorLocation = 3
> adoRecordset.CursorType = 3
> call adoRecordset.Open(SQLSelect)
> adoRecordset.PageSize = 12
> adoRecordset.CacheSize = adoRecordset.PageSize
> intPageCount = adoRecordset.PageCount
> intRecordCount = adoRecordset.RecordCount
>
> Do you see anything ? I am running IIS on Windows XP Pro with MS
> SQL Server 2005. I can access and see any MS Access Database but no
> SQL databases.
>


I see no attempt to check the recordset's EOF property. How are you
determining that no records were returned?

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Bob,
After
intRecordCount = adoRecordset.RecordCount

I have:
If intRecordCount <> 0 Then
Response.Write("Record Count <> 0")
Else
Response.Write("Record Count = 0")
End If

I also tried:
intRowsCount = adoRecordset.GetRows
If intRowsCount <> 0 Then
Response.Write("Rows Count <> 0")
Else
Response.Write("Rows Count = 0")
End If

Looking at the link you sent, it appears that I would need to totally
reconfigure the SQL server to use that method.

Thanks,
Bob Hiller
Lifts for the Disabled LLC

"Bob Barrows [MVP]" <> wrote in message
news:ue3OW%...
> Bob and Sharon Hiller wrote:
>> Here is what I have tried: No records are returned(This table has
>> 2094 rows, 28 columns)
>>
>> Set adoConnection = server.CreateObject("ADODB.Connection")
>> Set adoRecordset = server.CreateObject("ADODB.Recordset")
>> adoConnection.ConnectionString = "Provider=MSDASQL.1;" _
>> & "Persist Security Info=False;" _
>> & "Extended Properties=Description=Large Pump Data Source;" _
>> & "DRIVER=SQL Server;SERVER=LPDATASYSTEM\PL3LP;" _
>> & "APP=Microsoft Data Access Components;" _
>> & "WSID=LPDATASYSTEM;DATABASE=LargePump;" _
>> & "Trusted_Connection=Yes;Initial Catalog=LargePump"
>
> Nothing to do with your problem, but you should avoid odbc. See the link
> in
> my original reply.
>
>> adoConnection.Open
>>
>> adoRecordset.ActiveConnection = adoConnection
>> Dim SqlSelect
>> SqlSelect = "select * from [LargePumpFloor_TS1] order by Row_num"
>> adoRecordset.CursorLocation = 3
>> adoRecordset.CursorType = 3
>> call adoRecordset.Open(SQLSelect)
>> adoRecordset.PageSize = 12
>> adoRecordset.CacheSize = adoRecordset.PageSize
>> intPageCount = adoRecordset.PageCount
>> intRecordCount = adoRecordset.RecordCount
>>
>> Do you see anything ? I am running IIS on Windows XP Pro with MS
>> SQL Server 2005. I can access and see any MS Access Database but no
>> SQL databases.
>>
>
>
> I see no attempt to check the recordset's EOF property. How are you
> determining that no records were returned?
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Bob and Sharon Hiller wrote:
> Bob,
> After
> intRecordCount = adoRecordset.RecordCount
>
> I have:
> If intRecordCount <> 0 Then
> Response.Write("Record Count <> 0")
> Else
> Response.Write("Record Count = 0")
> End If
>
> I also tried:
> intRowsCount = adoRecordset.GetRows
> If intRowsCount <> 0 Then
> Response.Write("Rows Count <> 0")
> Else
> Response.Write("Rows Count = 0")
> End If

GetRows returns an array, not a numeric value.

arrRows = adoRecordset.GetRows()
If isarray(arrRows) Then
intRowsCount = ubound(arrRows,2)
response.write "Total Rows = " & intRowsCount +1
Else
response.write "No Records Found
End If

--
Mike Brind

"Mike Brind" <> wrote in message
news: ups.com...
>
> Bob and Sharon Hiller wrote:
> > Bob,
> > After
> > intRecordCount = adoRecordset.RecordCount
> >
> > I have:
> > If intRecordCount <> 0 Then
> > Response.Write("Record Count <> 0")
> > Else
> > Response.Write("Record Count = 0")
> > End If
> >
> > I also tried:
> > intRowsCount = adoRecordset.GetRows
> > If intRowsCount <> 0 Then
> > Response.Write("Rows Count <> 0")
> > Else
> > Response.Write("Rows Count = 0")
> > End If
>
> GetRows returns an array, not a numeric value.
>
> arrRows = adoRecordset.GetRows()
> If isarray(arrRows) Then
> intRowsCount = ubound(arrRows,2)
> response.write "Total Rows = " & intRowsCount +1
> Else
> response.write "No Records Found
> End If
>
> --
> Mike Brind
>

Problem is GetRows will error if there are no rows. Use:

If not adoRecordset.EOF Then
arrRows = adoRecordset.GetRows()
End If

If isarray(arrRows) Then
intRowsCount = ubound(arrRows,2)
response.write "Total Rows = " & intRowsCount +1
Else
response.write "No Records Found
End

Anthony.

Bob and Sharon Hiller wrote:
> Bob,
> After
> intRecordCount = adoRecordset.RecordCount
>
With a default server-side, forward-only cursor, Recordcount will always
contain -1.
http://www.aspfaq.com/show.asp?id=2193
As this article says, there are better ways to count the records returned by
a query.

>
> I also tried:
> intRowsCount = adoRecordset.GetRows

Mike addressed this one.
>
> Looking at the link you sent, it appears that I would need to totally
> reconfigure the SQL server to use that method.

Why? Where does that article say anything about reconfiguring SQL Server if
you don't have to? Simply change your connection string to the one he
suggests using for integrated (Windows) security. Here, let me show you:

>>> Set adoConnection = server.CreateObject("ADODB.Connection")
>>> Set adoRecordset = server.CreateObject("ADODB.Recordset")
>>> adoConnection.ConnectionString = "Provider=MSDASQL.1;" _
>>> & "Persist Security Info=False;" _
>>> & "Extended Properties=Description=Large Pump Data Source;" _
>>> & "DRIVER=SQL Server;SERVER=LPDATASYSTEM\PL3LP;" _
>>> & "APP=Microsoft Data Access Components;" _
>>> & "WSID=LPDATASYSTEM;DATABASE=LargePump;" _
>>> & "Trusted_Connection=Yes;Initial Catalog=LargePump"
>>
adoConnection.ConnectionString = "Provider=SQLOLEDB;" _
& "Persist Security Info=False;" _
& "Data Source=LPDATASYSTEM\PL3LP;" _
& "Application Name=Microsoft Data Access Components;" _
& "Integrated Security=SSP1;Initial Catalog=LargePump"

'I would suggest setting the Application Name to a more specific name rather
than the generic "Microsoft ... ". This will allow debugging using SQL
Profiler to be easier (you can set up a trace using a filter to display only
a specific application).

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

I am starting to think that there is no way to connect to SQL 2005 with
VBScript. I have tried 25 different suggestions from news groups and forums
and none have worked. The only thing that seems to work is report services
using .net and we are not going to change every page we have. I think we
will just go back to SQL 2000 where everything worked fine.

Thanks for the help.

Bob Hiller


"Bob Barrows [MVP]" <> wrote in message
news:...
> Bob and Sharon Hiller wrote:
>> Bob,
>> After
>> intRecordCount = adoRecordset.RecordCount
>>
> With a default server-side, forward-only cursor, Recordcount will always
> contain -1.
> http://www.aspfaq.com/show.asp?id=2193
> As this article says, there are better ways to count the records returned
> by
> a query.
>
>>
>> I also tried:
>> intRowsCount = adoRecordset.GetRows
>
> Mike addressed this one.
>>
>> Looking at the link you sent, it appears that I would need to totally
>> reconfigure the SQL server to use that method.
>
> Why? Where does that article say anything about reconfiguring SQL Server
> if
> you don't have to? Simply change your connection string to the one he
> suggests using for integrated (Windows) security. Here, let me show you:
>
>>>> Set adoConnection = server.CreateObject("ADODB.Connection")
>>>> Set adoRecordset = server.CreateObject("ADODB.Recordset")
>>>> adoConnection.ConnectionString = "Provider=MSDASQL.1;" _
>>>> & "Persist Security Info=False;" _
>>>> & "Extended Properties=Description=Large Pump Data Source;" _
>>>> & "DRIVER=SQL Server;SERVER=LPDATASYSTEM\PL3LP;" _
>>>> & "APP=Microsoft Data Access Components;" _
>>>> & "WSID=LPDATASYSTEM;DATABASE=LargePump;" _
>>>> & "Trusted_Connection=Yes;Initial Catalog=LargePump"
>>>
> adoConnection.ConnectionString = "Provider=SQLOLEDB;" _
> & "Persist Security Info=False;" _
> & "Data Source=LPDATASYSTEM\PL3LP;" _
> & "Application Name=Microsoft Data Access Components;" _
> & "Integrated Security=SSP1;Initial Catalog=LargePump"
>
> 'I would suggest setting the Application Name to a more specific name
> rather
> than the generic "Microsoft ... ". This will allow debugging using SQL
> Profiler to be easier (you can set up a trace using a filter to display
> only
> a specific application).
>
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Nobody can help you if you just throw up your hands instead of describing
your symptoms.





Bob and Sharon Hiller wrote:
> I am starting to think that there is no way to connect to SQL 2005
> with VBScript. I have tried 25 different suggestions from news groups
> and forums and none have worked. The only thing that seems to work is
> report services using .net and we are not going to change every page
> we have. I think we will just go back to SQL 2000 where everything
> worked fine.
>
> Thanks for the help.
>
> Bob Hiller
>
>
> "Bob Barrows [MVP]" <> wrote in message
> news:...
>> Bob and Sharon Hiller wrote:
>>> Bob,
>>> After
>>> intRecordCount = adoRecordset.RecordCount
>>>
>> With a default server-side, forward-only cursor, Recordcount will
>> always contain -1.
>> http://www.aspfaq.com/show.asp?id=2193
>> As this article says, there are better ways to count the records
>> returned by
>> a query.
>>
>>>
>>> I also tried:
>>> intRowsCount = adoRecordset.GetRows
>>
>> Mike addressed this one.
>>>
>>> Looking at the link you sent, it appears that I would need to
>>> totally reconfigure the SQL server to use that method.
>>
>> Why? Where does that article say anything about reconfiguring SQL
>> Server if
>> you don't have to? Simply change your connection string to the one he
>> suggests using for integrated (Windows) security. Here, let me show
>> you:
>>
>>>>> Set adoConnection = server.CreateObject("ADODB.Connection")
>>>>> Set adoRecordset = server.CreateObject("ADODB.Recordset")
>>>>> adoConnection.ConnectionString = "Provider=MSDASQL.1;" _
>>>>> & "Persist Security Info=False;" _
>>>>> & "Extended Properties=Description=Large Pump Data Source;" _
>>>>> & "DRIVER=SQL Server;SERVER=LPDATASYSTEM\PL3LP;" _
>>>>> & "APP=Microsoft Data Access Components;" _
>>>>> & "WSID=LPDATASYSTEM;DATABASE=LargePump;" _
>>>>> & "Trusted_Connection=Yes;Initial Catalog=LargePump"
>>>>
>> adoConnection.ConnectionString = "Provider=SQLOLEDB;" _
>> & "Persist Security Info=False;" _
>> & "Data Source=LPDATASYSTEM\PL3LP;" _
>> & "Application Name=Microsoft Data Access Components;" _
>> & "Integrated Security=SSP1;Initial Catalog=LargePump"
>>
>> 'I would suggest setting the Application Name to a more specific name
>> rather
>> than the generic "Microsoft ... ". This will allow debugging using
>> SQL Profiler to be easier (you can set up a trace using a filter to
>> display only
>> a specific application).
>>
>> Bob Barrows
>> --
>> Microsoft MVP -- ASP/ASP.NET
>> Please reply to the newsgroup. The email account listed in my From
>> header is my spam trap, so I don't check it very often. You will get
>> a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.