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

See more:

VB

SQL

DataGridView

Parameter

, +

Hi, all. I am a new VB.NET beginner. 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=@ and b=@b", the user can search the @a and @b parameter value 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!!!

What I have tried:

Private Sub btn1_Click(sender As Object, e As EventArgs) Handles btn1.Click

    'input from textbox
    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

cmd.Parameters("@para").Value = textbox1.Text
cmd.Parameters.AddWithValue("@parameter1", textbox1.Text)