How to pass dynamic parameter from SQL query to the SqlCommand after user input on the SQL query

Question

all. I am facing an issue on how to pass the dynamic parameter value from SQL query, that will be entered by the user into the textbox, to the SQL command to search on the parameter value on the datagridview datatable.

For my project, a textbox will be provided for the user to key in the SQL query dynamically to search on the database data. If the user keys in SQL query like

select * 
from table 
where a = @a

The user can search on @a parameter value; if the user keys in SQL query like

select * 
from table 
where a = @a and b = @b

The user can search the @a and @b parameter values by using textbox, which means that the parameter number that had been entered by the user needs to be calculated, retrieved, passed to the SQL command and allow the user to filter on the parameter by using textbox provided.

However, currently, due to the @a parameter and @b parameter will be key in by the user dynamically during runtime, so I faced difficulty to declare/define the parameter name on the cmd.Parameters.AddWithValue() statement.

Can anyone help me to solve my problem by providing me some solutions on codes? I had been stuck on this issue for a few days already. Thank you for all the help!

The code I had tried:

Private Sub btn1_Click(sender As Object, e As EventArgs) Handles btn1.Click
    Sql = TextBox4.Text
    Try
        'open database
        Dim con As New SqlConnection(dbstring)
        con.Open()
        Dim cmd As New SqlCommand(Sql, con)
        If param IsNot Nothing Then
            For Each para As SqlParameter In param
                'cmd.Parameters.Add(para)
                For m As Integer = 0 To param.Count - 1
                    cmd.Parameters.Add(New SqlParameter With {.ParameterName = para.ParameterName(m),
                                                              .Value = para.Value(m),
                                                              .SqlDbType = SqlDbType.NVarChar,
                                                              .Size = 99})
                Next
                cmd.ExecuteNonQuery()
            Next
        End If
        Using sda = New SqlDataAdapter()
            sda.SelectCommand = cmd
            cmd.CommandText = Sql
            Sql = cmd.ExecuteScalar()
            Using ds As DataSet = New DataSet()
                sda.Fill(ds)
                con.Close()
                DataGridView1.DataSource = ds.Tables(0)
            End Using
        End Using
        con.Close()
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

Screenshot of Window App Formhad been provided as a reference.

Answer 1

I am not sure why you would want the user to write Select statements. I assume this query is on a single table where you know the field names, types and field sizes where applicable. I made up the these items. You will have to check your database to get the correct information.

I used Optional parameters in case you have other datatypes like Booleans or numbers where you can supply the defaults. To pass no value for the parameter just leave a blank but insert the appropriate commans.

Private Function GetSearchResults(Optional FirstName As String = "", Optional LastName As String = "") As DataTable
    Dim dt As New DataTable
    Using con As New SqlConnection(dbstring),
            cmd As New SqlCommand()
        Dim sb As New StringBuilder
        sb.Append("Select * From SomeTable Where 1 = 1")
        If FirstName <> "" Then
            cmd.Parameters.Add("@a", SqlDbType.NVarChar, 100).Value = FirstName
            sb.Append(" And FirstName = @a")
        End If
        If LastName <> "" Then
            cmd.Parameters.Add("@b", SqlDbType.NVarChar, 100).Value = LastName
            sb.Append(" And LastName = @b ")
        End If
        sb.Append(";")
        Debug.Print(sb.ToString)
        cmd.CommandText = sb.ToString
        cmd.Connection = con
        con.Open()
        dt.Load(cmd.ExecuteReader)
    End Using
    Return dt
End Function

Alternative useage:

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    Dim dt = GetSearchResults(TextBox1.Text, TextBox2.Text)
    DataGridView1.DataSource = dt
End Sub
Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
    Dim dt = GetSearchResults(, TextBox2.Text)
    DataGridView1.DataSource = dt
End Sub

Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
    Dim dt = GetSearchResults(TextBox1.Text, )
    DataGridView1.DataSource = dt
End Sub

Answer 2

To avoid SQL Injection, im usally doing like this:

    Dim text1 As String = "text1"
    Dim objComm As SqlCommand = New SqlCommand
    objComm.Parameters.AddWithValue("@text1", text1)
    objComm.CommandText = "SELECT * FROM TABLE WHERE Text1 = @text1"