Creating a SQL MAX command

Hello All,

I'm currently in the process of developing a new PO system for work. What I need to accomplish is a SQL MAX command to find the largest PO number and then add 1 to that number. I'm then saving this in a session state so users can create multiple items for that same PO number. Here's what I have so far:

1protected void Page_Load(object sender, EventArgs e)2 {3// connection string to your database4 SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=Purchasing;Integrated Security=SSPI;");56// create command object to execute query7 SqlCommand mySqlCommand = mySqlConnection.CreateCommand();89// set command text to the INSERT sql query10 mySqlCommand.CommandText ="SELECT MAX(PONumber)FROM ItemMaster;";1112// open connection13 mySqlConnection.Open();1415// execute query1617int newPO1 = (int) mySqlCommand.ExecuteScalar();18int newPO = newPO1 + 1;1920// close connection21 mySqlConnection.Close();2223//Response.Write(newPO);24 Session["newPO"] = newPO.ToString();2526 }

I copied and modified the ExecuteScalar() command from another thread in another fourm, but continue to receive this error:

System.InvalidCastException: Specified cast is not valid.

Source Error:

Line 30: // execute queryLine 31:Line 32: int newPO1 = (int) mySqlCommand.ExecuteScalar();Line 33: int newPO = newPO1 + 1;Line 34:

I'm not sure what i'm doing wrong, any help to point me in the right direction would be greatly appreciated.

Thank you in advance

Instead of using (int), try Convert.ToInt32

Convert.ToInt32(mySqlCommand.ExecuteScalar());

|||

That did the trick, only thing is my logic must be incorrect somewhere. For each line item I add its adding another digit to the PO number. What I need it to do is create a new PO number and stick with that one throughout the session...but then when the user finishes the PO and wants to start a new one thats when it adds 1 to the max PO number. Any Ideas? I thought putting the code in the Protected void Page_Load would do the trick but apparently not.

|||

Hi GIZM,

I have some code here..

If u find this is useful mark it as answer

qlConnection con=new SqlConnection();
con.ConnectionString=System.Configuration.ConfigurationManager.AppSettings["strcon"];
con.Open();
SqlCommand cmd = new SqlCommand("select max(fdcatid) from maincate", con);

//SqlDataAdapter ad = new SqlDataAdapter(cmd);
int f0 = (int)cmd.ExecuteScalar();
int f1 = f0 + 1;
con.Close();
Session["f1"] = f1.ToString() ;
Response.Write(f0);

|||

Hey Raja,

That looks to be a lot like what I have in my code, I just cant figure out where to put it. As of right now, I have it on page load, and then later in the page I have a insert statement to add lines to the data table. Every time a user would update a line item I guess the page is reloading causing the PONumber to increase. I only want the number to increase after the page is redirected to my confirmation page and then back, or if the browser is closed and reopened.

|||

I wonder if I could pull this off using the "is post back" thing?