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

Change SQLStmt.CommandType = 1
to
SQLStmt.CommandType = adCmdText

Let me know if you are still receiving an error.

Hi shahchi1,

I will try your suggestion soon as I get home. Thanks a lot :)

Hi shahchi1,

I've tried your suggestion, but still i'm getting the error...

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

I think the error is in this part (since the error points to this line number),
rsProdInfo.Open SQLStmt, adOpenStatic, adLockOptimistic, adCmdTable

I'm not just very sure which exactly. Please help. Thanks.

rsProdInfo.Open SQLStmt, adOpenStatic, adLockOptimistic, adCmdTable

You should be using adCmdText instead of adCmdTable

rsProdInfo.Open SQLStmt, adOpenStatic, adLockOptimistic, adCmdText

Also, check out http://www.adopenstatic.com/faq/800a0bb9.asp