none
Problem with simple stored procedure and DAL RRS feed

  • Question

  • Got simple Stored procedure SELECT * FROM products; (name: "GetProducts")

    Have got DAL method look like:

     

    #region
            public static DataTable ExecuteDataTableSP(string storedProcedureName, params SqlParameter[] arrParam)
            {
                DataTable dt = new DataTable();
    
                // Open the connection
                using (SqlConnection cnn = new SqlConnection("Data Source=derc;" +
                                                                             "Initial Catalog=sen;" +
                                                                             "User ID=sa;" +
                                                                             "Password=pass"))
                {
                    cnn.Open();
    
                    // Define the command
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.Connection = cnn;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = storedProcedureName;
    
                        // Handle the parameters
                        if (arrParam != null)
                        {
                            foreach (SqlParameter param in arrParam)
                                cmd.Parameters.Add(param);
                        }
    
                        // Define the data adapter and fill the dataset
                        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                        {
                            da.Fill(dt);
                        }
                    }
                }
                return dt;
            }
            #endregion
    

     


    When I want to retrive result to datagrid view by this then it not fill it.. Have no idea its simple thing..

     

    DataTable dt = DAL.Attributes.ExecuteDataTable("GetProducts", null);
                dataGridView1.DataSource = dt;
    


    P.S If it is possible how to convert this method that I can declare in constructor that is stored procedure or just string query



    • Edited by JimmyJimm Sunday, November 20, 2011 6:43 PM
    • Moved by Naomi N Sunday, November 20, 2011 8:17 PM Better answer can be here (From:Transact-SQL)
    Sunday, November 20, 2011 6:40 PM

Answers

  • My guess is that the problem is because you are not passing in any params "params SqlParameter[] arrParam" -- you are just setting the StoredProcedureName parameter to "GetProducts" and the params are set to Null:

    DataTable dt = DAL.ExecuteDataTableSP("GetProducts", null);

    Check your GetProducts stored procedure and see what parameters it expects.  Then make sure you are assigning values to those params and inlcuding them into your DAL.ExecuteDataTableSP function.

     


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    Monday, November 21, 2011 2:20 PM

All replies


  • This forum is for Transact-SQL. I can answer some questions about data access from SqlClient as well, but that definitely stops at the DataTable and DataSet objects. If you have a grid that's a UI thing, and the only UI we know of in this forum is the Query Editor in SSMS!

    So I would recommend you try a suitable Visual Studio forum.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, November 20, 2011 8:14 PM
  • Understand but the only DAL method it is good? Or how to convert it to can i declare in constructor that is string query or stored procedure,

    btw can u migrate this post to Visual studio forum?

    Sunday, November 20, 2011 8:28 PM
  • Hey JimmyJimm!  ;0)

    Or how to convert it to can i declare in constructor that is string query or stored procedure,

    I'm not sure what you mean by that. Can you explain?

    Your DAL looks fine. Have you determined whether or not it's actually retrieving any data into the DataTable (debug, breakpoint)? Also, you could run a trace in the Sql Profiler to be sure the query is being passed to Sql Server.


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Monday, November 21, 2011 1:22 AM
  • Gratz Bonnie :)!


    I make this in try/catch:

     

    DataTable table = new DataTable();
    
    DataTable dt = DAL.ExecuteDataTableSP("GetProducts", null);
    
    dataGridView1.DataSource = dt; 

     

    and I retrive an exception:

    System.NullReferenceException: Object reference not set to an instance of an object


    on this line: DataTable dt = DAL.ExecuteDataTableSP("GetProducts", null);
    • Edited by JimmyJimm Monday, November 21, 2011 9:07 AM
    Monday, November 21, 2011 7:26 AM
  • My guess is that the problem is because you are not passing in any params "params SqlParameter[] arrParam" -- you are just setting the StoredProcedureName parameter to "GetProducts" and the params are set to Null:

    DataTable dt = DAL.ExecuteDataTableSP("GetProducts", null);

    Check your GetProducts stored procedure and see what parameters it expects.  Then make sure you are assigning values to those params and inlcuding them into your DAL.ExecuteDataTableSP function.

     


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    Monday, November 21, 2011 2:20 PM
  • If you debug & step through the code (and into DAL.ExecuteDataTableSP if necessary), you'll see exactly which object is still null. Much easier than us trying to guess!  ;0)
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Monday, November 21, 2011 3:42 PM
  • Hi Jimmy,

    I agree with james, please check your stored procedure's expected parameters.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Wednesday, November 23, 2011 2:25 AM
    Moderator