How to make the database able to store using ASP.NET into SQL Server

Question

Based on this code there are no error when I run but its not saving the data into the database. I tried to use only one connection string but still the bug is not fixed. But it can able to read the data from the database but not able to write into the database. I have checked my query and username and password is all correct but I don't able to know why its not storing into the database.

Here is the code:

edittemplyoees.aspx.vb:

Imports System.Data.SqlClient
Imports System.Web.Services

Public Class editemployees
    Inherits System.Web.UI.Page

    ' SQL Server connection string
    Private connectionString As String = "Data Source=10.1.32.111;Initial Catalog=sexyboy;User ID=im;Password=im"

    Private writeconnectionString As String = "Data Source=10.1.32.111;Initial Catalog=sexyboy;User ID=im;Password=im"

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            ' Load employee data from SQL Server
            LoadEmployees()
        End If

        If Request("__EVENTTARGET") = "AddEmployee" Then
            Dim data As String = Request("__EVENTARGUMENT")
            Dim employeeData As String() = data.Split("|"c)
            Dim employeeNo As String = employeeData(0)
            Dim employeeName As String = employeeData(1)
            Dim badgeNumber As String = employeeData(2)

            ' Call the method to insert the data into the database
            AddEmployee(employeeNo, employeeName, badgeNumber)
        End If
    End Sub

    ' Load employees from SQL Server and bind to the table
    Private Sub LoadEmployees()
        Dim employeesTable As New DataTable()

        Using connection As New SqlConnection(connectionString)
            Dim query As String = "SELECT employee_visa, employee_name, badgenumber FROM ERTemployees"
            Dim command As New SqlCommand(query, connection)

            Try
                connection.Open()
                Using reader As SqlDataReader = command.ExecuteReader()
                    employeesTable.Load(reader)
                End Using
            Catch ex As Exception
                ' Handle exception
            End Try
        End Using

        ' Store the employees table in ViewState for access in the HTML markup
        ViewState("EmployeesTable") = employeesTable
    End Sub


    ' Handle the Add button click event
    Private Sub AddEmployee(ByVal employeeNo As String, ByVal employeeName As String, ByVal badgeNumber As String)
        ' Insert the employee into the database
        Using connection As New SqlConnection(writeconnectionString)
            Dim query As String = "INSERT INTO ERTemployees (employee_visa, employee_name, badgenumber) VALUES (@EmployeeNo, @EmployeeName, @BadgeNumber)"
            Dim command As New SqlCommand(query, connection)
            command.Parameters.AddWithValue("@EmployeeNo", employeeNo)
            command.Parameters.AddWithValue("@EmployeeName", employeeName)
            command.Parameters.AddWithValue("@BadgeNumber", badgeNumber)

            Try
                connection.Open()
                command.ExecuteNonQuery()
                Response.Write("Employee added successfully!")
            Catch ex As Exception
                Response.Write("Error: " & ex.Message)
            End Try
        End Using

        ' Reload the employee data from SQL Server
        LoadEmployees()
    End Sub

    'Edit employee function
    Private Sub UpdateEmployee(ByVal employeeID As String, ByVal employeeName As String, ByVal badgeNumber As String)
        ' Update the employee in the database
        Using connection As New SqlConnection(writeconnectionString)
            Dim query As String = "UPDATE ERTemployees SET employee_visa = @EmployeeID, employee_name = @EmployeeName WHERE badgenumber = @BadgeNumber"
            Dim command As New SqlCommand(query, connection)
            command.Parameters.AddWithValue("@EmployeeID", employeeID)
            command.Parameters.AddWithValue("@EmployeeName", employeeName)
            command.Parameters.AddWithValue("@BadgeNumber", badgeNumber)

            Try
                connection.Open()
                Dim rowsAffected As Integer = command.ExecuteNonQuery()
                If rowsAffected > 0 Then
                    Response.Write("Employee updated successfully!")
                Else
                    Response.Write("No employee found with the provided badge number.")
                End If
            Catch ex As Exception
                Response.Write("Error: " & ex.Message)
            End Try
        End Using
    End Sub

    'Delete function 

    <WebMethod>
    Public Shared Sub DeleteEmployee(ByVal employeeID As String)
        ' Delete the employee from the database
        Using connection As New SqlConnection("Data Source=10.1.32.111;Initial Catalog=sexyboy;User ID=im;Password=im")
            Dim query As String = "DELETE FROM ERTemployees WHERE employee_visa = @EmployeeID"
            Dim command As New SqlCommand(query, connection)
            command.Parameters.AddWithValue("@EmployeeID", employeeID)

            Try
                connection.Open()
                command.ExecuteNonQuery()
            Catch ex As Exception
                ' Handle exception
            End Try
        End Using
    End Sub

