MS SQL to ODBC transition, do variables work? RRS feed

  • Question

  • In our organization we have an application that has historically run on MS SQL, but customers have been demanding connection to other databases.  Because of this I am working on a project to port what was developed to connect to MS SQL to ODBC.

    I’ve already mucked around with a lot as we realize this is a major change.  What I find odd is that I’ve come to a point where I’ve changed my data sources from SQL to ODBC.  I’m doing this by backing up in the wizard to connection type, changing to ODBC and pushing final.

    I’ve tried a lot of other ways to save previously written SQL, but this appears to get me the farthest.  The problem I’m having has to do with the parameters and the @variable declarations.

    For instance what gets generated in the code is of a form…

    Imports System.Data.Common

    Imports System.Data.Odbc

    Dim Command As DbCommand = New OdbcCommand()

    Command.Connection = Me.Connection

    Command.CommandText = “SELECT * FROM mytable WHERE toshow = @showable”

    Command.Parameters.Add(New System.Data.Odbc.OdbcParameter("@showable", OdbcType.BigInt, 8, System.Data.ParameterDirection.Input, CType(0,Byte), CType(0,Byte), "toshow", System.Data.DataRowVersion.Current, false, Nothing))

    Me.Adapter.SelectCommand = Command

    Me.Adapter.SelectCommand.Parameters(0).Value = CType(1,Integer)


    The problem is that I get an error as though setting replaceable strings like @showable may not be supported.

    The exception I receive has a message similar to…

    System.Data.Odbc.OdbcException = {"ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@ showable’."}

    By the looks of the error it looks like performing this is permitted since it’s specifying I need to declare the variable, but still I don’t know.  I haven’t been able to find a clear definition of how to perform this in ODBC or if the DataGrid style components support ODBC.

    Any lead on this would be greatly appreciated.

    Monday, March 17, 2008 9:17 PM

All replies