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 :)

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

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

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 :)

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