Check if a record exists in the database

Question

I am using these lines of code to check if the record exists or not.

SqlCommand check_User_Name = new SqlCommand("SELECT * FROM Table WHERE ([user] = '" + txtBox_UserName.Text + "') ", conn);

int UserExist = (int)check_User_Name.ExecuteScalar();

But I am getting an error:

Object reference not set to an instance of an object.

I want to do:

if (UserExist > 0)
    // Update record

else

    // Insert record

when wil this error display if the user name in table or not? — r.vengadesh, Jan 23 '14 at 07:54
@NicklasWinger Its giving me error on this line, {int UserExist = (int)check_User_Name.ExecuteScalar();} — Kamran, Jan 23 '14 at 08:28

score 24 · Accepted Answer · edited Jan 08 '17 at 15:12

24

ExecuteScalar returns the first column of the first row. Other columns or rows are ignored. It looks like your first column of the first row is null, and that's why you get NullReferenceException when you try to use the ExecuteScalar method.

From MSDN;

Return Value

The first column of the first row in the result set, or a null reference if the result set is empty.

You might need to use COUNT in your statement instead which returns the number of rows affected...

Using parameterized queries is always a good practise. It prevents SQL Injection attacks.

And Table is a reserved keyword in T-SQL. You should use it with square brackets, like [Table] also.

As a final suggestion, use the using statement for dispose your SqlConnection and SqlCommand:

SqlCommand check_User_Name = new SqlCommand("SELECT COUNT(*) FROM [Table] WHERE ([user] = @user)" , conn);
check_User_Name.Parameters.AddWithValue("@user", txtBox_UserName.Text);
int UserExist = (int)check_User_Name.ExecuteScalar();

if(UserExist > 0)
{
   //Username exist
}
else
{
   //Username doesn't exist.
}

edited Jan 08 '17 at 15:12

Peter Mortensen

30,738
21
105
131

answered Jan 23 '14 at 07:47

Soner Gönül

97,193
102
206
364

4

From database point of view you should use: SELECT top 1 1 FROM [Table] WHERE ([user] = @user) and check if ExecuteScalar is null or not. It performs better than count(*) – Bartosz Pierzchlewicz Jul 09 '15 at 06:48
Be careful when using `AddWithValue`: https://web.archive.org/web/20210412195621/http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ . – BCdotWEB Sep 20 '16 at 11:10
hasRows is a much better option because in your scenario you count on first field value and you should never rely on that, rather has rows will tell you of data was returned or not – zuboje Mar 23 '23 at 17:18

score 7 · Answer 2 · edited Jan 08 '17 at 15:14

7

The ExecuteScalar method should be used when you are really sure your query returns only one value like below:

SELECT ID FROM USERS WHERE USERNAME = 'SOMENAME'

If you want the whole row then the below code should more appropriate.

SqlCommand check_User_Name = new SqlCommand("SELECT * FROM Table WHERE ([user] = @user)" , conn);
check_User_Name.Parameters.AddWithValue("@user", txtBox_UserName.Text);
SqlDataReader reader = check_User_Name.ExecuteReader();
if(reader.HasRows)
{
   //User Exists
}
else
{
   //User NOT Exists
}

edited Jan 08 '17 at 15:14

Peter Mortensen

30,738
21
105
131

answered Jan 23 '14 at 08:01

Cem Sönmez

506
1
3
15

This won't work because you didn't even add parameter in `SqlCommand`. – Soner Gönül Jan 23 '14 at 08:05
using ExecuteScalar() is the shortest way. – Ramgy Borja Aug 02 '17 at 00:38

score 4 · Answer 3 · edited Jan 30 '17 at 11:15

4

sqlConnection.Open();
using (var sqlCommand = new SqlCommand("SELECT COUNT(*) FROM Table WHERE ([user] = '" + txtBox_UserName.Text + "'", sqlConnection))
{

    SqlDataReader reader = sqlCommand.ExecuteReader();
    if (reader.HasRows)
    {
        lblMessage.Text ="Record Already Exists.";

    }
    else
    {
        lblMessage.Text ="Record Not Exists.";
    }

    reader.Close();
    reader.Dispose();
}

sqlConnection.Close();

edited Jan 30 '17 at 11:15

Dipiks

3,818
2
23
39

answered Jan 30 '17 at 10:08

Singh Kailash

621
5
16

If you're using `COUNT(*)` you will always get a row with a number even if it's 0 you should just use `*` – Yox Apr 24 '21 at 11:15

score 1 · Answer 4 · edited Jan 21 '18 at 16:58

1

MySqlCommand cmd = new MySqlCommand("select * from table where user = '" + user.Text + "'", con);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataSet ds1 = new DataSet();
da.Fill(ds1);
int i = ds1.Tables[0].Rows.Count;
if (i > 0) {
    // Exist
}
else {
    // Add 
}

