SQL query using Ajax

I'm currently working on an Ajax call using a query string, but I've hit a roadblock midway through. My goal is to update a SQL table with the JavaScript sortable function. After an item has been moved up or down, I want to pass it through Ajax and have it updated in the SQL database. Any assistance would be greatly appreciated.

  
                $("#tableBody").sortable({
                    connectWith: "#tableBody",
                    update: function (event, ui) {
                        // this.id = #tableBody
                        //  ui.item = Dragged item
                        console.log(ui.item.attr("data-id"));
                        console.log(ui.item.attr("data-prod-id"));
                        console.log(ui.item.index());
                        $.get('shop_prod_options_update_sort_order.aspx?id=' + id + '&prodid=' + prodid + '&sortorder=' + sortorder);
                    }
                });
            
<Script Language="VB" RunAt="Server">
                Dim intOptionId As Integer = 1
                Dim intProdId As Integer = 1
                Dim intNewSortOrder As Integer = 0
                Dim intOldSortOrder As Integer = 0

                Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

                    If IsNumeric(Request.QueryString("id")) Then
                        intOptionId = Val(Request.QueryString("id"))
                    End If

                    If Request.QueryString("sortorder") = "sortorder" Then
                        intOptionId = True
                    Else
                        intOptionId = False
                    End If

                    If Not IsPostBack Then
                        UpdateList()
                    End If
                End Sub

                Private Sub UpdateList()

                    ' Database connection and SQL queries here

                End Sub
            </script>

            <html xmlns="http://www.w3.org/1999/xhtml">
                <head id="Head1" runat="server">
                </head>

                <body>
                    <form id="form1" runat="server">
                        <asp:ScriptManager ID="ScriptManager1" runat="server" />
                    </form>
                </body>
            </html>
            

Answer №1

While I may not have all the details on the Page_Load function, I did clean up the UpdateList() method.

I consolidated the connection and command within a single Using block for efficiency.

Instead of using .AddWithValue, I switched to the .Add method after researching its benefits at this source and this article, as well as another helpful link: here Here is another useful resource: here

I rearranged the order by moving the .Open connection right before the .Execute step.

To adhere to Option Strict, I included CInt when dealing with the .ExecuteScalar method's object return value.

Your Update syntax has been corrected for accuracy.

For executing an update command, remember to use .ExecuteNonQuery instead of .ExecuteScalar.

The redundant Try...Catch...Finally block was removed since it served no purpose except suppressing errors. The functionality of

Database.Disconnect(connDBConnection)
in the context of your Using blocks remains unclear.

Dim intOptionId As Integer = 1
Dim intProdId As Integer = 1
Dim intNewSortOrder As Integer = 0
Dim intOldSortOrder As Integer = 0

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Integer.TryParse(Request.QueryString("id"), intOptionId)
    
    If Not IsPostBack Then
        UpdateList()
    End If
End Sub

Private Sub UpdateList()
    Dim strSQLQuery = "SELECT option_order FROM shop_option WHERE option_id = @option_id"
    Using connConnnection As New SqlConnection(Database.GetDatabaseConnString),
                commCommand As New SqlCommand(strSQLQuery, connConnnection)
        commCommand.Parameters.Add("@option_id", SqlDbType.Int).Value = intOptionId
        connConnnection.Open()
        intOldSortOrder = CInt(commCommand.ExecuteScalar())
    End Using
    
    If intOldSortOrder > intNewSortOrder Then
        strSQLQuery = "UPDATE shop_option SET option_order = option_order + 1 WHERE option_id = @option_id"
    Else
        strSQLQuery = "UPDATE shop_option SET option_order = option_order - 1 WHERE option_id = @option_id;"
    End If
    
    Using connConnnection As New SqlConnection(Database.GetDatabaseConnString),
                commCommand As New SqlCommand(strSQLQuery, connConnnection)
        commCommand.Parameters.Add("@option_id", SqlDbType.Int).Value = intOptionId
        connConnnection.Open()
        commCommand.ExecuteNonQuery()
    End Using

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Error: React-Redux unable to locate Redux context value while utilizing useSelector function

After creating a store and authreducer, everything was working as expected. However, when I added the useSelector in app.js, an error occurred: ERROR Error: could not find react-redux context value; please ensure the component is wrapped in a <Provid ...

What issue lies within this particular for loop?

