SqlClient Data Adapter Fill Method

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I am having a problem with the SqlClient Data Adapter. I originally noticed
the problem by placing a trace on my sql server and watching which stored
procedures are being logged.

More specifically the problem that is occurring seems to relate to the
datetime fields that I pass in to my stored procedure as parameters. I have
found that if I leave the end date parameter as null then the procedure is
executed. However, if I specify a value for this parameter then the Fill
method of the data adapter does not appear to execute my command object. No
errors are raised the fill command just returns 0 rows and I do not see the
command executed in my Sql profiler. Below are some excerpts of the code.

I am running .NET v1.1 runtime w/ SQL Server 2000 database.

C# Code
public long GetOpenTime(string strAccessCode, OpenTimeSearch
udtAppOpenTimeSearch, out OpenTime dsOpenTime)
{
DateTime dtStartDate, dtEndDate;
try
{
dsOpenTime = new OpenTime();
dsOpenTime.EnforceConstraints=false;

SqlParameter[] commandParameters = new SqlParameter[14];
commandParameters[0] = new SqlParameter("@strAccessCd", strAccessCode);

if(udtAppOpenTimeSearch.strProvider=="")
commandParameters[1] = new SqlParameter("@strProviderCd", null);
else
commandParameters[1] = new SqlParameter("@strProviderCd",
udtAppOpenTimeSearch.strProvider);

dtStartDate=udtAppOpenTimeSearch.startDate;
if(dtStartDate==DateTime.MinValue)
commandParameters[2] = new SqlParameter("@dtStartDate", null);
else
commandParameters[2] = new SqlParameter("@dtStartDate", dtStartDate);

dtEndDate=udtAppOpenTimeSearch.endDate;
if(dtEndDate==DateTime.MinValue)
commandParameters[3] = new SqlParameter("@dtEndDate", null);
else
commandParameters[3] = new SqlParameter("@dtEndDate", dtEndDate );

if(udtAppOpenTimeSearch.strMonth=="")
commandParameters[4] = new SqlParameter("@intMonth", null );
else
commandParameters[4] = new SqlParameter("@intMonth",
Int16.Parse(udtAppOpenTimeSearch.strMonth));


if(udtAppOpenTimeSearch.strYear=="")
commandParameters[5] = new SqlParameter("@intYear", null );
else
commandParameters[5] = new SqlParameter("@intYear",
Int16.Parse(udtAppOpenTimeSearch.strYear));

if(udtAppOpenTimeSearch.intAMPM==1)
{
commandParameters[6] = new SqlParameter("@strAM", "Y" );
commandParameters[7] = new SqlParameter("@strPM", null );
}
else if(udtAppOpenTimeSearch.intAMPM==2)
{
commandParameters[6] = new SqlParameter("@strAM", null );
commandParameters[7] = new SqlParameter("@strPM", "Y");
}
else
{
commandParameters[6] = new SqlParameter("@strAM", null );
commandParameters[7] = new SqlParameter("@strPM", null );
}

if(udtAppOpenTimeSearch.strState =="")
commandParameters[8] = new SqlParameter("@strStateCd", null);
else
commandParameters[8] = new SqlParameter("@strStateCd",
udtAppOpenTimeSearch.strState);

if(udtAppOpenTimeSearch.strLocation=="")
commandParameters[9] = new SqlParameter("@strLocation", null);
else
commandParameters[9] = new SqlParameter("@strLocation",
udtAppOpenTimeSearch.strLocation);

if(udtAppOpenTimeSearch.strDay=="")
commandParameters[10] = new SqlParameter("@strDay", null);
else
commandParameters[10] = new SqlParameter("@strDay",
udtAppOpenTimeSearch.strDay);

if(udtAppOpenTimeSearch.strDuration=="")
commandParameters[11] = new SqlParameter("@intDuration", null );
else
commandParameters[11] = new SqlParameter("@intDuration",
Int16.Parse(udtAppOpenTimeSearch.strDuration));

if(udtAppOpenTimeSearch.AppointmentType=="")
commandParameters[12] = new SqlParameter("@strApptType", null);
else
commandParameters[12] = new SqlParameter("@strApptType",
udtAppOpenTimeSearch.AppointmentType);

if (udtAppOpenTimeSearch.strPagingProvider == "")
commandParameters[13] = new SqlParameter("@strPaging", "");
else
commandParameters[13] = new SqlParameter("@strPaging",
udtAppOpenTimeSearch.dtPagingDate.ToString("yyyyMMdd") +
udtAppOpenTimeSearch.dtPagingTime.ToString("HH:mm:ss") +
udtAppOpenTimeSearch.strPagingProvider.PadRight(4, ' ') +
udtAppOpenTimeSearch.intPagingChair.ToString());

//SqlHelper.FillDataset(Global.GetConnectionString(),CommandType.StoredProcedure,
"GetOpenTime_sp", dsOpenTime, new string[] {"tblOpenTime"},commandParameters);
using(SqlConnection conn = new
SqlConnection(Global.GetConnectionString()))
{
conn.Open();
using(SqlCommand cmd = new SqlCommand("GetOpenTime_sp", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(cmd);
foreach (SqlParameter p in commandParameters)
cmd.Parameters[p.ParameterName].Value = p.Value;

using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
System.Diagnostics.Debug.WriteLine("Rows: " + da.Fill(dsOpenTime,
"tblOpenTime"));
}
}
conn.Close();
}

if(dsOpenTime.tblOpenTime.Rows.Count>0)
return SUCCESS;
else
return EOF;
}
catch (Exception errAll)
{
throw errAll;
}

