Showing posts with label passing. Show all posts
Showing posts with label passing. Show all posts

Monday, March 12, 2012

Mysterious Date Problem

Is there a way to either set Sql Server 2000 or ASP.net datetime fields to a standard format. The problem is that I am passing correct datetime fields using stored procedures and keep getting "Cannot convert datetime into string". It seems to me that many other developers are having that same problem. I tried alot of different methods and still have the same problem. I'm using c# and I never had a problem with datetime fields when I was using vb.net. The problem is that SQl Server is returning datetime formats that are not compatible with c#. I have code that works in other projects but when I try to use that same code I get that conversion error. How do I set the datetime in SQl Server and ASP.Net when I run queries so that the datetime output is in mm/dd/yyyy?What is the statement where you get the error "Cannot convert datetime into string"|||well anytime I return a dataset and bind it to a datagrid I get a conversion error. Sometimes I even got 'The conversion of a char data type to a datetime data type resulted in an out-of-range'. When I ran a simple query in Query Analyzer like "select convert (datetime,'12/20/2003',101)" the output returns 2003-12-20 00:00:00.000
What I need is to be mm/dd/yyyy. Is there a way to change the default setting in Sql Server to that particular format instead of yyyy-mm-dd. Because I think C# is using mm-dd-yyyy as a default. In the asp net datagrid all I'm doing it just binding the dataset to the grid and I get that error? Thanks for your reply...|||you should do the formatting at the front end...for instance when you bind the result set to the datagrid...you can use the dataformatstring property of the datagrid columns to format it to mm/dd/yyyy format which is much simpler rather than enforcing the sql server to some other format..

hth|||Yah, I used that format and that is not the problem. The problem is that I can't even bind the dataset to the datagrid. Its in the binding that is causing the error. But thanks anyway.|||What is the code that you are using??|||basically I just call pass parameters to a method and it returns a dataset

datagrid.DataSource = ds;
datagrid.DataBind();

public DataSet GetOriginationReport( DateTime dtStartDate, DateTime dtEndDate)
{
// instantiate connection and command objects
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connString2"]);
SqlDataAdapter myCommand = new SqlDataAdapter("rptSelectReport", myConnection);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;

myCommand.SelectCommand.Parameters.Add("@.pStartDate", SqlDbType.DateTime );
myCommand.SelectCommand.Parameters.Add("@.pEndDate", SqlDbType.DateTime );

myCommand.SelectCommand.Parameters["@.pStartDate"].Value = dtStartDate;
myCommand.SelectCommand.Parameters["@.pEndDate"].Value = dtEndDate;

DataSet myDS = new DataSet();
myCommand.Fill(myDS,"viewReportPipe");
return myDS;
}

===============================
Stored Procedure
===============================
CREATE PROCEDURE rptSelectReport
(
@.pStartDate DateTime,
@.pEndDate DateTime,
)
As

Select * from viewReportPipe Where AppDate Between @.pStartDate And @.pEndDate|||What result do u get when u run this query in Sql analyzer??
Select * from viewReportPipe Where AppDate Between @.pStartDate And @.pEndDate|||well I get a list of results. The problem is occuring in the binding. When I run the query and bind it to a datagrid it works. But I have sorting enable in the datagrid and when I run that same query and bind it to the grid it fails and gives me that "Cannot convert datetime to string" error. The datefield in the table is "AppDate" and is a datetime datatype and is not null. I know Sequel stores dates internally and is different then what is displayed. I have dates 01/01/1900 as default dates. I just discover that sequel uses that same date as a default date. To me its seems like Sequel is switching datetime formats and C# is not recognizing it as a valid datetime. Thanks for you quick response...|||Also should I changed my select statement and convert the date in the stored procedure
like "select AppDate = convert(datetime,AppDate) from ...|||What value does dtStartDate have??|||dtStartDate has 12/01/2003 12:00:00 AM

and the dtEndDate has 12/31/2003 12:00:00 AM|||I am not sure why you get error??
Cannot convert datetime to string

It must be probably your sorting procedure handling some parameter in wrong manner|||its funny because that what I originally though. I was able to sort by a few columns and it works. But some columns would throw that conversion error. I know I am sorting by the right field name but for some reason and I don't why but Sequel is switching datetime formats. That leads me to my original question that Sequel is returning the recordset as yyyy-mm-dd and and is there a way to change it to mm-dd-yyyy. I notice that other developers are experience the same problem and the answer seems to be that SQl Server and asp.net application have to have the same regional setting(datetime). But I haven't found an answer to change the setting in SQL Server or asp.net application. I know how to change the settings on the server but that doesn't help.|||Post your Sorting code...

Monday, February 20, 2012

My Own - Multi Select Parameter Passing Implementation

Hi,
I would like to share how I implemented in my application by passing multi
select parameters into the report.
1. In my .net file, I have created a list box which I load all the multi
values that the user wants to report on(for Ex: Equipment Id = 100,200,300,400 and so on).
2. When the user submits the page, I load the above list into a table.
3. In my stored procedure, I go after this table and build the SELECT
statement.
4. Since the report has to refreshed everytime they add another value into
the list, the report has to know that it has to refresh. For this what I have
done is on the table that i load all the values, I extract the SEQUENCE # and
pass it to the report(it is a unique # everytime when you refresh the page).
U can also pass a Random # but the uniqueness is not guaranteed and the
report may not refresh.
In the Report Designer:
1. I create a parameter called RefreshReport. This value that gets passed
into the parameter is the SEQUENCE value.
I poured my head into 3 books for some clue and couldn't find one hint.
I will be more than happy to answer any questions regarding this
implementation..hi, can you be more detailing? I've been following a guide from a book,
there's this query:
="SELECT * FROM vProductProfitability WHERE (Year = @.Year) AND (Mon
thNumberOfYear = @.Month) AND (Category IN (â'+Parameters!CategoryStrin
g.Value+â'))â'
yes, like what you said, you created a new parameter, in here, the the param
is category string, I dn't know where to put this query since i'm using a
stored procedure.. the only way we can use this query is by using text, so
about your stored procedure that you changed.. what is it?
what i can still select is 1 item, although i'm using checkboxlist, i can
select all of them, but w/ an error of course.
can you please discuss how you manage to enable multi select, since you've
posted a helping memo in here already.. advance thanks!
--
~SiMPLe~
"Suresh" wrote:
> Hi,
> I would like to share how I implemented in my application by passing multi
> select parameters into the report.
> 1. In my .net file, I have created a list box which I load all the multi
> values that the user wants to report on(for Ex: Equipment Id => 100,200,300,400 and so on).
> 2. When the user submits the page, I load the above list into a table.
> 3. In my stored procedure, I go after this table and build the SELECT
> statement.
> 4. Since the report has to refreshed everytime they add another value into
> the list, the report has to know that it has to refresh. For this what I have
> done is on the table that i load all the values, I extract the SEQUENCE # and
> pass it to the report(it is a unique # everytime when you refresh the page).
> U can also pass a Random # but the uniqueness is not guaranteed and the
> report may not refresh.
> In the Report Designer:
> 1. I create a parameter called RefreshReport. This value that gets passed
> into the parameter is the SEQUENCE value.
> I poured my head into 3 books for some clue and couldn't find one hint.
> I will be more than happy to answer any questions regarding this
> implementation..