get the latest receiving price

Question

1.00/5 (2 votes)

See more:

C#

private void GetLastReceivingPrice()
        {
            sqlCon.Open();
            strSQL = "select top 1 receiving from Delivery order by date desc where description=@desc and dosage=@dosage";
            SqlCommand cmd = new SqlCommand(strSQL, sqlCon);
            cmd.Parameters.AddWithValue("@desc", txtItemDescription.Text);
            cmd.Parameters.AddWithValue("@dosage", txtDosage.Text);
            txtPrevious.Text = cmd.ExecuteScalar().ToString();
            sqlCon.Close();
        }

ERROR IS " INCORRECT SYNTAX NEAR THE KEYWORD "WHERE"

I AM RETRIEVING THE LATEST PRICE RECEIVED FROM A SUPPLIER ORDER BY DATE DESCENDING

TO BE DISPLAYED IN TXTPREVIOUS.TEXT

THANKS IN ADVANCE

Posted 9-May-14 4:27am

Member 10717094

Add a Solution

Comments

[no name] 9-May-14 11:02am

Why are you screaming at us? "Date" is a reserved word.

Andrius Leonavicius 9-May-14 12:43pm

Also, please read this:
http://www.hoax-slayer.com/do-not-use-all-capitals.html

3 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)

Andrius Leonavicius · Accepted Answer · 2014-05-09T05:21:00

Solution 3

Hi,

I can see two problems in your code:
1. As Wes Aday said, date is a reserved word. Put it in the square brackets: [date].
2. ORDER BY clause must be after the WHERE clause.

So try changing this:

SQL

strSQL = "select top 1 receiving from Delivery order by date desc where description=@desc and dosage=@dosage";

with this:

SQL

strSQL = "select top 1 receiving from Delivery where description = @desc and dosage = @dosage order by [date] desc";

Posted 9-May-14 5:21am

Andrius Leonavicius

Comments

Andrius Leonavicius 9-May-14 12:33pm

Also, be careful with ExecuteScalar().ToString(). The ExecuteScalar() method can return null and your code would fail here.

For instance, change this:
cmd.ExecuteScalar().ToString()
with this (null values are handled):
Convert.ToString(cmd.ExecuteScalar())

Member 10717094 9-May-14 19:32pm

Thanks a lot Andrius

Andrius Leonavicius 9-May-14 19:34pm

You're welcome.

SteveyJDay · Accepted Answer · 2014-05-09T05:16:00

Solution 1

Your SQL is incorrect. The order by needs to be after the where clause.

SQL

select top 1 receiving 
from Delivery 
where description=@desc 
and dosage=@dosage
order by date desc

Posted 9-May-14 5:16am

SteveyJDay

José Amílcar Casimiro · Accepted Answer · 2014-05-09T05:17:00

C#

private void GetLastReceivingPrice()
{
    sqlCon.Open();
    strSQL = "select top 1 receiving from Delivery where description=@desc and dosage=@dosage order by date desc ";
    SqlCommand cmd = new SqlCommand(strSQL, sqlCon);
    cmd.Parameters.AddWithValue("@desc", txtItemDescription.Text);
    cmd.Parameters.AddWithValue("@dosage", txtDosage.Text);
    txtPrevious.Text = cmd.ExecuteScalar().ToString();
    sqlCon.Close();
}

JAFC