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:

VB.NET
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
VB.NET
cmd.Parameters("@para").Value = textbox1.Text
cmd.Parameters.AddWithValue("@parameter1", textbox1.Text)
Posted
Updated 27-Nov-20 2:14am
v2
Comments
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