Change ASP Connection String Access to SQL

Changing a connection string on Web Site.

They were using an Access connection to connect, and I want to substitute the SQL Server connection string. Just wanted to make sure I do it right.

This is the Acces Connection that I have:

Code:

---

Dim cn
Set cn=server.createobject("adodb.connection")
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("\") & "\data\kallenrs.mdb;"
cn.open

---

This is the SQL Connection that I want for LOCAL testing, and I can change that later:

Code:

---

Driver={SQL Server Native Client 10.0};Server=.\SQLEXPRESS; Database=tracking2010;Trusted_Connection=yes;

---

I'll use "Select fID as FileNumber, x as a, y as b, z as c" to keep the naming convention the same.

Thanks!

Something like this?

Code:

---

Dim cn
Set cn=server.createobject("adodb.connection")
cn.ConnectionString = "Driver={SQL Server Native Client 10.0};Server=.\SQLEXPRESS; Database=tracking2010;Trusted_Connection=yes;"

---

Hi David :)

Why don't you make use of the SQL connection objects? Any specific reason?

You could Add the Namspace :

Code:

---

Imports System.Data.SqlClient

---

Then :

Code:

---

Dim con As SqlConnection
Dim cmd As SqlCommand
Dim ds As DataSet
Dim da As SqlDataAdapter

con = new SqlConnection("Server=You server name or compname; Database=Yourdatabasename;Trusted_Connection=True")
cmd = new SqlCommand("Write your sql query here eg. select * from Table name")

con.Open();

ds = new DataSet(cmd,con)

da = new SqlDataAdapter()

da.Fill(ds)

con.Close()

---

Does this help ?

Nope. Legacy DB, and the app runs on it.

I have that already. I need to modify it to open an MDB file using ADODB or OLEDB, if it's possible.

Besides this code is running in an ASP page, using VBA

I am using some more like this if memory serves

"Provider=MSDASQL; DRIVER=Sql Server; SERVER=x24400; DATABASE=MyDatabase; UID=sa; PWD=PW"

I'll look and see if I can find the web code I have where I used SQL server

hmm.. I must have archived the old web code no longer on my system I can't be sure now but I think that is the way I did it. Worked with SQL server 2000 don't know about express

Same thing. Never displays anything. Must not be connecting.

Code:

---

Dim cn

Set cn=server.createobject("adodb.connection")
'  Set cn=server.createobject("MSDASQL.connection")

'  cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("\") & "\data\kallenrs.mdb;"
'  cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\kallenrs\data\kallenrs.mdb;"

cn.ConnectionString = "Provider=MSDASQL;DRIVER=Sql Server;Server=whs-siok\SQLSERVER;Database='Tracking2010';Integrated Security = SSPI;"

'
'  cn.ConnectionString = "Provider=SQLOLEDB;Data Source=whs-siok\SQLSERVER;Initial Catalog='Tracking2010';Integrated Security = SSPI;"
'  cn.ConnectionString = "Driver={SQL Native Client};Server=whs-siok\SQLEXPRESS;Database=Tracking2010;Trusted_Connection=yes;"
response.write(cn.ConnectionString)

'      <add name="ConnectionString" connectionString="Integrated Security=SSPI;Pooling=false;Data Source=.\SQLEXPRESS;Initial Catalog=tracking2010"/>
'      <add name="ConnectionString" connectionString="Data Source=rs.db.*****.*****.com;Initial Catalog=kallenrs;User Id=sa;Password=pw;" />

cn.open

If Err.Number > 0 Then
        Response.Redirect "Error.htm"
End If

break

---

Being redirected to ERROR.HTM

Well your syntax does actually look right :eek:

Try this though :

Code:

---

Dim objConn
objConn = new ActiveXObject("ADODB.Connection");
objConn.Open(ConnectionString, User, Password);
objConn.Close();

---

Obciously the "ConnectionString", "User", and "Password" fields are your stuff that you have to fill in. :)

Try this, and if it works we can work on the Recordset object.

I really do hope it helps :)

It opens the Jet db but not the SQL db.

Have a look at this page

http://www.asp101.com/articles/john/...ng/default.asp

http://support.microsoft.com/default...b;en-us;265808

Thanks, tried them all, but none seem to be working. Not sure how to debug this. I guess I need to find someone that has already done this. It might help others.

SUCCESS!

Code:

---

<%
Dim dbhost, dbname_and_username, dbpwd, oConn, dbConn
dbhost = "x.x.x"
dbname_and_username = "xxx"
dbpwd = "yyy"
dbase1 ="zzz"
Set oConn = Server.CreateObject("ADODB.Connection")
dbConn = "DRIVER=SQL Server; SERVER="& dbhost &"; UID="& dbname_and_username &"; database="& dbase1 &";PWD="& dbpwd &"; Network Library=dbmssocn;"
oConn.Open(dbConn)


Set oConn = Server.CreateObject("ADODB.Connection")
dbConn = "DRIVER=SQL Server; SERVER="& dbhost &"; UID="& dbname_and_username &"; database="& dbase1 &";PWD="& dbpwd &"; Network Library=dbmssocn;"
oConn.Open(dbConn)
 

Dim dateRS, dateSQL
Set dateRS = Server.CreateObject("ADODB.Recordset")
dateSQL = "SELECT [fSaleDate], [fCaseNumber], [fAddress], [fJudgmentAmount], " & _
          "[fSaleResult] FROM [********] ORDER BY [fSaleDate]"

Set dateRS = oConn.Execute(dateSQL)
 

While Not dateRS.EOF
response.write("* " & dateRs("fSaleDate") & " * " & dateRs("fCaseNumber") & " * " & dateRs("fAddress")  &  _
  " * " & dateRs("fJudgmentAmount")  &" * " & dateRs("fSaleResult")  & " * <BR>")
response.write("* ------------------------------------------------------------------------------" & _
  "--------------------------------------------------------------- " & "  * <BR>")

dateRS.MoveNext
Wend
Set dateRS = Nothing
oConn.Close
Set oConn = Nothing

If Err.Number > 0 Then
      Response.Redirect "Error.htm"
 '              Else 
'                  response.write "Success"
End If
%>

---

Did it require the connection to be entered twice or is this just left over from testing?

Code:

---

Set oConn = Server.CreateObject("ADODB.Connection")
dbConn = "DRIVER=SQL Server; SERVER="& dbhost &"; UID="& dbname_and_username &"; database="& dbase1 &";PWD="& dbpwd &"; Network Library=dbmssocn;"
oConn.Open(dbConn)


Set oConn = Server.CreateObject("ADODB.Connection")
dbConn = "DRIVER=SQL Server; SERVER="& dbhost &"; UID="& dbname_and_username &"; database="& dbase1 &";PWD="& dbpwd &"; Network Library=dbmssocn;"
oConn.Open(dbConn)

---

Glad you got it fixed! Sorry I wasn't much help :)