locked
return a sqldatareader and populate labels, textboxes etc RRS feed

  • Question

  • User1573072572 posted

    I am trying to populate some textboxes and labels on my page with values from a database table (sql server 2005).

    I have a class called database with a function which returns a sqldatareader (code below), it takes a parameter called JobID (uses in select query).

     Public Shared Function returndata(ByVal jobId As Integer) As SqlDataReader
            Dim conn As SqlConnection
            Dim retrieveOrder As SqlCommand
            Dim connectionString As String = ConfigurationManager.ConnectionStrings("webConn").ConnectionString
            Dim reader As SqlDataReader

            Dim ssql As String
            ssql = "select * from employabilityoffice_jobs where jobid = '" & jobId & "'"

            conn = New SqlConnection(connectionString)
            conn.Open()

            retrieveOrder = New SqlCommand(ssql, conn)
            Try
                reader = retrieveOrder.ExecuteReader(Data.CommandBehavior.CloseConnection)
            Catch ex As Exception
                Throw ex
            End Try

            Return reader
    End Function

    Then I tried something like below on a normal aspx page but I keep getting errors such as "array bounds cannot appear in type specifiers". Any help to point me in the right direction would be greatly appreciated.

            If Request.QueryString("jobID") <> "" Then
                Dim jobID As String = Request.QueryString("jobID")

                Dim reader As database.returndata(jobID)

                If reader.Read Then
                    nameLbl.Text = "Instructions"
                End If

    Tuesday, October 7, 2008 10:49 AM

Answers

  • User-334755090 posted

    a couple of things here...

    first you will want to change the following line:
    Dim reader As database.returnData(jobID)
    to something like this:
    Dim reader As SqlDataReader = database.returnData(jobID)

    that should clear up your error

    secondly... you do realize that you are sending a string to a function that requires an integer right?
    jobID in your aspx page is a string while your returnData function has jobId as an Integer
    you may want to clean that up

    thirdly... your sql statement is just screaming sql injection attack
    burn a few more lines of code and declare parameters for your SqlCommand
    the SqlCommand object has a property for parameters that you can add to

    hope that helps

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 7, 2008 3:20 PM

All replies

  • User-334755090 posted

    a couple of things here...

    first you will want to change the following line:
    Dim reader As database.returnData(jobID)
    to something like this:
    Dim reader As SqlDataReader = database.returnData(jobID)

    that should clear up your error

    secondly... you do realize that you are sending a string to a function that requires an integer right?
    jobID in your aspx page is a string while your returnData function has jobId as an Integer
    you may want to clean that up

    thirdly... your sql statement is just screaming sql injection attack
    burn a few more lines of code and declare parameters for your SqlCommand
    the SqlCommand object has a property for parameters that you can add to

    hope that helps

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 7, 2008 3:20 PM
  • User1573072572 posted

     Thanks mmeisinger, I changed the code as you suggested:

            If Request.QueryString("jobID") <> "" Then
                Dim jobID As Integer = Request.QueryString("jobID")

                Dim reader As SqlDataReader = database.returndata(jobID)

                If reader.Read Then
                    nameLbl.Text = reader("Instructions")
                End If

    Its working great now. I will look into preventing sql injection attacks, I was more worried about leaving the connection open but the Data.CommandBehavior.CloseConnection seems to be dealing with that (I hope), thanks again.

    Wednesday, October 8, 2008 4:52 AM