Populate dropdown list from Oracle database

[lbrechler]

I'm trying to do several things here, and not having much luck with any of them...! Any help would be greatly appreciated. I need to first connect to a database (trying Oracle now, as that is my end goal, but couldn't even get a local Access Database to work) and then use values from the database to populate a dropdown list.

Current Oracle string error message is: System.ArgumentException: An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'.

If I try the Access string, I get: System.Data.OleDb.OleDbException: The Microsoft Jet database engine cannot open the file 'C:\test.mdb'. It is already opened exclusively by another user, or you need permission to view its data. (even though I don't have it open, and it's not write- or password-protected)

Also, I've tried to add the System.Data.OracleClient namespace, but it doesn't recognize it. Has anyone run into this before?

TIA,
Lindsay

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'Load the values from the database into the drop down list
If Not IsPostBack Then

Dim connectionString As String
'Testing local Access database - also doesn't work
'connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.mdb;"
'Testing Oracle database on another server
connectionString = "server=111.111.111.111:1521;uid=myuser;pwd=mypassword;database=DBNAME;"

Dim myOleConnection As New OleDbConnection(connectionString)
Dim myOleCommand As OleDbCommand
Dim myReader As OleDbDataReader
Dim sqlText As String = "SELECT FIRSTNAME, LASTNAME from USER"
myOleCommand = New OleDbCommand(sqlText, myOleConnection)
myOleCommand = New OleDbCommand(sqlText, myOleConnection)

Dim values As ArrayList = New ArrayList()

Try
myOleConnection.Open()
myReader = myOleCommand.ExecuteReader()
Do While (myReader.Read())
values.Add(myReader("FIRSTNAME&quot)
values.Add(myReader("LASTNAME&quot)
Loop
ddNameCode.DataSource = values
ddNameCode.DataBind()

Catch x As Exception
txtOpen.Text = "Connection failed to open"
txtClose.Text = x.ToString()
End Try
End If


End Sub

 

[Custom24]

Hi Lindsay
1. Access connection - instead of generating the connection string yourself, let VS.net do it for you. Drop a connection object on the form and click it's connection string property - scroll down to new connection and you should end up with the standard oledb connection dialog box. Choose the Microsoft Jet 3.51 provider for Access 97 or the Jet 4.0 provider for Access 2000. Then, on the next tab, browse to the location of your database, select it. If you've enabled security, fill in the user name and password. Then, click test connection - if this is OK then continue.

There is a problem which gives the error message you are having with Access - it is because the Internet user local account does not have read and write priviledges to the folder where the Access mdb file is. Move the mdb to the application folder and see if this fixes this. If it does, then you can either leave the mbd file here or set up the appropriate permissions.

2. Oracle connection. You can set this connection string up in the same way as the Access one. Choose the Microsoft OLE DB Provider for Oracle, and on the next page, enter the Server Name and the schema name and password you want to connect to. The server name can be found in the tnsnames.ora file if you don't know what it is. The schema and password will be the same as what you use when logging in using sqlplus.

Then press test connection to make sure that everything is OK.

3. System.Data.OracleClient - when you say "tried to add" this, do you mean putting an imports statement at the top of the module? You also have to add a reference to the .dll to your project. Click on the project explorer, then right click on references, and add reference. The reference you are after is System.Data.OracleClient.dll If it does not appear in the .net tab, then you have to install it - it does not come with the framework (v1.0) by default. You can get the installer from MSDN.

4. Databinding to a dropdown - just to point out in asp.net, you can bind directly to a datareader. But probably a more useful SQL statement for a drop down would be

SELECT FIRSTNAME || LASTNAME FullName, ID from USER

and then set the dropdown's datasource to the open datareader, the datavalue to 'ID' and the datatext to 'FullName'

Hope this fixes it for you - if not, post back
Mark