locked
How to get a value from the database and assign it to parameter RRS feed

  • Question

  • User-1480523733 posted

    Hi,

    I m trying to get a value in a database. For example, in my database, in the patient table, I have a location_code column and the location_code can be 1, 2, or 3.

    I want to get this values from the database and count how many patients are in such locations. Can you give me an advise how to do?

    Thanks

    Sunday, April 3, 2011 2:09 PM

Answers

  • User-1460196090 posted

    Hi hknpr,

    Well, since I don't have your database table schema and the current implementation you use, I have made something that might be similar. Have made this scenario in order to help you change the inside logic and make your own one work.

    As I know, this is second query after the one which is resolved in the other thread. So, if you use the same code, you have the data in DataTable and then add the DataTable to a GridView or any other data binding control.

    So, now, if you want to use the data in the DataTable to get some statistics from it, you can do this on the following way (this is sample code working with one of my db tables):

     

    protected void SearchByTagButton_Click(object sender, EventArgs e)
            {
                String strConn = "Data Source=.;Initial Catalog=MyTestDatabase;Integrated Security=True";
                SqlConnection conn = new SqlConnection(strConn);
                conn.Open();
                SqlCommand cmd = new SqlCommand("Select * FROM [Childs] WHERE Gender = @SearchByTagTB", conn);
    
                try
                {
                    SqlParameter search = new SqlParameter();
                    search.ParameterName = "@SearchByTagTB";
                    search.Value = SearchByTagTB.Text.Trim();
    
                    cmd.Parameters.Add(search);
                    SqlDataReader dr = cmd.ExecuteReader();
                    DataTable dt = new DataTable();
                    dt.Load(dr);
    
    
                    //THIS IS THE NEW CODE BLOCK ADDED FROM THE PREVIOUS CODE
                    int lessThen10 = 0;
                    int between10and20 = 0;
                    int greaterThan20 = 0;
                    foreach (DataRow tDr in dt.Rows)
                    {
                        if (tDr["Years"] != null)
                        {
                            int val = Convert.ToInt32(tDr["Years"]);
                            if (val < 10)
                            {
                                lessThen10++;
                            }
                            else if (val >= 10 && val < 20)
                            {
                                between10and20++;
                            }
                            else
                            {
                                greaterThan20++;
                            }
                        }
                    }
                    //END NEW
    
                    gvPatients.DataSource = dt;
                    gvPatients.DataBind();
                    lblYearsInfo.Text = "Less then 10:" + lessThen10 + ", Between 10 and 20:" + between10and20 + ", Greater than 20:" + greaterThan20; //PRINT THE RESULT IN LABEL
                }
                catch (Exception ex)
                {
                    Response.Write(ex.Message);
                }
                finally
                {
                    //Connection Object Closed
                    conn.Close();
                }
            }

    So, I basically loop each row in the Data Table, and get the row Years. Then I make some inner comparison logic and increment the appropriate predefined counter. At the end I show the result.

    If I understood your query well, you should do something similar with your own logic.

    Hope this helps.

     

    Years
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 4, 2011 5:26 PM

All replies

  • User1483970546 posted

    Hi,

    There could be couple of ways to achive the desired functionality , based on the data access framework you are using.

    all you need is

    1. Create a stored procedure in database, which should take Location_Code as input parameter and return the patient list.

    2. Create a connection with your databse in your application.

    3. Create stored procedure command and provide location_code value in parameter.

    4. Execute the command and Get the results.

    Please have look at this article for code http://support.microsoft.com/kb/310070

    Let me know if you have any question.

    Thanks

    -Akhtar

    Sunday, April 3, 2011 2:50 PM
  • User-1480523733 posted

    Hi Akhhttar,

    I just want to read the data row by row and compare with if statement. In that article, it just gives row number.

    I have a button in my application and when I click, it will show the statistics: how many patient in location 1, how many in 2, and how many in 3.

    Can anyone help me about it?

    Thanks

    Sunday, April 3, 2011 3:38 PM
  • User1483970546 posted

    I would like to suggest you to go through some getting started ado.net/entity framework tutorial, Following vidoes could be a helpful for you to get start work with Entity Framework and to get an understanding of how to implement the required functionality,

    http://msdn.microsoft.com/en-us/data/ee712907

     

    Thanks

    -Akhtar

    Monday, April 4, 2011 1:50 PM
  • User-1460196090 posted

    Hi hknpr,

    Well, since I don't have your database table schema and the current implementation you use, I have made something that might be similar. Have made this scenario in order to help you change the inside logic and make your own one work.

    As I know, this is second query after the one which is resolved in the other thread. So, if you use the same code, you have the data in DataTable and then add the DataTable to a GridView or any other data binding control.

    So, now, if you want to use the data in the DataTable to get some statistics from it, you can do this on the following way (this is sample code working with one of my db tables):

     

    protected void SearchByTagButton_Click(object sender, EventArgs e)
            {
                String strConn = "Data Source=.;Initial Catalog=MyTestDatabase;Integrated Security=True";
                SqlConnection conn = new SqlConnection(strConn);
                conn.Open();
                SqlCommand cmd = new SqlCommand("Select * FROM [Childs] WHERE Gender = @SearchByTagTB", conn);
    
                try
                {
                    SqlParameter search = new SqlParameter();
                    search.ParameterName = "@SearchByTagTB";
                    search.Value = SearchByTagTB.Text.Trim();
    
                    cmd.Parameters.Add(search);
                    SqlDataReader dr = cmd.ExecuteReader();
                    DataTable dt = new DataTable();
                    dt.Load(dr);
    
    
                    //THIS IS THE NEW CODE BLOCK ADDED FROM THE PREVIOUS CODE
                    int lessThen10 = 0;
                    int between10and20 = 0;
                    int greaterThan20 = 0;
                    foreach (DataRow tDr in dt.Rows)
                    {
                        if (tDr["Years"] != null)
                        {
                            int val = Convert.ToInt32(tDr["Years"]);
                            if (val < 10)
                            {
                                lessThen10++;
                            }
                            else if (val >= 10 && val < 20)
                            {
                                between10and20++;
                            }
                            else
                            {
                                greaterThan20++;
                            }
                        }
                    }
                    //END NEW
    
                    gvPatients.DataSource = dt;
                    gvPatients.DataBind();
                    lblYearsInfo.Text = "Less then 10:" + lessThen10 + ", Between 10 and 20:" + between10and20 + ", Greater than 20:" + greaterThan20; //PRINT THE RESULT IN LABEL
                }
                catch (Exception ex)
                {
                    Response.Write(ex.Message);
                }
                finally
                {
                    //Connection Object Closed
                    conn.Close();
                }
            }

    So, I basically loop each row in the Data Table, and get the row Years. Then I make some inner comparison logic and increment the appropriate predefined counter. At the end I show the result.

    If I understood your query well, you should do something similar with your own logic.

    Hope this helps.

     

    Years
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 4, 2011 5:26 PM
  • User-1480523733 posted

    Thank you so much Hajan, It works right now thanks againSmile

    Monday, April 4, 2011 6:41 PM
  • User-1460196090 posted

    Always welcomed! ;)

    Monday, April 4, 2011 7:01 PM