Thanks in advance,

Phil
 
One thing sticks out although the symptoms seem to indicate a few things may
be at play. Let's go for the low hanging fruit first.
Replace each parameter value of null with DBNull.Value and see if that
doesn't ameliorate this. If not, let me know.

Bill
ptaylor said:
Hello,

I am having a problem with the SqlClient Data Adapter. I originally
noticed
the problem by placing a trace on my sql server and watching which stored
procedures are being logged.

More specifically the problem that is occurring seems to relate to the
datetime fields that I pass in to my stored procedure as parameters. I
have
found that if I leave the end date parameter as null then the procedure is
executed. However, if I specify a value for this parameter then the Fill
method of the data adapter does not appear to execute my command object.
No
errors are raised the fill command just returns 0 rows and I do not see
the
command executed in my Sql profiler. Below are some excerpts of the code.

I am running .NET v1.1 runtime w/ SQL Server 2000 database.

C# Code
public long GetOpenTime(string strAccessCode, OpenTimeSearch
udtAppOpenTimeSearch, out OpenTime dsOpenTime)
{
DateTime dtStartDate, dtEndDate;
try
{
dsOpenTime = new OpenTime();
dsOpenTime.EnforceConstraints=false;

SqlParameter[] commandParameters = new SqlParameter[14];
commandParameters[0] = new SqlParameter("@strAccessCd", strAccessCode);

if(udtAppOpenTimeSearch.strProvider=="")
commandParameters[1] = new SqlParameter("@strProviderCd", null);
else
commandParameters[1] = new SqlParameter("@strProviderCd",
udtAppOpenTimeSearch.strProvider);

dtStartDate=udtAppOpenTimeSearch.startDate;
if(dtStartDate==DateTime.MinValue)
commandParameters[2] = new SqlParameter("@dtStartDate", null);
else
commandParameters[2] = new SqlParameter("@dtStartDate", dtStartDate);

dtEndDate=udtAppOpenTimeSearch.endDate;
if(dtEndDate==DateTime.MinValue)
commandParameters[3] = new SqlParameter("@dtEndDate", null);
else
commandParameters[3] = new SqlParameter("@dtEndDate", dtEndDate );

if(udtAppOpenTimeSearch.strMonth=="")
commandParameters[4] = new SqlParameter("@intMonth", null );
else
commandParameters[4] = new SqlParameter("@intMonth",
Int16.Parse(udtAppOpenTimeSearch.strMonth));


if(udtAppOpenTimeSearch.strYear=="")
commandParameters[5] = new SqlParameter("@intYear", null );
else
commandParameters[5] = new SqlParameter("@intYear",
Int16.Parse(udtAppOpenTimeSearch.strYear));

if(udtAppOpenTimeSearch.intAMPM==1)
{
commandParameters[6] = new SqlParameter("@strAM", "Y" );
commandParameters[7] = new SqlParameter("@strPM", null );
}
else if(udtAppOpenTimeSearch.intAMPM==2)
{
commandParameters[6] = new SqlParameter("@strAM", null );
commandParameters[7] = new SqlParameter("@strPM", "Y");
}
else
{
commandParameters[6] = new SqlParameter("@strAM", null );
commandParameters[7] = new SqlParameter("@strPM", null );
}

if(udtAppOpenTimeSearch.strState =="")
commandParameters[8] = new SqlParameter("@strStateCd", null);
else
commandParameters[8] = new SqlParameter("@strStateCd",
udtAppOpenTimeSearch.strState);

if(udtAppOpenTimeSearch.strLocation=="")
commandParameters[9] = new SqlParameter("@strLocation", null);
else
commandParameters[9] = new SqlParameter("@strLocation",
udtAppOpenTimeSearch.strLocation);

if(udtAppOpenTimeSearch.strDay=="")
commandParameters[10] = new SqlParameter("@strDay", null);
else
commandParameters[10] = new SqlParameter("@strDay",
udtAppOpenTimeSearch.strDay);

if(udtAppOpenTimeSearch.strDuration=="")
commandParameters[11] = new SqlParameter("@intDuration", null );
else
commandParameters[11] = new SqlParameter("@intDuration",
Int16.Parse(udtAppOpenTimeSearch.strDuration));

if(udtAppOpenTimeSearch.AppointmentType=="")
commandParameters[12] = new SqlParameter("@strApptType", null);
else
commandParameters[12] = new SqlParameter("@strApptType",
udtAppOpenTimeSearch.AppointmentType);

if (udtAppOpenTimeSearch.strPagingProvider == "")
commandParameters[13] = new SqlParameter("@strPaging", "");
else
commandParameters[13] = new SqlParameter("@strPaging",
udtAppOpenTimeSearch.dtPagingDate.ToString("yyyyMMdd") +
udtAppOpenTimeSearch.dtPagingTime.ToString("HH:mm:ss") +
udtAppOpenTimeSearch.strPagingProvider.PadRight(4, ' ') +
udtAppOpenTimeSearch.intPagingChair.ToString());

//SqlHelper.FillDataset(Global.GetConnectionString(),CommandType.StoredProcedure,
"GetOpenTime_sp", dsOpenTime, new string[]
{"tblOpenTime"},commandParameters);
using(SqlConnection conn = new
SqlConnection(Global.GetConnectionString()))
{
conn.Open();
using(SqlCommand cmd = new SqlCommand("GetOpenTime_sp", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(cmd);
foreach (SqlParameter p in commandParameters)
cmd.Parameters[p.ParameterName].Value = p.Value;

using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
System.Diagnostics.Debug.WriteLine("Rows: " + da.Fill(dsOpenTime,
"tblOpenTime"));
}
}
conn.Close();
}

if(dsOpenTime.tblOpenTime.Rows.Count>0)
return SUCCESS;
else
return EOF;
}
catch (Exception errAll)
{
throw errAll;
}

