DeveloperBarn Forums

DeveloperBarn

Programming & IT forum

mdb to SQL SERVER

This is a discussion on mdb to SQL SERVER within the ASP Development forums, part of the Programming & Scripting category; Hee hee..so it is. Didn't read back past mehere's post becasue in post #5 it appears that you are creating ...

Go Back   DeveloperBarn Forums > Programming & Scripting > ASP Development

mdb to SQL SERVER mdb to SQL SERVER

Register FAQ Social Groups Calendar Search Today's Posts Mark Forums Read

Reply
Page 2 of 3 1 2 3

 

LinkBack Thread Tools Display Modes
  #11  
Old July 9th, 2009, 09:57 AM
Wolffy's Avatar
Wolfmaster
  
Join Date: Mar 2008
Real name: Wolff
Location: Peoria, IL
Posts: 779
Blog Entries: 1
Rep Power: 9
Wolffy is a splendid one to beholdWolffy is a splendid one to beholdWolffy is a splendid one to beholdWolffy is a splendid one to beholdWolffy is a splendid one to beholdWolffy is a splendid one to beholdWolffy is a splendid one to behold
Default
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.
__________________
Wolffy
------------------------
Opinions expressed are my own and do not necessity reflect those of any sane person. Any code provided is intended to be an example and is provided AS IS. Rework for your specific environment may be required. Void where prohibited by law. Not valid in California. Your mileage may vary.
Reply With Quote
  #12  
Old July 9th, 2009, 10:34 AM
mehere's Avatar
Super Sarcasm Mistress
  
Join Date: Mar 2008
Real name: Joanne
Location: Wide Awake In Dreamland
Posts: 375
Rep Power: 6
mehere is just really nicemehere is just really nicemehere is just really nicemehere is just really nicemehere is just really nice
Default
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
__________________
Quote of the Month:
Mistakes: It could be that the purpose of your life is only to serve as a warning to others.

Questions to Ponder:
Why do banks charge you a "non-sufficient funds fee" on money they already know you don't have?

iif([sarcasm]=true,iif([you have to ask]=true,"didn't work","ha ha ha"),"not sarcasm")
copyright © 2008 sbenj69

Sarchasm: The gulf between the author of sarcastic wit and the person who doesn't get it.
Reply With Quote
  #13  
Old July 10th, 2009, 03:43 AM
micky's Avatar
Lazy Bum
  
Join Date: Jul 2008
Location: India
Posts: 566
Rep Power: 4
micky has a spectacular aura aboutmicky has a spectacular aura aboutmicky has a spectacular aura about
Default
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.

Comments on this post
Wolffy agrees:
__________________
Get the Mantra!
Reply With Quote
  #14  
Old July 10th, 2009, 09:49 AM
Wolffy's Avatar
Wolfmaster
  
Join Date: Mar 2008
Real name: Wolff
Location: Peoria, IL
Posts: 779
Blog Entries: 1
Rep Power: 9
Wolffy is a splendid one to beholdWolffy is a splendid one to beholdWolffy is a splendid one to beholdWolffy is a splendid one to beholdWolffy is a splendid one to beholdWolffy is a splendid one to beholdWolffy is a splendid one to behold
Default
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".
Reply With Quote
  #15  
Old July 14th, 2009, 04:43 AM
Centurion's Avatar
Barn Enthusiast
  
Join Date: Dec 2008
Posts: 352
Rep Power: 2
Centurion is on a distinguished road
Default
Quote:
Originally Posted by micky View Post
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
Reply With Quote
  #16  
Old July 14th, 2009, 06:18 AM
micky's Avatar
Lazy Bum
  
Join Date: Jul 2008
Location: India
Posts: 566
Rep Power: 4
micky has a spectacular aura aboutmicky has a spectacular aura aboutmicky has a spectacular aura about
Default
Show your code please
Reply With Quote
  #17  
Old July 14th, 2009, 06:32 AM
Centurion's Avatar
Barn Enthusiast
  
Join Date: Dec 2008
Posts: 352
Rep Power: 2
Centurion is on a distinguished road
Default
Quote:
Originally Posted by micky View Post
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>
Reply With Quote
  #18  
Old July 14th, 2009, 06:41 AM
micky's Avatar
Lazy Bum
  
Join Date: Jul 2008
Location: India
Posts: 566
Rep Power: 4
micky has a spectacular aura aboutmicky has a spectacular aura aboutmicky has a spectacular aura about
Default
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>
Reply With Quote
  #19  
Old July 14th, 2009, 07:03 AM
Centurion's Avatar
Barn Enthusiast
  
Join Date: Dec 2008
Posts: 352
Rep Power: 2
Centurion is on a distinguished road
Default
Quote:
Originally Posted by micky View Post
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
Reply With Quote
  #20  
Old July 14th, 2009, 07:12 AM
micky's Avatar
Lazy Bum
  
Join Date: Jul 2008
Location: India
Posts: 566
Rep Power: 4
micky has a spectacular aura aboutmicky has a spectacular aura aboutmicky has a spectacular aura about
Default
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
Reply With Quote
Reply
Page 2 of 3 1 2 3

  DeveloperBarn Forums > Programming & Scripting > ASP Development

Bookmarks

« Previous Thread | Next Thread »
Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads

Thread Thread Starter Forum Replies Last Post
SQL Server Integration Services guddu Microsoft SQL Server 1 April 24th, 2009 07:08 AM
SQL Server CREATE TABLE..LIKE Wolffy SQL Code Samples 2 March 12th, 2009 04:51 PM
SQL Server 2008 stephenhy88 Microsoft SQL Server 7 August 13th, 2008 01:55 AM
SQL Server Views theChris Microsoft SQL Server 3 March 23rd, 2008 07:35 PM


All times are GMT -4. The time now is 05:13 PM.

Contact Us - DeveloperBarn.com - Top

Copyright ©2008-2010, DeveloperBarn

Content Relevant URLs by vBSEO 3.3.2