End Class

editemployees.aspx:

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="editemployees.aspx.vb" Inherits="ERT.editemployees" %>
<%@ Import Namespace="System.Data" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>EditEmployees</title>
    <link rel="stylesheet" href="login_style.css">
    <link rel="stylesheet" href="editemployees.css">
</head>
<body>
    <form runat="server" onsubmit="return false;" id="form1">

        <div>
            <div class="group">
                <svg class="icon" aria-hidden="true" viewBox="0 0 24 24"><g><path d="M21.53 20.47l-3.66-3.66C19.195 15.24 20 13.214 20 11c0-4.97-4.03-9-9-9s-9 4.03-9 9 4.03 9 9 9c2.215 0 4.24-.804 5.808-2.13l3.66 3.66c.147.146.34.22.53.22s.385-.073.53-.22c.295-.293.295-.767.002-1.06zM3.5 11c0-4.135 3.365-7.5 7.5-7.5s7.5 3.365 7.5 7.5-3.365 7.5-7.5 7.5-7.5-3.365-7.5-7.5z"></path></g></svg>
                <input placeholder="Search" type="search" class="input" onkeyup="searchTable()">

            </div>
        </div>
        <div class="btn">
            <button class="button2" onclick="AddEmployee()">Add</button>
            <button class="button3" onclick="editEmployee()">Edit</button>
            <button class="button4" onclick="deleteEmployee()">Delete</button>
        </div>
        <div class="table">
            <table class="styled-table" width="100%" cellspacing="0">
    <thead>
        <tr>
            <th>Employee ID</th>
            <th>Name</th>
            <th>Badge Number</th>
        </tr>
    </thead>
        <tbody id="employeesPlaceholder" class="styled-table">
    <% For Each row As DataRow In DirectCast(ViewState("EmployeesTable"), DataTable).Rows %>
        <tr onclick="selectRow(this)">
            <td><%= row("employee_visa") %></td>
            <td><%= row("employee_name") %></td>
            <td><%= row("badgenumber") %></td>
        </tr>
    <% Next %>
</tbody>

</table>

