Marius Vasile

Marius Vasile

  • 601
  • 1.9k
  • 142.8k

Passing query result as parameter on same page

Apr 2 2023 1:31 PM

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);
    }
}

 


Answers (10)