locked
WebMethod - how to declare objects into sql consult string RRS feed

  • Question

  • User17798125 posted

    Hi forum, 

    I have another doubt, I am trying to implement google charts, but when I tried to implement the method I found a little issue, I am selecting columns where another columns take the value from an object, in this case labels. For example: numpart = '" & Me.numpart.Text & "' , the console says that I cannot declare this in my method. 

    Is there any form to do it? Because I need that info to pull the parts that I need. Thanks!!!

    <WebMethod()>
        Public Shared Function GetChartData() As List(Of Object)
            Dim query As String = "SELECT inspT11, okT11, nokT11, retrT11 FROM stdplusreport1 WHERE numpart = '" & Me.numpart.Text & "' And emp = '" & Me.empresa.Text & "' AND rename = '" & Me.rename.Text & "'"
            Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
            Dim chartData As New List(Of Object)()
            chartData.Add(New Object() {"Issues", "Totales"})
            Using con As New SqlConnection(constr)
                Using cmd As New SqlCommand(query)
                    cmd.CommandType = CommandType.Text
                    cmd.Connection = con
                    con.Open()
                    Using sdr As SqlDataReader = cmd.ExecuteReader()
                        While sdr.Read()
                            chartData.Add(New Object() {sdr("Issues"), sdr("Totales")})
                        End While
                    End Using
                    con.Close()
                    Return chartData
                End Using
            End Using
        End Function

    Saturday, February 9, 2019 6:37 AM

Answers

  • User475983607 posted

    I have another doubt, I am trying to implement google charts, but when I tried to implement the method I found a little issue, I am selecting columns where another columns take the value from an object, in this case labels. For example: numpart = '" & Me.numpart.Text & "' , the console says that I cannot declare this in my method. 

    Web methods are shared and do not have access to class members like TextBoxes.  See the VB programming guide for language constructs.

    https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/modifiers/shared

    To solve this problem, pass the numpart, empresa, and rename values to the Web Method

    GetChartData(string numpart, string empresa, string rename)



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, February 10, 2019 12:44 PM
  • User753101303 posted

    Hi,

    A "page method" call is done out of band and doesn't go through all the ASPX page lifecycle (this is why it is shared ie it can't refer to an object instance as no page object is created for this call).

    You'll need to pass those values as parameters to your GetChartData method and pass those values from your client side script. You are using PageMethods.GetChartData or jQuery ? See for example http://aspalliance.com/1922_PageMethods_In_ASPNET_AJAX.2

    Not directly related but its best to use SQL parameters: https://csharp-station.com/Tutorial/AdoDotNet/Lesson06

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 11, 2019 4:52 PM
  • User61956409 posted

    Hi Jonsey10,

    It seems that you'd like to get user input value(s) of TextBoxes in your WebMethod (a Shared Function), as mgebhard mentioned, you can modify your code to pass user input values as parameters, like below.

    <WebMethod()>
    Public Shared Function GetChartData(numpart As String, emp As String, rename As String) As List(Of Object)
    
        'your code logic here
    
        Return chartData
    End Function

    On JavaScript client side:

    var numpart = $("#numpart").val();
    var empresa = $("#empresa").val();
    var rename = $("#rename").val();
    
    var user_inputs = { "numpart": numpart, "emp": empresa, "rename": rename };
    $.ajax({
        type: "POST",
        url: "TestPage.aspx/GetChartData",
        data: JSON.stringify(user_inputs),
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        success: function (data) {
            //your code logic here
            alert("success");
    
        }
    });

    Besides, instead of passing multiple parameters, you can modify your WebMethod to accept a custom object, like below.

    <WebMethod()>
    Public Shared Function GetChartData(user_inputs As UserInputs) As List(Of Object)
    
        'your code logic here
    
        Return chartData
    
    End Function

    On JavaScript client:

    var numpart = $("#numpart").val();
    var empresa = $("#empresa").val();
    var rename = $("#rename").val();
    
    var user_inputs = { "numpart": numpart, "emp": empresa, "rename": rename };
    $.ajax({
        type: "POST",
        url: "WebForm1.aspx/GetChartData",
        //data: JSON.stringify(user_inputs),
        data: "{'user_inputs':" + JSON.stringify(user_inputs) + "}",
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        success: function (data) {
            //your code logic here
            alert("success");
        }
    });

    Test Result:

    With Regards,

    Fei Han

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 12, 2019 7:18 AM

