Just a quick question, looking at your SQL, why aren't you using the built in Auto-Increment feature of your database to increment the orderr column by 1 rather than trying to manually increment it yourself. If you start deleting orders from your table you
are going to start reusing order numbers that have been used and will run into all sort of problems.
If you disagree with the above let me know and i will be happy to try and help you fix your statement, but i advise you turn the orderr column to an autoincrementing column that you dont have to touch with your SQL statements.
Just a quick question, looking at your SQL, why aren't you using the built in Auto-Increment feature of your database to increment the orderr column by 1 rather than trying to manually increment it yourself. If you start deleting orders from your table you
are going to start reusing order numbers that have been used and will run into all sort of problems.
If you disagree with the above let me know and i will be happy to try and help you fix your statement, but i advise you turn the orderr column to an autoincrementing column that you dont have to touch with your SQL statements.
in this case i disagree to the above since i need to keep it that way when they start moving the order around.
Or well i dont really need it to be so but i prefer it that way, Anyway i actually tried with a Auto-Increment but it didnt work since i
already have one for the ID in the table and it says i can't have two :o
i know what the problem is now with this statement, it doesnt work since i try to use the information in select from the same table i'm using to insert in and i can't use the same table since it gets a temprorary table or something like that :o wich doesnt
work in mySql but in mssql , so i just picked it out first with a reader and then did another query , so now i'm simply executing two querys instead of one since it's not that much of a problem but i would prefer to only have to execute info from/to the database
once =)
so if you have a solution for MySql i'd gladly try it :D
Looks like circular reference issue, try this query
string hemtinfo ="INSERT INTO gallery(ref_name,bild,bild_namn,orderr) values(
select `" + bildmeny.SelectedValue.ToString() + "`,`" + xFilePath.Value + "`,`" + bildnamn.Text + "`,count(orderr) +1 FROM gallery where gallery.ref_name='" + bildmeny.SelectedValue.ToString() + "')";
i didn't compiled above query, might be need to add some group clause etc., u just test and let us know.
Looks like that one will crash. You have to use a subquery to get the count.
I agree with the first responder who suggested letting the database generate the number. This method will be slow. Also, if orderr is the primary key and records get deleted, there will be a problem.
Sorry, not checked my emails for a day or two. Try this;
INSERT INTO gallery (ref_name,bild,bild_namn,orderr)
VALUES('" + bildmeny.SelectedValue.ToString() + "',
'" + xFilePath.Value + "',
'" + bildnamn.Text + "',
(SELECT COUNT(orderr) FROM (SELECT * FROM gallery) AS g WHERE g.ref_name='" + bildmeny.SelectedValue.ToString() + "') + 1)";
EDIT: Apologies for the misunderstanding on my previous post, i have been doing alot of ecommerce work lateley so when i see "order" automatically assume itsan order for a product or service rather than "sort_order".
Sorry, not checked my emails for a day or two. Try this;
INSERT INTO gallery (ref_name,bild,bild_namn,orderr)
VALUES('" + bildmeny.SelectedValue.ToString() + "',
'" + xFilePath.Value + "',
'" + bildnamn.Text + "',
(SELECT COUNT(orderr) FROM (SELECT * FROM gallery) AS g WHERE g.ref_name='" + bildmeny.SelectedValue.ToString() + "') + 1)";
EDIT: Apologies for the misunderstanding on my previous post, i have been doing alot of ecommerce work lateley so when i see "order" automatically assume itsan order for a product or service rather than "sort_order".
I don't mind at all =) i appreciate all comments i get =) and this worked perfectly thank you alot :D
SKDl
Member
202 Points
208 Posts
How to use SELECT COUNT inside INSERT command for MySQL Database
Nov 09, 2012 06:38 AM|LINK
Hi i'm currently trying to insert some info into my DB and i'm trying to do it with just one simple sql but it's not working.
I got a insert and in there i got a orderr where i want to set it to the last orderr + 1 so here's the code i'm trying with:
string hemtinfo = "INSERT INTO gallery(ref_name,bild,bild_namn,orderr) VALUES('" + bildmeny.SelectedValue.ToString() + "','" + xFilePath.Value + "','" + bildnamn.Text + "',(SELECT COUNT(orderr) FROM gallery WHERE gallery.ref_name='" + bildmeny.SelectedValue.ToString() + "') + 1)";Any ideas how to do this?
i get this error from using this code :
teh munk
Participant
1466 Points
297 Posts
Re: How to use SELECT COUNT inside INSERT command for MySQL Database
Nov 09, 2012 08:48 AM|LINK
Just a quick question, looking at your SQL, why aren't you using the built in Auto-Increment feature of your database to increment the orderr column by 1 rather than trying to manually increment it yourself. If you start deleting orders from your table you are going to start reusing order numbers that have been used and will run into all sort of problems.
If you disagree with the above let me know and i will be happy to try and help you fix your statement, but i advise you turn the orderr column to an autoincrementing column that you dont have to touch with your SQL statements.
SKDl
Member
202 Points
208 Posts
Re: How to use SELECT COUNT inside INSERT command for MySQL Database
Nov 09, 2012 08:56 AM|LINK
in this case i disagree to the above since i need to keep it that way when they start moving the order around.
Or well i dont really need it to be so but i prefer it that way, Anyway i actually tried with a Auto-Increment but it didnt work since i
already have one for the ID in the table and it says i can't have two :o
i know what the problem is now with this statement, it doesnt work since i try to use the information in select from the same table i'm using to insert in and i can't use the same table since it gets a temprorary table or something like that :o wich doesnt work in mySql but in mssql , so i just picked it out first with a reader and then did another query , so now i'm simply executing two querys instead of one since it's not that much of a problem but i would prefer to only have to execute info from/to the database once =)
so if you have a solution for MySql i'd gladly try it :D
1Madhu
Member
317 Points
119 Posts
Re: How to use SELECT COUNT inside INSERT command for MySQL Database
Nov 12, 2012 12:08 PM|LINK
Hi,
Looks like circular reference issue, try this query
i didn't compiled above query, might be need to add some group clause etc., u just test and let us know.
Regards,
Madhu,India
Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: How to use SELECT COUNT inside INSERT command for MySQL Database
Nov 12, 2012 12:42 PM|LINK
Looks like that one will crash. You have to use a subquery to get the count.
I agree with the first responder who suggested letting the database generate the number. This method will be slow. Also, if orderr is the primary key and records get deleted, there will be a problem.
SKDl
Member
202 Points
208 Posts
Re: How to use SELECT COUNT inside INSERT command for MySQL Database
Nov 12, 2012 09:59 PM|LINK
teh munk
Participant
1466 Points
297 Posts
Re: How to use SELECT COUNT inside INSERT command for MySQL Database
Nov 13, 2012 08:36 AM|LINK
Sorry, not checked my emails for a day or two. Try this;
INSERT INTO gallery (ref_name,bild,bild_namn,orderr) VALUES('" + bildmeny.SelectedValue.ToString() + "', '" + xFilePath.Value + "', '" + bildnamn.Text + "', (SELECT COUNT(orderr) FROM (SELECT * FROM gallery) AS g WHERE g.ref_name='" + bildmeny.SelectedValue.ToString() + "') + 1)";EDIT: Apologies for the misunderstanding on my previous post, i have been doing alot of ecommerce work lateley so when i see "order" automatically assume itsan order for a product or service rather than "sort_order".
SKDl
Member
202 Points
208 Posts
Re: How to use SELECT COUNT inside INSERT command for MySQL Database
Nov 15, 2012 06:27 AM|LINK
I don't mind at all =) i appreciate all comments i get =) and this worked perfectly thank you alot :D