edited Jan 21 '18 at 16:58

סטנלי גרונן

2,917
23
46
68

answered Jan 21 '18 at 13:41

Emir Simon Francisco Llagas

19
1

This question is using MSSQL, not MySQL. – TylerH Apr 28 '22 at 16:42

score 0 · Answer 5 · answered Jan 23 '14 at 08:01

I would use the "count" for having always an integer as a result

SqlCommand check_User_Name = new SqlCommand("SELECT count([user]) FROM Table WHERE ([user] = '" + txtBox_UserName.Text + "') " , conn);

int UserExist = (int)check_User_Name.ExecuteScalar();

if (UserExist == 1) //anything different from 1 should be wrong
{
  //Username Exist
}

score 0 · Answer 6 · answered Apr 27 '17 at 09:46

try this

 public static bool CheckUserData(string phone, string config)
    {
        string sql = @"SELECT * FROM AspNetUsers WHERE PhoneNumber = @PhoneNumber";
        using (SqlConnection conn = new SqlConnection(config)
            )
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                cmd.Parameters.AddWithValue("@PhoneNumber", phone);
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                if (reader.HasRows)
                {
                    return true;  // data exist
                }
                else
                {
                    return false; //data not exist
                }
            }
        }
    }

What does this do? How does it answer OP's question? How is it different from the existing answers? Good answers explain themselves. — TylerH, Apr 28 '22 at 16:44

score -1 · Answer 7 · edited Jan 08 '17 at 15:13

-1

Use try catch:

try
{
    SqlCommand check_User_Name = new SqlCommand("SELECT * FROM Table WHERE ([user] = '" + txtBox_UserName.Text + "') ", conn);

    int UserExist = (int)check_User_Name.ExecuteScalar();
    // Update query
}
catch
{
    // Insert query
}

edited Jan 08 '17 at 15:13

Peter Mortensen

30,738
21
105
131

answered Jan 23 '14 at 07:57

r.vengadesh

1,721
3
20
36

score -1 · Answer 8 · edited Jan 08 '17 at 15:14

-1

You can write as follows:

SqlCommand check_User_Name = new SqlCommand("SELECT * FROM Table WHERE ([user] = '" + txtBox_UserName.Text + "') ", conn);
if (check_User_Name.ExecuteScalar()!=null)
{
    int UserExist = (int)check_User_Name.ExecuteScalar();
    if (UserExist > 0)
    {
        //Username Exist
    }
}

edited Jan 08 '17 at 15:14

Peter Mortensen

30,738
21
105
131

answered Jan 23 '14 at 08:05

Chirag

324
2
4
27

2

What if the first column of the first row is not username? You are just guess it is. `ExecuteNonQuery` is a better approach in this case IMO.. – Soner Gönül Jan 23 '14 at 08:10
Yes. you are absolutely right @SonerGönül but with his implemented code, i have given this solution. – Chirag Jan 23 '14 at 08:15

score -1 · Answer 9 · edited Jan 08 '17 at 15:16

I was asking myself the same question, and I found no clear answers, so I created a simple test.

I tried to add 100 rows with duplicate primary keys and measured the time needed to process it. I am using SQL Server 2014 Developer and Entity Framework 6.1.3 with a custom repository.

Dim newE As New Employee With {.Name = "e"}
For index = 1 To 100
  Dim e = employees.Select(Function(item) item.Name = "e").FirstOrDefault()
  If e Is Nothing Then
    employees.Insert(newE)
  End If
Next

2.1 seconds

Dim newE As New Employee With {.Name = "e"}
For index = 1 To 100
  Try
    employees.Insert(newE)
  Catch ex As Exception
  End Try
Next

3.1 seconds

This may answer another question, but it does not answer the question asked above, which is how to do this in ASP.NET WinForms with a SqlCommand. — TylerH, Apr 28 '22 at 16:43

score -1 · Answer 10 · edited Jan 08 '17 at 15:19

-1

sda = new SqlCeDataAdapter("SELECT COUNT(regNumber) AS i FROM  tblAttendance",con);
sda.Fill(dt);

string i = dt.Rows[0]["i"].ToString();
int bar = Convert.ToInt32(i);

if (bar >= 1){

    dt.Clear();
    MetroFramework.MetroMessageBox.Show(this, "something");
}
else if(bar <= 0) {

    dt.Clear();
    MetroFramework.MetroMessageBox.Show(this, "empty");
}

edited Jan 08 '17 at 15:19

Peter Mortensen

30,738
21
105
131

answered Sep 24 '16 at 16:35

tendai katsande

1

score -1 · Answer 11 · edited May 16 '17 at 05:52

