Dynamic Define Parameters in MySql Command

rachayita jaiswal

13y1.6kDatabases & DBA

I have one file (database.vb) where i have mention a function like -

Public Sub Execute(ByVal SP As String)

Dim QueryConnection As New OdbcConnection(ConfigurationManager.ConnectionStrings("DBConnection").ToString)

QueryConnection.Open()

Dim cmd As New OdbcCommand(SP, QueryConnection)

cmd.Parameters.Add(---------------------------)

cmd.ExecuteNonQuery()

QueryConnection.Close()

End Sub

/* in place of query i want to use stored procedure(SP)

this function i am going to use for insert data but i am facing problem with declaring parameters.

how i will declare parameters here because there will be different parameters and it will dynamically ?

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

rachayita jaiswal13y ago
Hi Sanjeeb thanks for reply but i know this code, here you are passing static values means you know the stored procedure and their parameters but i have created this function -

Public Sub Execute(ByVal SP As String)
Dim QueryConnection As New OdbcConnection(ConfigurationManager.ConnectionStrings("DBConnection").ToString)
QueryConnection.Open()
Dim cmd As New OdbcCommand(SP, QueryConnection)

cmd.Parameters.Add(---------------------------)

cmd.ExecuteNonQuery()
QueryConnection.Close()
End Sub

so that where ever i want to use insert statement, i will call this function on testpage.aspx.vb like this -

Dim DBClass As New Database
Dim dtUsers As New DataTable
DBClass.Execute(SP)

in this case i will have many stored procedures and related parameters.
Sanjeeb Lenka13y ago
check this code

Dim strConnString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString

Dim con As New SqlConnection(strConnString)

Dim cmd As New SqlCommand()

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "AddEmployee"//pass your stored procedure name

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text.Trim()

cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtLastName.Text.Trim()

cmd.Parameters.Add("@BirthDate", SqlDbType.DateTime).Value = txtBirthDate.Text.Trim()

cmd.Parameters.Add("@City", SqlDbType.VarChar).Value = txtCity.Text.Trim()

cmd.Parameters.Add("@Country", SqlDbType.VarChar).Value = txtCountry.Text.Trim()

cmd.Connection = con

Try

      con.Open()

      cmd.ExecuteNonQuery()

      lblMessage.Text = "Record inserted successfully"

Catch ex As Exception

      Throw ex

Finally

      con.Close()

      con.Dispose()

End Try