Thanks in advance,

Phil
Click to expand...
 
Bill,

This was my initial thought too. I did the replace and the problem still
exists. Let me know if you have any other ideas.

Thanks,

Phil

W.G. Ryan MVP said:
One thing sticks out although the symptoms seem to indicate a few things may
be at play. Let's go for the low hanging fruit first.
Replace each parameter value of null with DBNull.Value and see if that
doesn't ameliorate this. If not, let me know.

Bill
ptaylor said:
Hello,

I am having a problem with the SqlClient Data Adapter. I originally
noticed
the problem by placing a trace on my sql server and watching which stored
procedures are being logged.

More specifically the problem that is occurring seems to relate to the
datetime fields that I pass in to my stored procedure as parameters. I
have
found that if I leave the end date parameter as null then the procedure is
executed. However, if I specify a value for this parameter then the Fill
method of the data adapter does not appear to execute my command object.
No
errors are raised the fill command just returns 0 rows and I do not see
the
command executed in my Sql profiler. Below are some excerpts of the code.

I am running .NET v1.1 runtime w/ SQL Server 2000 database.

C# Code
public long GetOpenTime(string strAccessCode, OpenTimeSearch
udtAppOpenTimeSearch, out OpenTime dsOpenTime)
{
DateTime dtStartDate, dtEndDate;
try
{
dsOpenTime = new OpenTime();
dsOpenTime.EnforceConstraints=false;

SqlParameter[] commandParameters = new SqlParameter[14];
commandParameters[0] = new SqlParameter("@strAccessCd", strAccessCode);

if(udtAppOpenTimeSearch.strProvider=="")
commandParameters[1] = new SqlParameter("@strProviderCd", null);
else
commandParameters[1] = new SqlParameter("@strProviderCd",
udtAppOpenTimeSearch.strProvider);

dtStartDate=udtAppOpenTimeSearch.startDate;
if(dtStartDate==DateTime.MinValue)
commandParameters[2] = new SqlParameter("@dtStartDate", null);
else
commandParameters[2] = new SqlParameter("@dtStartDate", dtStartDate);

dtEndDate=udtAppOpenTimeSearch.endDate;
if(dtEndDate==DateTime.MinValue)
commandParameters[3] = new SqlParameter("@dtEndDate", null);
else
commandParameters[3] = new SqlParameter("@dtEndDate", dtEndDate );

if(udtAppOpenTimeSearch.strMonth=="")
commandParameters[4] = new SqlParameter("@intMonth", null );
else
commandParameters[4] = new SqlParameter("@intMonth",
Int16.Parse(udtAppOpenTimeSearch.strMonth));


if(udtAppOpenTimeSearch.strYear=="")
commandParameters[5] = new SqlParameter("@intYear", null );
else
commandParameters[5] = new SqlParameter("@intYear",
Int16.Parse(udtAppOpenTimeSearch.strYear));

if(udtAppOpenTimeSearch.intAMPM==1)
{
commandParameters[6] = new SqlParameter("@strAM", "Y" );
commandParameters[7] = new SqlParameter("@strPM", null );
}
else if(udtAppOpenTimeSearch.intAMPM==2)
{
commandParameters[6] = new SqlParameter("@strAM", null );
commandParameters[7] = new SqlParameter("@strPM", "Y");
}
else
{
commandParameters[6] = new SqlParameter("@strAM", null );
commandParameters[7] = new SqlParameter("@strPM", null );
}

if(udtAppOpenTimeSearch.strState =="")
commandParameters[8] = new SqlParameter("@strStateCd", null);
else
commandParameters[8] = new SqlParameter("@strStateCd",
udtAppOpenTimeSearch.strState);

if(udtAppOpenTimeSearch.strLocation=="")
commandParameters[9] = new SqlParameter("@strLocation", null);
else
commandParameters[9] = new SqlParameter("@strLocation",
udtAppOpenTimeSearch.strLocation);

if(udtAppOpenTimeSearch.strDay=="")
commandParameters[10] = new SqlParameter("@strDay", null);
else
commandParameters[10] = new SqlParameter("@strDay",
udtAppOpenTimeSearch.strDay);

if(udtAppOpenTimeSearch.strDuration=="")
commandParameters[11] = new SqlParameter("@intDuration", null );
else
commandParameters[11] = new SqlParameter("@intDuration",
Int16.Parse(udtAppOpenTimeSearch.strDuration));

if(udtAppOpenTimeSearch.AppointmentType=="")
commandParameters[12] = new SqlParameter("@strApptType", null);
else
commandParameters[12] = new SqlParameter("@strApptType",
udtAppOpenTimeSearch.AppointmentType);

if (udtAppOpenTimeSearch.strPagingProvider == "")
commandParameters[13] = new SqlParameter("@strPaging", "");
else
commandParameters[13] = new SqlParameter("@strPaging",
udtAppOpenTimeSearch.dtPagingDate.ToString("yyyyMMdd") +
udtAppOpenTimeSearch.dtPagingTime.ToString("HH:mm:ss") +
udtAppOpenTimeSearch.strPagingProvider.PadRight(4, ' ') +
udtAppOpenTimeSearch.intPagingChair.ToString());

//SqlHelper.FillDataset(Global.GetConnectionString(),CommandType.StoredProcedure,
"GetOpenTime_sp", dsOpenTime, new string[]
{"tblOpenTime"},commandParameters);
using(SqlConnection conn = new
SqlConnection(Global.GetConnectionString()))
{
conn.Open();
using(SqlCommand cmd = new SqlCommand("GetOpenTime_sp", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(cmd);
foreach (SqlParameter p in commandParameters)
cmd.Parameters[p.ParameterName].Value = p.Value;

using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
System.Diagnostics.Debug.WriteLine("Rows: " + da.Fill(dsOpenTime,
"tblOpenTime"));
}
}
conn.Close();
}

if(dsOpenTime.tblOpenTime.Rows.Count>0)
return SUCCESS;
else
return EOF;
}
catch (Exception errAll)
{
throw errAll;
}

