Getting problem when trying to check if value already exist in sql table. Dont want dupes-CodePudding

I'm right now learning more about C# and SQL connections and doing a challenge their I'm making a simple atm.

This atm will handle create account, check if its exist in database and if not then add to database, if it exist then close the program. Also it will handle rest of the parts an atm does once you have logged in to your card. With take outs and input.

This challenge are just for me to learn more of SQL and C#.

My issue: I'm trying to check if the value exist already in my SQL table but I'm not sure what I'm doing wrong.

Thanks in advance!

  bool checkifExist(int number) {
        bool itExist;
        string queryString;
        var cnn = new SqlConnection(connectionString);
        DataTable dt = new DataTable();
        try
        {
            cnn.Open();

            queryString = "select Count(*) from dbo.User_Card_Information where [CardNumbers] = @cardnumbers";
            SqlCommand check_cardnumber = new SqlCommand(queryString, cnn);
            check_cardnumber.Parameters.AddWithValue("@cardnumbers", cardnumber);
            int cardExist = (int)check_cardnumber.ExecuteScalar(); 
            if(check_cardnumber.ExecuteNonQuery() != cardnumber)
            {
             
                Console.WriteLine("Create completed!");
                cnn.Close();
                return itExist = false;

            }
            else
            {
                Console.WriteLine("This Card Number already exist!");
                itExist = true;
                cnn.Close();
                return itExist;

            }
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Fetch Error:";
            msg  = ex.Message;
            throw new Exception(msg);
        }
        finally
        {
            cnn.Close();
        }
    }

CodePudding user response：

If you want to avoid duplicates in a database, you can set a constraint on the column. The constraint UNIQUE makes sure that the value of that column only exists once.

More about constraints

In your code you execute the same query twice, which does not make sense:

            queryString = "select Count(*) from dbo.User_Card_Information where 
            [CardNumbers] = @cardnumbers";
            SqlCommand check_cardnumber = new SqlCommand(queryString, cnn);
            check_cardnumber.Parameters.AddWithValue("@cardnumbers", 
            cardnumber);
        >   int cardExist = (int) check_cardnumber.ExecuteScalar();
        >   if(check_cardnumber.ExecuteNonQuery() != cardnumber)
            {
             
                Console.WriteLine("Create completed!");
                cnn.Close();
                return itExist = false;

            }

Furthermore you are using SqlCommand.ExecuteNonQuery() which returns the rows affected, which is not what you want.

You should compare the result of (int)check_cardnumber.ExecuteScalar() with the given card_number.

CodePudding user response：

If the intention is to check if the data is present in the table or not, you can query only the top row instead of all, in which case you would not get exceptions for multiple or duplicate rows.

CodePudding user response：

Hi i think you need something like this

        object result = cmd.ExecuteScalar(); // ExecuteScalar fails on null
        if (result.GetType() != typeof(DBNull))
        {
            test = (int?)result;
        }