October 5, 2013 at 11:10 am
I'm relatively new with this version of SQL and haven't coded in a number of years. This is a VB program trying to connect to a SQL 2008 R2 database. I've tried everything I can think of or have read on the blogs. Any help would be appreciated.
Here are the particulars:
Visual Studio VB 2010 : Code Snippet
Dim Provider As String = "SQLOLEDB"
Dim DataSource As String = "Local"
Dim Database As String = "Jims"
Dim UserID As String = "test"
Dim pwd As String = "test"
Provider = "SQLNCLI10" '<--tried changing the provider here same result
'cn = New OleDbConnection("Provider=SQLNCLI10;Server=Jim-PC;Database=Jims;Uid=Jim-PC\Jim;Pwd=test;")
cn = New OleDbConnection("Provider=" & Provider & ";DataSource=" & DataSource & ";Initial Catalog=" & Database & _
";UserID=" & UserID & ";password=" & pwd & ";")
Try
cn.Open() '* open access database
Executed with two (2) error messages
Index #0
Message Invalid authorization specification
Native:0
Source:Microsoft SLServer Native Client 10.0
SQL:28000
Index #1
Message: Invalid connection string atribute
Native:0
Source: Microsoft SQLServer Native Client 10.0
SQL:01s00
Content of cn
? cn
{System.Data.OleDb.OleDbConnection}
CanRaiseEvents: True
CanRaiseEventsInternal: True
CloseCount: 0
ConnectionFactory: {System.Data.OleDb.OleDbConnectionFactory}
ConnectionOptions: {System.Data.OleDb.OleDbConnectionString}
ConnectionString: "Provider=SQLNCLI10;DataSource=Local;Initial Catalog=Jims;UserID=test;password=test;"
ConnectionTimeout: 15
Container: Nothing
Database: "Jims"
DataSource: ""
DbProviderFactory: Nothing
DesignMode: False
Events: {System.ComponentModel.EventHandlerList}
ExecutePermission: {<IPermission class="System.Data.OleDb.OleDbPermission, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" version="1" AllowBlankPassword="False"> <add KeyRestrictions="" KeyRestrictionBehavior="AllowOnly"/> </IPermission> }
InnerConnection: {System.Data.ProviderBase.DbConnectionClosedNeverOpened}
IsOpen: False
LocalTransaction: Write Only Properties are not supported
ObjectID: 1
PoolGroup: {System.Data.ProviderBase.DbConnectionPoolGroup}
Provider: "SQLNCLI10"
ProviderFactory: Nothing
ProviderInfo: {System.Data.OleDb.OleDbConnectionPoolGroupProviderInfo}
ServerVersion: {"Invalid operation. The connection is closed."}
Site: Nothing
State: Closed {0}
UserConnectionOptions: {System.Data.OleDb.OleDbConnectionString}
October 6, 2013 at 5:37 am
dodobdr2011 (10/6/2013)
????? ??? ????
I can't read that!!??
October 6, 2013 at 6:15 am
view your connections String carefully
read some example from this site http://www.connectionstrings.com/sql-server-native-client-10-0-oledb-provider/
best would be to write on some vb forums
October 6, 2013 at 6:53 am
I have tried all that and I thought that maybe it was something like a misplaced period or something that I'm just not seeing. I've tried every string that I could find in a form just to open the database. That's all the form does is open it and put up a message "All Done". Not too difficult. I'm even may all the options strings so I could try different options easier, still no luck.
Any other ideas? Is it maybe the database? I'm having some problems getting SQL 2008 R2 installed after putting up SQL 2008 but I could go into the maintenance screen and create tables, change owners, etc. So I didn't think it was the database.
At this point I'm open to anything. Right now I'm trying to removed the database entirely and reinstall it just to make sure there are no ruminants of SQL 2008. :w00t:
I'm at a total loss at this point and just trying anything that seams remotely possible!!:hehe:
October 6, 2013 at 7:14 am
I would suggest that you not use all the variables, just type out the connection string as a whole. Start off by logging into the database, via SSMS using the same credentials. See what you see, and if you get any errors. Use SQL authentication, see what that tells you.
When you install SQL Server again, use a different directory that where the old one was installed.
Start off by creating one very simple page(piece of code) that does nothing more than open the database connection.
This kind of thing requires you taking it apart, and putting the pieces back in one at a time, until you run across that piece of code that is causing the issue.
Andrew SQLDBA
October 6, 2013 at 7:36 am
My intention was to remove all references to SQL (any version) from my machine. Do a registry clean up and all and of course reboot it.
I did try typing the complete connection string before and then commenting it out and trying the next combination that I found and that didn't work. So I tried using the variables thinking it would be an easier way to change them. Still no luck.
I will post what happens when I get my machine clean from SQL and then reinstall.
Thanks for the ideas.
Jim:-D
October 6, 2013 at 8:00 am
Jim
You know that you run multiple instances on the same box. You can even run different versions, with multiple instances on the same box. It may be easier and better to do that. Just install multiple instances of SQL, instead of trying to remove something that is already installed.
Not always a good idea to use variables in a connection string. It is very simple to have a class that all the methods use to pull the connection string from a single place. And store that in a config file.
This is what my connection string looks like from a .NET web app. I am not sure if a GUI app would be that much different.
<connectionStrings>
<add name="NameOfTheApp" connectionString="Data Source=ServerName\InstanceName;Initial Catalog=DatabaseName;user id=SQLUserName;password=SQLPassword;" providerName="System.Data.SqlClient"/>
</connectionStrings>
From the SQLDBConnectionString method that I have created, I call the "NameOfTheApp" and it pulls that into the method for use.
private string SQLDBConnectionString()
{
//DBConnection is the name of the connection string that was set up from the web config file
return System.Configuration.ConfigurationManager.ConnectionStrings["NameOfTheApp"].ToString();
}
And in the method that needs to open a connection, is just this
SqlConnection SQLDBConn = new SqlConnection(SQLDBConnectionString());
Then at the last possible second, I call the Open Method
SQLDBConn.Open()
So as you see, I store the credentials in only one location, that can easily be modified when moving from server to server.
Andrew SQLDBA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply