need some help connecting to SQL SERVER 2014 connection string

[salsa31]

hey,
its been a half hour that i am trying to connect to sql server 2014 with a connection string
I search the web all kind of codes
nothing works for me
please i need some help
i added some samples i tried to use

Dim ConnectionString As String
Dim Cn As ADODB.Connection
Set CN = New ADODB.Connection
ConnectionString = "Provider=SQLNCLI11.1;Integrated Security=SSPI;Persist Security Info=False;User ID='';Initial 

Catalog=BeautyDesign;Data Source=SQLSERVER;Initial File Name='';Server SPN='' "

ConnectionString = "Provider=SQLNCLI10;Server=10.1.100.1;Database=BeautyDesign;Uid=sa;Pwd=sa;"

CN.Open "Driver=SQL Server;Server=.\SQLEXPRESS;Database=BeautyDesign;uid=sa;pwd=sa;"

ConnectionString = "Provider=SQLNCLI10; DRIVER=Sql Server; SERVER=TECH-PC\SQLEXPRESS; DATABASE=BeautyDesign; UID=sa; PWD=sa"
CN.ConnectionString = ConnectionString
CN.Open

tnx for any help
salsa

[wqweto]

These might help

'--- standard security
sConnStr = "Provider=SQLOLEDB" & _
    ";Data Source=" & sSqlServer & _
    ";Initial Catalog=" & sSqlDb & _
    ";User ID=" & sDbUser & ";Password=" & sDbPass
 
'--- integrated security
sConnStr = "Provider=SQLOLEDB" & _
    ";Data Source=" & sSqlServer & _
    ";Initial Catalog=" & sSqlDb & _
    ";Integrated Security=SSPI"

cheers,
</wqw>

[salsa31]

there is no password
no its not helping my friend

[wqweto]

Did you try using '--- integrated security snippet then? Does connecting from SSMS (SQL Server Management Studio) work?

cheers,
</wqw>

[LaVolpe]

ConnectionStrings.com is a good site for almost any connection string example.

In the examples posted by wqweto, simply don't include the parts of the connection string that do not apply, i.e., the password and user ID.

I think you should post the exact error you are getting

[DataMiser]

I notice you use sa for the login in all the attempts. Is SQL Server configured to use the SQL Server Login, Windows Login, or Both?

You also say there is no password but you are specifying a password in each of those strings. To the best of my knowledge there will always be a password when using SQL server.

sa only works if configured to use SQL Server login and the default sa account is present.

[salsa31]

the test connectios is ok

[salsa31]

when i use this

Dim ConnectionString As String
ConnectionString = "Provider=SQLNCLI11;Integrated Security=SSPI;Persist Security Info=False;User ID='';Initial Catalog=BeautyDesign;Data Source=.\SQLEXPRESS;Initial File Name='';Server SPN='' "
CN.ConnectionString = ConnectionString
CN.Open

i dont get any errors
and the sql sever is not connected yet

[DataMiser]

You are specifying a blank user ID.

Your ODBC connection above is using your windows login info.

[wqweto]

This should work then

sConnStr = "Provider=SQLOLEDB" & _
    ";Data Source=" & "TECH-PC\SQLEXPRESS" & _
    ";Initial Catalog=" & "master" & _
    ";Integrated Security=SSPI"

cheers,
</wqw>

[salsa31]

You are specifying a blank user ID.

Your ODBC connection above is using your windows login info.

i dont have any passwords in windows sir

[salsa31]

I think you should post the exact error you are getting

i dont get any errors yet sir
and the sql sever is not connected yet

[wqweto]

In an empty Std-Exe project add a reference to Microsoft ActiveX Data Objects 2.8 Library and try this code in the default Form1

Private Sub Form_Load()
    Dim sConnStr As String
    
    sConnStr = "Provider=SQLOLEDB" & _
        ";Data Source=" & "TECH-PC\SQLEXPRESS" & _
        ";Initial Catalog=" & "master" & _
        ";Integrated Security=SSPI"
    With New ADODB.Connection
        .Open sConnStr
        .CursorLocation = adUseClient
        Debug.Print .OpenSchema(adSchemaCatalogs).RecordCount
    End With
End Sub

What errors do you get?

cheers,
</wqw>

[salsa31]

this is when i use access

Set CN = New ADODB.Connection
StrSQL = "Provider=Microsoft.Jet.OLEDB.4.0;"
StrSQL = StrSQL & "Data Source=" & App.Path & "\BeautyDesign.mdb;"
StrSQL = StrSQL & "Jet OLEDB:Database Password=035924794"
CN.ConnectionString = StrSQL
CN.Open

i didint add any password to sql

[DataMiser]

i dont have any passwords in windows sir

Not sure what you mean there. I said you are giving a blank user ID.

Been a while since I dived into it but pretty sure that you can not install Windows without a password anymore.
I've had all my systems password protected in every version of windows from 95 on.

[DataMiser]

this is when i use access

Set CN = New ADODB.Connection
StrSQL = "Provider=Microsoft.Jet.OLEDB.4.0;"
StrSQL = StrSQL & "Data Source=" & App.Path & "\BeautyDesign.mdb;"
StrSQL = StrSQL & "Jet OLEDB:Database Password=035924794"
CN.ConnectionString = StrSQL
CN.Open

i didint add any password to sql

Passwords are added to SQL Server during install. If you choose the integrated windows mode then it uses your windows user name and password that you logged into windows with.

[salsa31]

well sir i use windows 7 and i have no password
anyway funny thing is
when i use the string connection

Dim ConnectionString As String
ConnectionString = "Provider=SQLNCLI11.1;Integrated Security=SSPI;Persist Security Info=False;User ID='';Initial Catalog=BeautyDesign;Data Source=.\SQLEXPRESS;Initial File Name='';Server SPN='' "
CN.ConnectionString = ConnectionString
CN.Open

i can see the data load to the combobox But
when i am trying to check something then i get a error

    Dim Rs As New ADODB.Recordset
    Rs.Open "Select * From Users Where UserName = '" & CmbUserName.Text & "'", CN
    If TxtPassWord.Text <> Rs!UserPass Then

[DataMiser]

The message is telling you that there was no match to your query

As for the password are you sure that you do not have one? As I recall Windows asks you for the admin password during install. I'm not sure if it can be left blank, I know on older versions you could set up an account without a password but you could not use networking properly if you did not have a password.

[LaVolpe]

Good news, you are connecting to your database.

FYI:
If rs.EOF is True, recordset position is after the final record. Moving next generates an error
If rs.BOF is True, recordset position is before the first record. Moving previous generates an error
If both .EOF and .BOF are true, there are no records in the recordset
If either .BOF or .EOF are true, attempting to access a record property/value generates an error

[salsa31]

The message is telling you that there was no match to your query

As for the password are you sure that you do not have one? As I recall Windows asks you for the admin password during install. I'm not sure if it can be left blank, I know on older versions you could set up an account without a password but you could not use networking properly if you did not have a password.

yes sir im a sure
well i got connected some how to the server
i dont think it is to the sql beacuse it is disconnected and still i can login
for eof or bof i just use

if not rs.eof ' i know this :)

[salsa31]

i dont think i am connecting to the SQL server
beacuse its disconnected and still shows me username and passwords
very strange

[DataMiser]

Hard to tell based on the small amount of code shown, Are those pieces of code in the same routine?

[salsa31]

Hard to tell based on the small amount of code shown, Are those pieces of code in the same routine?

those are codes that i try to make the connection
im not using it all at once

[DataMiser]

those are codes that i try to make the connection
im not using it all at once

Like I said it is hard to tell what kind of problem you are having from just that little bit of code, harder still if they are in different routines. For all I know you have some other code executing that is looking in an access database and is why you are seeing data.

[salsa31]

Like I said it is hard to tell what kind of problem you are having from just that little bit of code, harder still if they are in different routines. For all I know you have some other code executing that is looking in an access database and is why you are seeing data.

hey sir
there is no connection to access at all

[DataMiser]

The problem is that you only showed a couple of lines of code that are apparently in two different routines, You also show an error that indicates you are not getting a match to your query but you have given no real details to help figure out what the problem may be nor what else may be going on in your program between those lines of code.

If there is a problem with the connection then you will get an error when you try to open the connection not when you try to read data from a recordset.

Try this code in a single routine and see what you get in the debug window

On Error GoTo ErrTrap
Dim ConnectionString As String
ConnectionString = "Provider=SQLNCLI11.1;Integrated Security=SSPI;Persist Security Info=False;User ID='';Initial Catalog=BeautyDesign;Data Source=.\SQLEXPRESS;Initial File Name='';Server SPN='' "
CN.ConnectionString = ConnectionString
CN.Open
Dim Rs As adodb.Recordset
Set Rs = New adodb.Recordset
Rs.Open "Select * From Users Where UserName = '" & CmbUserName.Text & "'", CN
If Not Rs.EOF Then
    Debug.Print Rs!UserPass
Else
    Debug.Print "No match found"
End If
Exit Sub
ErrTrap:
Debug.Print Err.Description

If you get an error then set a break point at the top and step through the code to see where it occurs