System.Data.SqlClient.SqlException: Must declare the scalar variable in ASP.Net

Last Reply 11 months ago By dharmendr

Posted 11 months ago

Hello Sir,

Below is the code which im using to insert the data based on checkboxlist selection.

If chechboxlist1 is selected only date1 should be entered and date value should be null and vice versa.

date is used to insert all the dates for sunday which comes in the given date range and date1 is for all mondays in given date range.

If im trying to insert only sunday or monday then no error.

Must declare the scalar variable "@date0".

        DateTime startDate = Convert.ToDateTime(txt_date.Text);
        DateTime endDate = Convert.ToDateTime(txt_todate.Text);

        DateTime d1 = DateTime.Parse(txtTime.Text);
        DateTime d2 = DateTime.Parse(txt_totime.Text);
        TimeSpan timeFrom = TimeSpan.Parse(d1.ToString("HH:mm"));
        TimeSpan timeTo = TimeSpan.Parse(d2.ToString("HH:mm"));
        TimeSpan timeDiff;
        if (timeFrom.TotalSeconds > timeTo.TotalSeconds)
        { d2 = d2.AddDays(1); timeDiff = d2.Subtract(d1); }
        else { timeDiff = d2.Subtract(d1); }
        TimeSpan DateDiff = endDate.Subtract(startDate);
        for (int i = 0; i <= DateDiff.Days; i++)
        {
            if (startDate.Date.AddDays(i).DayOfWeek == DayOfWeek.Sunday)
            {
                string sql = "INSERT INTO crm_tbl_newalign(name, from_date,to_date,from_time,to_time,date,date1,time_diff) VALUES";

                sql += "(@name" + i.ToString() + ", @from_date" + i.ToString() + ", @to_date" + i.ToString() + ", @from_time" + i.ToString() + ", @to_time" + i.ToString() + ", @date" + i.ToString() + ",@time_diff" + i.ToString() + ")";
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.Parameters.AddWithValue("@name" + i.ToString(), txt_rolename.Text);
                cmd.Parameters.AddWithValue("@from_date" + i.ToString(), Convert.ToDateTime(txt_date.Text));
                cmd.Parameters.AddWithValue("@to_date" + i.ToString(), Convert.ToDateTime(txt_todate.Text));
                cmd.Parameters.AddWithValue("@from_time" + i.ToString(), txtTime.Text);
                cmd.Parameters.AddWithValue("@to_time" + i.ToString(), txt_totime.Text);
                cmd.Parameters.AddWithValue("@date" + i.ToString(), Convert.ToDateTime(startDate.Date.AddDays(i).ToShortDateString()));
                cmd.Parameters.AddWithValue("@date1" + i.ToString(), DBNull.Value);
                cmd.Parameters.AddWithValue("@time_diff" + i.ToString(), timeDiff.TotalHours);               
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
        for (int i = 0; i <= DateDiff.Days; i++)
        {
            if (startDate.Date.AddDays(i).DayOfWeek == DayOfWeek.Monday)
            {
                string sql = "INSERT INTO crm_tbl_newalign(name, from_date,to_date,from_time,to_time,date,date1,time_diff) VALUES";
                sql += "(@name" + i.ToString() + ", @from_date" + i.ToString() + ", @to_date" + i.ToString() + ", @from_time" + i.ToString() + ", @to_time" + i.ToString() + ", @date" + i.ToString() + ",@time_diff" + i.ToString() + ")";
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.Parameters.AddWithValue("@name" + i.ToString(), txt_rolename.Text);
                cmd.Parameters.AddWithValue("@from_date" + i.ToString(), Convert.ToDateTime(txt_date.Text));
                cmd.Parameters.AddWithValue("@to_date" + i.ToString(), Convert.ToDateTime(txt_todate.Text));
                cmd.Parameters.AddWithValue("@from_time" + i.ToString(), txtTime.Text);
                cmd.Parameters.AddWithValue("@to_time" + i.ToString(), txt_totime.Text);
                cmd.Parameters.AddWithValue("@date" + i.ToString(), DBNull.Value);
                cmd.Parameters.AddWithValue("@date1" + i.ToString(), Convert.ToDateTime(startDate.Date.AddDays(i).ToShortDateString()));
                cmd.Parameters.AddWithValue("@time_diff" + i.ToString(), timeDiff.TotalHours);                
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }

 

You are viewing reply posted by: dharmendr 11 months ago.
Posted 11 months ago
democloud says:
if (startDate.Date.AddDays(i).DayOfWeek == DayOfWeek.Sunday)
            {
                if (CheckBoxList7.Items[0].Selected == true)
                {
                    cmd.Parameters.AddWithValue("@date" + i.ToString(), Convert.ToDateTime(startDate.Date.AddDays(i).ToShortDateString()));
                }
                else
                {
                    cmd.Parameters.AddWithValue("@date" + i.ToString(), "");
                }
            }
            if (startDate.Date.AddDays(i).DayOfWeek == DayOfWeek.Monday)
            {
                if (CheckBoxList1.Items[0].Selected == true)
                {
                    cmd.Parameters.AddWithValue("@date1" + i.ToString(), Convert.ToDateTime(startDate.Date.AddDays(i).ToShortDateString()));
                }
                else
                {
                    cmd.Parameters.AddWithValue("@date1" + i.ToString(), "");
                }
            }

Replace above code with below.

if (startDate.Date.AddDays(i).DayOfWeek == DayOfWeek.Sunday)
{
    if (CheckBoxList7.Items[0].Selected)
    {
        cmd.Parameters.AddWithValue("@date" + i.ToString(), Convert.ToDateTime(startDate.Date.AddDays(i).ToShortDateString()));
    }
    else
    {
        cmd.Parameters.AddWithValue("@date" + i.ToString(), (object)DBNull.Value);
    }
}
else
{
    cmd.Parameters.AddWithValue("@date" + i.ToString(), (object)DBNull.Value);
}

if (startDate.Date.AddDays(i).DayOfWeek == DayOfWeek.Monday)
{
    if (CheckBoxList1.Items[0].Selected)
    {
        cmd.Parameters.AddWithValue("@date1" + i.ToString(), Convert.ToDateTime(startDate.Date.AddDays(i).ToShortDateString()));
    }
    else
    {
        cmd.Parameters.AddWithValue("@date1" + i.ToString(), (object)DBNull.Value);
    }
}
else
{
    cmd.Parameters.AddWithValue("@date1" + i.ToString(), (object)DBNull.Value);
}