Query string databind

Question

I am trying to bind data to a label when it is the same as the query string that I have. I keep getting a error saying.

Must declare the scalar variable "@YouthClubID".

Here is my code.

      using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data;


namespace WebApplication1
{
    public partial class youthclubpage : System.Web.UI.Page
    {
        int YouthClubID;

       protected void Page_Load(object sender, EventArgs e)
        {
            using (SqlConnection sqlConnection = new SqlConnection("server = sql7.hostinguk.net; uid = joanton7865org7272_phelim; pwd = Armo32nk; database = joanton7865org7272_youthpodcast;"))
            {
                sqlConnection.Open();
                string strSQL = "Select youthclubname from youthclublist WHERE ([YouthClubID] = @YouthClubID)";
                using (SqlCommand sqlCommand = new SqlCommand(strSQL, sqlConnection))
                {
                    sqlCommand.Parameters.AddWithValue("@YouthClubID", Request.QueryString["club"]);
                    txtClubName.Text = sqlCommand.ExecuteScalar() + "";
                }
            }
        }
        }


    }

you should redact the connection information in your question before posting it. — Nicholas Murray, Feb 01 '12 at 11:39

score 3 · Accepted Answer · edited May 23 '17 at 11:56

3

The error happens, as others said already, because you're not populating the Parameter defined in the SQL statement.

Anyway, you don't need Adapter at all, in your case ExecuteScalar method is enough:

using (SqlConnection sqlConnection = new SqlConnection("..."))
{
    sqlConnection.Open();
    string strSQL = "Select youthclubname from youthclublist WHERE ([YouthClubID] = @YouthClubID)";
    using (SqlCommand sqlCommand = new SqlCommand(strSQL, sqlConnection))
    {
        sqlCommand.Parameters.AddWithValue("@YouthClubID", Request.QueryString["club"]);
        txtClubName.Text = sqlCommand.ExecuteScalar() + "";
    }
}

In the above I assumed the club ID is passed as page.aspx?club=[id here] meaning the query string variable name is "club", change to the real name of course.

The + "" part is meant to put empty text in case there is no matching value in database.
When there is no such value, sqlCommand.ExecuteScalar() will return null - having the plain way of:

txtClubName.Text = sqlCommand.ExecuteScalar().ToString();

Will generate error in such case - by adding empty string the conversion to string occurs automatically and it looks more pretty. See this question as well. Needless to say, if the value is not null, it won't be changed.

edited May 23 '17 at 11:56

Community

1
1

answered Feb 01 '12 at 10:15

Shadow The GPT Wizard

66,030
26
140
208

I'm not sure of the purpose of `+ ""` at the end of the `ExecuteScalar()`? – Curtis Feb 01 '12 at 10:19
Cheers thats helped explain the reason – Curtis Feb 01 '12 at 10:45
When i use this code i get the following error. The parameterized query '(@YouthClubID nvarchar(4000))Select youthclubname from youthclub' expects the parameter '@YouthClubID', which was not supplied. – Inkey Feb 01 '12 at 11:12
@Inkey so you didn't copy it properly. Post your new code and I'll try to see what you done wrong. – Shadow The GPT Wizard Feb 01 '12 at 11:15
@Inkey this means `Request.QueryString["club"]` is null - how your query string looks like? I was just guessing with "club". – Shadow The GPT Wizard Feb 01 '12 at 11:47
heres my query string

Inkey

Feb 01 '12 at 11:49

@Inkey So change `Request.QueryString["club"]` to `Request.QueryString["CategoryID"]`. That's what I meant in *meaning the query string variable name is "club", change to the real name of course.* – Shadow The GPT Wizard Feb 01 '12 at 11:52

Andreas Rohde · Answer 2 · 2012-02-01T10:20:32.237

You're using a Variable in your SQL Statement, that you have do define/ set in your C# code.

sqlConnection = new SqlConnection(<ConnectionString>);
sqlCommand = new _SQLCli.SqlCommand("Select youthclubname from youthclublist WHERE ([YouthClubID] = @YouthClubID)",sqlConnection);
            sqlCommand.Parameters
                         .Add(new _SQLCli.SqlParameter( "@YouthClubID", <neededValue>);)
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);

score 0 · Answer 3 · answered Feb 01 '12 at 10:08

SqlConnection sqlConnection = new SqlConnection("...");
SqlCommand command = new SqlCommand("Select youthclubname from youthclublist WHERE ([YouthClubID] = @YouthClubID)", sqlConnection);
command.Parameters.Add("@YouthClubID", SqlDbType.Int);
command.Parameters["@YouthClubID"].Value = clubID;

SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(command);

score 0 · Answer 4 · answered Feb 01 '12 at 10:19

you need add the parameters to your command object. SqlDataAdapter, try like this . a your returning only one value from db, you can use ExecuteScalar method

  SqlDataAdapter adapter = new SqlDataAdapter();

    // Create the SelectCommand.
    SqlCommand command = new SqlCommand(Select youthclubname from youthclublist WHERE ([YouthClubID] = @YouthClubID)", sqlConnection);

    // Add the parameters for the SelectCommand.
    command.Parameters.AddWithValue("@YouthClubID", yourYouthClubID);
    yourClubName = sqlCommand.ExecuteScalar();

Query string databind

4 Answers4