• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 441
  • Last Modified:

executeReader or ExecuteScalar?

if I am selecting two values from sql sp like select @updated as update, @message as Message
updated is bit and message is varchar

on cs code can I use executeScalar to see if updated returns 1 or 0 and especially Message reuturns a message  or Do I need to use ExecuteReader
0
1jaws
Asked:
1jaws
  • 10
  • 6
  • 2
  • +2
5 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as soon as you need to get more than 1 column OR more than 1 row, you cannot use ExecuteScalar.
so, the answer is: you have to use ExecuteReader, unless you have a stored procedure with more output parameters, for which you might neither neither of those, but a ExecuteNonQuery
0
 
1jawsAuthor Commented:
ok good explanation angellll :) I have this select @updated as update, @message as Message
 is that means I need ExecuteNonQuery or Reader, not too clear about it.. those two parameters are giving me value like
updated -- 1
message -  my message blah blah.
when I executed sp, which means I need ExecuteNonQuery than?
0
 
1jawsAuthor Commented:
I do actually need to use ExecuteReader because I am checking and seeing the values comes from sp which is I am trying to do this and giving me error saying == cannot be using bool to string for this statement
 if (bool.Parse(reader["updated"].ToString()) == "0")
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, ExecuteReader is what you need for that SELECT.
0
 
mohd_haqCommented:
Hi,
the error says all. you have converted to bool but you are comparing it with string. Your if statement should be like this
 if (bool.Parse(reader["updated"].ToString()) == 0)
or simply
 if (reader["updated"].ToString() == "0")
0
 
1jawsAuthor Commented:
first one didnt work but second one worked  if (reader["updated"].ToString() == "0") as a snytax but
it is giving me IndexOutOFRangeException {updated}  
dont know why?
0
 
guru_samiCommented:
are you calling reader.Read() method before reader["updated"] or not ?
we need to see more code for that part...
also what is the type of "updated" field  in your DB is it bit  or string or int?
0
 
1jawsAuthor Commented:
I think I know why  on sp I have two other select statement that get executed before the last one with updated and message, so it is looking for and cant find it, but what I need to know to only point out to last select statement I dont know

like first one
Select blah blah    
Select blah
select @updated as update, @message as Message      -->>> I want this one not the other two select statement...
0
 
1jawsAuthor Commented:
reder.Read() first comes..
updated is bit
0
 
käµfm³d 👽Commented:
>>  as soon as you need to get more than 1 column

I would argue that if you wanted multiple columns and wanted to use ExecuteScalar, then you could concatenate your columns as string values, then parse the return from ExecuteScalar in code. A little sloppy, but possible. :)
0
 
1jawsAuthor Commented:
I have two values at the third select statement which I want third one. dont think can use ExecuteScalar .. I need to do simpliest and easiest way to do that..
0
 
mohd_haqCommented:
hi,
as you have mentioned your select query is
select @updated as update, @message as Message
so what i think is that you gave an allias to @updated variable as update and so you should refer that with update in your if condition so your if statement should be  
 if (reader["update"].ToString() == "0")
0
 
1jawsAuthor Commented:
no I gave updated that part is correct...
0
 
mohd_haqCommented:
ok if that so go for the index value index just in case. try this
 if (reader[0].ToString() == "0")
if you are selecting message first. or
 if (reader[1].ToString() == "0") if you are selecting message second.
0
 
1jawsAuthor Commented:
my results looks like this when I executed my sp

No column Name
1

ID
12232

updated     message
1                it is updated

as you can see I am getting three different resutls. but when I am reading from cs code I just want third result... Thats all I need.
0
 
guru_samiCommented:
ok here you are dealing with multiple resultsets...so you will need reader.NextResult().
Check on how to use it here under - Retrieving Multiple Result Sets using NextResult -:
http://msdn.microsoft.com/en-us/library/haa3afyz.aspx
0
 
mohd_haqCommented:
yes i agree with guru sami, here what you can do
            SqlDataReader dr;
            conn.Open();
            dr = com.ExecuteReader();
            dr.NextResult();
            dr.NextResult();
            dr.Read();
            if (reader[0].ToString() == "0")
change as appropriate. but what it does is that it will ignore the first two results and will read the third result.
hope this helps you.
0
 
mohd_haqCommented:
instead of
if (reader[0].ToString() == "0")
you can also use the column names like this
if (reader["updated"].ToString() == "0")
since you have ignored the first two results. previously it wasn't working since we were looking at the first result.
0
 
1jawsAuthor Commented:
YES it worked, thank you so much guys, I have learned something new!!! I will give points momentarily...
0
 
mohd_haqCommented:
Thanks for sharing your result and i am pleased to help you.
0
 
1jawsAuthor Commented:
Thanks All!!!
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 10
  • 6
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now