ExecuteScalar() return 0 value

Question

I searched this title but I didn't find useful thing.

Look at my code:

string q = "exec searchUser {0}";
q = string.Format(q, id);

SqlCommand cm2 = new SqlCommand();
cm2.Connection = sqlCon;
cm2.CommandText = q;

SqlDataAdapter sqldadapter1 = new SqlDataAdapter(cm2);

DataTable dataTabel1 = new DataTable();
sqldadapter1.Fill(dataTabel1);

if (sqlCon.State != ConnectionState.Open)
{
    sqlCon.Open();
}

cm2.ExecuteNonQuery();

if (sqlCon.State != ConnectionState.Closed)
{
    sqlCon.Close();
}

string uname = dataTabel1.Rows[0][1].ToString();
string logName = dataTabel1.Rows[0][2].ToString();
//DateTime.Now;
int countTrue = listBox_checked.Items.Count;
int countFalse = listBox_LogicFault.Items.Count;

MessageBox.Show(countTrue+"");
MessageBox.Show(countFalse+"");
MessageBox.Show(DateTime.Now.ToString());

DateTime time = DateTime.Now;
//string format = "yyyy-MM-dd HH:mm:ss";

string query_insertReport = "exec insertReport {0},{1},{2},'{3}',{4}, {5},'{6}'";            
query_insertReport = string.Format(query_insertReport, id, uname, logName, time, countFalse, countTrue, sendFilename.filename);

SqlCommand cmdInsertReport = new SqlCommand();
cmdInsertReport.Connection = sqlCon;
cmdInsertReport.CommandText = query_insertReport;

if(sqlCon.State != ConnectionState.Open)
{
    sqlCon.Open();
}

int idd = Convert.ToInt32 (cmdInsertReport.ExecuteScalar());
MessageBox.Show(idd+"report ID value");    

if (sqlCon.State != ConnectionState.Closed)
{
    sqlCon.Close();
}

MessageBox.Show("Report Inserted");

I have a problem in the second part of my code where I want to insert report into Reports table and get the each record's reportID with ExecuteScalar() because I want to insert it in other table. Insert is done successfully but ExecuteScalar() returned 0 value (wrong value). I have some records in Reports table that numbers start off 32. What's the problem?

Here is my stored procedure:

ALTER PROCEDURE [dbo].[insertReport]
    @userID int,
    @userName nvarchar(50),
    @logicName nvarchar(50),
    @date datetime,
    @faultNumber int,
    @trueNumber int,
    @fileName nvarchar(MAX)
AS
    INSERT INTO Reports ([UserID], [UserName], [LogicName], [Date],[FaultNumber], [TrueNumber], [filename]) 
    VALUES (@userID, @userName, @logicName, @date, @faultNumber, @trueNumber, @fileName)

The return value of ExecuteScalar in this case will be the return value of your stored procedure insertReport. If you didn't select the new reports ID it will be 0 for success. — Romano Zumbé, May 11 '17 at 07:17
Why do you consider it to be a wrong value? What does your stored procedure return? *Where* is the stored procedure? If you don't have any `RETURN` or `SELECT` statements, why would `ExecuteScalar` return a non-zero value? — Panagiotis Kanavos, May 11 '17 at 07:17
Where are you 'filling' the parameter `{0}` in `"exec searchUser {0}"`? — Jeroen van Langen, May 11 '17 at 07:17
apart from the return value issue, please use parameters instead of the String.Format to make your code [safe for SQL injection](http://bobby-tables.com/csharp). — Cee McSharpface, May 11 '17 at 07:18
@Jeroen in the next line. The string.Format does the trick ;-) — Romano Zumbé, May 11 '17 at 07:19
BTW, your code isn't using parameters, just string concatenation to create a SQL statement vulnerable to SQL injection and conversion problems. Use a *proper* parameterized query and proper parameters. Since you call a stored proc, you can just set the sproc name as `CommandText` and start adding parameters with `.Parameters.Add`, using the sproc parameter names — Panagiotis Kanavos, May 11 '17 at 07:19
@RomanoZumbé the "trick" may involve injecting commas in the string — Panagiotis Kanavos, May 11 '17 at 07:20
@Panagiotis Thats true. But nevertheless, the parameter is 'filled' there — Romano Zumbé, May 11 '17 at 07:21
@PanagiotisKanavos buti used store procedure, still vulnerable to sql injection? and you meant i have to use ` cmdInsertReport. Parameters.AddWithValue("@userID", id);` if not help me with code please — Mo0rteza, May 11 '17 at 07:36
@Mo0rteza you haven't used a stored procedure. You created a SQL string using concatenation, that just happens to call a stored procedure. The string can contain anything. You still haven't explained why you expect the stored procedure to return anything though — Panagiotis Kanavos, May 11 '17 at 07:38
@Mo0rteza if you have a problem adding parameters, post the code and explain the problem. Parameters aren't broken. Everyone uses them since the 90s. — Panagiotis Kanavos, May 11 '17 at 07:39
You seem to expect for some reason, that the return value of a procedure call would somehow transport `@@IDENTITY` back to the calling code, which is just not happening without additional effort. There used to be a concept in connection with (I think) MS Access, where command execution would have returned the number of affected records IIRC, but even that is something completely different. — Cee McSharpface, May 11 '17 at 07:43
Possible duplicate of [Stored procedure - return identity as output parameter or scalar](http://stackoverflow.com/questions/3142444/stored-procedure-return-identity-as-output-parameter-or-scalar) — Cee McSharpface, May 11 '17 at 07:54
@PanagiotisKanavos i don't have problem with adding parameters.but i useed procedure and its name is "insertReport" .the only problem is ExecuteScalar doesn't return true value, and just return 0. i want to take report id and insert it in other Table. — Mo0rteza, May 11 '17 at 07:55
@Mo0rteza why do you except it to return *anything* else? You aren't using SELECT or `RETURN`. What did you expect? — Panagiotis Kanavos, May 11 '17 at 07:57

score 1 · Accepted Answer · answered May 11 '17 at 08:10

i got what was going wrong. the problem was SQl code not C# and ADO.net.i needed to add this line code.

select CAST (SCOPE_IDENTITY() As int)

after i modified my procedure now ExecuteScalar() return true value. the complete procedure code :

ALTER procedure [dbo].[insertReport]
@userID int,
@userName nvarchar(50),
@logicName nvarchar(50),
@date datetime,
@faultNumber int,
@trueNumber int,
@fileName nvarchar(MAX)

as

INSERT into Reports ([UserID],[UserName],[LogicName],[Date],[FaultNumber],[TrueNumber],[filename]) values (@userID,@userName,@logicName,@date,@faultNumber,@trueNumber,@fileName)
select CAST (SCOPE_IDENTITY() As int)

That is exactly, what everybody was telling you in the comments section — Romano Zumbé, May 11 '17 at 09:37

ExecuteScalar() return 0 value

1 Answers1