Thanks in advance,

Phil
Click to expand...
Click to expand...
 
Ok, one other thing you may want to check. If you are specifying lengths on
any of the parameters in the proc, make sure you do it client side two and
that they match. Also, when you run a trace, do all of the parameter values
that Sql Server is seeing look ok?
ptaylor said:
Bill,

This was my initial thought too. I did the replace and the problem still
exists. Let me know if you have any other ideas.

Thanks,

Phil

W.G. Ryan MVP said:
One thing sticks out although the symptoms seem to indicate a few things
may
be at play. Let's go for the low hanging fruit first.
Replace each parameter value of null with DBNull.Value and see if that
doesn't ameliorate this. If not, let me know.

Bill
ptaylor said:
Hello,

I am having a problem with the SqlClient Data Adapter. I originally
noticed
the problem by placing a trace on my sql server and watching which
stored
procedures are being logged.

More specifically the problem that is occurring seems to relate to the
datetime fields that I pass in to my stored procedure as parameters. I
have
found that if I leave the end date parameter as null then the procedure
is
executed. However, if I specify a value for this parameter then the
Fill
method of the data adapter does not appear to execute my command
object.
No
errors are raised the fill command just returns 0 rows and I do not see
the
command executed in my Sql profiler. Below are some excerpts of the
code.

