VBScript - ASP Access to SQL SERVER change

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;Trusted_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
Wed Mar 15 10:29:49 CST 2006

Bob and Sharon Hiller wrote:

Already answered in .inetserver.asp.general

Please don't multipost Bob. While .inetserver.asp.db would probably have
been the more appropriate place for this question, .general was fine.
Posting it here as
well did not increase your chances of getting an answer (most of us
subscribe to both groups). On the contrary, if somebody had taken his time
to answer it here, only to find that it was already resolved in the other
group, that person may have been annoyed enough to ignore any future posts
from you, thereby decreasing your chances of getting help in the future.

There are times when you will not be sure which group is most appropriate,
and you will want to post a question to both groups. In that situation, you
should use the cross-posting technique, rather than posting the same message
multiple times. To crosspost, put a semicolon-delimited* list of the
newsgroups to which you wish to post in the To: header of your post and post
it once. It, and any replies to it, will appear in all the newsgroups in
your list. So, if I reply in .asp.general, my reply will also appear here in
.asp.general.

* ... or whatever delimiter is recognized by your news client

--
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.

Richard
Wed Mar 15 10:43:15 CST 2006


"Bob and Sharon Hiller" <aoklans@tir.com> wrote in message
news:eLQabvESGHA.4440@TK2MSFTNGP11.phx.gbl...
> 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;Trusted_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

Hi,

I would try:

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

In VBScript I personally use:

strDB = "MyDatabase"
strServer = "MyServer"
strInstance = "MyInstance"

strConnect = "DRIVER=SQL Server;" _
& "Trusted_Connection=Yes;" _
& "DATABASE=" & strDB & ";" _
& "SERVER=" & strServer & "\" & strInstance

Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.ConnectionString = strConnect
adoConnection.CommandTimeout = 120
adoConnection.Open

--
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net

Bob
Wed Mar 15 12:00:40 CST 2006

Richard,
I changed my code as you suggested and I am not getting any records.(there
are currently 2054 rows in the table)

Here is what I have:
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 else wrong? 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. Is there a msgbox statement or equivalent that I can use to
variables as the script executes and a web page?


"Richard Mueller" <rlmueller-NOSPAM@ameritech.NOSPAM.net> wrote in message
news:%232nvQ%23ESGHA.5036@TK2MSFTNGP12.phx.gbl...
>
> "Bob and Sharon Hiller" <aoklans@tir.com> wrote in message
> news:eLQabvESGHA.4440@TK2MSFTNGP11.phx.gbl...
>> 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;Trusted_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
>
> Hi,
>
> I would try:
>
> 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
>
> In VBScript I personally use:
>
> strDB = "MyDatabase"
> strServer = "MyServer"
> strInstance = "MyInstance"
>
> strConnect = "DRIVER=SQL Server;" _
> & "Trusted_Connection=Yes;" _
> & "DATABASE=" & strDB & ";" _
> & "SERVER=" & strServer & "\" & strInstance
>
> Set adoConnection = CreateObject("ADODB.Connection")
> adoConnection.ConnectionString = strConnect
> adoConnection.CommandTimeout = 120
> adoConnection.Open
>
> --
> Richard
> Microsoft MVP Scripting and ADSI
> Hilltop Lab - http://www.rlmueller.net
>
>

Richard
Wed Mar 15 14:12:33 CST 2006

My best guess is an authentication issue in asp. Trusted_Connection=Yes
means use Windows Integrated authentication, which you indicate works in
VB6. My guess is you don't have permission in ASP. Maybe you could test this
theory by granting public rights to the database temporarily in SQL
Enterprise Manager. Otherwise, you need to post in an ASP newsgroup.

--
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net

"Bob and Sharon Hiller" <aoklans@tir.com> wrote in message
news:uUPMjpFSGHA.1780@TK2MSFTNGP12.phx.gbl...
> Richard,
> I changed my code as you suggested and I am not getting any records.(there
> are currently 2054 rows in the table)
>
> Here is what I have:
> 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 else wrong? 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. Is there a msgbox statement or equivalent that I can use to
> variables as the script executes and a web page?
>
>
> "Richard Mueller" <rlmueller-NOSPAM@ameritech.NOSPAM.net> wrote in message
> news:%232nvQ%23ESGHA.5036@TK2MSFTNGP12.phx.gbl...
>>
>> "Bob and Sharon Hiller" <aoklans@tir.com> wrote in message
>> news:eLQabvESGHA.4440@TK2MSFTNGP11.phx.gbl...
>>> 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;Trusted_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
>>
>> Hi,
>>
>> I would try:
>>
>> 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
>>
>> In VBScript I personally use:
>>
>> strDB = "MyDatabase"
>> strServer = "MyServer"
>> strInstance = "MyInstance"
>>
>> strConnect = "DRIVER=SQL Server;" _
>> & "Trusted_Connection=Yes;" _
>> & "DATABASE=" & strDB & ";" _
>> & "SERVER=" & strServer & "\" & strInstance
>>
>> Set adoConnection = CreateObject("ADODB.Connection")
>> adoConnection.ConnectionString = strConnect
>> adoConnection.CommandTimeout = 120
>> adoConnection.Open
>>
>> --
>> Richard
>> Microsoft MVP Scripting and ADSI
>> Hilltop Lab - http://www.rlmueller.net
>>
>>
>
>