[RESOLVED] Connecting to SQL Server using VB6

[JAVAness]

Hello, I'm new to MS SQL server. In our class, we were using MS Access Database in storing records. I have no idea how. I already searched for 2 nights on ways on how to connect it. Could someone please guide me on how to establish connection esp. setting up data source in ODBC and the connection string to use (I have really hard time on understanding what data should I put) My system is to be deployed this week by the company but I'm still using MS Access till now.

I have this code now, but I'm not sure if this is it.

Dim MyConnObj As ADODB.Connection 'ADODB Connection Object
        Dim myRecSet As New ADODB.Recordset 'Recordset Object
        Dim sqlStr As String ' String variable to store sql command
        
        Set MyConnObj = New ADODB.Connection
        
        MyConnObj.ConnectionString = "Provider = MSDASQL;" & _
            "Data Source=LocalServer;" & _
            "Initial Catalog=sample;" & _
            "Login ID=SERVANT;" & _
            "Driver=SQL Server;"

        MyConnObj.Open
        
        Set myRecSet = New ADODB.Recordset
        
         sqlStr = "select * from Employee"
         
        myRecSet.Open sqlStr, MyConnObj, adOpenKeyset

What provider should be used? I am using MS SQL Server 2000. Some sites are using SQLOLEDB and others use MSDASQL. I'm so confused right now. What is data source? I only put there LocalServer since it is in System DSN of ODBC and I'm using local. In MS SQL Server - Console Root\Microsoft SQL Servers\SQL Server Group\(local)(Windows NT)
I created a sample database under it and a table called Employee.

Is that the correct connectionString?

I have run this code but i get this error.


Run-time error'-2147467259(80004005)':[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

[DataMiser]

Here is a string I use in one of my projects which connects to SQL Server, Tested with SQL Server 6.5 - 2008 using ADO.

Provider=MSDASQL; DRIVER=Sql Server; SERVER=p42800; DATABASE=myDatabase; UID=MyUserID; PWD=MyPassword

Here is another which uses a DSN created by odbc manager

PROVIDER=MSDASQL;dsn=myDSNName;uid=MyUserID;pwd=MyPassword;

I have used both of these in several projects and never had an issue. I would recommend the top one as it does not require you to create a DSN.


btw in my Case the PC Named p42800 is running SQL Server 2000 and by using the PC name rather than local host that string works both on that pc and from any other pc on the network as well.

[JAVAness]

Hi, thank you very much for you reply. I hope you'll guide me all the way. I have already changed my code

Dim MyConnObj As ADODB.Connection 'ADODB Connection Object
        Dim myRecSet As New ADODB.Recordset 'Recordset Object
        Dim sqlStr As String ' String variable to store sql command
        
        Set MyConnObj = New ADODB.Connection
        
        MyConnObj.ConnectionString = "Provider = MSDASQL;" & _
            "Driver=SQL Server;" & _
            "Server=SERVANT;" & _
            "Data Source= (local)" & _
            "Database =sample;" & _
            "User ID=SERVANT;"

        MyConnObj.Open
        
        Set myRecSet = New ADODB.Recordset
        
         sqlStr = "select * from Employee"
         
        myRecSet.Open sqlStr, MyConnObj, adOpenKeyset
             
        MsgBox "Total Number of records = " & myRecSet.RecordCount
         
        Dim i As Integer 'variable to keep count
        i = 1
         
        Print "#"; Tab; "ID"; Tab; "Name"; Tab; "Salary"
        Print ""
        
        While Not myRecSet.EOF ' Loop until endd fo file is reached
         
            Print i; Tab; myRecSet(0); Tab; myRecSet(1); Tab; myRecSet(2)
                '0- 1st filed, 1- 2nd Field and so on...
                 
            myRecSet.MoveNext 'Moves the RecordSet pointer to the next position
             
            i = i + 1
        Wend
             
        MyConnObj.Close

I have error here

MyConnObj.Open

same error message - Run-time error'-2147467259(80004005)':[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Questions :
1) How do you know the server name?
2) How do you create your own user id and password?

Please bear with me for I have blank knowledge on this. Thank you very much.

[JAVAness]

I already got my pc name, that's what you put in the SERVER, right?

MyConnObj.ConnectionString = "Provider = MSDASQL;" & _
            "Driver=SQL Server;" & _
            "Server=servant-5dc1c25;" & _
            "Data Source= (local)" & _
            "Database =sample;" & _
            "User ID=SERVANT;"

This is the case. I created a "sample" database on the local and have one table in it called "Employee"

How can I connect this to ODBC or register ? Should I even create new User/System DSN?
I have the ff in my SYSTEM DSN :
Name : LocalServer
Driver : SQL Server

in my USER DSN :
(default)
In short, I haven't touched anything on the ODBC. I'm confused why others make another System DSN. Should I or should I not?

[DataMiser]

You do not need to create a DSN via ODBC manager though it is an option. In your case to connect without creating a DSN in the manager you would want your string to be something like this assuming the info you have above is correct.

Provider=MSDASQL; DRIVER=Sql Server; SERVER=servant-5dc1c25; DATABASE=sample; UID=SERVANT; PWD=;

In my case I am using SQL Server login method so the user ID and password is as defined in SQL Server settings. I think though am not sure that if your server is set to allow windows authentication the UID and PWD can be ommitted. If set to both then you can do either and if set the SQL Server then you must provide these. Not providing a password will assume a blank password and is case sensitive.

