Click here to Skip to main content
15,030,240 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

        'open database
        Dim con As New SqlConnection(dbstring)
        Dim cmd As New SqlCommand(sql, con)
        If param IsNot Nothing Then
            For Each para As SqlParameter In param
                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
        End If

        Using sda = New SqlDataAdapter()
            sda.SelectCommand = cmd
            cmd.CommandText = sql
            sql = cmd.ExecuteScalar()

            Using ds As DataSet = New DataSet()
                DataGridView1.DataSource = ds.Tables(0)
            End Using
        End Using

    Catch ex As Exception
    End Try
End Sub
cmd.Parameters("@para").Value = textbox1.Text
cmd.Parameters.AddWithValue("@parameter1", textbox1.Text)
Updated 27-Nov-20 2:14am
CHill60 27-Nov-20 8:22am
What is param? Personally I would clear down the parameter list completely and regenerate it based on whatever textboxes have been populated
Member 14969271 27-Nov-20 8:40am
Hi, param is sqlparameterCollection. I am not sure whether I should put parameters into collection.
CHill60 27-Nov-20 8:47am
Yes you should use a sqlparameterCollection. Clear it down so the collection is empty and then only add parameters as required. E.g. If textbox1 has content then add parameter @a, if textbox2 has content then add parameter @b, etc. A series of textboxes might not be the best UI solution - consider a datagridview or list of some description

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900