SqlClient Data Adapter Fill Method
- From: "ptaylor" <ptaylor@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 9 Aug 2005 06:46:38 -0700
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
.
- Follow-Ups:
- RE: SqlClient Data Adapter Fill Method
- From: ptaylor
- Re: SqlClient Data Adapter Fill Method
- From: W.G. Ryan MVP
- RE: SqlClient Data Adapter Fill Method
- Prev by Date: Re: Microsoft Application Blocks Data Modification
- Next by Date: Re: Join Tables in DataSet
- Previous by thread: rowstate management
- Next by thread: Re: SqlClient Data Adapter Fill Method
- Index(es):
Relevant Pages
|
