Re: SqlClient Data Adapter Fill Method
- From: "ptaylor" <ptaylor@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 9 Aug 2005 14:39:13 -0700
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" wrote:
> 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" <ptaylor@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:24E763B4-9CAF-4665-9667-7E673D0BDDF0@xxxxxxxxxxxxxxxx
> > 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" wrote:
> >
> >> 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" <ptaylor@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> news:4F4E15E8-7C62-476E-8A0D-C858068421A7@xxxxxxxxxxxxxxxx
> >> > 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
> >> >
> >>
> >>
> >>
>
>
>
.
- References:
- SqlClient Data Adapter Fill Method
- From: ptaylor
- Re: SqlClient Data Adapter Fill Method
- From: W.G. Ryan MVP
- Re: SqlClient Data Adapter Fill Method
- From: ptaylor
- Re: SqlClient Data Adapter Fill Method
- From: W.G. Ryan MVP
- SqlClient Data Adapter Fill Method
- Prev by Date: Dataset column Names
- Next by Date: Re: Dataset column Names
- Previous by thread: Re: SqlClient Data Adapter Fill Method
- Next by thread: RE: SqlClient Data Adapter Fill Method
- Index(es):
Relevant Pages
|
