SQL Message?!

LanceStorm

ive a website project here and there's one or a dozen things going wrong with it!!

just a small problem to start off with tho
when I try enter data into the database via the webpage above an SQL thing comes up at the top of the pages telling me where its entered the data
"SQL Query: INSERT INTO orders VALUES ('admin', '5', 'snickers')"

just want to know if there's a way to stop that appearring??
any help'd be great!!

<%
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("db/order.mdb"))

'First we should check if trying to insert
'Only execute the INSERT statement if it has been requested
If Request("submit") = "Insert" Then
sSQL = "INSERT INTO orders VALUES ('" & Request("uname") & "', '" & Request("quantity") & "', '" & Request("product") & "')"

Response.Write("SQL Query: " & sSQL & "<BR><BR>")
oConn.Execute(sSQL)
End If

'SELECT from the database
sSQL = "SELECT * FROM orders"
set oRS = oConn.Execute(sSQL)
%>
<%
oConn.Close
Set oRS = Nothing
Set oConn = Nothing
%>

Mutant_Fruit

Remove this: Response.Write("SQL Query: " & sSQL & "<BR><BR>")

LanceStorm

I cannot belive it was that easy!!
thanks mutant fruit!!

one problem down!!

---

another problem arises!!

[Microsoft][ODBC Microsoft Access Driver] The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

when I enter any data into the database through the website twice in any of the fields it wont let me...
is there an option in access to allow it??

LanceStorm

Fairly sure its not a code problem, it seems to be the database itself that wont allow multiple entires of the same type?!

For Example

It takes in 3 details

Username
Quantity
Product

But if after ordering Quantity - 20
It wont let anyone else enter 20 again in the Quantity field!!

bit of a problem...!!

mathie

LanceStorm wrote:

I cannot belive it was that easy!!
thanks mutant fruit!!

one problem down!!

---

another problem arises!!

[Microsoft][ODBC Microsoft Access Driver] The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

when I enter any data into the database through the website twice in any of the fields it wont let me...
is there an option in access to allow it??

That looks like a Primary Key error.
Whats your Primary Key for the table?
M

mathie

LanceStorm wrote:

Fairly sure its not a code problem, it seems to be the database itself that wont allow multiple entires of the same type?!

For Example

It takes in 3 details

Username
Quantity
Product

But if after ordering Quantity - 20
It wont let anyone else enter 20 again in the Quantity field!!

bit of a problem...!!

Ok looks like Quantity is the PK.
That sounds incorrect.
Why would you want the unique identifier for a row to be quantity?
Surely (if this table takes orders) then the PK would be an Order_ID or something like that

M

LanceStorm

the primary key is uname!!

mathie

LanceStorm wrote:

the primary key is uname!!

Why?
In your setup a user can only ever have one order.

That's the reason why the insert won't work.
Not because the quantity is the same but because the uname is the same.

M

Inspector Gadget

Does the "Indexed" property of the field in question (look at the table in Design view) say "Yes - No Duplicates"? If so, set it to something else...

Gadget

LanceStorm

you legend!!

that makes total sense now!!
so ive to add in a field with a unique order number so that it avoids that problem!!

tonyj

