Showing posts with label sqldatasource. Show all posts
Showing posts with label sqldatasource. Show all posts

Thursday, March 8, 2012

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