Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.
using (SqlConnection con = db.con(user.Authority))
{
    string command = "[Dashboard_GetUserPTOAndIncidentTotals] ";
    using (SqlCommand cmd = new SqlCommand(command, con))
    {
        cmd.Parameters.AddWithValue("@UserName", cboIncidentsActiveDirectory.Text.Trim());
        // Display the selected users information
        con.Open();

        using (SqlDataReader dr = cmd.ExecuteReader())
        {
            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    nudPTO.Value = Convert.ToDecimal(dr["PTO_REMAINING"]);
                    nudIncidents.Value = Convert.ToDecimal(dr["TOTAL_INCIDENTS"]);
                }
            }
            else
            {
                nudPTO.Value = 0;
                nudIncidents.Value = 0;
            }
            dr.Close();
        }
    }
}

I am writing a business application to help track the remaining PTO time and incidents for our employees, however, I am running into an issue with the parameters of my code. I initially started out with an embedded SQL statement in place of [Dashboard_GetUserPTOAndIncidentTotals], which worked perfectly, however, after finishing the base code I was asked to migrate all application statements to stored procedures on the server side. After this transition I have ran into multiple issues and this is the only one I can't seem to work out. Every time I run this code, it throws this error message "Procedure or function 'Dashboard_GetUserPTOAndIncidentTotals' expects parameter '@UserName', which was not supplied".

This error occurs on this line :

 using (SqlDataReader dr = cmd.ExecuteReader())

I'm confused because to the best of my knowledge, I am supplying the requested parameter here :

cmd.Parameters.AddWithValue("@UserName", cboIncidentsActiveDirectory.Text.Trim());

Am I just setting this up incorrectly, or does something additional just have to be done to correctly instantiate the datareader object with my stored procedure?

share|improve this question
    
Maybe this will help you? stackoverflow.com/questions/9999751/… –  Thomas Apr 1 '13 at 15:21
    
I appreciate the comment and link, but this just arrives at the exact same error. :\ The way they are showing to parameterize the query and the way I have yield the exact same result, mine just explicitly sets the type of parameter being passed (which is the correct parameter type by the way). –  Joshua Volearix Apr 1 '13 at 15:35
1  
If this is in fact true you should change your code in such a way that the AddWithValue signature is used correctly also in your question. In your example code you use the type as the second argument. There does not seem to be a documented overload of that method which accepts a type as second argument. –  Thomas Apr 1 '13 at 15:43
    
Okay, I changed it in the code above and within the actual code in my application, still gives that same error. –  Joshua Volearix Apr 1 '13 at 16:03
add comment

closed as off topic by Jesse C. Slicer, Robert Harvey, MichaelT, JeffO, Martijn Pieters Apr 2 '13 at 0:29

Questions on Programmers Stack Exchange are expected to relate to software development within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

3 Answers

According to the online documentation the second parameter takes the value, not the type.

share|improve this answer
add comment

You have confused the living daylights out of the AddWithValue method on the SqlCommand object -- you either need to do:

cmd.Parameters.AddWithValue("@UserName", cboIncidentsActiveDirectory.Text.Trim());

-OR-

cmd.Parameters.Add("@UserName", SqlDbType.VarChar).Value = cboIncidentsActiveDirectory.Text.Trim();

Both should work fine.

share|improve this answer
    
Definitely not saying it's right, but all this time I have been using the above listed format without the slightest hiccup! I see that your provided answer is in fact the proper format and I'll be using it going forward, it just strikes me as so odd that the syntax I've been using works too! :S –  Joshua Volearix Apr 1 '13 at 15:52
add comment

I've figured it out. Upon further investigation, you can't have a space between the stored procedure name and the ending quotation mark.

This is all that needed to be changed for this to work... >.<

string command = "[ServerName].[dbo].[Dashboard_GetUserPTOAndIncidentTotals]";

Here is the finished and working example.

using (SqlConnection con = db.con(user.Authority))
            {
                string command = "[ServerName].[dbo].[Dashboard_GetUserPTOAndIncidentTotals]";
                using (SqlCommand cmd = new SqlCommand(command, con))
                {
                    cmd.Parameters.AddWithValue("@UserName", cboIncidentsActiveDirectory.Text.Trim());
                    // Display the selected users information
                    con.Open();

                    using (SqlDataReader dr = cmd.ExecuteReader())
                    {
                        if (dr.HasRows)
                        {
                            while (dr.Read())
                            {
                                nudPTO.Value = Convert.ToDecimal(dr["PTO_REMAINING"]);
                                nudIncidents.Value = Convert.ToDecimal(dr["TOTAL_INCIDENTS"]);
                            }
                        }
                        else
                        {
                            nudPTO.Value = 0;
                            nudIncidents.Value = 0;
                        }
                        dr.Close();
                    }
                }
            }
share|improve this answer
    
No error indicating this stored proc doesn't exist in your database? –  JeffO Apr 1 '13 at 21:26
    
Nope, this code works perfect. Not a single issue with it now. –  Joshua Volearix Apr 2 '13 at 13:12
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.