Is this a work project (i.e. something that's going to be used for real), or is it just something you're doing for school/college?

If it's a work project, then I'd take a good look at the design of your 'orders' table. You're going to need something a bit more complex than just three fields if you're planning on writing an order-taking system. Most order processing systems have something like;

Customer Table
Product Table
Order Header
Order Detail

With fields like 'Order Number, Order Date, Order Line No, Customer No., Product Code, Quantity, Order Status' etc...

If it's something for college, then just make a new field called 'OrderLineNo' integer, and make that the PK.

You're right, you can't have something like 'quantity' as PK - Makes no sense. Also, make sure you're declaring the correct data types for these fields (numeric or varchar)

mathie

Inspector Gadget wrote:

Does the "Indexed" property of the field in question (look at the table in Design view) say "Yes - No Duplicates"? If so, set it to something else...

Gadget

Probably best to keep with a PK that cannot be duplicated.

I think the OP needs to undestand the concept of a PK and what constitues a PK.

In this case I'd have

Order_ID (PK)
Username
Quantity
Product_ID (Foreign Key - which will point to the Products table)

M

Inspector Gadget

mathie wrote:

Probably best to keep with a PK that cannot be duplicated.

I think the OP needs to undestand the concept of a PK and what constitues a PK.

In this case I'd have

Order_ID (PK)
Username
Quantity
Product_ID (Foreign Key - which will point to the Products table)

M

I think you may need to read the thread a bit more thoroughly. If a customer were to repeat an order (not an uncommon event in such a situation, I think you'd agree), and you used that PK, you'd be in a little trouble, wouldn't you??? It's possible it'd work with some modifications, but...

Gadget

mathie

Inspector Gadget wrote:

I think you may need to read the thread a bit more thoroughly. If a customer were to repeat an order (not an uncommon event in such a situation, I think you'd agree), and you used that PK, you'd be in a little trouble, wouldn't you??? It's possible it'd work with some modifications, but...

Gadget

I've read the thread quite thoroughly.

If a customer repeats an order he gets a new order_id.

???
M

LanceStorm

Inspector Gadget wrote:

Does the "Indexed" property of the field in question (look at the table in Design view) say "Yes - No Duplicates"? If so, set it to something else...

Gadget

It didnt have that, but ive added an autonumber for the order ID and it has that set obviously, is that likely to cause the same problem??

Inspector Gadget

mathie wrote:

I've read the thread quite thoroughly.

If a customer repeats an order he gets a new order_id.

???
M

My mistake, missed that bit, sorry... :rolleyes:

@LanceStorm:

Mathie's suggestion for a key is fine, and will help keep your database integrity intact. However, it does mean that you'll have to check if an error occurs whenever you attempt to insert a new record into the table, in case the attempt fails because of the constraint imposed by this key... it's good practice anyway, but if it's only for a small college project, it may be overkill.

Gadget

tonyj

LanceStorm wrote:

It didnt have that, but ive added an autonumber for the order ID and it has that set obviously, is that likely to cause the same problem??

If all you are using is one simple table, then OrderID as the PK will work fine.

i.e. if you're not trying to keep order lines together in batches, or do anything with customer or product codes - in other words, as long as you don't get relational on us...

LanceStorm

lol

no relationships in here!!
just trying to keep it as simple as possible and avoid all that...

there's no product table, just a field where the user will enter the name of the product they want, bit open ended I know... but drop down boxes are a bit confusing, I dont mind the SQL its the fooking 'access' thats my problem...

LanceStorm

ok so now ive 4 fields

order id (primary key) - set to autonumber
products - set to text
uname - set to text
quantity - set to number

and my code is looking like this

---

<%
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("db/order.mdb"))

'First we should check if trying to insert
'Only execute the INSERT statement if it has been requested
If Request("submit") = "Insert" Then
sSQL = "INSERT INTO orders VALUES ('" & Request("uname") & "', '" & Request("quantity") & "', '" & Request("product") & "', '" & Request("order id") & "')"


oConn.Execute(sSQL)
End If

'SELECT from the database
sSQL = "SELECT * FROM orders"
set oRS = oConn.Execute(sSQL)
%>
<%
oConn.Close
Set oRS = Nothing
Set oConn = Nothing
%>

---

Question is this, do I need to have order id in the Insert section of above code?! or an order section on the website itself or should I leave that out as it should be auto given a number??

Mutant_Fruit

You're primary key should be auto-created by the SQL server in this case. (Unless you have a damn good idea of how to create a unique ID for each order that you are 100% sure will never be duplicatable in the future by another customer making another order.)

To autogenerate, you just insert your data into the table as per usual, except don't insert ANYTHING into the primary key column. i.e.

INSERT INTO orders VALUES ('" & Request("uname") & "', '" & Request("quantity") & "', '" & Request("product") & "')"

A few more tips. Never ever insert they way you're doing now. I'm updating a program who's designer inserted the way you do, and now i have bucketloads of changes to make every time i modify a column in his SQL tables.

An insert/update/select statement should always reference ONLY the columns it needs. You should never ever use "SELECT * FROM tablename" or "INSERT INTO tablename VALUES(blah, blah, blah)" simply because if i want to remove a column or insert a column, your queries will all start taking that column, and they shouldn't!

Inserts should always be: "INSERT INTO tablename(col1, col2, col3) VALUES(val1, val2, val3)". Even if you're table has only three columns, and you can't even imagine putting in another, do it this way!

Selects should always be "SELECT col1, col2, col3 FROM tablename". Never "select * from tablename".

Same goes for updates.

Lastly, use stored procedures if at all possible. Never hardcode INSERT statements into your program. It means if you ever need to change a statement (for whatever reason, such as changing where data is coming from) you do not have to recompile your program, you can just make a quick 5second change to your SQL stored procedure.

rsynnott

May I just point out that this application is a horrible security hazard. Try typing ';drop table orders; into one of the inputs (REALLY, REALLY, don't try this; it'll drop the table). Read up on SQL injection.

Inspector Gadget

rsynnott wrote:

May I just point out that this application is a horrible security hazard. Try typing ';drop table orders; into one of the inputs (REALLY, REALLY, don't try this; it'll drop the table). Read up on SQL injection.

Hmm... unless I'm missing something (which is eminently likely, I'll be the first to admit) wouldn't you have to at least type ");drop table orders;" ? (And of course know what the table is called... )

I'm assuming this is a first-year or second year mini-project or something, and will never make it to be a commercial application, and as such things of this nature aren't critical (ideally, you should be sanity-checking the type and nature of your inputs to help prevent this particular problem), but it's useful to know that they exist for future reference.

Gadget

rsynnott

No, the '; (quotemark, semicolon) will break out of any existing statement (causing it to fail, but the server won't mind). From there, there are a number of well-known techniques for forcing the database to reveal its structure.

