executescalar() method

dotnet framework


  • 1. Acessing a field in an OleDbDataReader
    I have an OleDbDataReader that gets created based on the following (simplified) SQL select table1.field1, table2.field2 f from table1, table2 where ...... on my development machine, the following line works fine. myOleDbDataReader.GetString(myOleDbDataReader.GetOrdinal("field1")); However, on my colleagues machine this throws an exception, and the line has to be modified to myOleDbDataReader.GetString(myOleDbDataReader.GetOrdinal("table1.field1")); The database drivers are the same version, same version of .NET, same version of MDAC...etc TIA Bisley
  • 2. Tracing Data Adapter Activity ?
    How can I trace what a data adapter is actually trying to do ? I have a problem since my adapter is always affecting 0 rows on any "Added" or "Modified" records in my data table, but deleted rows work fine. I can't seem to "step into" the stored procedures when the Update method of the data adapter is invoked. Is there some way I can check what stored procedures are being invoked and debug these ?
  • 3. query statement returns error only in code
    Hello all, I have to execute the following simple query in MS Access and I am using the OleDbCommand object to do this. SELECT Entries.MessageID, Entries.DatePosted, Entries.MessageBody, User.UserName FROM Entries INNER JOIN User ON Entries.UserID=User.UserID I get an error saying: Syntax error in FROM clause I saved the SQL statement as a query in MS Access and I am able to run the query from Access and get results. Why is that I get an error when trying to execute the query from my C# code? Thanks CSharp
  • 4. data type mapping
    Does anyone know how .Net data types are mapped to DbType and vice versa?
  • 5. Calling a stored procedure / package from Crystal Reports.NET
    Hello, Does anyone have any experience or knowledge regarding calling a stored procedure / package from Crystal Reports.NET? This damn product is killing me! Regards, James Kent

executescalar() method

Postby buran » Wed, 23 Jul 2003 21:43:14 GMT

Dear .NET Programmers,

I use the following code snippet but can't figure out what is going wrong
since the result object equals to nothing although it shouldn't be :)

Function CheckMFileToBeClosed() As Boolean
    Dim result As Object
    myCommand.CommandText = "SELECT Result = CASE WHEN DischargeDate IS NULL
THEN 1 " & _
    "ELSE 0 END " & _
    "FROM dbo.MedicalFollowUpInHospital " & _
    "WHERE OurFileNo = @medicalFileNo"
    myCommand.CommandType = CommandType.Text
    myCommand.Parameters.Add("@medicalFileNo", Session("selectedFileNumber")
+ "M")
    result = myCommand.ExecuteScalar()
    If CInt(result) = 1 Then
        Return False
        Return True
    End If
End Function

Thanks in advance,


Re: executescalar() method

Postby Marc Hoeppner » Wed, 23 Jul 2003 22:59:31 GMT


my guess is that there is no row returned, therefore the result set is empty
and the scalar result is set to NULL. The reason for not returning a row
could be due to the WHERE OurFileNo = @medicalFileNo clause. Debug into the
code and take a look at Session("selectedFileNumber") especially the type.

Best regards,

Marc Hpner


Re: executescalar() method

Postby Cowboy (Gregory A. Beamer) » Wed, 23 Jul 2003 23:04:05 GMT

