[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Output from SQL query

Posted on 2011-02-17
16
Medium Priority
?
301 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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 1000 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 1000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

834 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