none
Performing SQL query from my program and previous queries are being stored when I don't want them to be RRS feed

  • Question

  • I'm new to C#, even newer to SQL and having a difficult time figuring out what I'm doing wrong.

    I want to query a database and show only the current query in a datagridview.  Problem I'm having is that every time I run the query all previous queries are also showing in my datagridview.  Even if I completely close the program and start over.  Could someone please tell me what I'm doing wrong?

    I should mention that I'm calling a 'stored procedure' ("usp_PopulationIndex") that my coworker was nice enough to set up for me.  This procedure takes only one parameter "Geo" which is a three character string.  Is it possible that there's something in that procedure that's doing this?  I don't want to bother my coworker about it unless it's definitely their error and not something I'm doing wrong...

                string thisMarket = "ATL";
                using (SqlConnection connection = new SqlConnection("Data Source=vortex;Integrated Security=SSPI;Initial Catalog=Intropages"))
                using (SqlCommand cmd = new SqlCommand("usp_PopulationIndex", connection))
                {
                    cmd.CommandText = "usp_PopulationIndex";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Geo", thisMarket);
    
                    connection.Open();
    
                    SqlDataReader reader = cmd.ExecuteReader();
                    var dataTable = new DataTable();
    
                    dataTable.Clear();         
                    dataTable.Load(reader);
    
                    dataGridView2.DataSource = dataTable;
    
                }

    Thanks for reading :)

    Joan


    - Joan :)


    • Edited by _Joan B_ Tuesday, November 3, 2015 4:40 PM typo
    • Moved by Kristin Xie Wednesday, November 4, 2015 6:25 AM move to appropriate forum
    Tuesday, November 3, 2015 4:39 PM

Answers

  • Thanks to everyone for your help :)

    It turns out it was something is the stored procedure.  He said he was was preserving the results in a Permanent table.  He fixed it and my code works fine (after a few little tweaks, thanks Naomi!)

                string thisMarket = "ABJ";
                using (SqlConnection connection = new SqlConnection("Data Source=vortz;Integrated Security=SSPI;Initial Catalog=Intropages"))
                using (SqlCommand cmd = new SqlCommand("usp_PopulationIndex", connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@Geo", 3)).Value = thisMarket;
                   
                    connection.Open();
    
                    SqlDataReader reader = cmd.ExecuteReader();
                    var dataTable = new DataTable();
                    dataTable.Clear();
                    dataTable.Load(reader);
                    dataGridView2.DataSource = dataTable;
    
                }



    - Joan :)

    Wednesday, November 4, 2015 5:24 PM

All replies

  • Can you post the procedure definition?

    I also suggest to avoid using AddWithValue method but rather use something like

    SqlParameters par = cmd.Parameters.Add("@Geo", SqlDbType.Char, 3);

    par.Value = thisMarket;


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, November 3, 2015 4:47 PM
  • Hi Naomi, thank you for responding :)

    I will absolutely take your advice and get rid of AddWithValue.  Like I said, I'm new to this so any/all suggestions on how I can do this the 'correct' way are more than welcome!

    I'm sorry, I'm not sure how to show you the procedure definition.  Is that something I can get while in Visual Studio or do I need to go into the SQL Configuration Manager?  My apologies for being clueless here, I've literally just started using SQL less than a week ago.


    - Joan :)

    Tuesday, November 3, 2015 4:57 PM
  • Hi Naomi, thank you for responding :)

    I will absolutely take your advice and get rid of AddWithValue.  Like I said, I'm new to this so any/all suggestions on how I can do this the 'correct' way are more than welcome!

    I'm sorry, I'm not sure how to show you the procedure definition.  Is that something I can get while in Visual Studio or do I need to go into the SQL Configuration Manager?  My apologies for being clueless here, I've literally just started using SQL less than a week ago.


    - Joan :)

    We need the definition of "usp_PopulationIndex".
    As the error persists beyond closing of the application, it is propably on the DB end. Not the programm end. And thus somewhere inside that stored procedure.
    Tuesday, November 3, 2015 5:07 PM
  • If you have SQL Server installed on your computer, then try going to SSMS (SQL Server Management Studio), connect to your server (Database Engine), then select your database in the Object Explorer, go to 'Programmability', then to stored procedures, find your procedure, right click on it, script stored procedures as 'ALTER' to (you can chose clipboard), then paste here.

    If you don't have SSMS installed, you can do it directly from Visual Studio, but I don't normally do it this way.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, November 3, 2015 5:11 PM
  • Run the stored procedure in SQL server Management studio and see the result. May be something wrong with Store procedure. You can run the stored procedure using below syntax.

    exec usp_PopulationIndex 'ALT'

    If Stored prcoedure rturn correct data then set grid datasource to null before bind to datatable. My guess is something wrong with SP.

    • Proposed as answer by Jigneshk Tuesday, November 3, 2015 5:12 PM
    Tuesday, November 3, 2015 5:12 PM
  • Thanks Christopher!

    If it's something in the procedure then I'll need to mention it to my coworker.  He told me the procedure name/location and said to just pass that one parameter to it, that's all I really know. 

    I wish I could provide the info you need but I have no idea how to get it.  I know it's most likely something simple to figure out but I honestly have no clue..


    - Joan :)

    Tuesday, November 3, 2015 5:13 PM
  • Thanks again Naomi..

    I just checked and I don't have SSMS installed.  I have SQL Server Configuration Manager but I don't think that's the same thing.

    I have a meeting on this in 10 minutes so I'll just have to bring it up there.  I'll let you know how it goes.

    Thanks so much for trying to help :)


    - Joan :)

    Tuesday, November 3, 2015 5:17 PM
  • Hi Joan,

    After the meeting talk with your co-worker about installing SSMS on your computer. If you're going to be involved in the development, SSMS is a must.

    Once you have it installed, go ahead with the steps I listed, post the procedure code and we will be able to find out what may be the problem. Or just ask the co-worker.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, November 3, 2015 5:21 PM
  • Thanks to everyone for your help :)

    It turns out it was something is the stored procedure.  He said he was was preserving the results in a Permanent table.  He fixed it and my code works fine (after a few little tweaks, thanks Naomi!)

                string thisMarket = "ABJ";
                using (SqlConnection connection = new SqlConnection("Data Source=vortz;Integrated Security=SSPI;Initial Catalog=Intropages"))
                using (SqlCommand cmd = new SqlCommand("usp_PopulationIndex", connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@Geo", 3)).Value = thisMarket;
                   
                    connection.Open();
    
                    SqlDataReader reader = cmd.ExecuteReader();
                    var dataTable = new DataTable();
                    dataTable.Clear();
                    dataTable.Load(reader);
                    dataGridView2.DataSource = dataTable;
    
                }



    - Joan :)

    Wednesday, November 4, 2015 5:24 PM
  • I think the version of Add you've chosen is not the best one.

    I was thinking about this type of syntax instead:

     sqlCommand.Parameters.Add("@pod_id", SqlDbType.VarChar, 12).Value = pod;
                    sqlCommand.Parameters.Add("@tStart", SqlDbType.DateTime).Value = tStart;
                    sqlCommand.Parameters.Add("@tEnd", SqlDbType.DateTime).Value = tEnd;

    And yes, my first suspicion was that in the stored procedure he was just adding info in the permanent table.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, November 4, 2015 6:23 PM