View Single Post
  #1 (permalink)  
Old November 16th, 2004, 12:03 AM
mikersantiago mikersantiago is offline
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