SQLExpress - SQL DataAdapter Conflict

[dseydel]

I have a VS 2005 Pro VB - Windows XP Pro Application that connects to an
SQLExpress Server, obtains & displays database & table information,
and then makes an SQLDataAdapter connection to a selected database
to obtain data from a selected table.

My SQLExpress Connection String is:

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] SC [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] ServerConnection = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] ServerConnection
SC.ServerInstance = [/SIZE][SIZE=2][COLOR=#800000]"OptiplexGX270\SQLExpress"
[/COLOR][/SIZE][SIZE=2]SC.SqlExecutionModes = SqlExecutionModes.ExecuteAndCaptureSql
SC.ConnectTimeout = 15 
SC.LoginSecure = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE][SIZE=2] 
SC.Connect()
SQLServer = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Server(SC)
[/SIZE]

​

My SQL DataAdapter connection string is:

[SIZE=2]queryString = [/SIZE][SIZE=2][COLOR=#800000]"SELECT * FROM "[/COLOR][/SIZE][SIZE=2] & sTableName
ConnectionString = [/SIZE][SIZE=2][COLOR=#800000]"Data Source=.\SQLEXPRESS;AttachDbFilename="[/COLOR][/SIZE][SIZE=2] & _
"g:\d\db.mdf[/SIZE][SIZE=2][COLOR=#800000]";Integrated Security=True;User Instance=True"
[/COLOR][/SIZE][SIZE=2]connection.ConnectionString = ConnectionString
D[/SIZE][SIZE=2][COLOR=#0000ff]im[/COLOR][/SIZE][SIZE=2] adapter [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlDataAdapter()
adapter.SelectCommand = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlCommand(queryString, connection)
adapter.Fill(ds, [sTableName])
[/SIZE]

​

Independently these connections both work.


In the Application, I first do the ServerConnection, get the data, then do

[FONT=Courier New][SIZE=2]SQLServer.ConnectionContext.Disconnect()
[/SIZE][/FONT]

When I then do the Adapter connection it fails at "adapter.Fill" with the error:

Can Not Open user default database. Login Failed.
Login Failed for user OPTIPLEXGX270\Donald G. Seydel

If I then re-start, and try to independently do the DataAdapter bit, it fails with same error.
If I then stop and re-start the SQLExpress Server, I can independently do the
DataAdapter connection until I again make the Server connection.

I suspect that the error message is not accurate.
Any help would be appreciated.

Don Seydel

​

 

[dseydel]

I have narrowed this problem down as follows.

After I connect to the server, I get & display the names of the databases for that server. After a database is selected, I get & display the names of the tables in that database with the following code:

For each tbl in SQLServer.Databases(index).Tables
       frmTv.cmbTables.Items.add(tbl.name)
Next

If I do the for-next code, I get the Login error. If I do not do the for-next code, but instead just name the table in code, I can do the connection for the data adapter & fill the table.

Any help would be appreciated.

Don Seydel