Passing query result as parameter on same page

Got it to work, damn, plain sql is much harder than EF :)

using (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();
        object result = cmd.ExecuteScalar().ToString();
        PMSNo = Convert.ToInt32(result);

        if (PMSNo == 0)
        {
            txtPMSNo = 1;
        }
        else
        {
            txtPMSNo = PMSNo + 1;
        }

        conn.Close();
    }

    string sqlQueryADD = "IF NOT EXISTS (SELECT nrPMS, dataPMS FROM PMSmain WHERE nrPMS=@PMSNr) INSERT INTO PMSmain (nrPMS, dataPMS) VALUES (@PMSnr, @PMSData)";
    using (SqlCommand cmd2 = new SqlCommand(sqlQueryADD, conn))
    {
        cmd2.CommandType = CommandType.Text;
        cmd2.Parameters.AddWithValue("@PMSnr", txtPMSNo);
        DateTime tmpPMSData = DateTime.Now.Date;
        cmd2.Parameters.AddWithValue("@PMSData", tmpPMSData);
        conn.Open();
        cmd2.ExecuteNonQuery();
        conn.Close();
    }
    Response.Redirect("/Pages/IssuePMS.aspx?PMSNo=" + txtPMSNo, false);
}

There may be a problem in the next query

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

result shouldn't be exactly the query result? I mean a number? So the query should look like

int PMSNo;
int txtPMSNo;
string sqlQuery = "SELECT Max(nrPMS) FROM PMSmain";
using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
{
    cmd.CommandType = CommandType.Text;
    conn.Open();
    object result = cmd.ExecuteScalar().ToString();
    PMSNo = Convert.ToInt32(result);
    
    if (PMSNo == 0)
    {
        txtPMSNo = 1;
    }
    else
    {
        txtPMSNo = PMSNo + 1;
    }

    conn.Close();
}

I no longer have the error but the parameter is still not correct

I tried that but then it will always use that 0 in the second query.I thought of storing txtPMSNo value in a hidden field? but then I will have this kind of hidden fields for all passing parameters?

May be one way to solve this would be to initialize txtPMSNo to a default value outside of the if (result > 0) block, like so

int PMSNo;
int txtPMSNo = 0; // Initialize to a default value
string sqlQuery = "SELECT Max(nrPMS) FROM PMSmain";
using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
{
    // ...
}

within the if (result > 0) block, you can set the value of txtPMSNo based on the value of PMSNo

if (result > 0)
{
    using (SqlDataReader rdr = cmd.ExecuteReader())
    {
        while (rdr.Read())
        {
            PMSNo = rdr.GetInt32(0);

            if (PMSNo == 0)
            {
                txtPMSNo = 1;
            }
            else
            {
                txtPMSNo = PMSNo + 1;
            }
        }
    }
}

This way, txtPMSNo will always have a value before it is used as a parameter in the second query.

the error I have is "Use of unassigned local variable txtPMSNo"

Can you post the error?

still, the error I have is "Use of unassigned variable". cmd is not an issue since I closed the first one, the parameter is not recognized

Thank you Tuhin Paul for answer. I open and close connections for both querries and even if I split the query into its own procedure, the problem I have is passing the parameter

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 cmd2 = new SqlCommand(sqlQueryADD, conn))
        {
            cmd2.CommandType = CommandType.Text;
            cmd2.Parameters.AddWithValue("@PMSnr", txtPMSNo);
            DateTime tmpPMSData = DateTime.Now.Date;
            cmd2.Parameters.AddWithValue("@PMSData", tmpPMSData);
            conn.Open();
            cmd2.ExecuteNonQuery();
            conn.Close();
        }
        Response.Redirect("/Pages/IssuePMS.aspx?PMSNo=" + txtPMSNo, false);
    }
}

I created a new SqlCommand object named cmd2 for the second query, and passed the txtPMSNo parameter to it using cmd2.Parameters.AddWithValue("@PMSnr", txtPMSNo).

I think you're trying to execute the second query using the same SqlCommand object from the first query, which is not possible since the connection is already open and in use. Instead, you should create a new SqlCommand object for the second query and pass the parameter to it.