Re: SqlClient Data Adapter Fill Method

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
>> >
>>
>>
>>


.

Relevant Pages

  • Execute Process Task not failing, but not executing the batch comm
    ... I can execute the following command from the windows "Run" prompt and it ... Might I have something set weird in SQL Server? ... server being by default configured to run as localsystem account, ...
    (microsoft.public.sqlserver.dts)
  • Re: ADO.NET Timout
    ... That in turn execute SP ... BeginExecute method of the Command object. ... How can I call a stored procedure to do its thing asynchronously. ... see "Explicit Transactions" in SQL Server Books ...
    (microsoft.public.dotnet.framework.adonet)
  • SqlClient Data Adapter Fill Method
    ... I am having a problem with the SqlClient Data Adapter. ... the problem by placing a trace on my sql server and watching which stored ... method of the data adapter does not appear to execute my command object. ...
    (microsoft.public.dotnet.framework.adonet)
  • 100% cpu usage
    ... Access 2002 adp connected to SQL server, ... When I execute any command through ADO ... 6380 Set cmd6 = New ADODB.Command ...
    (microsoft.public.access.adp.sqlserver)
  • [Full-Disclosure] Advanced usage of system() function.
    ... and call its arguments as a command for shell. ... as we can see we still didnt get what we want (typing exit ... Connection closed by foreign host. ... think what we want to execute. ...
    (Full-Disclosure)
Loading