CODEWITHSUNDEEP
asp.netvisual-studio
xorpower
xorpower

Reputation: 19003

how to write select parameterized query in asp.net

Below code is written to call parameterized select query in asp.net

public bool checkConflictTime()
{
    bool TimeExists = false;

    DataSet ds = new DataSet();

    SqlConnection sqlconn = new SqlConnection();
    sqlconn.ConnectionString = ConfigurationManager.ConnectionStrings["TestConn"].ConnectionString;
    string sql = @"SELECT * FROM Images WHERE starttime= @starttime AND endtime = @endtime";

    SqlCommand sqlcommand = new SqlCommand(sql,sqlconn);

    //sqlcommand.Connection = sqlconn;

    //string sql = "CheckConflictTimings";


    sqlcommand.CommandType = CommandType.Text;
    sqlcommand.CommandText = sql;

    sqlcommand.Parameters.Add(new SqlParameter("@starttime", ddlStartTime.SelectedItem.Text));
    sqlcommand.Parameters.Add(new SqlParameter("@endtime", ddlEndTime.SelectedItem.Text));

    SqlDataAdapter da = new SqlDataAdapter(sql, sqlconn);
    try
    {
        da.Fill(ds);
        if (ds.Tables[0].Rows.Count > 0)
        {
            TimeExists = true;
        }
    }   
    catch (Exception ex)
    {

    }
    finally
    {
        sqlconn.Close();
        sqlconn.Dispose();  
    }
    return TimeExists;
}

Is there something wrong? it threw error of :Must declare the scalar variable "@starttime" when filling data adapter.

Upvotes: 0

Views: 1632

Answers (4)

simplyaarti
simplyaarti

Reputation: 253

Try 

  sqlcommand.Parameters.AddWithValue("@starttime",ddlStartTime.SelectedItem.Text);

instead of

   sqlcommand.Parameters.Add(new SqlParameter("@starttime", ddlStartTime.SelectedItem.Text));

Upvotes: 0

Tim B James
Tim B James

Reputation: 20374

Try

SqlDataAdapter da = new SqlDataAdapter(sqlcommand);

Upvotes: 1

Alessandro
Alessandro

Reputation: 3760

I think you're not passing your command as a SelectCommand to the adapter.

da.SelectCommand = sqlcommand;

Upvotes: 0

Tom Gullen
Tom Gullen

Reputation: 61773

Try

sqlcommand.Parameters.Add(new SqlParameter("starttime", ddlStartTime.SelectedItem.Text));

I don't think you need the @ prefix when adding the parameter.

Upvotes: 0

Related Questions