CODEWITHSUNDEEP
c#sql
leventkalay92
leventkalay92

Reputation: 585

how can i put @ in select sql statement?

I have to use the "@"(I don't know it's name). I can use it in update delete or insert statements but I cannot use it in there it gives URL MUST BE DECLARED

       //SQL string to count the amount of rows within the OSDE_Users table
        string sql = "SELECT * FROM RSSFeeds where URL = @URL";

        SqlCommand cmd = new SqlCommand(sql, Connect());

        cmd.Parameters.Add("@URL", SqlDbType.VarChar, 500).Value = url;
        closeConnection();
        SqlDataAdapter adapt = new SqlDataAdapter(sql, Connect());
        DataSet ds = new DataSet();
        adapt.Fill(ds);

        // result of query filled into datasource
        adapt.Dispose();

        closeConnection();

        return ds;

Upvotes: 2

Views: 174

Answers (2)

Steve
Steve

Reputation: 216332

I can only suppose that this line is not correct:

cmd.Parameters.Add("@URL", SqlDbType.Int).Value = url;

Probably URL is not an Int but a NVarChar or other character type
If this is the case then change your line in this way
(255 is the supposed length of your field URL)

cmd.Parameters.Add("@URL", SqlDbType.NVarChar, 255).Value = url;

And, by the way, '@' is called "Parameter Prefix"

EDIT: Seeing the last edit from the OP I update my answer to show what I think is the correct way to go.

   //SQL string to count the amount of rows within the OSDE_Users table 
    string sql = "SELECT * FROM RSSFeeds where URL = @URL"; 
DataSet ds = new DataSet(); 
    using(SqlConnection cnn = Connect())
    using(SqlCommand cmd = new SqlCommand(sql, cnn)) 
    {
        cmd.Parameters.Add("@URL", SqlDbType.VarChar, 500).Value = url; 
        using(SqlDataAdapter adapt = new SqlDataAdapter(cmd))
        {

            adapt.Fill(ds); 
        }
    }
    return ds;

What I have changed:

  • Encapsulated every disposable object inside an using statement that is guaranteed to close/dispose objects
  • Called Connect() just one time and captured the SqlConnection returned to reuse without creating another one
  • Created the SqlDataAdapter using the SqlCommand created before (so the @URL parameter reaches the Sql)

The OP used a closeConnection() and we don't see the internal of this method, but I think that using is enough to close and dispose the connection.

EDIT: The line that creates SqlDataAdapter should be 

using(SqlDataAdapter adapt = new SqlDataAdapter(cmd))

Upvotes: 4

iefpw
iefpw

Reputation: 7052

cmd.Parameters.AddWithValue("@URL", url);

should work

Upvotes: 1

Related Questions