Thread: mdb to SQL SERVER

i have a connection:

Code:

<!--#include file="adovbs.inc"-->

<%
Response.Expires = -1500
Response.Buffer = true
Server.ScriptTimeout = 100
Session.Timeout = 90



			'Create an ADO connection object
			set conn=Server.CreateObject("ADODB.Connection")
			strSQLServerName = "XXXXXXX" 'Holds the name of the SQL Server (This is the name/location or IP address of the SQL Server)
			strSQLDBUserName = "SA" 'Holds the user name (for SQL Server Authentication)
			strSQLDBPassword = "test" 'Holds the password (for SQL Server Authentication)
			strSQLDBName = "db2SQL" 
			
				'Initilise the DB Connection String
			conn.open = "provider=SQLOLEDB; Server=" & strSQLServerName & ";User ID=" & strSQLDBUserName & ";Password=" & strSQLDBPassword & ";Database=" & strSQLDBName & ";"
		
%>

and i have this code below which is working with an access database.. my problem is, i have tryed changing cn to conn but it still doesnt work.. could anyone suggest what i should do? or help me code this? pretty please

Code:

<%
Dim submit1, oldpassword, newpassword, confirmpassword
submit1 = Server.HTMLEncode(Request.Form("submit1"))
oldpassword = Request.Form("oldpassword")
newpassword = Request.Form("newpassword")
confirmpassword = Request.Form("confirmpassword")
'If submit1 = "confirmpassword" Then
 Dim sc, cn, rs, sql
 
 sc = "driver={Microsoft Access Driver (*.mdb)};dbq=" _
 & Server.MapPath("../DB/H2.mdb")
 
 Set cn = Server.CreateObject("ADODB.Connection")
 
 Set rs = Server.CreateObject("ADODB.Recordset")
 
 cn.Open conn
 
 sql = "SELECT [password] FROM [member] " _
  & "WHERE [username] = '" & Request.Form("username") & "'"


 rs.Open sql, cn, 1
 
 If oldpassword = "" Or newpassword = "" Then
  Response.Write "Make sure password is valid and fill all the fields please!"
 ElseIf rs("password") <> oldpassword Then
  Response.Write "Wrong password inserted!"
 ElseIf newpassword <> confirmpassword Then
  Response.Write "Confirm password and new password are not the same!"
 ElseIf oldpassword = newpassword Then
  Response.Write "Old password and new password are the same!"
 Else
    
    sql = "UPDATE mem SET password = '" & newpassword & "' WHERE " _
  & "username = '" & Request.Form("username") & "'"

  cn.Execute(sql)
  Response.Write "Your Password has been changed!"
 End If
 rs.Close
 Set rs = Nothing
 cn.Close
 conn.close
 Set cn = Nothing
'End If
%>

Originally Posted by micky

Have you included the connection file or is it on the same page??

Also do you get any error?
If yes what is it?

the connection file is included in the page.. and the line in the connection file is called "Conn"

the connection is to a sql server database.

Originally Posted by jmurrayhead

We'll need more info than that...what happens when you try to run the code. Also, you will have to change all the variables to either cn or conn. You can't have both.

i have changed cn to conn :

Code:

<%
Dim submit1, oldpassword, newpassword, confirmpassword
submit1 = Server.HTMLEncode(Request.Form("submit1"))
oldpassword = Request.Form("oldpassword")
newpassword = Request.Form("newpassword")
confirmpassword = Request.Form("confirmpassword")
'If submit1 = "confirmpassword" Then
 Dim sc, conn, rs, sql
 
' sc = "driver={Microsoft Access Driver (*.mdb)};dbq=" _
' & Server.MapPath("../DB/H2.mdb")
 
 Set conn = Server.CreateObject("ADODB.Connection")
 
 Set rs = Server.CreateObject("ADODB.Recordset")
 
 conn.Open conn
 
 sql = "SELECT [password] FROM [member] " _
  & "WHERE [username] = '" & Request.Form("username") & "'"


 rs.Open sql, conn, 1
 
 If oldpassword = "" Or newpassword = "" Then
  Response.Write "Make sure password is valid and fill all the fields please!"
 ElseIf rs("password") <> oldpassword Then
  Response.Write "Wrong password inserted!"
 ElseIf newpassword <> confirmpassword Then
  Response.Write "Confirm password and new password are not the same!"
 ElseIf oldpassword = newpassword Then
  Response.Write "Old password and new password are the same!"
 Else
    
    sql = "UPDATE mem SET password = '" & newpassword & "' WHERE " _
  & "username = '" & Request.Form("username") & "'"

  conn.Execute(sql)
  Response.Write "Your Password has been changed!"
 End If
 rs.Close
 Set rs = Nothing
 conn.Close
 conn.close
 Set conn = Nothing
'End If
%>

and the error i get is..:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
/changep3.asp, line 23

line 23 is :

Code:

 conn.Open conn

Originally Posted by mehere

maybe it's me, but i don't even see a connection string to SQL ... the only one i see, is the one to Access and that one is commented out.

the include file contains the connection.

when i did ..

cn = conn

it crashed

Originally Posted by Wolffy

Well, the problem is probably with the connection string. Without seeing that, we can't really help much.

the connection string was in my first post!...