I am running .NET v1.1 runtime w/ SQL Server 2000 database.

C# Code
public long GetOpenTime(string strAccessCode, OpenTimeSearch
udtAppOpenTimeSearch, out OpenTime dsOpenTime)
{
DateTime dtStartDate, dtEndDate;
try
{
dsOpenTime = new OpenTime();
dsOpenTime.EnforceConstraints=false;

SqlParameter[] commandParameters = new SqlParameter[14];
commandParameters[0] = new SqlParameter("@strAccessCd", strAccessCode);

if(udtAppOpenTimeSearch.strProvider=="")
commandParameters[1] = new SqlParameter("@strProviderCd", null);
else
commandParameters[1] = new SqlParameter("@strProviderCd",
udtAppOpenTimeSearch.strProvider);

dtStartDate=udtAppOpenTimeSearch.startDate;
if(dtStartDate==DateTime.MinValue)
commandParameters[2] = new SqlParameter("@dtStartDate", null);
else
commandParameters[2] = new SqlParameter("@dtStartDate", dtStartDate);

dtEndDate=udtAppOpenTimeSearch.endDate;
if(dtEndDate==DateTime.MinValue)
commandParameters[3] = new SqlParameter("@dtEndDate", null);
else
commandParameters[3] = new SqlParameter("@dtEndDate", dtEndDate );

if(udtAppOpenTimeSearch.strMonth=="")
commandParameters[4] = new SqlParameter("@intMonth", null );
else
commandParameters[4] = new SqlParameter("@intMonth",
Int16.Parse(udtAppOpenTimeSearch.strMonth));


if(udtAppOpenTimeSearch.strYear=="")
commandParameters[5] = new SqlParameter("@intYear", null );
else
commandParameters[5] = new SqlParameter("@intYear",
Int16.Parse(udtAppOpenTimeSearch.strYear));

if(udtAppOpenTimeSearch.intAMPM==1)
{
commandParameters[6] = new SqlParameter("@strAM", "Y" );
commandParameters[7] = new SqlParameter("@strPM", null );
}
else if(udtAppOpenTimeSearch.intAMPM==2)
{
commandParameters[6] = new SqlParameter("@strAM", null );
commandParameters[7] = new SqlParameter("@strPM", "Y");
}
else
{
commandParameters[6] = new SqlParameter("@strAM", null );
commandParameters[7] = new SqlParameter("@strPM", null );
}

if(udtAppOpenTimeSearch.strState =="")
commandParameters[8] = new SqlParameter("@strStateCd", null);
else
commandParameters[8] = new SqlParameter("@strStateCd",
udtAppOpenTimeSearch.strState);

if(udtAppOpenTimeSearch.strLocation=="")
commandParameters[9] = new SqlParameter("@strLocation", null);
else
commandParameters[9] = new SqlParameter("@strLocation",
udtAppOpenTimeSearch.strLocation);

if(udtAppOpenTimeSearch.strDay=="")
commandParameters[10] = new SqlParameter("@strDay", null);
else
commandParameters[10] = new SqlParameter("@strDay",
udtAppOpenTimeSearch.strDay);

if(udtAppOpenTimeSearch.strDuration=="")
commandParameters[11] = new SqlParameter("@intDuration", null );
else
commandParameters[11] = new SqlParameter("@intDuration",
Int16.Parse(udtAppOpenTimeSearch.strDuration));

if(udtAppOpenTimeSearch.AppointmentType=="")
commandParameters[12] = new SqlParameter("@strApptType", null);
else
commandParameters[12] = new SqlParameter("@strApptType",
udtAppOpenTimeSearch.AppointmentType);

if (udtAppOpenTimeSearch.strPagingProvider == "")
commandParameters[13] = new SqlParameter("@strPaging", "");
else
commandParameters[13] = new SqlParameter("@strPaging",
udtAppOpenTimeSearch.dtPagingDate.ToString("yyyyMMdd") +
udtAppOpenTimeSearch.dtPagingTime.ToString("HH:mm:ss") +
udtAppOpenTimeSearch.strPagingProvider.PadRight(4, ' ') +
udtAppOpenTimeSearch.intPagingChair.ToString());

//SqlHelper.FillDataset(Global.GetConnectionString(),CommandType.StoredProcedure,
"GetOpenTime_sp", dsOpenTime, new string[]
{"tblOpenTime"},commandParameters);
using(SqlConnection conn = new
SqlConnection(Global.GetConnectionString()))
{
conn.Open();
using(SqlCommand cmd = new SqlCommand("GetOpenTime_sp", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(cmd);
foreach (SqlParameter p in commandParameters)
cmd.Parameters[p.ParameterName].Value = p.Value;

using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
System.Diagnostics.Debug.WriteLine("Rows: " + da.Fill(dsOpenTime,
"tblOpenTime"));
}
}
conn.Close();
}

if(dsOpenTime.tblOpenTime.Rows.Count>0)
return SUCCESS;
else
return EOF;
}
catch (Exception errAll)
{
throw errAll;
}

