Hi All,

Can you please guide me to convert my Access/ASP website to SQL Server/ASP, beginning from connecting to the database from ASP.

TIA

Antony

__________________
Antony

There are several things need to be looked into. It is not as simple as you say in a sentence. I can run you through some of them, that strikes my brain immediately.

Have you ported the data from Access to SQL?

1) You got to change you Connection string to connect to SQL Server based on the provider that you prefer. Standard connection string is

"Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"

Still you can refer for other ways to connect to SQLServer at this URL - www.connectionstrings.com

2) Some places where date fields are used should be looked into.
    Access uses #DATEFIELD# format for date manipulation.
    SQLServer does not use that, just have to put them within quotes - '2004-04-23 00:00:00.000' or '2004-04-23'

Still more to be done, I dont actually remember everything, but I can assure you that you would get a lot of help here then and there.

Feel free to ask if any clarifications needed.

All the best.

-Vijay G

I could port the data using transformation option in SQL.

Now trying Connection strings.

Thank you vijay

Antony

Hi,

The connection string i used is,

Set conn=server.createobject("ADODB.connection")
Conn.open "Provider=sqloledb;Data Source=raghav;Initial Catalog=pubs;User Id=antony;Password=abcd;"

and it went through.

How to refer the table in SQL. What i did in access is,

set rs=server.createobject("ADODB.Recordset")
sql="select * from mytable where noActive=1 order by SortCode"
set rs=conn.Execute(sql)

when i used the same code after the connection string, it gave me,
Invalid object name 'mytable' error

I have given "select" permission to user antony in the server.

The question i have is, I have not referred the Database anywhere in the connectionstring or in the sql i supplied to conn.execute. and I suppose that this could be causing the error. please help.

TIA

Antony

Give your database name in the connection string.

Conn.open "Provider=sqloledb;Data Source=raghav;Initial Catalog=YOUR_DATABASE_NAME;User Id=antony;Password=abcd;"

Currently it refers to PUBS databse.

-Vijay G