ERROR: Failed to convert parameter value from a String to a DateTime
0
votes
1
answer
4458
views
I have faced this error: Failed to convert parameter value from a
String to a DateTime when trying to pass multiple selected dates from checkboxlist to my parameter to be used in sql.
I have tried it with other datatypes such as nvarchar and it works when I pass multiple selected values to 1 stored procedure parameter and return the select statement using dynamic sql to populate my gridview.
Ps. In my webserver i'm displaying in checkboxlist as e.g 31-Aug-2013, using DATE.DataTextFormatString = "{0:dd-MMM-yyyy}";. In my sql database, it is displayed as e.g 2013-08-31.
During debugging selectedDATE do read it as e.g 2013-08-31. however there is error at da.Fill(ds); line
ASPX.CS
protected void Page_Load(object sender, EventArgs e)
{
DATE.DataTextFormatString = "{0:dd-MMM-yyyy}";
using (SqlConnection conn = new SqlConnection(dbConn))
{
try //Call stored procedure
{
SqlCommand cmd = new SqlCommand(spddl, conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
if (!IsPostBack)
{
DATE.DataSource = ds.Tables;
DATE.DataTextField = ds.Tables.Columns["DATE"].ToString();
DATE.DataBind();
}
if (IsPostBack)
{
Bind();
}
}
catch (Exception i)
{
bool exception = true;
if (exception == true)
{
//txtMessage.Text += e.Message;
}
}
}
}
public void Bind()
{
using (SqlConnection conn = new SqlConnection(dbConn))
{
using (SqlCommand cmd = new SqlCommand(spretrieve, conn))
{
String selectedDATE = String.Empty;
if (DATE.SelectedValue == "All")
{
selectedDATE = "DATE";
}
else
{
foreach (ListItem item in DATE.Items)
{
if (item.Selected)
{
DateTime dtTemp = Convert.ToDateTime(item.Value);
selectedDATE += "'" + dtTemp.ToString("yyyy-MM-dd") + "',";
}
}
selectedDATE = selectedDATE.Substring(0, selectedDATE.Length - 1);
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@param", SqlDbType.DateTime).Value = selectedDATE;
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds= new DataSet();
da.Fill(ds);
GRIDVIEW.DataSource = ds.Tables;
GRIDVIEW.DataBind();
}
}
SQL
ALTER PROCEDURE [dbo].[SP]
@param nvarchar(512)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @sql nvarchar(max)
SET @sql = 'SELECT * FROM TABLENAME WHERE [COLUMN] IN (' + @param + ')'
EXEC sp_executesql @sql;
END
Asked by newtoasp
(1 rep)
Sep 22, 2015, 06:48 AM
Last activity: Jun 17, 2016, 10:33 AM
Last activity: Jun 17, 2016, 10:33 AM