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