|
Subject:
|
Acess 2000 to Sql Server
|
|
Posted By:
|
mikersantiago
|
Post Date:
|
11/15/2004 10:15:41 AM
|
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 <!-- #include file="adovbs.inc" --> 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 By:
|
shahchi1
|
Reply Date:
|
11/15/2004 11:19:05 AM
|
Change SQLStmt.CommandType = 1
to
SQLStmt.CommandType = adCmdText
Let me know if you are still receiving an error.
|
|
Reply By:
|
mikersantiago
|
Reply Date:
|
11/15/2004 10:49:26 PM
|
Hi shahchi1,
I will try your suggestion soon as I get home. Thanks a lot :)
|
|
Reply By:
|
mikersantiago
|
Reply Date:
|
11/16/2004 6:58:07 AM
|
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.
|
|
Reply By:
|
shahchi1
|
Reply Date:
|
11/16/2004 9:26:28 AM
|
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
|
|