Only thing I see strange is the following:
myCommand.Parameters.Add("@medicalFileNo", Session("selectedFileNumber") +

myCommand.Parameters.Add("@medicalFileNo", Session("selectedFileNumber") &

Gregory A. Beamer
Author: ADO.NET and XML: ASP.NET on the Edge

Think Outside the Box!


Re: executescalar() method

Postby David Browne » Thu, 24 Jul 2003 00:49:34 GMT


 - ExecuteScalar is slow and sloppy.
 - don't reuse command objects.
 - VB now has unlimited line lengths. Use them for static SQL queries.
     SQL queries should no longer be broken over lines.
 - Test it in Query Analyzer.  With the static query it's easy to paste it
into QA
   just add declare's for the parameters

in QA:

declare @Result int
declare @medicalFileNo varchar(50)
set @medicalFileNo = '123546M'

MedicalFollowUpInHospital WHERE OurFileNo = @medicalFileNo

select @Result

Updated Function:

Function CheckMFileToBeClosed() As Boolean

    dim myCommand as new SQLCommand("SELECT @Result = CASE WHEN
DischargeDate IS NULL THEN 1 ELSE 0 END FROM MedicalFollowUpInHospital WHERE
OurFileNo = @medicalFileNo",con)
    Dim pResult As New SqlClient.SqlParameter("@Result", SqlDbType.Int)
    pResult.Direction = ParameterDirection.Output
& "M")) 'always cast explicitly for this constructor.


    If CInt(pResresult) = 1 Then
        Return False
        Return True
    End If
End Function


Re: executescalar() method

Postby buran » Thu, 24 Jul 2003 21:38:52 GMT

Dear David, thanks for help but following code says "Invalid cast from
sqlparameter to integer" ??

 If CInt(pResresult) = 1 Then
         Return False
         Return True
End If

"David Browne" <davidbaxterbrowne no potted  XXXX@XXXXX.COM > wrote in


Re: executescalar() method

Postby buran » Thu, 24 Jul 2003 22:03:23 GMT

Thanks David, this solved the problem :)

"David Browne" <davidbaxterbrowne no potted  XXXX@XXXXX.COM > wrote in


Similar Threads:

1.ExecuteScalar method sometimes work, sometimes doesn't


I've got some weird behavior happening within one of the datamappers.  It 
all has to do with inserting a new row, and returning the Id of the row 
being entered.

Here is what the code looks like that is getting "Object reference not set 
to an instance ..." run-time error:

       string ateId = cm.ExecuteScalar().ToString();          -----------  
this line crashes

None of the parameters are null, nor do they appear to have any illegal data 
inside of them.  The above code inserts a row into the AuditTrailEntry 

On the other hand, this code inserts a new jobStep row, and doesn't cause 
any errors:

     if (storedProcedure == "updateJobStep")   //use this if update

            cm.ExecuteNonQuery();    // no return value needed



            string jobStepId = cm.ExecuteScalar().ToString();   //insert new 
jobStep, get id of new row

            js.Id = Convert.ToInt16(jobStepId);


Does anyone see a problem with this code, or know why it doesn't work?

TIA, Randy

2.Confused with ExecuteScalar method of SQLCommand

 I have one stored procedure in SQL server in which i have written one 
insert statement. Now in my cs file i pass the parameters require to execute 
that stored procedure and finaly by mistaken I used command.ExecuteScalar 
instead of command.ExecuteNonQuery. Surprisingly i am able to insert the data 
in table. Can anyone please tell me how i am able to insert data using 
ExecuteScalar of SQLCommand class. 

Help is really appriciated.

Thx in advance

3.oracleclient and executescalar

Why this code doesn't function?

Dim oraCn As OracleConnection, oraCmd As OracleCommand
Dim ris As Object

oraCn = New OracleConnection("user id=user;data source=pcname;password=pwd")

oraCmd = oraCn.CreateCommand
oraCmd.CommandText = "SELECT Tabe1.Campo1 From Tabe1;"
ris = oraCmd.ExecuteScalar

I try to use ExecuteOracleScalar too, but it doesn't function, I have a


4.Can't get oleDbCommand to ExecuteScalar more than once

While debugging a stored procedure in a test application, I was doing 
this against an MS Access database in a button click event:

oleDbCommand1.Parameters[0].Value = textBox.Text;
object dude = oleDbCommand1.ExecuteScalar();
if ( dude == null )
   MessageBox.Show("no result from stored proc");
   txtAnother.Text = dude.ToString();

This works fine the first time it is called and any subsequent times as 
long as the parameter passed in from the textBox doesn't change, but if 
I change it to another string that should give me a valid result from 
the stored procedure, the result of ExecuteScalar is null.
If I restart the application and use that new string from the beginning, 
I get a valid result.
The connection remains open, and I'm not changing parameter collection 
of the command object.

What the hell?

5.sqlCommand.ExecuteScalar problem on return value


"Select UserId from users where userid = 2"

How come it return Undefined value if the select doesn't return any result?
How can I know if the select succeded or not?


6. Using ExecuteScalar on ,Net Framework 1.1

7. ExecuteScalar don't return the corrent Int value.

8. ExecuteScalar closes connection

Return to dotnet framework


Who is online

Users browsing this forum: No registered users and 94 guest