Issue in crystal reports with SQL

Question

0.00/5 (No votes)

See more:

, +

im facing issue in crystal reports while using sql statement .

SQL

select Top 1 openingbalance,dated
FROM ledgerbalance 
where accounttitle= '{?tran}' and  dated='{?dateb}'
order by ledgeridnew asc, dated desc;

im getting error

Failed to retrive data from database.
details: ado error code: 0x
sourcemicrosoft sql server native client 10.0
descriptionincorrect syntax near 02
sql state:42000
native error: [database vendor code:102]

What I have tried:

select Top 1 openingbalance,dated
FROM ledgerbalance 
where accounttitle= '{?tran}' and  dated='{?dateb}'
order by ledgeridnew asc, dated desc;

Posted 24-Feb-20 22:20pm

Member 12654313

Updated 27-Feb-20 1:18am

Add a Solution

Comments

ZurdoDev 25-Feb-20 8:03am

You need to debug it and see what the actual values are in your parameters. It appears they are not what you think they are.

Member 12654313 25-Feb-20 8:34am

parameters are correct
one is tran and other is dateb

ZurdoDev 25-Feb-20 8:36am

Yes, but the values being passed in are probably not what you are expecting, causing a syntax error.

Member 12654313 25-Feb-20 8:49am

actually tran and dateb im using to get data from ledger database ...tran is transection id and date is entry date. now if i use only
where accounttitle='{tran}' then it working fine but if i use
where accounttitle='{tran}' and dated='{dateb}'
then it give error

ZurdoDev 25-Feb-20 8:57am

Then the value that is being put into dateb is not a date. That is why you get a syntax error. Syntax error means the sql you are sending is not valid.

All you have to do is debug it and see what the actual sql is and you'll see the problem right away. But we can't run your code for you so I can't tell you what the problem is with the date.

Maciej Los 26-Feb-20 3:34am

Are you sure that ['] sign is necessary around the date parameter?
As far as i remember, it's not.

Member 12654313 27-Feb-20 2:21am

i remove it and tried but still same issue

2 solutions

Solution 1

There's something wrong with your sql query or crystal report datasource.
First, check the datasource for crystal report. If necessary, re-bind it.
Second, check the sql statement. Change your input parameters to constant values and check out if it it helps. If yes, change constant value one by one. I'd start with {?tran}. If crystal report will display data without errors, then change constant value to parameter for {?dateb}. If you'll get an error message, then that means that date parameter is passed with wrong format.
See: SET DATEFORMAT (Transact-SQL) - SQL Server | Microsoft Docs[^] for further details.

Posted 26-Feb-20 20:52pm

Maciej Los

Updated 26-Feb-20 20:53pm

v2

Comments

Member 12654313 27-Feb-20 4:12am

hi maciej.
how r u
thank you for your reply. actually i tried with {?tran} and it working fine but when i tried with date, it give error. in database date format is 2020-02-01 and in crystal reports its 01-02-2020 ... how can i fix it ?

Maciej Los 27-Feb-20 4:25am

Change date format while passing argument to the query ;)
For example:

DateTime dateb = new DateTime(2020, 2, 1);
myReport.SetParameterValue("dateb", dateb.ToString("yyyy-MM-dd"));

Member 12654313 27-Feb-20 4:38am

ok so can i write query like below in crystal reports?

select Top 1 openingbalance,dated
FROM ledgerbalance
where accounttitle='{?tran} and dated={?dateb}
order by ledgeridnew asc, dated desc;

DateTime dateb = new DateTime(2020, 2, 1);
myReport.SetParameterValue("dateb", dateb.ToString("yyyy-MM-dd"));

Maciej Los 27-Feb-20 4:44am

This part:

DateTime dateb = new DateTime(2020, 2, 1);
myReport.SetParameterValue("dateb", dateb.ToString("yyyy-MM-dd"));

is not a query!
This is a code which is responsible for passing parameter to Crystal Report.

Member 12654313 27-Feb-20 5:00am

really sorry to ask again . do i have to use it in parameter's format part?like right click the parameter and in formula? or in vb.net

Maciej Los 27-Feb-20 5:13am

vb.net (i'm using c#, so you have to convert it into vb.net).

Member 12654313 27-Feb-20 5:31am

alright i will check it

Member 12654313 27-Feb-20 7:18am

issue resolved
i fix it by doing changes in below code

select top 1 dated,openingbalance
from ledgerbalance
where dated=({?dateb}) and accounttitle='{?tran}'
order by ledgeridnew desc

date must be ({?dateb}) in this format instead of {?dateb} and tran has to be '{?tran}'

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 12654313 · Accepted Answer · 2020-02-27T01:19:00

Solution 2

issue resolved
i fix it by doing changes in below code

select top 1 dated,openingbalance
from ledgerbalance
where dated=({?dateb}) and accounttitle='{?tran}'
order by ledgeridnew desc

date must be ({?dateb}) in this format instead of {?dateb} and tran has to be '{?tran}'

Posted 27-Feb-20 1:19am

Member 12654313