Change ASP Connection String Access to SQL

[dglienna]

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:

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:

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!

[dglienna]

Something like this?

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

[HanneSThEGreaT]

Hi David :)

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

You could Add the Namspace :

Imports System.Data.SqlClient


Then :

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 ?

[dglienna]

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

[DataMiser]

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

[DataMiser]

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

[dglienna]

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

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

[HanneSThEGreaT]

Well your syntax does actually look right :eek:

Try this though :

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 :)

[dglienna]

It opens the Jet db but not the SQL db.

[DataMiser]

Have a look at this page

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

http://support.microsoft.com/default.aspx?scid=kb;en-us;265808

[dglienna]

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.

[dglienna]

SUCCESS!


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

[DataMiser]

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

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)

[HanneSThEGreaT]

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