mdb to SQL SERVER

**Wolffy** · Wolffy, July 9th, 2009 09:57 AM

Hee hee..so it is. Didn't read back past mehere's post becasue in post #5 it appears that you are creating a connection object and then attempting to open it without setting the connection string, or anything else.

**mehere** · mehere, July 9th, 2009 10:34 AM

since the connection string is in an include and you're already setting the object ... you just need to do the following:

Code:

'This line is not needed - Set cn = Server.CreateObject("ADODB.Connection")
 
 Set rs = Server.CreateObject("ADODB.Recordset")
 
 'This is not needed ... cn.Open conn
 
 sql = "SELECT [password] FROM [member] " _
  & "WHERE [username] = '" & Request.Form("username") & "'"


 rs.Open sql, conn, 1

**micky** · micky, July 10th, 2009 03:43 AM

In my opinion, you should'nt open the connection object in the include file, as that would open the connection as soon the include page is called.
It should be opened in the page calling the connection object.

I would do it like this

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.ConnectionString = "provider=SQLOLEDB; Server=" & strSQLServerName & ";User ID=" & strSQLDBUserName & ";Password=" & strSQLDBPassword & ";Database=" & strSQLDBName & ";"	
%>

And then on pages where you need the connection

Code:

Set rs = Server.CreateObject("ADODB.Recordset")
 
conn.Open
 
sql = "SELECT [password] FROM [member] " _
  & "WHERE [username] = '" & Request.Form("username") & "'"

rs.Open sql, conn, 1

Also i think that you should include the file adovbs.inc on each page rather than the connection page.

**Wolffy** · Wolffy, July 10th, 2009 09:49 AM

I agree with micky here. It's kinda considered bad manners to open a connection and hold it without using it right away. There are limited connections available (as we have seen on this board of late

) to Sql Server, so the general rule is "open late, close early".

**Centurion** · Centurion, July 14th, 2009 04:43 AM

Originally Posted by micky

In my opinion, you should'nt open the connection object in the include file, as that would open the connection as soon the include page is called.
It should be opened in the page calling the connection object.

I would do it like this

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.ConnectionString = "provider=SQLOLEDB; Server=" & strSQLServerName & ";User ID=" & strSQLDBUserName & ";Password=" & strSQLDBPassword & ";Database=" & strSQLDBName & ";"	
%>

And then on pages where you need the connection

Code:

Set rs = Server.CreateObject("ADODB.Recordset")
 
conn.Open
 
sql = "SELECT [password] FROM [member] " _
  & "WHERE [username] = '" & Request.Form("username") & "'"

rs.Open sql, conn, 1

Also i think that you should include the file adovbs.inc on each page rather than the connection page.

getting this error message:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

/changep3.asp, line 43

line 43 :

Code:

 conn.Open

**micky** · micky, July 14th, 2009 06:18 AM

Show your code please

**Centurion** · Centurion, July 14th, 2009 06:32 AM

Originally Posted by micky

Show your code please

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 = "xxxxxxxxxx" '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.connectionstring = "provider=SQLOLEDB; Server=" & strSQLServerName & ";User ID=" & strSQLDBUserName & ";Password=" & strSQLDBPassword & ";Database=" & strSQLDBName & ";"
			
%> 

<head>
<meta http-equiv="Content-Language" content="en-gb">
</head>

<%
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 = conn
 
 Set conn = Server.CreateObject("ADODB.Connection")
 
 Set rs = Server.CreateObject("ADODB.Recordset")
 
 conn.Open conn
 
 sql = "SELECT [password] FROM [mem] " _
  & "WHERE [username] = '" & Request.Form("username") & "'"


 rs.Open sql, sc, 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
%>


</body>
</html>

<BR><A
  HREF="#"
  ONCLICK='
   self.close();
  '
>
 Click here to close the page
</A></BR>

**micky** · micky, July 14th, 2009 06:41 AM

You dont seem to get whats being suggested here.
You have already set the connection object once, so why do it again and why assign it to another variable sc??
Keep this in one file like connection.asp

Code:

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


'Create an ADO connection object
set conn=Server.CreateObject("ADODB.Connection")
strSQLServerName = "xxxxxxxxxx" '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.connectionstring = "provider=SQLOLEDB; Server=" & strSQLServerName & ";User ID=" & strSQLDBUserName & ";Password=" & strSQLDBPassword & ";Database=" & strSQLDBName & ";"%>

Then on other page.

Code:

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

<head>
<meta http-equiv="Content-Language" content="en-gb">
</head>

<%
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 rs, sql

conn.open
Set rs = Server.CreateObject("ADODB.Recordset")
 
sql = "SELECT [password] FROM [mem] " _
  & "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
 Set conn = Nothing
'End If
%>


</body>
</html>

<BR><A
  HREF="#"
  ONCLICK='
   self.close();
  '
>
 Click here to close the page
</A></BR>

**Centurion** · Centurion, July 14th, 2009 07:03 AM

Originally Posted by micky

You dont seem to get whats being suggested here.
You have already set the connection object once, so why do it again and why assign it to another variable sc??
Keep this in one file like connection.asp

Code:

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


'Create an ADO connection object
set conn=Server.CreateObject("ADODB.Connection")
strSQLServerName = "xxxxxxxxxx" '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.connectionstring = "provider=SQLOLEDB; Server=" & strSQLServerName & ";User ID=" & strSQLDBUserName & ";Password=" & strSQLDBPassword & ";Database=" & strSQLDBName & ";"%>

Then on other page.

Code:

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

<head>
<meta http-equiv="Content-Language" content="en-gb">
</head>

<%
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 rs, sql

conn.open
Set rs = Server.CreateObject("ADODB.Recordset")
 
sql = "SELECT [password] FROM [mem] " _
  & "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
 Set conn = Nothing
'End If
%>


</body>
</html>

<BR><A
  HREF="#"
  ONCLICK='
   self.close();
  '
>
 Click here to close the page
</A></BR>

Hi Micky,

getting this error?

error '80020009'

line 28, :

Code:

 ElseIf rs("password") <> oldpassword Then

mera website kaam nahi kar raha hei

**micky** · micky, July 14th, 2009 07:12 AM

koi baat nahi, chalne lagegi, chinta mat kar

You need to check if your recordset has a record or not, before you can read its value like rs("password").
So add such code

Code:

If NOT rs.BOF and NOT rs.EOF
 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
 End If
rs.Close
 Set rs = Nothing
 conn.Close
 Set conn = Nothing
'End If

Thread: mdb to SQL SERVER

LinkBack

Thread Tools

Display

Similar Threads

SQL Server Integration Services

SQL Server CREATE TABLE..LIKE

SQL Server 2008

SQL Server Views

Bookmarks

Bookmarks

Posting Permissions