Thank you in advance. I believe the items below should give a whole view.

.Net 4.5.1 / x86

When using a SqlAdapter with new SqlParameter() { SqlDbType = SqlDbType.DateTime, ParameterName = "@FromDate", Value = "09/01/2008" },
I get the error "Conversion failed when converting date and/or time from character string" at the SqlAdapter.Fill method.

After trying every date format I could find, specifying the CommandType and DataType, I still get the above error.

If I replace the ParameterName with that date in the CommandText and add no SqlParameters, the SqlAdapter returns the expected records.

What am I missing?

public SqlConnection Connection { get; set; }

public CSqlServerDL(string Key, string Database)
{
    this.Connection = new SqlConnection(string.Format(CSqlServerDLHelper.GetConnString(Key), Database));
}

public void Execute(DataSet ds, List<CSqlServerDLTransactionObj> lis_sto)
{
    if (Connection.Equals(null) || string.IsNullOrEmpty(Connection.ConnectionString))
    {
        return;
    }

    try
    {
        using (Connection)
        {
            Connection.Open();

            foreach (CSqlServerDLTransactionObj sto in lis_sto)
            {
                if (!string.IsNullOrEmpty(sto.TableName))
                {
                    FillDataSet(sto, ds, DebugExecute);
                    continue;
                }
            }
        }
    }
    catch (Exception exc)
    {
        Debug.WriteLine(exc.Message);
    }
    finally
    {
        Connection.Close();
    }
}

private void FillDataSet(CSqlServerDLTransactionObj sto, DataSet ds)
{
    SqlDataAdapter sa = new SqlDataAdapter(sto.Command.CommandText, Connection);
    sa.SelectCommand.CommandType = CommandType.Text;

    if (sto.Parameters.Count.Equals(0))
    {
        int intRecords = sa.Fill(ds, sto.TableName);
        return;
    }

    try
    {
        sto.Parameters.ForEach(lis_sp =>
        {
            lis_sp.ForEach(sp => sa.SelectCommand.Parameters.AddWithValue(sp.ParameterName, sp.Value));

            sa.Fill(ds, sto.TableName);

            sa.SelectCommand.Parameters.Clear();
        });
    }
    catch (SqlException se)
    {
        Debug.WriteLine(se.Message);
    }
    catch (Exception e)
    {
        Debug.WriteLine(e.Message);
    }
}

The problem ended up being a combination of the query and data format.

This question has already been answered. Start a new discussion instead.