Don't know how to connect to OLEDB

Question

User-1706579310 posted

This is all the information I have to go off of. 

select * from OpenDataSource('SQLOLEDB','Data Source=secret; User ID=secret; Password=secret').DeskA.dbo.brokermaster bl

I tried to put it in the web.config file and can't figure out how to do it. There is a lot of information that I have found, but nothing has worked yet. I don't have access to this database, have never seen it, but I can use this select statement in SQL Server so I know what information to expect. The way I have this set up, I get an error that says:

Cannot open database "OpenDataSource" requested by the login. The login failed.

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

This is how I am using it:

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

Answer 1

User-1706579310 posted

I think I successfully connected to it using 

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

but now I have an error saying 'Must declare scalar variable @BAccount' but I declared it in the function name, so what is the problem here?

 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

Answer 2

User-1706579310 posted

Figured that one out too. The code above is actually correct. I reran it after a couple minutes and it worked, but it shows another person's name. In debugging it gets the correct value, but it displays the wrong "Name" I looked for the name in the database and it didn't have a BAccount code, so I added IS NOT NULL to the end of the select statements.

I don't understand why it would be displaying the wrong name?