Hey everyone, I'm having trouble with my code that involves appending an array to the DOM. Here's what I have: function inputFeedContent(data){ for(var k=0; k < columns; k++) { var col = "<div class='col-1'>"; ...

problem with the clientHeight attribute in window event handlers

The component below is designed to react to changes in window resize based on the container height. However, there seems to be an issue where the containerHeight variable increases as the window size decreases, but does not decrease when I make the window ...

Extract a section of the table

I'm looking to copy an HTML table to the clipboard, but I only want to include the rows and not the header row. Here is the structure of the table: <table style="width:100%" #table> <tr> <th class="border"></th> ...

Transform the selected component in Material-UI from being a function to a class

Hello, I am currently learning about React and I have started using the select button from material-ui. The code I found on the material ui page is for a functional component, but I am more comfortable with class components. Here is the code snippet provid ...

Error occurs when running Visual Studio Code locally - variable not defined

After successfully going through a Microsoft online demo on setting up an httpTrigger in Visual Studio Code with JavaScript to upload it to Azure Functions, I decided to customize the code for a specific calculation. I managed to get the calculation done a ...

Ensure the camera flag remains set in three.js

I am currently experimenting with the WebGLRenderer in three.js and I am looking for a way to keep the camera in motion without resetting, similar to how it is shown in this video at the 4:00 minute mark. You can view the video here: https://www.youtube.c ...

Is there a way to load an image onto the ngx-image-cropper without triggering the imageChangedEvent event?

My latest project involved creating a custom cropper using ngx-image-cropper, which allows for cropping and rotating images. For the sprint demo, I needed the images to be displayed as soon as the application loads without having to trigger the fileChangeE ...

Modifying numerous array keys/names in JavaScript

Alright, I have an array named arrayObj containing three objects: arrayObj[0], arrayObj[1], and arrayObj[2]. All of these objects have the key name["user"] repeated three times. To change these key names using a function, here's what I've come up ...

Can JSON-formatted JavaScript variables be inserted and utilized in MongoDB?

I am currently in the process of formatting a large JavaScript file so that I can insert it into a MongoDB collection and retrieve it using AJAX. However, I am encountering issues with the syntax. Here is an example snippet: var MyData = [ { Elements: ...

Achieving Vertical Centering of Text in Bootstrap 5 Buttons with Flex Box to Prevent Overlapping Icons

How can I prevent the text on a Bootstrap 5 button with horizontally and vertically centered text, along with a right aligned icon, from overlapping when it wraps? Additionally, how do I ensure that the icon stays vertically centered when the button text w ...

Having trouble with the addEventListener function not working on a vanilla JS form?

I'm struggling to get a form working on a simple rails project. The idea is to collect a name and date from the form and display them on the same page using JS. Later, I'll process this information to determine if it's your birthday and cal ...

What is the best way to implement the useCallback hook in Svelte?

When utilizing the useCallback hook in React, my code block appears like this. However, I am now looking to use the same function in Svelte but want to incorporate it within a useCallback hook. Are there any alternatives for achieving this in Svelte? con ...

Trouble with incrementing JavaScript dictionary values within a nested for loop

I am currently working on analyzing shark attack data with d3.js using a csv file named shark.csv. I have encountered a problem while implementing a nested for-loop in my code. The csv file contains information about shark attacks categorized by continent ...

Connect main data to sub-component

Example Vue Structure: <Root> <App> <component> Main function in main.js: function() { axios.get('/app-api/call').then(function (resp, error) { _this.response = resp.data; }) ...

How to showcase an image with Angular 2?

Just starting out with Angular 2 and ran into an issue while trying to display an image using a relative path. <img src="./../images/publicVideo1.PNG"> Unfortunately, I encountered the following error: null:1 GET http://localhost:4200/null 404 (No ...

What is the process for obtaining the updated file name of a file that has been saved using ajax AsyncFile

When utilizing the asyncfileupload ajax plugin from the ajax toolkit to save a file, I am running into an issue where the filename needs to be changed to prevent duplicates. To inform the user of the new filename after upload, I have implemented the follo ...

Two components, one scroll bar, both moving within the same plane

For those interested, here is the JSFiddle link for further exploration: https://jsfiddle.net/q6q499ew/ Currently, there is a basic functionality in place where when you scroll past a certain point, an element becomes stuck until you start scrolling back ...

Using Vue's computed property setter with an object as a prop

I have a new concept for an input component where different objects can be passed, displayed as CSV, and allow for text editing/validation with style changes based on validation results. Check out the code snippet I've been working on: <div id=&quo ...

Modify the path name in Next.js according to the local environment

In my upcoming next.js project, I aim to dynamically alter the URL according to the language selection of the user. By default, the URL for the English language is set to: http://localhost:3000/en/privacy-policy If the language is switched from English ...