Check duplicate value in the database

Question

Before I save value from TextBox into the database I need to check if that value already exists in the database.

This is the TextBox code:

 <tr>
        <td>
            <asp:Label ID="lblProductConstruction" runat="server" Text="Product Construction:" Font-Names="Open Sans"></asp:Label></td>
        <td>
            <asp:TextBox ID="txtProductConstruction" runat="server"  Font-Names="Merriweather" margin-Left="100px" ></asp:TextBox><br />
        </td>
    </tr>
    <tr>

Save button:

<input type="button" class="button" id="myButton" value="Save"/>

Ajax on button click:

 $(function () {

             $('#myButton').on('click', function () {

                 var lvl = $('#MainContent_txtProductConstruction').val()

                 $.ajax({
                     type: "POST",
                     url: "NewProductConstruction.aspx/GetCollection",

                     data: JSON.stringify({'lvl': lvl }),

                     contentType: "application/json; charset=utf-8",
                     dataType: "json",

                     success: function (response) {
                         alert("Saved successfully.");
                         console.log(response);
                         location.reload(true);

                     },
                     error: function (response) {
                         alert("Not Saved!");
                         console.log(response);
                         location.reload(true);
                     }

                 });

             });

         });

WebMethod that takes the value and sends that parameter(@LvlName) to the database:

[WebMethod(EnableSession = true)]
        public static void GetCollection(string lvl)
        {

            //string strMsg = "";
            string conn = ConfigurationManager.ConnectionStrings["Connection"].ConnectionString;
            using (SqlConnection connection = new SqlConnection(conn))
                try
                {
                    connection.Open();
                    SqlCommand cmdProc = new SqlCommand("InsertLvlName", connection);
                    cmdProc.CommandType = CommandType.StoredProcedure;
                    cmdProc.Parameters.AddWithValue("@LvlName", lvl);
                    cmdProc.ExecuteNonQuery();
                    //strMsg = "Saved successfully.";

                }
                catch
                {

                }
                finally
                {
                    connection.Close();

                }

            return;

        }

I need help to check two things: 1) To see if textbox is empty, and if this is the case then don't save the value to the database and show alert that this field needs to have some kind of a value.

2) I need some kind of a check if the same value of a field is already in the database then don't save it.

Thanks in advance !

Answer 1

This is the Simple Validation We can do using jquery

if (inp.val().length > 0) {
    //do something
}
else
{
alert("Enter Value")
}

Full Example:-

 $(function () {

         $('#myButton').on('click', function () {

             var lvl = $('#MainContent_txtProductConstruction').val()
                if(lvl.length>0)
                {
             $.ajax({
                 type: "POST",
                 url: "NewProductConstruction.aspx/GetCollection",

                 data: JSON.stringify({'lvl': lvl }),

                 contentType: "application/json; charset=utf-8",
                 dataType: "json",

                 success: function (response) {
                     alert("Saved successfully.");
                     console.log(response);
                     location.reload(true);

                 },
                 error: function (response) {
                     alert("Not Saved!");
                     console.log(response);
                     location.reload(true);
                 }
             });
             }
            else
            {
            alert("Please enter Value")
            }
         });

     });

Second Part:-

SqlCommand checkvalue = new SqlCommand("SELECT COUNT(*) FROM [TableName] WHERE ([ColumnNameUser] = @user)" , connection);
checkvalue.Parameters.AddWithValue("@user", lvl);
int UserExist = (int)checkvalue.ExecuteScalar();

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

Reference Link

If you Want Sp to check then:-

Edit it based on your name and field names.

CREATE PROCEDURE InsertName
(
  @username varchar(25), 
  @userpassword varchar(25)
)
AS
IF EXISTS(SELECT 'True' FROM MyTable WHERE username = @username)
BEGIN
  --This means it exists, return it to ASP and tell us
  SELECT 'This record already exists!'
END
ELSE
BEGIN
  --This means the record isn't in there already, let's go ahead and add it
  SELECT 'Record Added'
  INSERT into MyTable(username, userpassword) VALUES(@username, @userpassword)
END

Answer 2

First you will want to probably use jQuery to check if your textbox is empty and if it is then do not fire off the call to the webmethod. See Accessing Asp.net controls using jquery (all options) for calling asp.net controls from jQuery

if ($('#<%= myTextBox.ClientID %>').val().length == 0) 
{
   alert("Text Box is empty");
}
else
{
    ///make ajax call to webmethod...
}

Side note what do u want to happen if the user enters a space in the textbox?

Next you could either make a call to insert or update the record in the db so as to not insert any duplicates. Or I would probably want to select all data from the db and then comparing the data to see if the entry from the textbox already exists in the db.

This answer here should be helpful c# update if record exists else insert new record

Something like this should give you the result you need:

SqlCommand cmdCount = new SqlCommand("SELECT * from Table WHERE TextboxValue= @textBoxValue", myConnection);
cmdCount.Parameters.AddWithValue("@textBoxValue", _textBoxValue);
int count = (int)cmdCount.ExecuteScalar();

if (count > 0)
{
    ///Run Insert statement
}