Writing complex database queries in VB can be quite taxing. Often creating a Stored Procedure in MSSQL, or even better, accessing one that has already been created for you is a great solution to avoid the hassle. In another scenario, multple applications that perform the same queries can utilize Stored Procedures to enhance efficiency and centralize data management. This example will show how to do this using either ODBC or a SQL connection

Mi-Forms is known for it's flexibility, and it's ability to access stored procedures is no exception. All you need to get started is a database with a stored procedure. I won't go into detail about how to build a stored procedure here. 

The example I will use here works with the AdventureWorks2012 sample database supplied by Microsoft. Once you install this database onto an MSSQL Server, you can navigate to the stored procedures to view them as shown in the following image.

For simplicity, we will be accessing the uspGetEMployeeManagers stored procedure, because it only requires one parameter to return results. 

If using ODBC for your connection type, create the Odbc connection.

In the Mi-Forms Designer, we created a Picklist to show the business entity ids and and a Grid to display the data returned by the stored procedure.

Because this database uses a complex schema structure, we populate the static Picklist using a .NET scripted method. I chose to use ODBC for this example. You may note that the SELECT statement is limiting the return results to the first 100. This is because there are over 20,000 entries in AdventureWorks2012.

<MiCode(FormScriptEventType.AfterOpen)> _ 
Public Sub Form_AfterOpen(ByVal e As AfterOpenEventArgs)
	' Fill _BusinessEntity Picklist values from Person.BusinessEntity table in AdventureWorks2012
	Dim connectionString As String = "DRIVER={SQL Server};DSN=AdventureWorks2012;Server=KEVIN\SQLEXPRESS;Database=AdventureWorks2012;Uid=mf;Pwd=pass;"
	Dim connection As New OdbcConnection(connectionString)
	Dim cmd As String = "SELECT BusinessEntityID FROM Person.BusinessEntity WHERE BusinessEntityID < 99"
	Dim adapter As New OdbcDataAdapter(cmd, connection)
	Dim dt As New Datatable
	adapter.Fill(dt)
	Dim list As New ArrayList 
	
	' Populate picklist
	For Each row As DataRow In dt.Rows
		_BusinessEntitySql.AddItem(row(0))
		_BusinessEntityOdbc.AddItem(row(0))
	Next		
End Sub

The Picklist uses its AfterSetData method to call the stored procedure call. It uses a single parameter, but is designed such that you can add multiple parameters.

<MiCode(ControlScriptEventType.AfterSetData, "BusinessEntityOdbc")> _ 
Public Sub Ctl_BusinessEntityOdbc_AfterSetData(ByVal e As AfterSetDataEventArgs)
	' odbc connection method
	Dim cmd As New OdbcCommand("{call uspGetEmployeeManagers (?)}")
	cmd.Parameters.Add("@BusinessEntityID", OdBcType.Int).Value = _BusinessEntityOdbc.Value
	Dim dt As DataTable = ExecuteOdBcCMD(cmd).Tables(0)
	' Populate grid
	_EmployeeManagers.Value = dt        
End Sub

Function ExecuteOdBcCMD(ByRef cmd As OdBcCommand) As DataSet
	Dim connectionString As String = "DRIVER={SQL Server};DSN=AdventureWorks2012;Server=KEVIN\SQLEXPRESS;Database=AdventureWorks2012;Uid=mf;Pwd=pass;"
    Dim ds As New DataSet()

	Try	
		Dim connection As New OdBcConnection(connectionString)
        cmd.Connection = connection
		cmd.CommandType = CommandType.StoredProcedure
        
		Dim adapter As New OdBcDataAdapter(cmd)
        adapter.SelectCommand.CommandTimeout = 300
	
        'fill the dataset
        adapter.Fill(ds)
        connection.Close()

    Catch ex As Exception
        ' The connection failed. Display an error message.
		MsgBox("Database Error: " & ex.Message)
    End Try

	Return ds
End Function

Adding multiple parameters to this query in the AfterSetData method could look like the following.

Dim cmd As New OdbcCommand("{? = call newStoredProcedure (?,?,?)}")
cmd.Parameters.Add("@Parameter1", OdBcType.Int).Value = 1
cmd.Parameters.Add("@Parameter2", OdBcType.Text).Value = "Parameter2Value"
cmd.Parameters.Add("@Parameter3", OdBcType.Text).Value = "Parameter3Value"

Similarly, you can create this using a standard SQL connection, but there are some minor changes so I will include that code below as well.

<MiCode(ControlScriptEventType.AfterSetData, "BusinessEntitySql")> _ 
Public Sub Ctl_BusinessEntity_AfterSetData(ByVal e As AfterSetDataEventArgs)
	' sql connection method
   	Dim cmd As New SqlCommand("uspGetEmployeeManagers")
	cmd.Parameters.Add("@BusinessEntityID", SqlDbType.Int).Value = _BusinessEntitySql.Value
	Dim dt As DataTable = ExecuteCMD(cmd).Tables(0)
	' Populate grid
	_EmployeeManagers.Value = dt
End Sub

Function ExecuteCMD(ByRef CMD As SqlCommand) As DataSet
	Dim connectionString As String = "Server=KEVIN\SQLEXPRESS;Database=AdventureWorks2012;Uid=mf;Pwd=pass;"
    Dim ds As New DataSet()		

	Try	
        Dim connection As New SqlConnection(connectionString)
        cmd.Connection = connection
        cmd.CommandType = CommandType.StoredProcedure

		Dim adapter As New SqlDataAdapter(cmd)
        adapter.SelectCommand.CommandTimeout = 300

        'fill the dataset
        adapter.Fill(ds)
        connection.Close()

    Catch ex As Exception
        ' The connection failed. Display an error message.
		MsgBox("Database Error: " & ex.Message)
    End Try

    Return ds
End Function

You can download the working version of this form from the link below. Keep in mind that you will need to install the AdventureWorks12012 database, either add a new user with user/pass of mf/pass or modify those in the connection string, and create the ODBC connection (be sure to use ODBC32).