If you want to create a DSN then you must go into the ODBC manager and create a new DSN, point it to SQL server, set the inital catalog to your dabase, enter the login info and give it a name then use a string like the shorter one I provided earlier replacing the DSN=.... with the name you have given your DSN

[JAVAness]

Hello anyone?

I've changed my code again:

Set MyConnObj = New ADODB.Connection
        
        MyConnObj.ConnectionString = "Provider = MSDASQL;" & _
            "Driver=SQL Server;" & _
            "Server=servant-5dc1c25;" & _
            "Data Source= (local)" & _
            "Initial Catalog =sample;" & _
            "Integrated Security = SSPI;" & _
            "Login name = dborces;" & _
            "Password = Ga4yegzy;"
            
        MyConnObj.Open

I went over SQL Server Enterprise Manager, clicked
(local) - Security - Logins

I have this as one of my Logins
Name : dborces
Type : standard
Server Access : permit
Default Database : sample
Default Language : English

I run the code and I get error in this line

MyConnObj.Open

Run-time error '-2147217887 (80040e21)':
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

[DataMiser]

Try this

Set MyConnObj = New ADODB.Connection
MyConnObj.CursorLocation=adUseClient

MyConnObj.Open "Provider=MSDASQL; DRIVER=Sql Server; SERVER=servant-5dc1c25; DATABASE=sample; UID=dborces; PWD=Ga4yegzy;"

This is assuming that you have already dim the var MyConnObj if not the instead of the set statament you should use

Dim MyConnObj as New ADODB.Connection

in place of the set

[JAVAness]

Thank you very much, DataMiser for being always there to reply to me.

I went to ODBC Data Source Administrator, selected
-System DSN Tab
-Configure.. LocalServer
-selected "With SQL Server authentication using a login ID and password entered by SQL Server Enterprise
-checked "Connect to SQL Server to obtain default setting for additional configuration options"

Login ID : (changed the default 'SERVANT' to) dborces
Password : (changed the default null to) ********

clicked Client Configuration
Server alias : (from null to) <PC Name>
Server name : <PC Name>

clicked Next and I get this

Connection failed SQL State : '28000'
SQL Server Error 18452 [Microsoft][ODBC SQL Server Driver][SQL Server]
Login failed for user 'dborces' Reason :
Not associated with a trusted SQL Server connection

[JAVAness]

I changed these parts

from

Dim MyConnObj as ADODB.Connection
Set MyConnObj = New ADODB.Connection

to

Dim MyConnObj As New ADODB.Connection 'ADODB Connection Object

made that connectionString part of my code comment and added yours
I get this message :

Run-time error '-2147217887 (80040e4d)':
[Microsoft][ODBC SQL Server Driver][SQL Server]
Login failed for user 'dborces' Reason :
Not associated with a trusted SQL Server connection

[DataMiser]

You don;t want to configure an exiting source. You want to create one if you go that route.

[DataMiser]

Not sure about the trusted connection thing. I have ran into this in the past but can not remember how I solved it (been a long time) try a google search on it.

Your code should be ok now

Edit: Check this out

http://support.microsoft.com/kb/889615

[JAVAness]

wow! thank you very much DataMiser! I really don't know how to thank you. It worked! For how many days and sleepless nights of worrying that I will not be able to pass my system on time. thank you thank you thank you so much!

I clicked on your link and followed this steps
A SQL Server login

Start Enterprise Manager.
Expand Microsoft SQL Servers, and then expand SQL Server Group.
Right-click the server that I want to change to SQL Server and Windows authentication, and then click Properties.
In the SQL Server Properties dialog box, click the Security tab, click SQL Server and Windows, and then click OK.
When prompted to re-start the SQL Server service, click Yes.

here's my final code (running)

Private Sub Command1_Click()
     
        Dim MyConnObj As New ADODB.Connection 'ADODB Connection Object
        Dim myRecSet As New ADODB.Recordset 'Recordset Object
        Dim sqlStr As String ' String variable to store sql command

        MyConnObj.CursorLocation = adUseClient
        MyConnObj.Open "Provider=MSDASQL; DRIVER=Sql Server; SERVER=servant-5dc1c25; DATABASE=sample; UID=dborces; PWD=Ga4yegzy;"
        'MyConnObj.Open
        
        Set myRecSet = New ADODB.Recordset
        
         sqlStr = "select * from Employee"
         
        myRecSet.Open sqlStr, MyConnObj, adOpenKeyset
             
        MsgBox "Total Number of records = " & myRecSet.RecordCount
         
        Dim i As Integer 'variable to keep count
        i = 1
         
        Print "#"; Tab; "ID"; Tab; "Name"; Tab; "Salary"
        Print ""
        
        While Not myRecSet.EOF ' Loop until endd fo file is reached
         
            Print i; Tab; myRecSet(0); Tab; myRecSet(1); Tab; myRecSet(2)
                '0- 1st filed, 1- 2nd Field and so on...
                 
            myRecSet.MoveNext 'Moves the RecordSet pointer to the next position
             
            i = i + 1
        Wend
             
        MyConnObj.Close
         
    End Sub

thank you so much again for guiding me all the way until I got this right. You're the BEST!

[DataMiser]

You're welcome, glad I could help