Dynamic Define Parameters in MySql Command

Question

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 ?

rachayita jaiswal · Answer

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 Lenka · Answer

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