locked
SQL column not found when using parameters. RRS feed

  • Question

  • User1079729179 posted

    Hello,

    I have the code below to look up a user name.  I am trying to use parameters to strengthen against SQL injections.  When I run the code below, I get the error:  ERROR [42S22] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0206 - Column or global variable @INUSR not found. This happens on the command.executereader line. 

    Any help as to what I am doing wrong would greatly be appreciated.

    Thank you in advance.

    		dim inName as string = "Roland"       
    		Dim querystring As String = "SELECT * FROM dbfile WHERE usertype = @inUSR"
    
                Using connection As New OdbcConnection(ConfigurationManager.ConnectionStrings("myConnection").ConnectionString)
                    Dim command As New OdbcCommand(querystring, connection)
                    command.Parameters.Add("@inUSR", System.Data.SqlDbType.Text).Value = Trim(inName)
                    connection.Open()
                    command.ExecuteReader()
                     reader.Read()
                    Session("access") = reader("usracc")
                End Using

    Wednesday, March 8, 2017 2:10 PM

Answers

  • User527778624 posted

    Hi,

    When using ODBC connection, use ? as placeholders for parameters but not @name.

    Also order of parameter passing is important.

    Check this site for code:

    http://stackoverflow.com/questions/1535994/asp-net-odbc-query-with-parameters

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 8, 2017 2:36 PM
  • User-1509636757 posted

    I am access files on an IBMi

    In that case you simply need to define parameters with ? mark as below:

    dim inName as string = "Roland"       
    Dim querystring As String = "SELECT * FROM dbfile WHERE usertype = ?"
    
                Using connection As New OdbcConnection(ConfigurationManager.ConnectionStrings("myConnection").ConnectionString)
                    Dim command As New OdbcCommand(querystring, connection)
                    command.Parameters.Add("inUSR", System.Data.SqlDbType.Text).Value = Trim(inName)
                    connection.Open()
                    command.ExecuteReader()
                     reader.Read()
                    Session("access") = reader("usracc")
                End Using

    Please note that order of parameter matters!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 8, 2017 2:36 PM

All replies

  • User-1509636757 posted

    If you are working with Sql Server then I would suggest you use SqlConnection instead of ODBC. Here is an example:

            Dim inName As String = "Roland"
            Dim querystring As String = "SELECT * FROM dbfile WHERE usertype = @inUSR"
    
            Using connection As New SqlConnection(ConfigurationManager.ConnectionStrings("myConnection").ConnectionString)
                Using command As New SqlCommand(querystring, connection)
                    command.Parameters.Add(New SqlParameter("@inUSR", Trim(inName)))
                    connection.Open()
                    command.ExecuteReader()
                    reader.Read()
                    Session("access") = reader("usracc")
                End Using
            End Using

    Wednesday, March 8, 2017 2:18 PM
  • User1079729179 posted

    I am not working with SQL server...I am access files on an IBMi - that is why I have the connection set that way.

    Thanks.

    Wednesday, March 8, 2017 2:25 PM
  • User527778624 posted

    Hi,

    When using ODBC connection, use ? as placeholders for parameters but not @name.

    Also order of parameter passing is important.

    Check this site for code:

    http://stackoverflow.com/questions/1535994/asp-net-odbc-query-with-parameters

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 8, 2017 2:36 PM
  • User-1509636757 posted

    I am access files on an IBMi

    In that case you simply need to define parameters with ? mark as below:

    dim inName as string = "Roland"       
    Dim querystring As String = "SELECT * FROM dbfile WHERE usertype = ?"
    
                Using connection As New OdbcConnection(ConfigurationManager.ConnectionStrings("myConnection").ConnectionString)
                    Dim command As New OdbcCommand(querystring, connection)
                    command.Parameters.Add("inUSR", System.Data.SqlDbType.Text).Value = Trim(inName)
                    connection.Open()
                    command.ExecuteReader()
                     reader.Read()
                    Session("access") = reader("usracc")
                End Using

    Please note that order of parameter matters!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 8, 2017 2:36 PM
  • User1079729179 posted

    Thats it....thank you both.

    Wednesday, March 8, 2017 3:10 PM