How do I assign the value I queried to a String which is an argument in a procedure if the argument's data type is an Object?

Question

I made this procedure to help me re-use a select query:

Private Sub selectQry(ByVal myColumn As String, ByVal myTable As String, ByVal myFilter As String, ByVal myObjectOne As Object, ByVal myObj As Object)
    Dim qrySlctCaldate As String = "SELECT " + myColumn + " FROM " + myTable + " WHERE " + myFilter + " = '" & Replace(myObjectOne, "'", "''") & "'"
    Dim cmdSlct As New SqlCommand(qrySlctCaldate, transConn.Connection)


    Dim readSCalDate As SqlDataReader
    readSCalDate = cmdSlct.ExecuteReader

    While readSCalDate.Read
        If TypeOf (myObj) Is TextBox Or TypeOf (myObj) Is ComboBox Then
            myObj.Text = readSCalDate.Item(myColumn).ToString
        Else
            myObj = readSCalDate.Item(myColumn).ToString

        End If
    End While
    readSCalDate.Close()

And I use it like this if I would want the selected value placed in a textbox and it works fine

selectQry("ProcConvDescription", "Line", "LineCode", nameValue.Value, txtProcess)

However if I want the value to be passed in a string like so:

 selectQry("LastCalibrationDate", "EquipmentItem", "ControlNo", txtControlNo.Text, strCalDate)

The string ends up having an empty string value. How do I assign the value I queried to that String?

score 2 · Accepted Answer · answered Dec 05 '11 at 13:55

You should modify your method to be a function returning the string rather than trying to assign it inside the function.

Private Function selectQry(ByVal myColumn As String, ByVal myTable As String, 
                           ByVal myFilter As String, ByVal myObjectOne As Object
                          ) As String
    Dim qrySlctCaldate As String = _
        String.Format("SELECT {0} FROM {1} WHERE {2} = '{3}'",
                      myColumn, myTable, myFilter,
                      Replace(myObjectOne, "'", "''"))
    Dim cmdSlct As New SqlCommand(qrySlctCaldate, transConn.Connection)

    Using readSCalDate As SqlDataReader = cmdSlct.ExecuteReader
        While readSCalDate.Read
            Return readSCalDate.Item(myColumn).ToString
        End While
    End Using
End Sub

txtProcess.Text = selectQry("ProcConvDescription", "Line", "LineCode", nameValue.Value)
strCalDate = selectQry("LastCalibrationDate", "EquipmentItem", "ControlNo", txtControlNo.Text)

Also, I would highly recommend turning on Option Strict to get better type safety.

Why I did not think this of this is beyond me. Thank you! But how come I can't assign the value to the obj which is a string — Kurusu, Dec 05 '11 at 14:08
@Kurusu The assignment you were doing is only applying to the local variable. To affect the calling method's variable, you need to pass ByRef as Bryce suggested. — Gideon Engelberth, Dec 05 '11 at 16:26

score 1 · Answer 2 · answered Dec 05 '11 at 13:51

Try passing myObj by reference:

ByRef myObj As Object)

...and see if that helps.

And while I do see the value of being able to build this query dynamically, you should really adjust your WHERE clause to use sql parameters instead (protects against SQL injection).

" WHERE " + myFilter + " = @objectOne "
cmdSlct.Parameters.Clear()
cmdSlct.Parameters.AddWithValue("objectOne",myObjectOne)

How do I assign the value I queried to a String which is an argument in a procedure if the argument's data type is an Object?

2 Answers2

Linked