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  gt  0         Update record  else         Insert record

User · Answer

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

User · Answer

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  gt  0         Username exist   else        Username doesn t exist

User · Answer

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  gt  0                  Username Exist

User · Answer

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   gt  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

User · Answer

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  gt  0                 Response Write   lt script gt alert  Record not Exist   lt  script gt                else               Response Write   lt script gt alert  Record Exist   lt  script gt                         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   lt script gt alert  Record Inserted   lt  script gt

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt  0           Exist   else          Add

User · Answer

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

User · Answer

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

User · Answer

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  gt   1        dt Clear        MetroFramework MetroMessageBox Show this   something      else if bar  lt   0         dt Clear        MetroFramework MetroMessageBox Show this   empty

User · Answer

Use the method Int Parse   instead  It will work

[c#] Check if a record exists in the database

Examples related to c#

Examples related to winforms

Examples related to sqlcommand