First Database complete, can't connect!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

First Database complete, can't connect!

Author

Topic

BIGZIPZ1
Starting Member

4 Posts

Posted - 2007-11-22 : 10:56:35

Hello all! A while ago I started cataloging my collection of old radio recordings and I wrote a simple Visual Basic 2005 program to display them in a nice UI. I have implemented an SQL database with the project and all is fine, when I run the program it connects to the sql server and downloads the data. But when I try and change something by connecting from within Visual Basic at runtime with this code:

Private Sub Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Save.Click
        Dim ConnectionString As New SqlConnection("Server=MARK-HOME\SQLEXPRESS;Database=Library.mdf;Uid=MARK-HOME\Mark;Pwd=sodoff;")
        Dim sqlString As String
        ConnectionString.Open()
        sqlString = "UPDATE JackBenny SET Rating='Excellent' WHERE [Episode Number]=" & epNumber
        ConnectionString.BeginTransaction(sqlString)
        ConnectionString.Close()
    End Sub

I get this error code in the log and a general unable to login error within Visual basic itself:

2007-11-22 15:44:39.78 Logon Error: 18456, Severity: 14, State: 5.
2007-11-22 15:44:39.78 Logon Login failed for user 'Mark'. [CLIENT: <local machine>

The state of 5 refers to "User ID is not valid." but I can login to SQL Server Management Studio Express just fine with the username and password in the code posted. Also of note, I can login to the Server Managment with the sa account username and password. When I use the credentials in visual basic code, same error. Any ideas? I am trying to connect to a database which is on this computer (local) in the root folder of the application, called Library.mdg.

Thanks so much all

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-11-22 : 15:13:41

You have SQL Server set up to use Windows Authentication in which case you do not put the username and password in the connection as you are trying to do (and that is also why you cannot connect with the sa user, which is a SQL Server Authenticated account). To use SQL Server Authentication you need to change the authentication mode of your server to Mixed Mode
(see https://msdn2.microsoft.com/en-us/library/ms143705.aspx)

However, you probably don't need to used Mixed Mode, just change your connection string to specify Windows Authentication - to do that remove the Uid and Pwd parameters and put in the Integrated Security parameter
(see http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(VS.71).aspx)

Your connection string becomes
Dim ConnectionString As New SqlConnection("Server=MARK-HOME\SQLEXPRESS;Database=Library.mdf;Integrated Security=SSPI;")

BIGZIPZ1
Starting Member

4 Posts

Posted - 2007-11-22 : 15:33:59

Thanks for replying.
I changed my code accordingly to:

Private Sub Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Save.Click
        Dim ConnectionString As New SqlConnection("Server=MARK-HOME\SQLEXPRESS;Database=Library.mdf;Integrated Security=SSPI;")
        Dim sqlString As String
        ConnectionString.Open()
        sqlString = "UPDATE JackBenny SET Rating='Excellent' WHERE [Episode Number]=" & epNumber
        ConnectionString.BeginTransaction(sqlString)
        ConnectionString.Close()
    End Sub

And I still couldn't login. The error message in the log was State 16:

2007-11-22 20:22:20.07 Logon Error: 18456, Severity: 14, State: 16.
2007-11-22 20:22:20.07 Logon Login failed for user 'MARK-HOME\Mark'. [CLIENT: <local machine>]

This is a screen shot of the Logins page in the Management tool and the login screen. Note the settings are on Windows Authentication, not mixed, yet sa shows up in the login page:

[url]http://www.otr-reviews.com/Stuff/screenie1.JPG[/url]
[url]http://www.otr-reviews.com/Stuff/screenie2.JPG[/url]

Argg!

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-11-22 : 15:48:26

OK, that's progress :-)
You need to remove the .mdb from the database name, sorry I missed that before. So try
Dim ConnectionString As New SqlConnection("Server=MARK-HOME\SQLEXPRESS;Database=Library;Integrated Security=SSPI;")
Basically it's saying you do not have permissions to a database named Library.mdb because that isn't the database name, that's the database file name.

It's correct that you'll see the sa user because the user is always there even if you do not have Mixed Mode authentication turned on, and cannot actually log on with that user.

BIGZIPZ1
Starting Member

4 Posts

Posted - 2007-11-22 : 17:33:42

hello there again! i've corrected the string but I am getting the same error message in log.

My database is called Library, it is in the root folder of the application on the desktop, however, I do not see it in the Studio Management studio Express program under databases.

Please see two screen shots to see what I mean:

http://www.otr-reviews.com/Stuff/data1.JPG
http://www.otr-reviews.com/Stuff/data2.JPG

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-22 : 22:39:09

Tried attach it in ssms or with sp_attach_db?

BIGZIPZ1
Starting Member

4 Posts

Posted - 2007-11-22 : 22:54:41

Hi I have managed to connect now but I have a new problem lol.

The identifier that starts with 'UPDATE' is too long. Maximum length is 32.

Huh? So the SQLString I send to the database can only contain 32 characters? How is one supposed to do complex query's with so few room?

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-11-23 : 14:32:31

quote:
Originally posted by BIGZIPZ1

Hi I have managed to connect now but I have a new problem lol.

The identifier that starts with 'UPDATE' is too long. Maximum length is 32.

Huh? So the SQLString I send to the database can only contain 32 characters? How is one supposed to do complex query's with so few room?

No, it says the identifier, not the SQL. An identifier is the name of something, like LIBRARY for a table. What the error is telling you is that you have an identifier that starts with LIBRARY that is over 32 characters long; something like


LibraryColumnNameThatIsTotallyTooLongAndUselessAndWontWork

I'd say you probably concatenated a string and forgot to use either trailing spaces or leading spaces between the sections. Something like:


WhereClause = "1 = 1 OR 2 = 2"
s = "SELECT * FROM LIBRARY" & WhereClause

'WhereClause now is 'SELECT * FROM LIBRARY1 = 1 OR 2 = 2'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources