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);
}
}