Thanks in advance,

Phil
Click to expand...
Click to expand...
Click to expand...
 
Bill,

If you look closely at the code you can see that I derive the parameters and
then just set their values. So they should all be defined with the correct
lengths. The original developer had created them manually. So I just do a
foreach loop after loading the parameters collection. The answer to your
second question is yes, when I do not specify the end date the command is
executed and all the other parameters look normal in the profiler.

C# Code:

SqlCommandBuilder.DeriveParameters(cmd);
foreach (SqlParameter p in commandParameters)
cmd.Parameters[p.ParameterName].Value = p.Value;

Thanks,

Phil
W.G. Ryan MVP said:
Ok, one other thing you may want to check. If you are specifying lengths on
any of the parameters in the proc, make sure you do it client side two and
that they match. Also, when you run a trace, do all of the parameter values
that Sql Server is seeing look ok?
ptaylor said:
Bill,

This was my initial thought too. I did the replace and the problem still
exists. Let me know if you have any other ideas.

Thanks,

Phil

W.G. Ryan MVP said:
One thing sticks out although the symptoms seem to indicate a few things
may
be at play. Let's go for the low hanging fruit first.
Replace each parameter value of null with DBNull.Value and see if that
doesn't ameliorate this. If not, let me know.

Bill
Hello,

I am having a problem with the SqlClient Data Adapter. I originally
noticed
the problem by placing a trace on my sql server and watching which
stored
procedures are being logged.

More specifically the problem that is occurring seems to relate to the
datetime fields that I pass in to my stored procedure as parameters. I
have
found that if I leave the end date parameter as null then the procedure
is
executed. However, if I specify a value for this parameter then the
Fill
method of the data adapter does not appear to execute my command
object.
No
errors are raised the fill command just returns 0 rows and I do not see
the
command executed in my Sql profiler. Below are some excerpts of the
code.

I am running .NET v1.1 runtime w/ SQL Server 2000 database.

