last insert id fails and returns null
-
hi i have this infuriating problem thats driving me up the wall... I took over a project and wrote the frontend for a site however the admin (someone elses effort) keeps breaking, this code below always return 0 ie: i is always null.
private void Page_Load(object sender, System.EventArgs e) { foo.Text += this.createPage( "foo_test", 2, 3, 18131).ToString()+"::"; } public int createPage(string name, int order, int page_resource_id, int customer_di_id) { string query; query = "INSERT INTO foo (id, ord, name, page_resource_id) VALUES (?id, ?ord, ?name, ?page_resource_id); SELECT LAST_INSERT_ID();"; MySqlConnection con = new MySqlConnection( ConfigurationSettings.AppSettings["database_connstring"].ToString() ); con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.Parameters.Add(new MySqlParameter("?name", name)); cmd.Parameters.Add(new MySqlParameter("?ord", order)); cmd.Parameters.Add(new MySqlParameter("?page_resource_id", page_resource_id)); cmd.Parameters.Add(new MySqlParameter("?id", customer_di_id)); object i = null; i = cmd.ExecuteScalar(); con.Close(); int result = 0; if (i != null) { result = int.Parse(i.ToString()); } return result; }i have similar code working fine elsewhere (the frontend) but this ALWAYS fails and the reason.... as soon as the id in foo goes above 65535. It works fine up to then, and then always returns 0. i've stuck it in one page just test it, theres a whole load of other stuff going on but that all fine its just hereand I really dont get it at all. foo.Text is just a label on a blank page again for testing only if im being incredibly thick please point it out, but ive been staring at this for hours now and cant work it out and cant find anything on google or anywhere. ta t -
hi i have this infuriating problem thats driving me up the wall... I took over a project and wrote the frontend for a site however the admin (someone elses effort) keeps breaking, this code below always return 0 ie: i is always null.
private void Page_Load(object sender, System.EventArgs e) { foo.Text += this.createPage( "foo_test", 2, 3, 18131).ToString()+"::"; } public int createPage(string name, int order, int page_resource_id, int customer_di_id) { string query; query = "INSERT INTO foo (id, ord, name, page_resource_id) VALUES (?id, ?ord, ?name, ?page_resource_id); SELECT LAST_INSERT_ID();"; MySqlConnection con = new MySqlConnection( ConfigurationSettings.AppSettings["database_connstring"].ToString() ); con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.Parameters.Add(new MySqlParameter("?name", name)); cmd.Parameters.Add(new MySqlParameter("?ord", order)); cmd.Parameters.Add(new MySqlParameter("?page_resource_id", page_resource_id)); cmd.Parameters.Add(new MySqlParameter("?id", customer_di_id)); object i = null; i = cmd.ExecuteScalar(); con.Close(); int result = 0; if (i != null) { result = int.Parse(i.ToString()); } return result; }i have similar code working fine elsewhere (the frontend) but this ALWAYS fails and the reason.... as soon as the id in foo goes above 65535. It works fine up to then, and then always returns 0. i've stuck it in one page just test it, theres a whole load of other stuff going on but that all fine its just hereand I really dont get it at all. foo.Text is just a label on a blank page again for testing only if im being incredibly thick please point it out, but ive been staring at this for hours now and cant work it out and cant find anything on google or anywhere. ta tgoing_mental wrote:
as soon as the id in foo goes above 65535
There is your clue. The datatype of the id field seems to be a smallint, which has a maximum value of...drum roll please...65535. Try using a mediumint or an int, depending on how large you think the table will grow.
only two letters away from being an asset
-
going_mental wrote:
as soon as the id in foo goes above 65535
There is your clue. The datatype of the id field seems to be a smallint, which has a maximum value of...drum roll please...65535. Try using a mediumint or an int, depending on how large you think the table will grow.
only two letters away from being an asset
hi mark if only that was the problem my life would easier.....;) the frontend uses the same database and table and doesnt have this issue and does a very similar thing ie: inserts a record grabs the last insert id and uses it as a reference in another table. the id datatype is an int so should be fine. something somewhere is having issues with it no longer being a smallint but i cant for the life of me se what. t
-
hi i have this infuriating problem thats driving me up the wall... I took over a project and wrote the frontend for a site however the admin (someone elses effort) keeps breaking, this code below always return 0 ie: i is always null.
private void Page_Load(object sender, System.EventArgs e) { foo.Text += this.createPage( "foo_test", 2, 3, 18131).ToString()+"::"; } public int createPage(string name, int order, int page_resource_id, int customer_di_id) { string query; query = "INSERT INTO foo (id, ord, name, page_resource_id) VALUES (?id, ?ord, ?name, ?page_resource_id); SELECT LAST_INSERT_ID();"; MySqlConnection con = new MySqlConnection( ConfigurationSettings.AppSettings["database_connstring"].ToString() ); con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.Parameters.Add(new MySqlParameter("?name", name)); cmd.Parameters.Add(new MySqlParameter("?ord", order)); cmd.Parameters.Add(new MySqlParameter("?page_resource_id", page_resource_id)); cmd.Parameters.Add(new MySqlParameter("?id", customer_di_id)); object i = null; i = cmd.ExecuteScalar(); con.Close(); int result = 0; if (i != null) { result = int.Parse(i.ToString()); } return result; }i have similar code working fine elsewhere (the frontend) but this ALWAYS fails and the reason.... as soon as the id in foo goes above 65535. It works fine up to then, and then always returns 0. i've stuck it in one page just test it, theres a whole load of other stuff going on but that all fine its just hereand I really dont get it at all. foo.Text is just a label on a blank page again for testing only if im being incredibly thick please point it out, but ive been staring at this for hours now and cant work it out and cant find anything on google or anywhere. ta tI see two reasons that might be possible: 1. The driver has problems with returning some data types. 2. The insert query also produces a result, so that you get two results from the call. (This is the case if you would run it in SQL Server.) Try to run the query that gets the id in a separate call. As long as you use the same connection, you get the right id.
--- b { font-weight: normal; }
-
hi mark if only that was the problem my life would easier.....;) the frontend uses the same database and table and doesnt have this issue and does a very similar thing ie: inserts a record grabs the last insert id and uses it as a reference in another table. the id datatype is an int so should be fine. something somewhere is having issues with it no longer being a smallint but i cant for the life of me se what. t
Is the front end inserting into the same table? If so then use that code, if not then compare them and the tables. May sound obvious but it's usually the simple things that cause the problems.
only two letters away from being an asset
-
I see two reasons that might be possible: 1. The driver has problems with returning some data types. 2. The insert query also produces a result, so that you get two results from the call. (This is the case if you would run it in SQL Server.) Try to run the query that gets the id in a separate call. As long as you use the same connection, you get the right id.
--- b { font-weight: normal; }
lordy lordy guffa yer a bladdy genius. it works, but im stil confused as to why it waits till the id's rise above smallint size before barfing. almost identical code works in the frontend (the two queries run together in one call) and thats fine. very perculiar, but hey ho hopefully thats the last I'll see of that code (for a while ;0) thanks again guffa tim
-
Is the front end inserting into the same table? If so then use that code, if not then compare them and the tables. May sound obvious but it's usually the simple things that cause the problems.
only two letters away from being an asset
hi mark the sql is identical, the way its handled in the call for the class is *slightly* different but the problem wasnt there which is why i was getting so confused by it, i thought it was originally but after getting rid of everything bar what was left in the example in my post and still having the problem, just kinda flummoxed me. the sql doesnt fail (it inserts a record) it just wasnt picking up the last insert id and returning it, guffa's solution to split the queries into two works fine, but i still dont see what would cause it or why it would fail after getting to 65535 as theres nothing that defines a smallint or restricts it to that. v odd me thinks thanks tim