protected void btnsubmit_Click(object sender, EventArgs e)
{

        string s = @"SELECT * FROM tbl1 WHERE CodNo = @CodNo";
    SqlCommand cmd1 = new SqlCommand(s, con);
    cmd1.Parameters.AddWithValue("@CodNo", txtid.Text);
    con.Open();
    int records = (int)cmd1.ExecuteScalar();

    if (records > 0)
    {

        Response.Write("<script>alert('Record not Exist')</script>");

    }
    else
    {
        Response.Write("<script>alert('Record Exist')</script>"); 
     }
  }
        private void  insert_data()
{

        SqlCommand comm = new SqlCommand("Insert into tbl1(CodNo,name,lname,fname,gname,EmailID,PhonNo,gender,image,province,district,village,address,phonNo2,DateOfBirth,school,YearOfGraduation,exlanguage,province2,district2,village2,PlaceOfBirth,NIDnumber,IDchapter,IDpage,IDRecordNumber,NIDCard,Kankur1Year,Kankur1ID,Kankur1Mark,Kankur2Year,Kankur2ID,Kankur2Mark,Kankur3Year,Kankur3ID,Kankur3Mark) values(@CodNo,N'" + txtname.Text.ToString() + "',N'" + txtlname.Text.ToString() + "',N'" + txtfname.Text.ToString() + "',N'" + txtgname.Text.ToString() + "',N'" + txtemail.Text.ToString() + "','" + txtphonnumber.Text.ToString() + "',N'" + ddlgender.Text.ToString() + "',@image,N'" + txtprovince.Text.ToString() + "',N'" + txtdistrict.Text.ToString() + "',N'" + txtvillage.Text.ToString() + "',N'" + txtaddress.Value.ToString() + "','" + txtphonNo2.Text.ToString() + "',N'" + txtdbo.Text.ToString() + "',N'" + txtschool.Text.ToString() + "','" + txtgraduate.Text.ToString() + "',N'" + txtexlanguage.Text.ToString() + "',N'" + txtprovince1.Text.ToString() + "',N'" + txtdistrict1.Text.ToString() + "',N'" + txtvillage1.Text.ToString() + "',N'" + txtpbirth.Text.ToString() + "','" + txtNIDnumber.Text.ToString() + "','" + txtidchapter.Text.ToString() + "', '" + txtidpage.Text.ToString() + "','" + txtrecordNo.Text.ToString() + "',@NIDCard,'" + txtkankuryear1.Text.ToString() + "','" + txtkankurid1.Text.ToString() + "','" + txtkankurscore1.Text.ToString() + "','" + txtkankuryear2.Text.ToString() + "','" + txtkankurid2.Text.ToString() + "','" + txtkankurscore2.Text.ToString() + "','" + txtkankuryear3.Text.ToString() + "','" + txtkankurid3.Text.ToString() + "','" + txtkankurscore3.Text.ToString() + "')", con);

        flpimage.SaveAs(Server.MapPath("~/File/") + flpimage.FileName);
        string img = @"~/File/" + flpimage.FileName;
        flpnidcard.SaveAs(Server.MapPath("~/Tazkiera/") + flpnidcard.FileName);
        string img1 = @"~/Tazkiera/" + flpnidcard.FileName;

        comm.Parameters.AddWithValue("CodNo", Convert.ToInt32(txtid.Text));
        comm.Parameters.AddWithValue("image", flpimage.FileName);
        comm.Parameters.AddWithValue("NIDCard", flpnidcard.FileName);

        comm.ExecuteNonQuery();
        con.Close();

        Response.Redirect("~/SecondPage.aspx");
        //Response.Write("<script>alert('Record Inserted')</script>");

        }
    }

score -2 · Answer 12 · edited Feb 20 '18 at 17:36

-2

Use the method Int.Parse() instead. It will work.

edited Feb 20 '18 at 17:36

Raj Baral

661
6
19

answered Jan 23 '14 at 07:55

shirbr510

819
1
11
19

2

How is that could be useful for OP? – Soner Gönül Jan 23 '14 at 08:06
I tried it and it seems as the object is not really null but rather has some akward casting issue. The parsing works – shirbr510 Jan 23 '14 at 13:25

Senthuran · Answer 13 · 2020-04-25T13:12:11.277

I had a requirement to register user. In that case I need to check whether that username is already present in the database or not. I have tried the below in C# windows form application(EntityFramework) and it worked.

 var result = incomeExpenseManagementDB.Users.FirstOrDefault(x => x.userName == registerUserView.uNameText);
  if (result == null) {
      register.registerUser(registerUserView.fnameText, registerUserView.lnameText, registerUserView.eMailText, registerUserView.mobileText, registerUserView.bDateText, registerUserView.uNameText, registerUserView.pWordText);
  } else {
      MessageBox.Show("User Alreay Exist. Try with Different Username");
  }

Check if a record exists in the database

13 Answers13

Linked

Related