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.

bonkey

Mutant_Fruit wrote:

A well designed database and application will always use stored procedures.

Not necessarily. More importantly, it may not use stored procedures for everything.

I havn't ever come across a place where i can't use them,

A search across a large table (row-count-wise) with a large number of fields, any permutation or combination of which can be included or excluded from the search.

Short of getting your stored procedure to dynamically build an SQL string which you then execute, there is simply no efficient way of building a stored procedure to do this, adn thus it is the wrong approach.

If you get your stored procedure to dynamically build an SQL string which you then execute....you're once-again going to have to code against injection attacks....at which point, the use of prepared statements becomes an arguably preferable option.

I'll paste in an example of what i considar a "properly worded" SP tomorrow.

If you can show me an efficient way of solving the problem that I've described with a "properly worded" Stored Procedure, I'll be impressed.

As a general rule, anyone presenting a solution as the "best in all cases" is wrong. There is no panacea.

jc

Mutant_Fruit

You're right, i could never say a stored procedure can and should always be used. There is doubtlessly a situation where it would be impossible to use one, but those should be very rare.

This is what i'd considar a basic enough "properly worded" statement. Basically, i have an ASP.NET webform with about 6 "selection criteria". People can filter the information displayed based on what they select from 6 different dropdown lists.

Here i was told i should probably dynamicly build my SQL statement based on what filters were enabled, but i used a Stored Procedure instead. I've edit parts of it to remove real table names, so if it doesn't make complete sense, its cos i edited it badly

I think this is roughly what you're asking me to show, if not, give me a more detailed explanation of what you're asking, and i'll see if i can code a stored procedure to do it as "dynamically" as you want it.

CREATE PROCEDURE FilteredCalls
	(
	   @categorystart int,
	   @categoryend int,
	   @subcategorystart int,
	   @subcategoryend int,
	   @assignedstart int,
	   @assignedend int,
	   @startdate datetime,
	   @enddate datetime,
	   @statusstart int,
	   @statusend int,
	   @prioritystart int,
	   @priorityend int
	)


AS
select callnumber, categorytable.category, subcategorytable.subcategory, companyname, summary, notes,
userinfo.fullname as Assigned, dateopened, historyinfo.dateadded as datemodified, statustable.status as status, prioritytable.priority
from calls 
inner join
statustable
on
statustable.listvalue = calls.status
inner join
prioritytable
on
prioritytable.listvalue = calls.priority
inner join
categorytable
on
calls.category = categorytable.listvalue
left outer join
subcategorytable
on calls.subcategory = subcategorytable.listvalue
left outer join
userinfo
on calls.owner = userinfo.userid
left outer join
historyinfo
on calls.callnumber = historyinfo.fkey
where callnumber like 'CI%'
AND calls.category BETWEEN @categorystart AND @categoryend
AND calls.subcategory BETWEEN @subcategorystart AND @subcategoryend
AND calls.owner BETWEEN @assignedstart AND @assignedend
AND dateopened BETWEEN @startdate AND @enddate
AND calls.status BETWEEN @statusstart AND @statusend
AND calls.priority BETWEEN @prioritystart AND @priorityend
order by datemodified desc

From my C# code, if any of the dropdowns is set to "All" or "N/A", then the min value for that specific item is set to 0 (you can't get -1 ) and the max is set to the max in the table. Therefore, making that specific filter return ALL rows. It won't filter anything. If a dropdown (such as priority) is set to high, then both max and min are set to the same value (high=3). And you will only get "high" priority calls. Combine the other filters, and you can get any combination.

Therefore we have a very dynamic stored procedure able to pick and choose what rows get returned based on dynamicly chosen items from 6 different dropdowns.

This kind of logic can be applied in a lot of cases.


EDIT: Whats the difference between a prepared statement and a stored procedure?

bonkey

Mutant_Fruit wrote:

You're right, i could never say a stored procedure can and should always be used.

You replied to a statement where I was asking for a situation that Prepared Statements can't handle saying that a well-coded app will always use Stored Procedures.

Forgive me for if I misunderstand that to mean you were suggesting one should always use SPs in preference to PSs

Here i was told i should probably dynamicly build my SQL statement based on what filters were enabled, but i used a Stored Procedure instead.

/me Nods. Seen this type of thing before. Also seen it crash and burn horribly (performance-wise) when dealing with more than one large table in the joins, where you have unnecessary joins occurring when they're not needed.

