I have a very big application here that connects to a mySQL database. For each stored procedure in the server, there is a function or sub in the application. The problem is that I needed to migrate this database to a SQL Server, so all the routines that were made for mySQL need to be adapted...
The app has a function to "ExecuteReader" and a "ExecuteCommand" to read tables and send commands with no return.
The ExecuteReader function is like this:
Function ExecuteReader(ByVal SQLString As String, Optional ByVal Parameters As List(Of OdbcParameter) = Nothing) As DataTable
'Function that executes a command bringing back results, usually used for selects. It returns all results as a Datatable.
Dim Result As DataTable = New DataTable
'bridgeCommand = New OdbcCommand("USE " & Database & ";", bridgeConnection)
'If Not ConnectionState = Data.ConnectionState.Open Then OpenConnection()
'Set Parameters if there are any
If Not Parameters Is Nothing Then
For Each Parameter As OdbcParameter In Parameters
If bridgeConnection.State = ConnectionState.Open Then
'Executes the command and gather the result back to the Result Datatable object.
bridgeCommand.CommandText = Replace(SQLString, "\", "\\")
'If the connection couldn't be opened, returns nothing.
Catch ex As Exception
And an example of stored procedure call from app is this:
Public Function prcUsrReturnSystem(ByVal id_System As Integer) As DataTable
'Returns user system details based on it's ID.
Dim Parameters As New List(Of OdbcParameter)
Dim AddingParameter As OdbcParameter
AddingParameter = New OdbcParameter("@id_System", OdbcType.Int, Len(id_System), "id_System")
AddingParameter.Value = id_System
Return ExecuteReader("exec dbMe..prcUsrReturnSystem (?);", Parameters)
Since there are tons of functions like this, I rather adapt the "ExecuteReader" function to work without changing them... One thing I noticed is that parameters in SQL Server have an "@" in front of them... I was trying to make it work but got the error:
ERROR  [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '@P1
thanks a lot