Search code examples
vb.netoledb

Never used OLEDB, scalar variable error


I'm trying to get a connection established for an OLEDB connection, but since I've never used it, I don't really know what I am doing wrong. I managed to get a connection in the web.config file, I think, but now I get the 'must declare scalar variable' error. I thought I declared it, but it must be wrong.

Private Function GetBackUpAccountName(ByVal BAccount As String, ByRef Name As String) As Boolean
    'sql statement for baccount information in case BAccount is not found, search here next
    Dim backupsql As String = "select * from brokermaster bl WHERE BAccount = @BAccount"
    Using conn As New OleDbConnection(System.Configuration.ConfigurationManager.ConnectionStrings("BackUpConnectionString").ConnectionString)
        Using cmd As New OleDbCommand(backupsql, conn)
            cmd.Parameters.AddWithValue("@BAccount", BAccount)
            conn.Open()
            Using backuprdr As OleDbDataReader = cmd.ExecuteReader
                If (backuprdr.Read) Then
                    Name = backuprdr("Name").ToString()
                    Return True
                Else
                    Return False
                End If
            End Using
            conn.Close()
        End Using
    End Using
End Function

<add name="BackUpConnectionString" 
     connectionString="Provider=SQLOLEDB;Data Source=*******;Initial Catalog=DeskA;Persist Security Info=True;User ID=******;Password=******" 
     providerName="System.Data.OleDb"/>

This function is a copy of another function I have on the page for an SQL connection. I have to get account information for clients, and if they are not found in that database, I need to search a different database for them before showing an error. This is the function that works. It's been working for months now and today it was requested that we search another database before the error pops up saying their account was not found.

Private Function GetAccountName(ByVal BAccount As String, ByRef FirstName As String, ByRef LastName As String) As Boolean
    'sql statement for baccount information
    Dim sql As String = "SELECT BAccount, First_Name, Last_Name FROM IB inner join IB_BUISNESS_INFORMATION ON (IB.IB_ID = IB_BUISNESS_INFORMATION.IB_ID) WHERE BAccount = @BAccount"
    Using conn As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("IBConnectionString").ConnectionString)
        Using cmd As New SqlCommand(sql, conn)
            cmd.Parameters.AddWithValue("@BAccount", BAccount)
            conn.Open()
            Using rdr As SqlDataReader = cmd.ExecuteReader
                If (rdr.Read) Then
                    FirstName = rdr("First_Name").ToString()
                    LastName = rdr("Last_Name").ToString()
                    Return True
                Else
                    Return False
                End If
            End Using
            conn.Close()
        End Using
    End Using
End Function

Solution

  • OleDB uses positional parameters. Do this...

    Dim backupsql As String = "select * from brokermaster bl WHERE BAccount = ?"
    

    And posting your data source value of your connection string in public posts is risky business.