Example - getting a list of all customers. I have the option to specify search criteria based on sales to those customers, but the returned information will not contain the sales info, just the customer info. If I don't search on sales there is nothing but a potentially massive performance loss asking the system to join all records in the sales table(s) that I don't want to filter against, nor retrieve information from. I'll simply force the join to occur, and then use a distinct (or somesuch) to remove the ensuing duplicated customer information. Do this on tables running into GBs, and you're screwed.

Your approach will generally work when joining "lookup" tables (i.e. for one record in your call record, only one record in the category table will match). But if you're talking about a one-to-many join (my sales example above) the performance impact of doing it that way can be unbelievable.

I used to be firmly in the Stored Procedure camp...particularly with MSSQL before Version 7.0. Since then, the performance benefits are - in the vast majority of cases - no longer there, and the security benefits are generally questionable, unless you're coding validation logic or somesuch into your stored procedures alongside the basic CRUD statements.

jc

bonkey

Mutant_Fruit wrote:

EDIT: Whats the difference between a prepared statement and a stored procedure?

A prepared statement is "ad-hoc" SQL built client-side, except where parameters are defined as just that - parameters.

The query is prepared in that the database server will compile its execution plan, set it up for parameters etc, and then typically re-use this over and over as you call repeatedly.

So, in kinda-code, you'd have something like :

[i]// Preparation[/i]
sqlStatement = " SELECT * from MyTable where someId = ?"
preparedStatement = dbConnection.PrepareStaetment(sqlStatement)
preparedStatement.AddParameter(
  new Parameter(someTypeInformationWillGoHere));
 
[i]// Usage[/i]
preparedStatement.Parameters[0].Value = 100;
resultSet1 = preparedStatement.GetResultSet();
 
preparedStatement.Parameters[0].Value = 999;
resultSet2 = preparedStatement.GetResultSet();

Obviously, in a real-world app, there are better ways to generate your SQL, but this shows you the basic idea.

You build like ad-hoc, and use like a stored procedure.

Couple with tools/technologies/components/widgets that do most of the building for you, and most of your work is done.

jc

Mutant_Fruit

Gotcha. Thats the exact same as "precompiled SQL statements" in SQLite. Its the half-way house between dynamic and stored procedures.

For functions that are called very frequently, a SP would be best simply because its compile once, use many. But for functions that would be used infrequently/change a lot, a prepared statement would probably be best.

In my example, those joins are all to lookup tables, and they always have to be done, regardless of how many rows are going to be returned. So my way works well in this situation.

As always, the order of your "where" statements makes a difference. You want to remove as many rows as possible, as soon as possible. The less rows available for later "filters", the faster they can go. So you always try and order your where statements where the first statement removes the most rows, second statement removes the second largest amount of rows etc.

You replied to a statement where I was asking for a situation that Prepared Statements can't handle saying that a well-coded app will always use Stored Procedures.

For some reason i was thinking "Prepared" = "Dynamic". I always refer to "prepared" as "precompiled" simply because my first database experience was SQlite, and SQlite calls em "precompiled" statements.

fatherdougalmag

Just like to say that, as someone learning re-learning SQL in my own time, it's through active discussions like this that you can learn bucket loads of stuff in 15 mins as opposed to the odd hours here and there that I can put in.

bonkey

Mutant_Fruit wrote:

For functions that are called very frequently, a SP would be best simply because its compile once, use many.

Most database systems (I believe) compile stored procedures once per session/connection, or more than that if explicitly instructed to do so and/or when the plan falls out of the cache.

Prepared Statements are handled identically - prepared once per session, and unless explicitly instructed to do so, or the plan falls out of cache, the prepared plan is re-used.

In my example, those joins are all to lookup tables, and they always have to be done, regardless of how many rows are going to be returned. So my way works well in this situation.

Absolutely...I was just pointing out (for other readers, if we still have any) where the approach might not work. And even in my examples, it might still work acceptably fine. A lot will depend on the table size, the usefulness of the indexes, how smart/good the optimiser is....and so on.

As always, the order of your "where" statements makes a difference.

As always? Maybe on some DB systems, but not on all.

With MSSQL (which I keep referring to because I know it the best), it makes no difference, nor does Join order. The optimiser will actually check which order makes most sense performance-wise.

