Trying to pull Testimonials from a database in C#; Receiving error 8178 and Parameterized Query Expe-CodePudding

I'm very new to backend development, and I'm feeling a bit stuck with accomplishing my goal here. I'm trying to pull testimonials and blurbs out of an SQL database and have them populate on the page that I've built. Currently, the code that I've written does nothing but pull an error (8178) and tell me that it expects a parameter that hasn't been supplied. Any guidance would be appreciated. I feel like it's something rather small that I'm missing, but I haven't had any luck in figuring it out.

This currently will provide the Response.Write that I've included, and that's working, but I can't seem to figure out what is stopping me from pulling the info off of the database. I've checked that my connection String is correct and active.

//This will load blurbs that will initially appear on page.
protected void Page_Load(object sender, EventArgs e)
{
    //BlurbID = Session["Blurb"];
    Page.Title = "Testimonials";

    try
    {
        sqlConnectionStr.Open();
        SqlCommand getBlurb = new SqlCommand(@"SELECT b.BlurbID, 
            b.BlurbText
            FROM TestimonialBlurb b LEFT JOIN Testimonial t ON
             t.BlurbID = b.BlurbID WHERE t.BlurbID=@BlurbID", sqlConnectionStr);

        getBlurb.Parameters.AddWithValue("@BlurbID", SqlDbType.Int);
        getBlurb.Parameters.AddWithValue("@BlurbText", SqlDbType.VarChar);
        DataTable userBlurb = new DataTable();
        using (SqlDataAdapter blurbDA = new SqlDataAdapter(getBlurb))
        {
            blurbDA.Fill(userBlurb);
            DataView blurbDV = new DataView(userBlurb);
            if (blurbDV.Count < 1)
            {
                Response.Write("There are currently no testimonials available.");
            }
            else
            {

                for (int i = 0; i < blurbDV.Count; i  )
                {
                    blurbPH.Controls.Add(new Literal
                    {
                        Text = blurbDV[i].Row["BlurbText"].ToString()   "<strong> "   blurbDV[i].Row["Blurb"].ToString()   "</strong>"
                    });
                }

            }
        }
    }

    catch (Exception ex)
    {
        blurbPH.Controls.Add(new Literal
        {
            Text = ex.ToString()
        });
    }

    finally
    {
        sqlConnectionStr.Close();
    }

CodePudding user response：

You have this query:

sqlConnectionStr.Open();
SqlCommand getBlurb = new SqlCommand(@"SELECT b.BlurbID, 
    b.BlurbText
    FROM TestimonialBlurb b LEFT JOIN Testimonial t ON
     t.BlurbID = b.BlurbID WHERE t.BlurbID=@BlurbID", sqlConnectionStr);

You can see here that you have no need for @BlurbText as only @BlurbID is used in the query. We can remove that parameter.

Now, let's look at your parameter that you're adding:

getBlurb.Parameters.AddWithValue("@BlurbID", SqlDbType.Int);

When you use AddWithValue, the second argument is the value you're assigning to @BlurbId. Clearly this is not what we want. I think you've got this mixed up with Add.

We can thus write it correctly as:

getBlurb.Parameters.Add("@BlurbID", SqlDbType.Int).Value = 5;

The value 5 would then be used where @BlurbID appears in your query.

By the way, you don't need to read into a DataTable to access your data, only to create a new object. You can just use SqlDataReader:

getBlurb.Parameters.Add("@BlurbID", SqlDbType.Int).Value = 5;
using (SqlDataReader reader = getBlurb.ExecuteReader())
{
    while (reader.Read())
    {
        blurbPH.Controls.Add(new Literal
        {
            Text = reader["BlurbText"].ToString()   "<strong> "   reader["Blurb"].ToString()   "</strong>"
        });
    }

    if (blurbPH.Controls.Count == 0)
    {
        Response.Write("There are currently no testimonials available.");
    }

}

Side note for using .Add with string values. You should use this overload. If your column type in the database is a varchar(255) then adding a string should look like this:

myCommand.Parameters.Add("@MyParameter", SqlDbType.VarChar, 255).Value = "Hello";

Note how we specify the length of the field here.

CodePudding user response：

Following lines, the code needs to be updated getBlurb.Parameters.AddWithValue("@BlurbID", SqlDbType.Int); getBlurb.Parameters.AddWithValue("@BlurbText", SqlDbType.VarChar);

getBlurb.Parameters.AddWithValue("@BlurbID", 1001);//Considering 1001 as as the ID BlurbID

and @BlurbText Parameter does not exist in the SQL query any need to pass it.

If you are using AddWithValue then no need to set the SqlDbType.