SQL again! It just doesn't work

Question

4.00/5 (1 vote)

See more:

SQL-Server-2005

Hi,

I am having a problem reading data from SQL database.

The syntax is like such:

SELECT Max(Property_ID) FROM Property

I tried it in SQL Server Management Studio Express and it works, but when I tried to execute it in Visual Web Developer..., it gets me an error.

Here the code:

string SelectProperty_ID = "SELECT Max(Property_ID) FROM Property";
        SqlCommand property_id_Command = new SqlCommand(SelectProperty_ID, connect1);
        SqlDataReader readProperty_ID;
        
        try
        {
            connect1.Open();//make connection
            readProperty_ID = property_id_Command.ExecuteReader();
            readProperty_ID.Read();
            lblSpecial.Text = readProperty_ID["Property_ID"].ToString();
            readProperty_ID.Close();
        }
        catch (Exception error) { Label1.Text = "Error Readin.......etc

BTW, having readProperty_ID.Read(); with or without while NO good.

Could anyone help please.

Posted 26-Jan-10 8:28am

techy_00

Updated 26-Jan-10 8:29am

v2

Add a Solution

4 solutions

Solution 3

Thanks for your replies.

But, can you please tell me how this,

SELECT MAX(Property_ID) \"Property_ID\" FROM Property

, works in c# code

while in a normal SQL query should be like this

SELECT MAX(Property_ID) "\Property_ID\" FROM Property

Posted 26-Jan-10 21:35pm

techy_00

Solution 2

make this change to your code and u will get desired result..

try
        {
            connect1.Open();//make connection
            readProperty_ID = property_id_Command.ExecuteReader();
            while(readProperty_ID.Read())
            {
                lblSpecial.Text = readProperty_ID["Property_ID"].ToString();
            }
       readProperty_ID.Close();
 
       }
        catch (Exception error) { Label1.Text = "Error Readin.......etc

happy coding

Posted 26-Jan-10 19:26pm

Indrajeet Sutar

v2

Solution 4

The reason being, in C#, in order to embed quotes in a string, you have to preface the quotes with a forward slash.

String sqlStatement = "SELECT MAX(PROPERTY_ID_MAX) \"PROPERTY_ID_MAX\" FROM PROPERTY"

That translates to

SELECT MAX(PROPERTY_ID_MAX) "PROPERTY_ID_MAX" FROM PROPERTY

Posted 27-Jan-10 5:04am

Douglas Troy

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)

Douglas Troy · Accepted Answer · 2010-01-26T08:41:00

Without digging into this and coding it, I have two suggestions you can try:

1). Use an alias on on the name in your SQL statement
e.g. "SELECT MAX(Property_ID) Property_ID FROM Property"
Most likely, you're getting an error because Property_ID does not actually exist in the reader, since you're using an aggergate function

2). instead of doing ExecuteReader do ExecuteScalar, since you have a single return value, and cast the return OBJECT type into an integer.