For some reason i was thinking "Prepared" = "Dynamic". I always refer to "prepared" as "precompiled" simply because my first database experience was SQlite, and SQlite calls em "precompiled" statements.

Prepared is mostly a term I've picked up from VB/java/c#. It seems to be fairly common across the various data-access models. I've no idea if its a formal description or not....so precompiled is just as good (and perhaps more clear) a term.

jc

bonkey

As a side-note about precompilation....soemtimes it can bit you in the ass. There are certain situations where a query should be optimised differently depending on the value of the parameters.

Think of a table where a column mostly (99% of a million rows, say) has a single v value, but for the other 1% has unique values. You're filtering on this field. Should you table-scan (optimal for retrieving 99% of rows), or use an index (optimal for the other 10,000 rows)?

Ideally, you choose both.

You either have two SPs/PSs, each with compiled with optimisation hints to tell the system which way to go, and you decide in advance whether its the duplicated value or not....or you instruct your SP/PS to recompile on each execution, which will then take the actual parameters into account.

Now I'm off to do some work.

Mutant_Fruit

bonkey wrote:

As always? Maybe on some DB systems, but not on all.

Ya learn something new every day. I thought most (if not all) benefited from proper placement of where clauses. Also, i've heard some databases are a lot faster if you say

WHERE a = b AND b = c AND c = a;

as opposed to

WHERE a = b AND b = c;

Does the optimiser take that into account to?

LanceStorm

wow, its amazing, this one thread has taught me more than an entire year of college!!

---

ok, security is 100% not an issue, some validation rules are all there after, its a 3rd year project belive it or not!!

---

for the autogenerated order ID, presumably that has to be set to autonumber in access yeah??

---

Is it possible to have a search bar in with the database?? desirably I want to be able to enter a username and have it bring me up that users details in table?? anyone any pointers how id go about that??

LanceStorm

Mutant Fruit,

When I leave the order id field out of the code it gives me this:

"Number of query values and destination fields are not the same"

Mutant_Fruit

I want to be able to enter a username and have it bring me up that users details in table??

string query = "SELECT Details1, Details2, Details3 FROM Table_Details WHERE username =" TextBox_Username.Text;

Something like that. Dead simple to do.

string query = "SELECT Details1, Details2, Details3 FROM Table_Details WHERE username =" TextBox_Username.Text;

Yup.

