C# ExecuteScalar()

Question

I am attempting to make a craps game, store users/game data etc. In this problem I am attempting to see if a username is already taken. But for some reason, regardless of how I try to declare my variable to take in the result of ExecuteScalar(), I always get the error "Cannot convert from nVarChar to int".

private bool CheckForUser()
{
        //returns true if Username is already registered
        SqlConnection connection = new SqlConnection("...");
        string query = "SELECT * FROM Users WHERE USERID = @alias";
        SqlCommand cmd = new SqlCommand(query, connection);
        cmd.Parameters.AddWithValue("@alias", tbNewUsername.Text);
        connection.Open();
        Object DbResult = cmd.ExecuteScalar(); //<-ERROR
        connection.Close();
        DbResult = (DbResult == DBNull.Value) ? null : DbResult;

        if (DbResult != null)
            return true;
        else
            return false;

}

This was my last attempt before looking for help. Casting to int does not work, string does not work, and declaring DbResult as an object does not work. I understand why it can not be converted to int, but i do not know why it continues to try.

Thank you for any help.

This is why this is a duplicate: you are passing a `string` to a parameter with `AddWithValue` which assumes the parameter is a `NVARCHAR`, when it should be an `int` (according to `USERID`). Use `cmd.Parameters.Add("alias", DbType.Int32).Value = tbNewUsername.Text;` (or something similar, I don't have VS open) — Camilo Terevinto, Oct 20 '18 at 23:10
I found it. "SELECT * FROM Users WHERE USERID = @alias" You are comparing the user ID (a integer Primary key) with a string input. Would have helped a lot if you specific if this was a compile time or runtime error :) Still I stand by a need to redesign this. — Christopher, Oct 20 '18 at 23:11

Christopher · Answer 1 · 2018-10-20T23:01:40.037

-1

Doing this kind of check in the GUI rather then the DB is propably a bad idea. You will only run into race conditions that way. Usually you:

Set the table up to only allow unique (login) names
try to insert a specific new user
the insert either fails or does not fail

Stuff like informing a registering user the name is already take? That is a additional Quality of Life feature for the Register UI, not a core thing.

The Compiler error does not make any sesne with the code you showed. That leaves only one option: Something else - propably way above those lines - is tripping up the compielr so badly, it can not even tell you where the issue is anymore.

Edit: Another issue is that you do not Dipose of the Connection. That will cause you issues. you should always implement the using pattern with Network Connections and File Access. And anything else that implements IDisposeable.

edited Oct 20 '18 at 23:01

answered Oct 20 '18 at 22:53

Christopher

9,634
2
17
31

Who keeps downvoting me without any information on what I am doing wrong? At a reputation just shy of 2000 I think I deserve an explanation at least. – Christopher Oct 20 '18 at 22:58
Read my comment under the question to understand why someone *may* have downvoted you – Camilo Terevinto Oct 20 '18 at 23:10
@CamiloTerevinto: Who does not agree with Race Condition proofing your code? what other *reliable* options are there to race condition proof a DB access? – Christopher Oct 20 '18 at 23:13
It *may* not be because of that, but because of the compiler error (there are none), the assumption that more than 1 user uses the system, etc. Or it could well be because someone doesn't like you, we cannot know :) – Camilo Terevinto Oct 20 '18 at 23:18

score -2 · Answer 2 · answered Oct 20 '18 at 22:51

-2

Have you looked at the examples at Microsoft? They have the following example, which tells you how to use the ExecuteScalar function and what kind of return type to expect:

cmd.CommandText = "SELECT COUNT(*) FROM dbo.region";  
Int32 count = (Int32) cmd.ExecuteScalar();

answered Oct 20 '18 at 22:51

Mark

5,089
2
20
31

While a good general answer, this does not really solve the issue. In particular how `Object DbResult = cmd.ExecuteScalar(); //<-ERROR` coudl give "Cannot convert from nVarChar to int" as compiler warning. Near as I can tell he is doing everything right in thise piece of code. – Christopher Oct 20 '18 at 22:59

C# ExecuteScalar()

2 Answers2