Trouble converting Access database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Trouble converting Access database

Author

Topic

havoc33
Starting Member

8 Posts

Posted - 2004-06-02 : 13:18:09

A total newbie to SQL/ASP, I decided to check out the free shoppingcart at http://www.urlogy.com/asp/ashopkart.asp. I've got SQL Server up and running, but I ran into some problems converting the Access database to SQL.

The tables converted fine, but there are 4 queries that don't and I have no idea how to fix them. Hope someone can help me with this, here are the 4 queries I got out of the database:

qryOrderInfo

SELECT orders.orderID, orders.*, products.ccode, products.cname, products.cprice, oitems.numitems,
customers.*
FROM customers INNER JOIN (orders INNER JOIN (products INNER JOIN oitems ON
products.catalogID = oitems.catalogid) ON orders.orderID = oitems.orderid) ON customers.custID =
orders.ocustomerid

qryOrders

SELECT products.*, orders.*, oitems.*, customers.*
FROM customers INNER JOIN (orders INNER JOIN (products INNER JOIN oitems ON
products.catalogID = oitems.catalogid) ON orders.orderID = oitems.orderid) ON customers.custID =

qryProdsCategory

SELECT products.*, products.ccategory, categories.catdescription
FROM categories INNER JOIN products ON categories.categoryID = products.ccategory
WHERE (((products.ccategory)=[theCategory]));

qryProduct

SELECT products.*, products.catalogID, products.catalogID AS shownID
FROM products
WHERE (((products.catalogID)=[prodID]));

If someone can help me with this I'll be forever grateful.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-02 : 13:24:38

[code]

SELECT o.orderID, o.*, p.ccode, p.cname, p.cprice, oi.numitems, c.*
FROM customers c
INNER JOIN orders o ON c.custID = o.ocustomerid
INNER JOIN oitems oi ON o.orderID = oi.orderid
INNER JOIN products p ON oi.catalogID = p.catalogid

SELECT p.*, o.*, oi.*, c.*
FROM customers c
INNER JOIN orders o ON c.custID = o.ocustomerid
INNER JOIN oitems oi ON o.orderID = oi.orderid
INNER JOIN products p ON oi.catalogID = p.catalogid

SELECT p.*, p.ccategory, c.catdescription
FROM categories c
INNER JOIN products p
ON c.categoryID = p.ccategory
WHERE p.ccategory=theCategory

SELECT *, catalogID, catalogID AS shownID
FROM products
WHERE catalogID=prodID

[/code]

And get rid of * in your queries! Explicitly write out which columns you need. * takes a performance hit and is considered bad programming.

Tara

havoc33
Starting Member

8 Posts

Posted - 2004-06-02 : 13:31:23

So then I'll just go to Stored Procedures in SQL Enterprise Manager and create each of the queries there?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-02 : 13:34:49

Use Query Analyzer. Enterprise Manager is not a good tool for writing code.

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-02 : 13:35:44

Or doing anything for that matter.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

havoc33
Starting Member

8 Posts

Posted - 2004-06-02 : 13:48:14

Um, bear with me here guys, but on the last two, "the category" and "prodID" doesn't seem to be valid table fields??

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-02 : 13:52:45

I'm sure they need to be variables. Variables in SQL stored procedures have @ in front of them.

Tara

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2004-06-02 : 13:53:15

I am guessing that those are variables. You will need to create a variable to pass to the procedure @theCategory and @prodID. When you run the query in Access, does it prompt you for theCategory or prodID?

Jeremy W. Oldham

by the princess!

havoc33
Starting Member

8 Posts

Posted - 2004-06-02 : 13:58:49

Yes, and Access prompt me for the "oid" in qryOrderInfo too. I have no clue what to do with this, can one of you guide me through it?

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2004-06-02 : 14:03:29

Create Procedure qryOrderInfo
as
SELECT o.orderID, o.*, p.ccode, p.cname, p.cprice, oi.numitems, c.*
FROM customers c
INNER JOIN orders o ON c.custID = o.ocustomerid
INNER JOIN oitems oi ON o.orderID = oi.orderid
INNER JOIN products p ON oi.catalogID = p.catalogid

Create Procedure qryOrders
as
SELECT p.*, o.*, oi.*, c.*
FROM customers c
INNER JOIN orders o ON c.custID = o.ocustomerid
INNER JOIN oitems oi ON o.orderID = oi.orderid
INNER JOIN products p ON oi.catalogID = p.catalogid

Create Procedure qryProdsCategory
@theCategory as nvarchar(200)
as
SELECT p.*, p.ccategory, c.catdescription
FROM categories c
INNER JOIN products p
ON c.categoryID = p.ccategory
WHERE p.ccategory=@theCategory

Create Procedure qryProduct
@prodID as int
as
SELECT *, catalogID, catalogID AS shownID
FROM products
WHERE catalogID=@prodID

