Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old November 15th, 2004, 11:03 PM
Authorized User
 
Join Date: Sep 2004
Location: Las Pinas, , Philippines.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to mikersantiago
Default access db to sql server db

Hi wrox people :)

I'm currently migrating shopping cart type application from access database to sql server database.

This is the script for the access db,

<%
Dim vPath, pPath, ConString
vPath = "database\gold_db.mdb" 'use this one if database is in root of cart folder
'vPath = "\database\microscotch.mdb" 'use this one if database is in root of your website
pPath = Server.MapPath( vPath )

ConString = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & pPath & ";" & "JET OLEDB:Database Password=password"
set rsProdInfo = Server.CreateObject("ADODB.Recordset")
rsProdInfo.Open "products", Conn, adOpenStatic, adLockOptimistic, adCmdTable

             intTotal = 0

rsProd.MoveFirst
   while not rsProd.EOF
   rsProdInfo.MoveFirst
   rsProdInfo.Find "productID = " & rsProd("productID")
   intProdID = rsProd("productID")
   strProdName = rsProdInfo("productName")
   intPrice = formatNumber(rsProdInfo("productPrice"), 2)
   intQuant = rsProd("quantity")
   intExtPrice = formatNumber((intPrice * intQuant), 2)
   intTotal = intTotal + intExtPrice
%>


I changed the script to sql server db below,

<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB; Data Source = (local); Initial Catalog = gold_db; User Id = sa; Password="
Set SQLStmt = Server.CreateObject("ADODB.Command")

Set rsProdInfo = Server.CreateObject ("ADODB.Recordset")
SQLStmt.CommandText = "SELECT * FROM products"
SQLStmt.CommandType = 1
Set SQLStmt.ActiveConnection = conn
rsProdInfo.Open SQLStmt, adOpenStatic , adLockOptimistic, adCmdTable

intTotal = 0

rsProd.MoveFirst
   while not rsProd.EOF
   rsProdInfo.MoveFirst
   rsProdInfo.Find "productID = " & rsProd("productID")
   intProdID = rsProd("productID")
   strProdName = rsProdInfo("productName")
   intPrice = formatNumber(rsProdInfo("productPrice"), 2)
   intQuant = rsProd("quantity")
   intExtPrice = formatNumber((intPrice * intQuant), 2)
   intTotal = intTotal + intExtPrice
%>


I'm getting errors,

Error Type:
ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

specifically in,
rsProdInfo.Open SQLStmt, adOpenStatic , adLockOptimistic, adCmdTable

I have on the top of the page and the file on the server. I think it has something to do with the adovbs contants on updating a table/database in SQL Server in which there is something wrong in,
rsProdInfo.Open SQLStmt, adOpenStatic , adLockOptimistic, adCmdTable

Hoping to hear from you people. Thank you very much :)


Reply With Quote
  #2 (permalink)  
Old November 15th, 2004, 11:37 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

This error can indicate the connection string you have chosen to use is incorrect, or some parameter is incorrect. The connection to your database failed.

This is an example of a working connection string printed to the page:

Provider=SQLOLEDB.1; Password=pass; Persist Security Info=True; UID=user; Initial Catalog=DBName; Data Source=PCName;

To avoid putting this on every page, try the following:

1..Create a file called scripts/global.asp containing:
  const uName = "someString"
  const pass = "somePassWord"
  const DBName = "DatabaseName"
  const ServerName = "MachineName"
  Dim = Conn

2..Create a file called scripts/functions.asp containing
Function GetConnectionString()
   GetConnectionString ="Provider=SQLOLEDB.1; " & _
                "Password=" & pass & "; " & _
                "Persist Security Info=True; " & _
                    "UID=" & uName & "; " & _
                    "Initial Catalog=" & DBName & "; " & _
                    "Data Source=" & Servername & ";"
End Function

3..Any page you want a connection put this at the TOP of each page:
<% Option Explicit %>


<% set Conn=server.createobject("ADODB.Connection")
    Conn.Open(GetConnectionString)
    'to see your connection string on the page:
    response.write GetConnectionString() %>


Wind is your friend
Matt
Reply With Quote
  #3 (permalink)  
Old November 16th, 2004, 12:47 AM
Authorized User
 
Join Date: Sep 2004
Location: Las Pinas, , Philippines.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to mikersantiago
Default

Thanks Mat. I will apply this asap. Thanks again :)

Reply With Quote
  #4 (permalink)  
Old November 16th, 2004, 01:22 AM
Authorized User
 
Join Date: Sep 2004
Location: Las Pinas, , Philippines.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to mikersantiago
Default

Hi again Mat,

I understand the script you gave is the right/ more proper way to establish database connections (thus avoiding redundancy, database connectivity problems, etc.). I will adopt this. How about the establishing recordset part?

This script for access works fine,

set rsProdInfo = Server.CreateObject("ADODB.Recordset")
rsProdInfo.Open "products", Conn, adOpenStatic, adLockOptimistic, adCmdTable
'products - table
'Conn - database connection

Will this script work for SQL Server as well?
Does SQL Server understands the part,
rsProdInfo.Open "products", Conn, adOpenStatic, adLockOptimistic, adCmdTable


Does SQL Server has different ADO constants from Access database or ADO is universal for all databases using the ADO technology? (i.e. adOpenStatic, adLockOptimistic, etc.)

Are there good website/links that discusses different connections to SQL Server that you know (aside from the oledb - connection string you gave above, I just want to familiarize myself to the different connections)? Thanks a lot :)

Reply With Quote
  #5 (permalink)  
Old November 16th, 2004, 02:33 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes, that works well with SQL server too. ADO constants are the same be it access/sql server.

For connection strings check out Connectionstrings

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
Reply


Thread Tools
Display Modes

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 Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
can't access db in sql server 2005 brillox BOOK: Beginning ASP.NET 2.0 BOOK VB ISBN: 978-0-7645-8850-1; C# ISBN: 978-0-470-04258-8 3 May 3rd, 2007 05:15 PM
Locking in Access DB linked to SQL Server backend gibbers Access 3 April 24th, 2007 03:20 PM
MS ACCESS 2003 FRONTEND AND MS SQL SERVER 2005 DB mohankumar0709 SQL Server 2005 3 March 23rd, 2007 12:48 AM
how to convert ms access db to sql server method SQL Server 2000 1 March 11th, 2005 10:44 PM



All times are GMT -4. The time now is 12:40 PM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.