C# Code
public long GetOpenTime(string strAccessCode, OpenTimeSearch
udtAppOpenTimeSearch, out OpenTime dsOpenTime)
{
DateTime dtStartDate, dtEndDate;
try
{
dsOpenTime = new OpenTime();
dsOpenTime.EnforceConstraints=false;

SqlParameter[] commandParameters = new SqlParameter[14];
commandParameters[0] = new SqlParameter("@strAccessCd", strAccessCode);

if(udtAppOpenTimeSearch.strProvider=="")
commandParameters[1] = new SqlParameter("@strProviderCd", null);
else
commandParameters[1] = new SqlParameter("@strProviderCd",
udtAppOpenTimeSearch.strProvider);

dtStartDate=udtAppOpenTimeSearch.startDate;
if(dtStartDate==DateTime.MinValue)
commandParameters[2] = new SqlParameter("@dtStartDate", null);
else
commandParameters[2] = new SqlParameter("@dtStartDate", dtStartDate);

dtEndDate=udtAppOpenTimeSearch.endDate;
if(dtEndDate==DateTime.MinValue)
commandParameters[3] = new SqlParameter("@dtEndDate", null);
else
commandParameters[3] = new SqlParameter("@dtEndDate", dtEndDate );

if(udtAppOpenTimeSearch.strMonth=="")
commandParameters[4] = new SqlParameter("@intMonth", null );
else
commandParameters[4] = new SqlParameter("@intMonth",
Int16.Parse(udtAppOpenTimeSearch.strMonth));


if(udtAppOpenTimeSearch.strYear=="")
commandParameters[5] = new SqlParameter("@intYear", null );
else
commandParameters[5] = new SqlParameter("@intYear",
Int16.Parse(udtAppOpenTimeSearch.strYear));

if(udtAppOpenTimeSearch.intAMPM==1)
{
commandParameters[6] = new SqlParameter("@strAM", "Y" );
commandParameters[7] = new SqlParameter("@strPM", null );
}
else if(udtAppOpenTimeSearch.intAMPM==2)
{
commandParameters[6] = new SqlParameter("@strAM", null );
commandParameters[7] = new SqlParameter("@strPM", "Y");
}
else
{
commandParameters[6] = new SqlParameter("@strAM", null );
commandParameters[7] = new SqlParameter("@strPM", null );
}

if(udtAppOpenTimeSearch.strState =="")
commandParameters[8] = new SqlParameter("@strStateCd", null);
else
commandParameters[8] = new SqlParameter("@strStateCd",
udtAppOpenTimeSearch.strState);

if(udtAppOpenTimeSearch.strLocation=="")
commandParameters[9] = new SqlParameter("@strLocation", null);
else
commandParameters[9] = new SqlParameter("@strLocation",
udtAppOpenTimeSearch.strLocation);

if(udtAppOpenTimeSearch.strDay=="")
commandParameters[10] = new SqlParameter("@strDay", null);
else
commandParameters[10] = new SqlParameter("@strDay",
udtAppOpenTimeSearch.strDay);

if(udtAppOpenTimeSearch.strDuration=="")
commandParameters[11] = new SqlParameter("@intDuration", null );
else
commandParameters[11] = new SqlParameter("@intDuration",
Int16.Parse(udtAppOpenTimeSearch.strDuration));

if(udtAppOpenTimeSearch.AppointmentType=="")
commandParameters[12] = new SqlParameter("@strApptType", null);
else
commandParameters[12] = new SqlParameter("@strApptType",
udtAppOpenTimeSearch.AppointmentType);

if (udtAppOpenTimeSearch.strPagingProvider == "")
commandParameters[13] = new SqlParameter("@strPaging", "");
else
commandParameters[13] = new SqlParameter("@strPaging",
udtAppOpenTimeSearch.dtPagingDate.ToString("yyyyMMdd") +
udtAppOpenTimeSearch.dtPagingTime.ToString("HH:mm:ss") +
udtAppOpenTimeSearch.strPagingProvider.PadRight(4, ' ') +
udtAppOpenTimeSearch.intPagingChair.ToString());

//SqlHelper.FillDataset(Global.GetConnectionString(),CommandType.StoredProcedure,
"GetOpenTime_sp", dsOpenTime, new string[]
{"tblOpenTime"},commandParameters);
using(SqlConnection conn = new
SqlConnection(Global.GetConnectionString()))
{
conn.Open();
using(SqlCommand cmd = new SqlCommand("GetOpenTime_sp", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(cmd);
foreach (SqlParameter p in commandParameters)
cmd.Parameters[p.ParameterName].Value = p.Value;

using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
System.Diagnostics.Debug.WriteLine("Rows: " + da.Fill(dsOpenTime,
"tblOpenTime"));
}
}
conn.Close();
}

if(dsOpenTime.tblOpenTime.Rows.Count>0)
return SUCCESS;
else
return EOF;
}
catch (Exception errAll)
{
throw errAll;
}

Thanks in advance,

Phil
Click to expand...
Click to expand...
Click to expand...
 
Thanks for all the help. I am not sure exactly what was happening with this
issue. I build a test project from scratch and executed the same procedure
and got the same resutls.

By chance the issue was fixed when I renamed the parameter from "@dtEndDate"
to "@dtThruDate". This made me think it was something with the name. However,
I have since named it back and everything is still working.

Thanks again,

Phil

ptaylor said:
Hello,

I am having a problem with the SqlClient Data Adapter. I originally noticed
the problem by placing a trace on my sql server and watching which stored
procedures are being logged.

More specifically the problem that is occurring seems to relate to the
datetime fields that I pass in to my stored procedure as parameters. I have
found that if I leave the end date parameter as null then the procedure is
executed. However, if I specify a value for this parameter then the Fill
method of the data adapter does not appear to execute my command object. No
errors are raised the fill command just returns 0 rows and I do not see the
command executed in my Sql profiler. Below are some excerpts of the code.

I am running .NET v1.1 runtime w/ SQL Server 2000 database.

