SQLServer stored procedure retrieves no lines when executed from my VB.Net App, but retrieves lines in SQLServer

Question

4.00/5 (1 vote)

See more:

SQL-Server-2008R2

Hello,

I've got a little problem with a SQLDataAdapter in VB.Net

Here's my code, working fine most of time :

VB

v_rrqAdapteur = New SqlDataAdapter(a_strRequete, m_cnxSQL)
v_rrqAdapteur.SelectCommand.CommandTimeout = 900
If o_rrqTable Is Nothing Then
    o_rrqTable = New DataTable
End If
v_rrqAdapteur.Fill(o_rrqTable)
v_rrqAdapteur.Dispose()

It works also when the query is the result of SQL Stored procedure

SQL

exec dbo.[DocumentFiltresOptime] N'Doc.TypeDocument in (2)';

but where in the argument of the procedure I use a condition of date

SQL

exec dbo.[DocumentFiltresOptime] N'Doc.TypeDocument in (2) and (Doc.DateCreation > ''2015/04/09'')';

The query return the attempted result when I execute it in SQLServerManager, but it return no lines when it's executed by my VB.Net App.

I don't understand why.

Could someone explain le the reason of this mystery ?

Thanks a lot for those who'll help.

Posted 19-Apr-15 22:17pm

Member 11621437

Add a Solution

Comments

Solai Raja 20-Apr-15 5:17am

is this procedure returning the Results in Execution of Procedure in SQL Server?

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 20-Apr-15 7:19am

What is the value of "a_strRequete"?

Member 11621437 20-Apr-15 7:46am

Value of the query is :
Hide Copy Code

exec dbo.[DocumentFiltresOptime] N'Doc.TypeDocument in (2) and (Doc.DateCreation > ''2015/04/09'')';

The SQL stored procedure accept as argument a filter as a nvarchar(256)
The filter is : N'Doc.TypeDocument in (2) and (Doc.DateCreation > ''2015/04/09'')'

The problem appears only when I use dates in the filter, never when I use simple strings

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 20-Apr-15 8:05am

Have you set CommandType to StoredProcedure?

Member 11621437 20-Apr-15 8:14am

No.

Is it absolutely essential ?

Member 11621437 20-Apr-15 8:49am

It doesn't work at all when I set the CommandType to StoredProcedure

Member 11621437 20-Apr-15 8:50am

Value of the query is :
<pre lang="SQL">
exec dbo.[DocumentFiltresOptime] N'Doc.TypeDocument in (2) and (Doc.DateCreation > ''2015/04/09'')';
</pre>

The SQL stored procedure accept as argument a filter as a nvarchar(256)
The filter is : N'Doc.TypeDocument in (2) and (Doc.DateCreation > ''2015/04/09'')'

The problem appears only when I use dates in the filter, never when I use simple strings

Member 11621437 20-Apr-15 8:51am

And, like I'vealready said, no query works when CommandType is set to "StoredProcedure"

2 solutions

Solution 3

You don't need to set the command type to stored procedure because your query already contains a 'EXEC' . If the same query is returning values when executing from SQL management studio, then the problem is with the date format try using date format with MMM, for example

exec dbo.[DocumentFiltresOptime] N'Doc.TypeDocument in (2) and (Doc.DateCreation > ''2015/Apr/09'')';

Posted 20-Apr-15 19:44pm

Abdul Samad KP

Comments

Member 11621437 21-Apr-15 2:39am

This works fine when directly executed in SQLServerManager, but always not from my VB.Net App. But I think your solution is the right way to resolve my problem. I my VB.Net Code, the line when the SQLAdapter fill the DataTable throws an exception for 'Invalid Date Format'. I have the local culture set to French in VB.Net an set to English in SQLServer. That's why I first try to give the dates to SQLServer only in numeric format

Member 11621437 21-Apr-15 2:47am

I'll try to use the SQLServer "Convert" function

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 11621437 · Accepted Answer · 2015-04-20T20:59:00

I've got the solution : use the "convert" SQL function, which allows me to specify the right date format to use.

SQL

exec SquarBTP_2012.dbo.[DocumentFiltresOptime] N'Doc.TypeDocument in (2) and (Doc.DateCreation > CONVERT(date, ''09/04/2015'', 103))';

Always use this function to give dates to SQL, there will be any problem of format.

Thanks to M.Samad for his good ideas