Show-Only-Most-Recent-Date-Fro...

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

cmd.CommandText = "select facno,max(fdate) as fdate,fbalance from MAINTRAN wheere fdate<='" & Format(prdate, "yyyy/MM/dd") & "' GROUP BY facno "

Posted 24-Nov-14 22:09pm

lakshjoshi

Add a Solution

Comments

/\jmot 25-Nov-14 4:18am

not clear.
be more specific.

Sinisa Hajnal 25-Nov-14 4:25am

What do you need to do? What is the problem?

Tomas Takac 25-Nov-14 4:48am

My guess is it fails because fbalance is neither aggregate nor in group by. What you most probably want is a sum: sum(fbalance) as totalfbalance

lakshjoshi 25-Nov-14 6:14am

yes it is thanks for reply you saved lots of time,i am working on this 2 days...where are you man before 2 days...:)..thanks here goes my working query....
"select facno,max(fdate) as fdate ,max(fbalance) as fbalance from MAINTRAN where MAINTRAN.fbankcode='" & "010" & "'" & _
"and MAINTRAN.fdate<='" & Format(prdate, "MM/dd/yyyy") & "' " & _
"and MAINTRAN.fbranchcode='" & "01" & "' group by facno"

Shweta N Mishra 25-Nov-14 5:21am

You are asking same question again and again why dont you clear it out in one thread only, It Makes other to rework same solution for you again and again.

lakshjoshi 25-Nov-14 5:50am

if you facing problem with me inform to blog close this discussion.

Shweta N Mishra 25-Nov-14 5:58am

Dear everyone is here to help each other, But if you will post same question again and again in different way, Many people is going to start giving the same answer which you have got earlier(like the one you have below, which was answered by King fisher earlier), This way you are not getting your solution and others time is getting wasted,if you ask same think in your earlier thread it will save other time and you may also get your solution.i hope you understand this .

lakshjoshi 25-Nov-14 6:17am

thank you mishra ... my code works...enjoy...i hope never see you again..

2 solutions

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)

OriginalGriff · Answer 1 · 2014-11-24T23:16:00

There are a couple of things here: there is the fbalance problem mentioned by Tomas Takac in the comments above, the spelling of "wheere", and the use of concatenation.

Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead. This is a safe use, but if you are doing it here, the chances are you do it elsewhere and leave your database wide open to attack.
Try this:

VB

cmd.CommandText = "SELECT facno, MAX(fdate) AS fdate, SUM(fbalance) AS totalfbalance FROM MainTran WHERE fdate<=@DT GROUP BY facno"
cmd.Parameters.AddWithValue("@DT", prdate.Date)

lakshjoshi · Answer 2 · 2014-11-25T00:16:00

thanks its works ...closing cermany:here my working query:----

SQL

"select facno,max(fdate) as fdate ,max(fbalance) as fbalance from MAINTRAN where MAINTRAN.fbankcode='" & "010" & "'" & _
                                          "and MAINTRAN.fdate<='" & Format(prdate, "MM/dd/yyyy") & "' " & _
                                          "and MAINTRAN.fbranchcode='" & "01" & "' group by facno"

thank god its works.....

Show-Only-Most-Recent-Date-Fro...

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0