C# Code
public long GetOpenTime(string strAccessCode, OpenTimeSearch
udtAppOpenTimeSearch, out OpenTime dsOpenTime)
{
DateTime dtStartDate, dtEndDate;
try
{
dsOpenTime = new OpenTime();
dsOpenTime.EnforceConstraints=false;

SqlParameter[] commandParameters = new SqlParameter[14];
commandParameters[0] = new SqlParameter("@strAccessCd", strAccessCode);

if(udtAppOpenTimeSearch.strProvider=="")
commandParameters[1] = new SqlParameter("@strProviderCd", null);
else
commandParameters[1] = new SqlParameter("@strProviderCd",
udtAppOpenTimeSearch.strProvider);

dtStartDate=udtAppOpenTimeSearch.startDate;
if(dtStartDate==DateTime.MinValue)
commandParameters[2] = new SqlParameter("@dtStartDate", null);
else
commandParameters[2] = new SqlParameter("@dtStartDate", dtStartDate);

dtEndDate=udtAppOpenTimeSearch.endDate;
if(dtEndDate==DateTime.MinValue)
commandParameters[3] = new SqlParameter("@dtEndDate", null);
else
commandParameters[3] = new SqlParameter("@dtEndDate", dtEndDate );

if(udtAppOpenTimeSearch.strMonth=="")
commandParameters[4] = new SqlParameter("@intMonth", null );
else
commandParameters[4] = new SqlParameter("@intMonth",
Int16.Parse(udtAppOpenTimeSearch.strMonth));


if(udtAppOpenTimeSearch.strYear=="")
commandParameters[5] = new SqlParameter("@intYear", null );
else
commandParameters[5] = new SqlParameter("@intYear",
Int16.Parse(udtAppOpenTimeSearch.strYear));

if(udtAppOpenTimeSearch.intAMPM==1)
{
commandParameters[6] = new SqlParameter("@strAM", "Y" );
commandParameters[7] = new SqlParameter("@strPM", null );
}
else if(udtAppOpenTimeSearch.intAMPM==2)
{
commandParameters[6] = new SqlParameter("@strAM", null );
commandParameters[7] = new SqlParameter("@strPM", "Y");
}
else
{
commandParameters[6] = new SqlParameter("@strAM", null );
commandParameters[7] = new SqlParameter("@strPM", null );
}

if(udtAppOpenTimeSearch.strState =="")
commandParameters[8] = new SqlParameter("@strStateCd", null);
else
commandParameters[8] = new SqlParameter("@strStateCd",
udtAppOpenTimeSearch.strState);

if(udtAppOpenTimeSearch.strLocation=="")
commandParameters[9] = new SqlParameter("@strLocation", null);
else
commandParameters[9] = new SqlParameter("@strLocation",
udtAppOpenTimeSearch.strLocation);

if(udtAppOpenTimeSearch.strDay=="")
commandParameters[10] = new SqlParameter("@strDay", null);
else
commandParameters[10] = new SqlParameter("@strDay",
udtAppOpenTimeSearch.strDay);

if(udtAppOpenTimeSearch.strDuration=="")
commandParameters[11] = new SqlParameter("@intDuration", null );
else
commandParameters[11] = new SqlParameter("@intDuration",
Int16.Parse(udtAppOpenTimeSearch.strDuration));

if(udtAppOpenTimeSearch.AppointmentType=="")
commandParameters[12] = new SqlParameter("@strApptType", null);
else
commandParameters[12] = new SqlParameter("@strApptType",
udtAppOpenTimeSearch.AppointmentType);

if (udtAppOpenTimeSearch.strPagingProvider == "")
commandParameters[13] = new SqlParameter("@strPaging", "");
else
commandParameters[13] = new SqlParameter("@strPaging",
udtAppOpenTimeSearch.dtPagingDate.ToString("yyyyMMdd") +
udtAppOpenTimeSearch.dtPagingTime.ToString("HH:mm:ss") +
udtAppOpenTimeSearch.strPagingProvider.PadRight(4, ' ') +
udtAppOpenTimeSearch.intPagingChair.ToString());

//SqlHelper.FillDataset(Global.GetConnectionString(),CommandType.StoredProcedure,
"GetOpenTime_sp", dsOpenTime, new string[] {"tblOpenTime"},commandParameters);
using(SqlConnection conn = new
SqlConnection(Global.GetConnectionString()))
{
conn.Open();
using(SqlCommand cmd = new SqlCommand("GetOpenTime_sp", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(cmd);
foreach (SqlParameter p in commandParameters)
cmd.Parameters[p.ParameterName].Value = p.Value;

using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
System.Diagnostics.Debug.WriteLine("Rows: " + da.Fill(dsOpenTime,
"tblOpenTime"));
}
}
conn.Close();
}

if(dsOpenTime.tblOpenTime.Rows.Count>0)
return SUCCESS;
else
return EOF;
}
catch (Exception errAll)
{
throw errAll;
}

Thanks in advance,

Phil
Click to expand...
 
Back
Top