Inspector Gadget

Didn't know that - is that part of the language spec, or do the various parsers just do it that way?

(Ouch!)

Gadget

bonkey

Its a fairly standard part of the language.

Read up on how to 'escape' characters to see how to prevent it. Its always worth pre-parsing user-supplied strings that will go into SQL statements - even aside from injection attacks, there's often valid reasons why the user would want to have an apostrophe in a field (think of a surname field in a table).

In MSSQL, for example, one replaces ' with '' (thats two apostrophes). In some other databases, ' is escaped as \'. This basically tells the parser "this is an apostrophe inside the string and not a string terminator". Its the same principle as using &gt and &lt instead of > and < in HTML.

jc

rsynnott

If your database and client support it, it may be preferable to use prepared statements (where tokens in the string are substituted with the required strings) or stored procedures.

Or on PHP, addslashes, stripslashes.

bonkey

Definitely. Dynamic SQL should really only be used when there's absolutely no other choice. Generally, thats never, assuming your DB and programming language support prepared statments

(Cue someone giving an example that prepared statements can't handle)

rsynnott

There are lots, but they're usually a bit contrived.

Of course, not all systems HAVE stored procedures.

bonkey

Oh, sure...but most of them that don't still support Prepared Statements, which are even more flexible than SPs. Even if you don't/can't leverage the reusability of PSs, they are still (IMHO) preferable to using "raw" SQL.

jc

Mutant_Fruit

(Cue someone giving an example that prepared statements can't handle)

A well designed database and application will always use stored procedures. I havn't ever come across a place where i can't use them, and i can't imagine (as of yet) coming across a time when i can't use a stored procedure to do the task i want.

A properly worded stored procedure will be extremely flexible in what you want retured. I'll paste in an example of what i considar a "properly worded" SP tomorrow.

rsynnott

Mutant_Fruit wrote:

A well designed database and application will always use stored procedures. I havn't ever come across a place where i can't use them, and i can't imagine (as of yet) coming across a time when i can't use a stored procedure to do the task i want.

Older versions of MySQL would be an example. (Or indeed any version that anyone currently USES).

Also, stored procedures impede portability to an extent.