Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 2.0 > ASP.NET 2.0 Professional
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
ASP.NET 2.0 Professional If you are an experienced ASP.NET programmer, this is the forum for your 2.0 questions. Please also see the Visual Web Developer 2005 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 2.0 Professional section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 4th, 2008, 03:47 AM
Authorized User
 
Join Date: Aug 2007
Location: Colne, Lancashire, United Kingdom.
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
Default Executing an SQL query and using it's result

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

  #2 (permalink)  
Old April 4th, 2008, 01:08 PM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

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
  #3 (permalink)  
Old April 7th, 2008, 04:37 AM
Authorized User
 
Join Date: Aug 2007
Location: Colne, Lancashire, United Kingdom.
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

  #4 (permalink)  
Old April 7th, 2008, 04:38 AM
Authorized User
 
Join Date: Aug 2007
Location: Colne, Lancashire, United Kingdom.
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #5 (permalink)  
Old April 7th, 2008, 05:39 AM
Authorized User
 
Join Date: Aug 2007
Location: Colne, Lancashire, United Kingdom.
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

  #6 (permalink)  
Old April 14th, 2008, 08:25 AM
Authorized User
 
Join Date: Aug 2007
Location: Colne, Lancashire, United Kingdom.
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Executing a query from SQL server to Oracle DB SoCalProgAna SQL Server 2000 7 March 24th, 2006 08:15 AM
check the result of sql query Abhinav_jain_mca ADO.NET 2 August 11th, 2004 11:58 AM
Paging of Sql Query Result. SubodhKumar SQL Language 4 December 13th, 2003 06:58 PM
Setting a Variable = Result of a SQL Query PeteS VB Databases Basics 1 June 20th, 2003 08:14 AM



All times are GMT -4. The time now is 10:20 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.