asp.net webform - I am trying to use query result (integer) as parameter for another sql query but I don't manage to pass the result to the next query. The parameter I try to pass is txtPMSNo which I get from first query and try to use in the second whitout putting it in a view
protected void AddNewPMS(object sender, EventArgs e) { SqlConnection conn = new SqlConnection(connString); { int PMSNo; int txtPMSNo; string sqlQuery = "SELECT Max(nrPMS) FROM PMSmain"; using (SqlCommand cmd = new SqlCommand(sqlQuery, conn)) { cmd.CommandType = CommandType.Text; conn.Open(); int result = Convert.ToInt32(cmd.ExecuteScalar()); if (result > 0) { using (SqlDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { PMSNo = rdr.GetInt32(0); if (PMSNo == 0) { txtPMSNo = 1; } else { txtPMSNo = PMSNo + 1; } } } } conn.Close(); } string sqlQueryADD = "IF NOT EXISTS (SELECT nrPMS, dataPMS FROM PMSmain) INSERT INTO PMSmain (nrPMS, dataPMS) VALUES (@PMSnr, @PMSData)"; using (SqlCommand cmd = new SqlCommand(sqlQueryADD, conn)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@PMSnr", txtPMSNo); DateTime tmpPMSData = DateTime.Now.Date; cmd.Parameters.AddWithValue("@PMSData", tmpPMSData); conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); } Response.Redirect("/Pages/IssuePMS.aspx?PMSNo=" + txtPMSNo, false); } }