ExecuteScalar return null

Question

I am using below SQL Server function to check is userName and password entered by user through C# windows forms are correct or not as show below too but at first I am getting error at line cmd.ExecuteScalar(); saying

{"Object reference not set to an instance of an object."}

after trying to handle that error I get the error on

return result.ToString();

How I am getting null value if the SQL Server function not returning null value it return 1 or -1 if there is no match within the database?

Even so I searched and tried to handle the null value returned from the cmd.ExecuteScalar(); as you can see my tries but none of the gose successfully

please if anyone can help me ...thanks

@mjwills sorry update my question i am getting error on `return result.ToString();` — sam, Aug 23 '18 at 12:24
probably because you are trying to call a function like a stored procedure? statement should be more like 'select dbo.USER_LOGIN(...)' — Markus Dresch, Aug 23 '18 at 12:26
Possible duplicate of [Calling SQL Defined function in C#](https://stackoverflow.com/questions/17047057/calling-sql-defined-function-in-c-sharp) — mjwills, Aug 23 '18 at 12:27
a thing to be noted in your code is that you are still returning a null result even after not null check... WHY ? — IteratioN7T, Aug 23 '18 at 12:30
@IteratioN7T that is what I am trying to know if you can help me ... — sam, Aug 23 '18 at 12:31
@MarkusDresch i tried to call it as you suggest `select dbo.USER_LOGIN` but I get error that saying `{"Could not find stored procedure 'select dbo.USER_LOGIN'."}` but it is exist and I can run it from SQL Server Management Studio and return correct result — sam, Aug 23 '18 at 12:31
Possible duplicate of [ExecuteScalar always returns null when calling a scalar-valued function](https://stackoverflow.com/questions/6932199/executescalar-always-returns-null-when-calling-a-scalar-valued-function) — GSerg, Aug 23 '18 at 12:47
@Crowcoder i tries what you suggest but now I am getting error `{"The multi-part identifier \"dbo.USER_LOGIN\" could not be bound."}` — sam, Aug 23 '18 at 13:09
Calling result.ToString() when the result is null will give the mentioned message. Your code calls result.ToString() even if you check for a null returned. — Steve, Aug 23 '18 at 13:38

iSR5 · Accepted Answer · 2018-08-23T13:40:18.020

2

you probably want to do something like this :

public string userLogin()
{
    string connStr = ConfigurationManager.ConnectionStrings["SRJDconnstr"].ToString();
    string cmdStr = @"SELECT dbo.USER_LOGIN(@USER_NAME, @PWD)";

    using (SqlConnection conn = new SqlConnection(connStr))
    using (SqlCommand cmd = new SqlCommand(cmdStr, conn))
    {
            conn.Open();

            cmd.Parameters.AddWithValue("@USER_NAME", TB_USER_NAME.Text);
            cmd.Parameters.AddWithValue("@PWD", TB_PWD.Text);

            var result = cmd.ExecuteScalar();

            return result.ToString();
    }
}

edited Aug 23 '18 at 13:40

answered Aug 23 '18 at 13:08

iSR5

3,274
2
14
13

@sam your query should be like this "SELECT dbo.USER_LOGIN (@USER_NAME, @PWD)" – IteratioN7T Aug 23 '18 at 13:21
@IteratioN7T thanks you are right but that is totally different from oracle syntax in oracle you just pass the oracle function name with cmd.Parameters.add and every thing will work fine – sam Aug 23 '18 at 13:25
@sam sorry, I forget the parentheses – iSR5 Aug 23 '18 at 13:41
@sam are you using Oracle or SQL Server ? both are two different DBMS with different syntax. ? – iSR5 Aug 23 '18 at 13:42
please add that line of code to your answer `cmd.CommandType = CommandType.Text;` so and i will mark it as answer and for other searcher in futureI used `cmd.Parameters.Add(new SqlParameter("@USER_NAME", SqlDbType.VarChar)).Value = TB_USER_NAME.Text;` and `cmd.Parameters.Add(new SqlParameter("@PWD", SqlDbType.VarChar)).Value = TB_PWD.Text;` not the the `AddWithValue` as I searched and found there is no difference between them except in `Parameters.Add` you have to take care of datatype and datatype conversion... correct me if I am wrong thanks all for help – sam Aug 23 '18 at 13:45
@iSR5 no `sql server` but all of my previous experience are working with `c#` and `oracle` and that is my first project that use `sql server` instead of `oracle` i was not expect that `c#` will be that difference between using sql server ... thanks alot for you – sam Aug 23 '18 at 13:49
@sam the defualt CommandType is CommandType.Text, and your query is select statement, so there is no need to specify it. As for Parameters, all methods are valid, you just need to use whatever you like, but mine is shorter version from the original one in your post and your comment. That's it ;).. – iSR5 Aug 23 '18 at 13:53
@iSR5 uh good information thanks for sharing I was not know that default CommandType is `CommandType.Tex` and I was testing a suggestion form commenter above you know when you get in problem you just start came in mass thanks alot for you again – sam Aug 23 '18 at 13:59

score 1 · Answer 2 · answered Aug 23 '18 at 15:10

1

cmd.ExecuteScalar() will return the first column of the first row in the result set.

Your SQL Server function code ends with RETURN @vResult which is the RETURN value.

Try and replace RETURN @vResult with SELECT @vResult.

answered Aug 23 '18 at 15:10

Barry Kaye

7,682
6
42
64

ExecuteScalar return null

2 Answers2