</div>

        <script>
            function AddEmployee() {
                var employeeNo = prompt("Enter Employee ID:");
                var employeeName = prompt("Enter Employee Name:");
                var badgeNumber = prompt("Enter Badge Number:");

                if (employeeNo && employeeName && badgeNumber) {
                    var data = employeeNo + '|' + employeeName + '|' + badgeNumber;
                    __doPostBack('AddEmployee', data);
                } else {
                    alert("Please enter all employee details.");
                }
            }
        </script>

        <script>

            // Call the LoadEmployees function when the page is loaded
            window.onload = function () {
                LoadEmployees();
            };

            function searchTable() {
                var input = document.querySelector('.input');
                var filter = input.value.toUpperCase();
                var table = document.querySelector('.styled-table');
                var rows = table.getElementsByTagName('tr');

                for (var i = 0; i < rows.length; i++) {
                    var td1 = rows[i].getElementsByTagName('td')[0]; // Employee ID column
                    var td2 = rows[i].getElementsByTagName('td')[1]; // Name column
                    var td3 = rows[i].getElementsByTagName('td')[2]; // Badge Number column
                    if (td1 || td2 || td3) {
                        var textValue1 = td1.textContent || td1.innerText;
                        var textValue2 = td2.textContent || td2.innerText;
                        var textValue3 = td3.textContent || td3.innerText;
                        if (textValue1.toUpperCase().indexOf(filter) > -1 || textValue2.toUpperCase().indexOf(filter) > -1 || textValue3.toUpperCase().indexOf(filter) > -1) {
                            rows[i].style.display = '';
                        } else {
                            rows[i].style.display = 'none';
                        }
                    }
                }
            }
        </script>
        <script>
            function selectRow(row) {
                var rows = document.querySelectorAll('tr');
                for (var i = 0; i < rows.length; i++) {
                    rows[i].classList.remove('selected-row');
                }
                row.classList.add('selected-row');
            }


            function editEmployee() {
                var selectedRow = document.querySelector('.selected-row');
                if (selectedRow) {
                    var employeeID = selectedRow.cells[0].innerText;
                    var employeeName = selectedRow.cells[1].innerText;
                    var badgeNumber = selectedRow.cells[2].innerText;
                    var updatedEmployeeID = prompt("Edit Employee ID:", employeeID);
                    var updatedEmployeeName = prompt("Edit Employee Name:", employeeName);
                    var updatedBadgeNumber = prompt("Edit Badge Number:", badgeNumber);

                    if (updatedEmployeeID && updatedEmployeeName && updatedBadgeNumber) {
                        selectedRow.cells[0].innerText = updatedEmployeeID;
                        selectedRow.cells[1].innerText = updatedEmployeeName;
                        selectedRow.cells[2].innerText = updatedBadgeNumber;
                        saveEmployeeEdits(updatedEmployeeID, updatedEmployeeName, updatedBadgeNumber);
                    } else {
                        alert("Please enter all employee details.");
                    }
                } else {
                    alert("Please select a row to edit.");
                }
            }

            
                function saveEmployeeEdits(employeeID, employeeName, badgeNumber) {
                    // Create a new XMLHttpRequest object
                var xhr = new XMLHttpRequest();

                // Define the request URL and method
                var url = "editemployees.aspx/UpdateEmployee";
                var method = "POST";

                // Create the request parameters object
                var params = {
                    employeeID: employeeID,
                employeeName: employeeName,
                badgeNumber: badgeNumber
        };

                // Convert the parameters object to JSON
                var jsonParams = JSON.stringify(params);

                // Set the appropriate headers
                xhr.setRequestHeader("Content-Type", "application/json");

                // Handle the AJAX response
                xhr.onreadystatechange = function () {
            if (xhr.readyState === XMLHttpRequest.DONE) {
                if (xhr.status === 200) {
                    // Success: Employee updated successfully
                    alert("Employee updated successfully!");
                } else {
                    // Error: Display the error message
                    alert("Error: " + xhr.responseText);
                }
            }
        };

                // Open and send the request
                xhr.open(method, url, true);
                xhr.send(jsonParams);
            }

            function deleteEmployee() {
                var selectedRow = document.querySelector('.selected-row');
                if (selectedRow) {
                    var confirmation = confirm("Are you sure you want to delete this employee?");
                    if (confirmation) {
                        selectedRow.remove();
                        var employeeID = selectedRow.cells[0].innerText;
                        deleteEmployeeFromDatabase(employeeID);
                        alert("Employee deleted successfully!");
                    }
                } else {
                    alert("Please select a row to delete.");
                }
            }

            function deleteEmployeeFromDatabase(employeeID) {
                // Create a new XMLHttpRequest object
                var xhr = new XMLHttpRequest();

                // Define the request URL and method
                var url = "editemployees.aspx/DeleteEmployee";
                var method = "POST";

                // Create the request parameters object
                var params = {
                    employeeID: employeeID
                };

                // Convert the parameters object to JSON
                var jsonParams = JSON.stringify(params);

                // Set the appropriate headers
                xhr.setRequestHeader("Content-Type", "application/json");

                // Handle the AJAX response
                xhr.onreadystatechange = function () {
                    if (xhr.readyState === XMLHttpRequest.DONE) {
                        if (xhr.status === 200) {
                            // Success: Employee deleted successfully
                            alert("Employee deleted successfully!");
                        } else {
                            // Error: Display the error message
                            alert("Error: " + xhr.responseText);
                        }
                    }
                };

                // Open and send the request
                xhr.open(method, url, true);
                xhr.send(jsonParams);
            }
        </script>

    </form>
</body>
</html>

I tried to change into one connection string but still wasn't working but it can able to read but not able to write or store into the database

Answer 1

I had to use JSON in order to retrieve the server to the client side here is the fixed code put this in the add,edit and delete below before closing and this will work

      // Open and send the request
        xhr.open(method, url, true);
        xhr.setRequestHeader("Content-Type", "application/json");
        xhr.send(JSON.stringify(params));