All replies

  • User1120430333 posted

    You'll want to prevent SQL injection attacks.

    https://en.wikipedia.org/wiki/SQL_injection

    The way you prevent the attack is to use parametrized T-SQL.

    https://tableplus.io/blog/2018/08/best-practices-to-prevent-sql-injection-attacks.html

    http://www.tutorialspanel.com/insert-update-delete-example-c-vb-net-using-executenonquery-method/

    Also in using parametrized T-SQL,  it prevents malformed T-SQL, becuase of the way you are formulating the T-SQL with adding of parms in the T-SQL statement. What if one of the parm's data  had a single-quote, an escape sequence in T-SQL,  in the data? It would cause the T-SQL to be malformed and not work, becuase of the way you are formulating the T-SQL and not dealing with a single-quote coming in the text data..

    It's just FYI below, but what you are doing to formulate the T-SQL, you are wide open to a SQL Injection attack.

    http://net-informations.com/vbprj/ado.net/single-quotes.htm

    Sunday, February 10, 2019 8:56 AM
  • User475983607 posted

    I have another doubt, I am trying to implement google charts, but when I tried to implement the method I found a little issue, I am selecting columns where another columns take the value from an object, in this case labels. For example: numpart = '" & Me.numpart.Text & "' , the console says that I cannot declare this in my method. 

    Web methods are shared and do not have access to class members like TextBoxes.  See the VB programming guide for language constructs.

    https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/modifiers/shared

    To solve this problem, pass the numpart, empresa, and rename values to the Web Method

    GetChartData(string numpart, string empresa, string rename)



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, February 10, 2019 12:44 PM
  • User753101303 posted

    Hi,

    A "page method" call is done out of band and doesn't go through all the ASPX page lifecycle (this is why it is shared ie it can't refer to an object instance as no page object is created for this call).

    You'll need to pass those values as parameters to your GetChartData method and pass those values from your client side script. You are using PageMethods.GetChartData or jQuery ? See for example http://aspalliance.com/1922_PageMethods_In_ASPNET_AJAX.2

    Not directly related but its best to use SQL parameters: https://csharp-station.com/Tutorial/AdoDotNet/Lesson06

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 11, 2019 4:52 PM
  • User61956409 posted

    Hi Jonsey10,

    It seems that you'd like to get user input value(s) of TextBoxes in your WebMethod (a Shared Function), as mgebhard mentioned, you can modify your code to pass user input values as parameters, like below.

    <WebMethod()>
    Public Shared Function GetChartData(numpart As String, emp As String, rename As String) As List(Of Object)
    
        'your code logic here
    
        Return chartData
    End Function

    On JavaScript client side:

    var numpart = $("#numpart").val();
    var empresa = $("#empresa").val();
    var rename = $("#rename").val();
    
    var user_inputs = { "numpart": numpart, "emp": empresa, "rename": rename };
    $.ajax({
        type: "POST",
        url: "TestPage.aspx/GetChartData",
        data: JSON.stringify(user_inputs),
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        success: function (data) {
            //your code logic here
            alert("success");
    
        }
    });

    Besides, instead of passing multiple parameters, you can modify your WebMethod to accept a custom object, like below.

    <WebMethod()>
    Public Shared Function GetChartData(user_inputs As UserInputs) As List(Of Object)
    
        'your code logic here
    
        Return chartData
    
    End Function

    On JavaScript client:

    var numpart = $("#numpart").val();
    var empresa = $("#empresa").val();
    var rename = $("#rename").val();
    
    var user_inputs = { "numpart": numpart, "emp": empresa, "rename": rename };
    $.ajax({
        type: "POST",
        url: "WebForm1.aspx/GetChartData",
        //data: JSON.stringify(user_inputs),
        data: "{'user_inputs':" + JSON.stringify(user_inputs) + "}",
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        success: function (data) {
            //your code logic here
            alert("success");
        }
    });

    Test Result:

    With Regards,

    Fei Han

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 12, 2019 7:18 AM