Slightly off topic, but are you using an Access database? If so, maybe try ditching it and use a real mans database (i've used some programs before that had an Access database as the backend, and they are sooo slow).

Check out SQLite. It supports pretty much everything any SQL server supports (except for stored procedures... it just has precompiled statements). Its also a lot faster than access, and a lot smaller than access. You can either access it via a commandline interface (if you're lazy) or you could use a wrapper dll and read/write to the database directly from your program. I'm in the middle of a project at the moment, and SQLite is what i'm using as my database. Tis a great engine.

When I leave the order id field out of the code it gives me this:

Paste in your insert statement so i can take a look. Also, check out access's help on auto-increment primary keys.

LanceStorm

If Request("submit") = "Insert" Then
sSQL = "INSERT INTO orders VALUES ('" & Request("uname") & "', '" & Request("quantity") & "', '" & Request("product") & "')"


Left out order id like ya said, but it gives an error that im leavin it out!!

---

Haha, a mans database!!
way too late to go changing database programs now im afraid, i'll have to suffer with access for now...!!

I'll try the string query in a minute!!

thanks again!!

bonkey

Mutant_Fruit wrote:

Ya learn something new every day. I thought most (if not all) benefited from proper placement of where clauses. Also, i've heard some databases are a lot faster if you say

WHERE a = b AND b = c AND c = a;

as opposed to

WHERE a = b AND b = c;

Does the optimiser take that into account to?

Good question

It rings a bell, but I'm not entirely convinced that what I'm saying here is fully correct. Here's how I'd understand the reasoning though....

If you think abotu comparing three values like this, ask why are you likely to do it? Odds are that what you're doing is linking fields across tables in at least one of these three, and then either linking a third table or comparing the linked field against a fixed value. The other combinations (all three in one table, two in one table and one "fixed" value, and so on) are all possible, but less likely.

So, I'll deal with the two cases I've described.

Option 1 : Two (joined) tables, and a filter on the joined value.

I'll name things A.a, B.b, and c, so its clear which are table values and which are filters.

A.a = B.b joins the tables.
B.b = c allows the filter to be done either on table B pre- or post- join. There's advantages to both, depending on the situation.

Yes?

Now, if we include A.a = c, this also allows table A to be filtered as well as table B or instead of table B before the join happens, as well as still makign it entirely possible to filter post-join.

It may give a performance boost, but whats more important is that it gives the optimiser all options without you having to second-guess which approach will be best.

Option 2 : Three (joined) tables, and no filter value

Here, its a similar logic - including all three options gives the optimiser the most flexibility in deciding which tables to join to which. It can join A to B, then the result to C, A to C and the result to B, B to C and the result to A, or the reverse of any of these (joining B to A being different to joining A to B in terms of which table you process seuqentially and which you look for join values in).

In general, I'd recommend that no-one takes any of this as gospel for the DB they're using. I've read a lot on optimising MSSQL across every version from 4.2. When I was at TechEd Europe last year, I was chatting to an MSSQL expert from whom I discovered that some of the optimisation techniques I was still using were not only obsolete since V 7.0 was released but were now decidedly non-optimal. So I don't even necessarily trust my own optimisation understanding.....I just use it as a starting point, adn then read up / google if it doesn't give me the performance I want.

I must say though...DB/Query optimisation is something I'd love to have the opportunity to do full-time. Not a DBA job....rather a DBO (DB Optimiser). Maybe some day...when I've another decade or two under my belt

jc

bonkey

If Request("submit") = "Insert" Then
sSQL = "INSERT INTO orders VALUES ('" & Request("uname") & "', '" & Request("quantity") & "', '" & Request("product") & "')"


Left out order id like ya said, but it gives an error that im leavin it out!!

If you're not supplying values for each field (i.e. if OrderId is an Autobnumber in Access) then you shoul dhave :

Insert into myTable (thisField, thatField, theOtherField) Values (ValueForThisField, ValueForThatField, ValueForOtherField).

In other words...if you ain't supplying a value for all fields (in teh order they're defined), then you must supply a list of the fields you are supplying values for (in the order you supply them).

jc

LanceStorm

bonkey wrote:

If you're not supplying values for each field (i.e. if OrderId is an Autobnumber in Access) then you shoul dhave :

Insert into myTable (thisField, thatField, theOtherField) Values (ValueForThisField, ValueForThatField, ValueForOtherField).

In other words...if you ain't supplying a value for all fields (in teh order they're defined), then you must supply a list of the fields you are supplying values for (in the order you supply them).

jc

but if im asking the user to enter the value's, what to I set for(ValueForThisField, ValueForThatField, ValueForOtherField)??

rsynnott

bonkey wrote:

Prepared is mostly a term I've picked up from VB/java/c#. It seems to be fairly common across the various data-access models. I've no idea if its a formal description or not....so precompiled is just as good (and perhaps more clear) a term.

It's not necessarily accurate, tho. JDBC can do prepared statements for numerous DBs that don't support precompiled ones; there the only advantages of using prepared statements are portability and easy injection avoidance.

Mutant_Fruit

LanceStorm wrote:

but if im asking the user to enter the value's, what to I set for(ValueForThisField, ValueForThatField, ValueForOtherField)??

Like this:

If Request("submit") = "Insert" Then
sSQL = "INSERT INTO orders(ThisField, ThatField, OtherField) VALUES ('" & Request("uname") & "', '" & Request("quantity") & "', '" & Request("product") & "')"

Or in your case:

If Request("submit") = "Insert" Then
sSQL = "INSERT INTO orders (uname, quantity, product) VALUES ('" & Request("uname") & "', '" & Request("quantity") & "', '" & Request("product") & "')"

Mutant_Fruit

rsynnott wrote:

It's not necessarily accurate, tho. JDBC can do prepared statements for numerous DBs that don't support precompiled ones; there the only advantages of using prepared statements are portability and easy injection avoidance.

I think you misunderstood my use of Precompiled.

We have:
Stored Procedures
Precompiled/Prepared Statements (same thing in my eyes)
Dynamic SQL (raw sql).

LanceStorm

it works....!!!!!!!

it actually works!!!!!!!!!!

thanks everyone that had an input into the gloriousness of it working!!!

I was 2 seconds away from radically changing the entire website to suit a product you could only ever order once!!