Wrox Programmer Forums
| 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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old November 16th, 2004, 12:03 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 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 :)


  #2 (permalink)  
Old November 16th, 2004, 12:37 AM
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
  #3 (permalink)  
Old November 16th, 2004, 01: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 :)

  #4 (permalink)  
Old November 16th, 2004, 02: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 :)

  #5 (permalink)  
Old November 16th, 2004, 03:33 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
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


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 11:44 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.