Not sure on the OID as I do not see this in the query.

Jeremy W. Oldham

havoc33
Starting Member

8 Posts

Posted - 2004-06-02 : 14:56:09

Finally, I got it to work! Well, almost cause I get an error msg when I put an item in the shoppingcart. "Your browser does not enable cookies". But my browser DO enable cookies.. man, this is driving me crazy. Guess I'll head for a vbscript/asp forum next...

Anyways, thanks a bunch for all the help!

havoc33
Starting Member

8 Posts

Posted - 2004-06-03 : 11:59:46

Hey guys, think you can help me out a bit more? I've been posting in ASP forums, but we can't get the final process page in the shopping cart script to work.
--------------------------------------------------------------------------------
INSERT INTO orders(ocustomerid,odate,orderamount,ocardtype,ocardno,ocardname,ocardexpires,ocardaddress) VALUES(100022,'3/6/2004',356.95,'Visa','6541254789654123','Harry Olsen','28/12/2006',' ')
---------------------------------------------------------------------------------

I get the following error msg:

The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
/eksamen/process.asp, line 70

Line 70 is: dbc.execute sqlAdd, intAffected
Here's the code:

sqlAdd = "INSERT INTO orders(ocustomerid,odate,orderamount,ocardtype,ocardno,"
sqlAdd = sqlAdd & "ocardname,ocardexpires,ocardaddress"
If Not Request.Form("shipaddress")="" then
sqlAdd = sqlAdd & ",oshipaddress,oshiptown,oshipzip,oshipstate,oshipcountry"
End If
sqlAdd = sqlAdd & ") VALUES("
sqlAdd = sqlAdd & Session("customerid") & ",'" & MyConvert(Date) & "'," & intTotal
sqlAdd = sqlAdd & ",'" & Request.Form("paymentm") & "','" & Request.Form("cardno") & "'"
sqlAdd = sqlAdd & ",'" & TwoSingleQ(Request.Form("cardname")) & "','" & expDate & "'"
sqlAdd = sqlAdd & ",'" & TwoSingleQ(Request.Form("cardaddress")) & " '"
If Not Request.Form("shipaddress")="" then
sqlAdd = sqlAdd & ",'" & TwoSingleQ(Request.Form("shipaddress")) & "'"
sqlAdd = sqlAdd & ",'" & TwoSingleQ(Request.Form("shiptown")) & " '"
sqlAdd = sqlAdd & ",'" & Request.Form("shipzip") & " '"
sqlAdd = sqlAdd & ",'" & Request.Form("shipstate") & " '"
sqlAdd = sqlAdd & ",'" & Request.Form("shipcountry") & " '"
End If
sqlAdd = sqlAdd & ")"

Function MyConvert(dtDate)
Dim strAns
strAns=Day(dtDate)&"/"&Month(dtDate)&"/"&Year(dtDate)
MyConvert=strAns
End Function

'Response.Write (sqlAdd&"<BR>")
'Response.END
call openConn()
dbc.execute sqlAdd, intAffected

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-06-03 : 12:26:26

Instead of Month(dtDate) is there a function that will give you monthname? If so use that instead...

havoc33
Starting Member

8 Posts

Posted - 2004-06-03 : 12:48:33

Does anyone here have SQL Server? If so, I can send the files. That way it'll be easier to find the error. I've been posting in forums all day, and no one has been able to come up with a solution. So, do anyone here have SQL Server?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-03 : 12:51:37

We all do. This is a SQL Server site.

The error suggests you need to be using datetime instead of smalldatetime for one of your columns.

Tara

havoc33
Starting Member

8 Posts

Posted - 2004-06-03 : 13:08:16

Hi Tara, I've tried changing each of the colums with smalldatetime to datetime, but no success. Maybe you have the time to look at the files? Can I send the files and database to you? I bet you can find the error in no time.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-03 : 13:17:35

Analysis like that would not be free. You might consider hiring a contractor for a few hours/days of work.

I would suggest pulling out the T-SQL code and putting it into Query Analyzer and testing it out there. Play with the INSERT statement to figure out where your problem is. Once you've figured out where, it should be easy to fix your code

Tara

havoc33
Starting Member

8 Posts

Posted - 2004-06-03 : 13:49:19

As you said, by using Query Analyser I found the mistake. Next error up, (does it never end?):

Microsoft OLE DB Provider for SQL Server (0x80040E14)
Procedure qryOrderInfo has no parameters and arguments were supplied.

I guess there should be a @ here somewhere?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-03 : 14:01:17

Well show us qryOrderInfo and show us what you are passing to it (the call to it).

Tara

havoc33
Starting Member

8 Posts

Posted - 2004-06-03 : 14:25:22

Nevermind, I got it to work! Finally! Thanks for all the help, hopefully I won't bother you again!! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources