Executing an SQL query and using it's result

Andrew.Berry · April 4th, 2008, 03:47 AM

Hi All,

Been getting on quite well with it now, but here goes.

I have a database with a number of entries. On PageLoad() I execute the following sql query:

Code:

"SELECT COUNT(ID) AS Expr1 from OUP_SAPData where JobNo = @JobNo"

I want to use this and perform the following logic:

If it returns a count of 1, goto page 1, else go to page 2.

I know how to do the redirecting of a page, it's just getting the count result out of the SQL.

Heres my .cs code that is relevant (note not done the redirect yet):

Code:

protected void Page_Load(object sender, EventArgs e)
    {
        TxtJobNo.Text = Request.QueryString["JobNo"];
        //Check to see if it exists. If it does, load page 2.

        string Connection = @"Data Source=HPDC7700-04\SQLEXPRESS;Initial Catalog=release;Integrated Security=True";
        string queryString = "SELECT COUNT(ID) AS Expr1 from OUP_SAPData where JobNo = @JobNo";

        using (SqlConnection connection = new SqlConnection(Connection))
        {
            try
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                connection.Open();

                SqlParameter JobNo = new SqlParameter("@JobNo", TxtJobNo.Text);
                command.Parameters.Add(JobNo);

                int result = command.ExecuteNonQuery();

                if (result > 0)
                {
                    //redirect to page 2, pass in jobno gridview.
                    MessageBox.Show("Already Exists");
                }
            }
            catch (Exception ex)
            {
                //MessageBox.Show(ex.ToString());
            }
            finally
            {
                // Always call Close when done reading.
                connection.Close();
            }

        }
    }

I know it's a bit messy, but it's in it's infancy as of yet.

Kind Regards

planoie · April 4th, 2008, 01:08 PM

I think the problem here is that you are calling ExecuteNonQuery. That method returns the number of rows affected. In this case, you will always get a 1 because you are doing a count.

To get the actual value of the count you could call ExecuteScalar which will return the value in the first column of the first result row. This will be your count. Then simply cast that to an integer and continue.

-Peter
peterlanoie.blog

Andrew.Berry · April 7th, 2008, 04:37 AM

Hi again. What you mention seems to be a valid point. I am trying to implement this but am having trouble:

Code:

try
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                connection.Open();

                SqlParameter JobNo = new SqlParameter("@JobNo", TxtJobNo.Text);
                command.Parameters.Add(JobNo);

                SqlParameter ReturnValue = new SqlParameter("@ReturnValue", Return);
                command.Parameters.Add(ReturnValue);
                command.Parameters["@ReturnValue"].Direction = ParameterDirection.ReturnValue;

                command.ExecuteScalar();

                MessageBox.Show(ReturnValue.ToString());


                if (ReturnValue.Equals(1))
                {
                    //redirect to page 2, pass in jobno gridview.
                    MessageBox.Show("Doesn't Exist");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                // Always call Close when done reading.
                connection.Close();
            }

It doesn't appear that I am getting anywhere, as all I get is the page loads with errors. The messagebox class is one that I have written myself and works with other projects that I have done.

Andrew.Berry · April 7th, 2008, 04:38 AM

Forgot to mention that the first messgaebox doesn't appear with the return value, which indicates that the query isn't executing correctly?

Andrew.Berry · April 7th, 2008, 05:39 AM

I've done a quick fix for now, by simply using an invisible field. I know it's cheating somewhat, but I t allows me to continue onwards for now, so it will be something that i'll try figure out for myself afterwards.

Cheers for the help though.

Andrew.Berry · April 14th, 2008, 08:25 AM

Got this working now. Here's what I did:

Code:

protected void Btn_Save_Click(object sender, EventArgs e)
    {
        if (!FormHasErrors())
        {

            string ConnectionString = @"Data Source=HPDC7700-04\SQLEXPRESS;Initial Catalog=release;Integrated Security=True";
            string queryString = "INSERT INTO OUP_SAPData(JobNo, GL_AccCode, CostCentre, SAPFirstCostOrderNo, ISBN, SAPPONum, POConditionType) " +
                "VALUES (@JobNo, @GL_AccCode, @CostCentre, @SAPFirstCostOrderNo, @ISBN, @SAPPONum, @POConditionType)";


            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = new SqlCommand(
                    queryString, connection);
                connection.Open();
                try
                {

                    SqlParameter JobNo = new SqlParameter("@JobNo", TxtJobNo.Text);
                    command.Parameters.Add(JobNo);

                    SqlParameter GL_AccCode = new SqlParameter("@GL_AccCode", DDL_AccCode.SelectedValue);
                    command.Parameters.Add(GL_AccCode);

                    SqlParameter CostCentre = new SqlParameter("@CostCentre", DDL_CostCentre.SelectedValue);
                    command.Parameters.Add(CostCentre);

                    SqlParameter SAPFirstCostOrderNo = new SqlParameter("@SAPFirstCostOrderNo", TxtBox_FCON.Text);
                    command.Parameters.Add(SAPFirstCostOrderNo);

                    SqlParameter ISBN = new SqlParameter("@ISBN", TxtBox_ISBN.Text);
                    command.Parameters.Add(ISBN);

                    SqlParameter SAPPONum = new SqlParameter("@SAPPONum", TxtBox_PONum.Text);
                    command.Parameters.Add(SAPPONum);

                    SqlParameter POConditionType = new SqlParameter("@POConditionType", DDL_POConditionType.SelectedValue);
                    command.Parameters.Add(POConditionType);

                    int result = command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                finally
                {
                    // Always call Close when done reading.
                    connection.Close();
                    MessageBox.Show("Record Added");
                }
            }
        }
    }