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
sql = TextBox4.Text
Try
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
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)