.Net passing bad formatted value to stored procedure

Hi, that's the problem:

I have a GridView, bound to a SQLDataSource, with an stored procedure as a Select query. The Select Parameters are bound to controls in the web form, acting like some filter fields.

When I submit the page, everythings works fine, except when I try to set some value in the DateTime fields. .Net is enclosing the date with extra single quotes, as I could see in the Profiler:

exec sel_despesa_procura @.codigo=NULL,@.fornecedor=NULL,@.descricao=NULL,@.vencto_ini=''2005-10-10 00:00:00:000'',@.vencto_fim=''2005-10-20
00:00:00:000'',@.pagto_ini=NULL,@.pagto_fim=NULL,@.valor=NULL,@.valor_pago=NULL,
@.centro_custo=NULL,@.pago=N'0,1'

The fields are defined as follows:

<SelectParameters>
...
<asp:ControlParameterControlID="txtFiltroVencIni"Name="vencto_ini"PropertyName="Text"Type="DateTime"/>
<asp:ControlParameterControlID="txtFiltroVencFim"Name="vencto_fim"PropertyName="Text"Type="DateTime"/>
...
</SelectParameters>

The stored procedure doesn't even execute, due to the bad formatted arguments. It returns the error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '2005'.

I'm going to change the parameter type to varchar, as a workaround, but I'd like to solve this problem.

Thanks in advance,

Anderson

That is not it's normal behavior. Just for laughs, set the pages culture and uiculture to "en-US", and run the page again and see if the problem disappears. If it does, then it's a bug with your particular culture settings.

*Passing parameters to an SP*

Hi
I need to embed Reports into an aspx page.
So I need to send parameters to the Report from the UI (aspx page).
I can do this using parameterized reports but I want to pass the parameters from the aspx page and not the report body .
Can anyone suggest how to do that.
Moreover in my application I have a Data Access Layer which is to be used to call the SPs. We dont call the SP directly. Then this Data Access layer returns the dataset.
Is it possible to integrate Reporting Services in such an architecture. I want this layer to get values from the Database otherwise I see calling the SP directly as the only option.
Thanks in advance
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.In Brian Larson's book there is an example of how to use aspx pages to call
RS. (Chapter 12)
http://www.osborne.com/products/0072232161/0072232161_code.zip
Regards,
Cem
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:eFyl4etWEHA.3476@.tk2msftngp13.phx.gbl...
> Hi
> I need to embed Reports into an aspx page.
> So I need to send parameters to the Report from the UI (aspx page).
> I can do this using parameterized reports but I want to pass the
parameters from the aspx page and not the report body .
> Can anyone suggest how to do that.
> Moreover in my application I have a Data Access Layer which is to be used
to call the SPs. We dont call the SP directly. Then this Data Access layer
returns the dataset.
> Is it possible to integrate Reporting Services in such an architecture. I
want this layer to get values from the Database otherwise I see calling the
SP directly as the only option.
> Thanks in advance
>
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.|||Cem Demircioglu wrote:
> In Brian Larson's book there is an example of how to use aspx pages
> to call RS. (Chapter 12)
> http://www.osborne.com/products/0072232161/0072232161_code.zip
> Regards,
> Cem
IMHO the sample code in ReportFrontEnd.aspx.vb has a minor error. In my
experience the call to
HttpContext.Current.Response.End()
within a try-catch block will _always_ end up in an exception of the kind
"the thread has been interrupted" or so.
Instead of coding
...
' Send the byte array containing the report as a binary response.
HttpContext.Current.Response.BinaryWrite(report)
HttpContext.Current.Response.End()
Catch ex As Exception
HttpContext.Current.Response.ClearHeaders()
HttpContext.Current.Response.ClearContent()
HttpContext.Current.Response.ContentType = "text/html"
HttpContext.Current.Response.Write("&

---

Error"
& ex.Message & "

---

")
HttpContext.Current.Response.End()
End Try
one should use
' Send the byte array containing the report as a binary response.
HttpContext.Current.Response.BinaryWrite(report)
Catch ex As Exception
HttpContext.Current.Response.ClearHeaders()
HttpContext.Current.Response.ClearContent()
HttpContext.Current.Response.ContentType = "text/html"
HttpContext.Current.Response.Write("<HTML><BODY><H1>Error</H1><br><br>"
& ex.Message & "</BODY></HTML>")
End Try
HttpContext.Current.Response.End()
... means keep the HttpContext.Current.Response.End() out of a try-catch
block.
roland

* * * Passing Parameter - Urgent * * *

I program an ASP.NET application that uses SQL Server 2005 Reporting
Services.
Recently I came into a dead end when I had to pass a parameter from my
application
to the report. I 've looked over the Internet and found absolutely nothing.
If someone is
capable of doing that he/she doesn't have to be shy to share it with me.
Thanks in advance,
Nick Hatzis
P.S. : To answer by e-mail remove the "bog" from the username.Hi,
What kind of parameter you must pass?|||Hi Cesar,
I have to pass four string parameters.
Two that will appear in the report and two
(from-to) that will be used in the query.
Thanks in advance,
Nick Hatzis
P.S. : To answer by e-mail remove the "bog" from the username.
"Cesar" <Cesar@.discussions.microsoft.com> wrote in message
news:490E74EF-519C-4D5E-BB69-C8412CB909C2@.microsoft.com...
> Hi,
> What kind of parameter you must pass?|||Are you using an URL to open the report? In that case, you should look at
the topic "URL access" in the SQL Server 2005 Books Online.
If you have it locally, this should be the URL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsprg9/html/52c3f2a3-3d6d-4fee-9c46-83f366919398.htm
Or find it online at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_urlaccess_374y.asp
Kaisa M. Lindahl Lervik
"Nick Hatzis" <boghatzisn@.panafonet.gr> wrote in message
news:%23MK70oQ7GHA.1012@.TK2MSFTNGP05.phx.gbl...
>I program an ASP.NET application that uses SQL Server 2005 Reporting
> Services.
> Recently I came into a dead end when I had to pass a parameter from my
> application
> to the report. I 've looked over the Internet and found absolutely
> nothing.
> If someone is
> capable of doing that he/she doesn't have to be shy to share it with me.
> Thanks in advance,
> Nick Hatzis
> P.S. : To answer by e-mail remove the "bog" from the username.
>|||On Wed, 11 Oct 2006 11:01:52 +0300, "Nick Hatzis"
<boghatzisn@.panafonet.gr> wrote:
>I program an ASP.NET application that uses SQL Server 2005 Reporting
>Services.
>Recently I came into a dead end when I had to pass a parameter from my
>application
>to the report. I 've looked over the Internet and found absolutely nothing.
>If someone is
>capable of doing that he/she doesn't have to be shy to share it with me.
>Thanks in advance,
>Nick Hatzis
>P.S. : To answer by e-mail remove the "bog" from the username.
>
Are you using reportviewer or just going direct to the URL? If direct
to the URL, something like this:
Response.Redirect("http://199.199.199.199/ReportServer?%2fReportProjectName%2fReportName&rs:Command=Render&rs:Format=PDF&IssueID=11000")
That IssueID at the end is a parameter. Note the rs:Format that gets
the report directly as a PDF.
If you're using a reportviewer control, parameters are done like this:
Dim RptParm As New
Microsoft.Reporting.WebForms.ReportParameter("FileNumParam",
lblFileNum.Text) ' name/value pair for a parameter
Dim RptParms As New Generic.List(Of
Microsoft.Reporting.WebForms.ReportParameter)
RptParms.Add(RptParm)
' add more parameters as necessary to RptParams
Reportviewer1.ServerReport.SetParameters(RptParms)