locked
Don't know how to connect to OLEDB RRS feed

  • 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
    Tuesday, March 6, 2012 1:59 PM

Answers

  • 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?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 6, 2012 4:40 PM

All replies

  • 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
    Tuesday, March 6, 2012 2:32 PM
  • 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?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 6, 2012 4:40 PM