Solved

Output from SQL query

Posted on 2011-02-17
16
247 Views
Last Modified: 2012-05-11
Hi all

I am trying to return an int from my sql query. (Identity)

But for some reason the int value is not being set, so I am thinking maybe I made a mistake with assigning the int.

Can somone point out where my mistake is ?
int test = (Int32) Convert.ToInt32(sqlCommand.ExecuteScalar());

Open in new window

0
Comment
Question by:samelamin
  • 7
  • 5
  • 3
  • +1
16 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 34915053
Can you show us your sql-command.
You probably need to add the following

set SQLCommand =
insert into table values ('string value');select scope_identity()

0
 

Author Comment

by:samelamin
ID: 34915065
here is my sql query
AS
Begin
	SET NOCOUNT ON;

	INSERT INTO    [Overtime]
					(WeekCom)	
	VALUES	(getdate())	
	
	SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];
END

Open in new window

0
 
LVL 10

Expert Comment

by:John Claes
ID: 34915111
Samelamin,

can you provide us with the Query?

this method (ExecuteScalar) reads the following value from the responseDataSet

Descriptive:       First table -> First row => First column
c# :       DataSet.Tables[0].Rows[0][0]

So if we have the query we can see that you indeed only return 1 value;




A second remark

(Int32) will set the following variable to the Object Int32
Convert.ToInt32 will try to convert the variable to a Object of Type Int32

So you don't need to add the (Int32) because you'll receive a Int32 from the Convert already
You don't need to try a Cast after a Convert.

regards
poor beggar
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 34915114
Can you try the following
int test = (Int32) Convert.ToInt32(sqlCommand.ExecuteNonQuery());
0
 
LVL 10

Expert Comment

by:John Claes
ID: 34915120
Samelin

Why is there a AS and a Begin and a END ??
Is this a part from a Procedure?
0
 

Author Comment

by:samelamin
ID: 34915128
It is the procedure yes.

Dhaest that didnt work, it returns a -1
0
 
LVL 10

Expert Comment

by:John Claes
ID: 34915178

Samelamin:

Dit you set all the needed attributes for you're command?
 
SqlConnection sqlConnection1 = new SqlConnection("Your Connection String");
SqlCommand cmd = new SqlCommand();
Object returnValue;

cmd.CommandText = "StoredProcedureName";
cmd.CommandType = CommandType. StoredProcedure;
cmd.Connection = sqlConnection1;

sqlConnection1.Open();

returnValue = cmd.ExecuteScalar();

sqlConnection1.Close();

Open in new window


http://msdn.microsoft.com/en-us/library/37hwc7kt%28v=vs.80%29.aspx

0
 
LVL 53

Expert Comment

by:Dhaest
ID: 34915185
>> Dhaest that didnt work, it returns a -1

Are you sure that you're record is inserted ?
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:samelamin
ID: 34915261
it was because you didnt use excuste scalar

I am returning an int not an object but tried returning an obj then converting and I still dont get the answer

The convert part is getting the right value, but test isnt being assigned the right value
int test = (Int32)Convert.ToInt32(sqlCommand.ExecuteScalar()); 
            int j =test;

Open in new window

0
 
LVL 10

Assisted Solution

by:John Claes
John Claes earned 250 total points
ID: 34915325
Samelamin

the executeScalar returns always a object.
So you need to convert/cast it.


But if you run you procedure upon sql you retrieve a single value?

Scope_IDentity
999888

then the ExecuteScalar will return a Object with value {999888}
This we can convert.ToInt32 to an Object of Type Int32 with the value 999888
So the folowing will work

int test = Convert.ToInt32(sqlCommand.ExecuteScalar());


Anyway
If the SP doesn't run opun SQL itself you might change
      SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];
into
      SELECT @@IDENTITY AS [SCOPE_IDENTITY];
0
 

Author Comment

by:samelamin
ID: 34915524
Guys its still not working

I am adding a watch and the value is not being changed.

I even tryied changing it to @@Identity and nothing.

When I excute it on SQL a value is returned, if i add a watch to "Convert.ToInt32(sqlCommand.ExecuteScalar()); "  the right value is returned

The problem is test is not being assigned the value
0
 
LVL 10

Expert Comment

by:John Claes
ID: 34915599
Samelamin


So Convert.ToInt32(sqlCommand.ExecuteScalar());  gives the right value in the Watch
        PS : You know that upon that moment already a record is created?

and Int test = Convert.ToInt32(sqlCommand.ExecuteScalar()); is not getting a value in test?
Or is it still  Int test = (int32)Convert.ToInt32(sqlCommand.ExecuteScalar()); ?

regards

poor beggar


ps : If it looks like i'm always asking the same questions, that's because I'm trying to folow and to be sure that I have on my scrren what I thinck you have. ...

0
 

Author Comment

by:samelamin
ID: 34915614
Test is not geting any value, goes to 0, but the convert part(right side)  is getting the right value
0
 
LVL 7

Accepted Solution

by:
mkobrin earned 250 total points
ID: 34915726
I always wrap my SQL commands in a using clause and read the data in the following way. I hope this will work for you
int test = 0;
using (SqlConnection connection = new SqlConnection(connectionString))
{
     connection.Open();
     SqlCommand command = new SqlCommand("YourProcedureName");
     command.CommandType = CommandType.StoredProcedure;
     command.Parameters.Add(new SqlParameter("@parameterName", SqlDbType.ParameterType)).Value = ParameterValue;
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows == true)
                    {
                        while (reader.Read())
                        {
                            test = Convert.ToInt32(reader["SCOPE_IDENTITY"]);
                        }
                    }
                }
}

Open in new window

0
 

Author Comment

by:samelamin
ID: 34915743
mkobrin that worked cheers
0
 

Author Closing Comment

by:samelamin
ID: 34915971
Thanks guys
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now