Visual Basic

5-Sep-04 4:13

Anybody to help connecting a VB6 program to access a database in SQL Server?

Mekong River5-Sep-04 7:27

5-Sep-04 7:27

You should use ADO reference or ADO control to connecto your SQL server. The suggest step for you (if you are the beginner), try to connect to database by using a wizard. Then learn from the connection string. If you have some problem to remember the connection string, try to read the information from here.[^]

APO-CEDC
Save Children Norway-Cambodia Office

RamBhatt5-Sep-04 20:03

5-Sep-04 20:03

Thank you very much sir. I'm a beginner, could you please tell me how to get the ADO wizard? i got some codes(strings) from some books and it doesn't work. it says 'object not found' etc. is it something related to user connection? really i don't know how to do it. please help me.

jonathan156-Sep-04 4:38

jonathan15

6-Sep-04 4:38

first is to create a reference to the ado library. to do this select references from the project menu and tick the 'microsoft ActiveX Data Objects 2.7 Library' box.

The Code to connect to SQL Server and Create a basic recordset is as follows:

dim cn As ADODB.Connection
dim rs as ADODB.Recordset

Set cn = New ADODB.Connection
cn.ConnectionTimeout = 25
cn.CommandTimeout = 120

cn.Provider = "sqloledb"
cn.Properties("Data Source").Value = YourServerName
cn.Properties("Initial Catalog").Value = YourDatabaseName cn.CursorLocation = adUseClient
cn.Properties("Integrated Security").Value = "SSPI" 'If using Windows integrated security
cn.Open

set rs = new ADODB.Recordset
rs.open "Select * from yourTable",cn

You now have a basic recordset

Dont forget to close everyhing and set to nothing when your done.

Sorry if it sounds basic but you did say you are a beginner

Jon

Mekong River6-Sep-04 5:11

6-Sep-04 5:11

Dear RamBhatt, an example from jonathan15 is good. Try to do it and learn the process step by step. But according to the code, if you copy and paste it in your project don't forget to add the following two lines at buttom of your code:

rs.Close<br />
Set cn = Nothing

If you still have a problem with this example, let me know I will explain you in another way to help you understand with this issue.

APO-CEDC
Save Children Norway-Cambodia Office

success

RamBhatt6-Sep-04 18:25

6-Sep-04 18:25

'Thank you very much sir. it worked as expected. i've some doubts.
'i don't have any ref books at present thatz why i'm putting questions here.

'i chose ActiveX Data Objects 2.5 - is it required to change 'sqloledb' to some other driver?
'in ODBC the setting was SQLServer Authentication, but it still worked, why?
'could you please tell me the difference between RDO and ADO?

to komsot man-sir u told me about wizard whatz it? how to use it? is it something related to 'user connection'?

'the following code, which i got from a book, gave error. could you please tell me why?

the RDODatabase is a class that i got from book. the relevant methods i've pasted down
history

1. when i ran the code as it is it showed 424 object not found
2. when i tried to debug (i don't know much about objects to use class,pls help me) it showed runtime error 40002, data source name not found and no default driver specified. this errmsg was not from my errpara! it was from the class errhandler.

----------------

Dim objDatabase As RDODatabase

'-------my code begins
Public Sub ReadFromStud()

On Error GoTo errPara

'connect to remote DB

'--my code to debug!!
Set objDatabase = New RDODatabase
objDatabase.Connect "ODBCTest", "test", "sa", "sqluser"
'-----

'RDODatabase.Connect "ODBCTest", "test", "sa", "sqluser"

'select query
'RDODatabase.Execute "SELECT * FROM Stud"

Do Until RDODatabase.EOF 'repeat till eof
Debug.Print RDODatabase("idx"), RDODatabase("Name")
RDODatabase.MoveNext
Loop

Exit Sub

errPara:
MsgBox Err & Chr(13) & Err.Description

End Sub
'----my code ends

'the class def is as follows...
'Public Function Connect(Optional strDSN As String, Optional strDatabaseName As String, Optional strUserName As String, Optional strPassword As Variant) As Long
'Call connect to attach to an RDO/ODBC datasource and be happy.

' If ConLiveFlag Then Exit Function

' On Error GoTo Connect_Error

' If strDSN <> "" Then mvarServerName = strDSN
' If strDatabaseName <> "" Then mvarDatabaseName = strDatabaseName
' If strUserName <> "" Then mvarUsername = strUserName
' If Not IsMissing(strPassword) Then mvarPassword = strPassword
' If mvarQueryTimeout = 0 Then mvarQueryTimeout = DEFAULT_QUERYTIMEOUT
' If mvarLoginTimeout = 0 Then mvarLoginTimeout = DEFAULT_LOGINTIMEOUT
'
' If ConLiveFlag Then Call Disconnect
'
' With mvarRDOConnection
' .Connect = ODBCConnectionString
' .LoginTimeout = mvarLoginTimeout
' .CursorDriver = rdUseOdbc
' On Error Resume Next
' .QueryTimeout = mvarQueryTimeout
' On Error GoTo Connect_Error
' .EstablishConnection rdDriverNoPrompt 'open the connection, if named DSN does not exist, ODBC will prompt the user to create one or select a different one
' End With
'
' DoEvents
'
' ConLiveFlag = True
' Connect = 0
'Exit Function
'Connect_Error:
'
' Call HandleError
'
'End Function

'Public Sub Execute(sSQL As String, Optional DatasetType As Variant, Optional Mute As Boolean)
'If the Mute param is True, then no dataset is returned from the server, as in an INSERT query or
'DROP query.
'On Error GoTo Execute_Error
'
' If mvarRDOConnection.rdoQueries.Count Then mvarQueryObject.Close
' Set mvarQueryObject = mvarRDOConnection.CreateQuery(QSQL, sSQL)
' If IsMissing(DatasetType) Then DatasetType = DATASETTYPES.DYNAMIC
'
' 'If we are mute, execute without results
' If Mute Then
' mvarQueryObject.Execute
' Else
' If DatasetType = DATASETTYPES.FORWARD_ONLY Then
' Set mvarRDOResults = mvarQueryObject.OpenResultset(rdOpenForwardOnly, rdConcurReadOnly)
' ElseIf DatasetType = DATASETTYPES.DYNAMIC Then
' Set mvarRDOResults = mvarQueryObject.OpenResultset(rdOpenKeyset, rdConcurRowVer)
' End If
' DoEvents
' boolResultsAvailable = True
' End If
'
'Exit Sub
'
'Execute_Error:
'
' Call HandleError
'
'End Sub

'-------------------
'Public Sub MoveNext()
'On Error GoTo MoveNext_Error
' mvarRDOResults.MoveNext
'Exit Sub
'MoveNext_Error:
' Call HandleError
'End Sub

'Private Sub GenDatabaseError()
' Dim MSG As String
' MSG = Err.Description + "|"
'
' For Each DataErr In rdoErrors
' MSG = MSG + "[" & DataErr.Number & "]" & DataErr.Description & Chr(13)
' Next DataErr
'
' lErrorNumber = Err.Number
' sErrorMessage = MSG
'
' Err.Raise lErrorNumber, ERROR_SOURCE, sErrorMessage

'End Sub

'Private Sub HandleError()

' Dim lErrNum As Long
' Dim sErrMsg As String

' lErrNum = Err.Number
' sErrMsg = Err.Description

' If lErrNum = 40002 Then
' Call GenDatabaseError
' Else
' Err.Raise lErrNum, ERROR_SOURCE, sErrMsg
' End If

'End Sub

Re: success

Mekong River7-Sep-04 21:42

7-Sep-04 21:42

I can answer for you for only question that I know and base on documentation.

1. Difference between RDO and ADO.

RDO stand for Remote Data Objects

Remote Data Objects implement a set of objects to deal with the special requirements of remote data access. RDO implements a thin code layer over the ODBC API and driver manager that establishes connections, creates result sets and cursors, and executes complex procedures using minimal workstation resources. Although RDO is also accessed by DAO when your code creates an ODBCDirect Workspace object, documentation for this implementation can be found by searching for specific ODBCDirect topics.

Note RDO and the RemoteData control are designed to be used only in 32-bit operating systems such as Microsoft Windows 95 (or later) or Microsoft Windows NT.

With RDO and the RemoteData control, your applications can access ODBC data sources without using a local query processor. This can mean significantly higher performance and more flexibility when accessing remote database engines.

By using RDO, you can:

Create simple cursorless result sets, or more complex cursors.

Run queries and process any number of result sets.

Execute stored procedures that return result sets with or without output parameters and return values.

Execute action queries that perform data manipulation or data definition operations.

Limit the number of rows returned or processed.

Monitor all of the messages and errors generated by the remote data source without compromising the executing query.

Enable synchronous, asynchronous, or event-driven asynchronous processing so your application isn't blocked while lengthy queries are executed or the current row pointer is repositioned.

ADO stand for ActiveX Data Object

Solutions for Local Data Access

The Issue
You want a simple, consistent application programming interface (API) that enables applications to gain access to, and modify a wide variety of data sources. A data source may be as simple as a text file, as complex as a cluster of different types of databases, or something yet to be invented. Furthermore, the API should not presume the means of gaining access to and manipulating the data source.

Although these are your specific requirements, the typical data source is a relational database that supports the Open Database Connectivity (ODBC) standard and is manipulated with commands written in Structured Query Language (SQL).

The general solution Microsoft offers to this problem is OLE DB, a set of Component Object Model (COM) interfaces that provide uniform access to data stored in diverse information sources. However, because the OLE DB application programming interface is designed to provide optimal functionality in a wide variety of applications; it does not meet the requirement for simplicity.

You need an API that is a bridge between the application and OLE DB. ActiveX Data Objects (ADO) is that bridge.

The Solution
ADO defines a programming model—the sequence of activities necessary to gain access to and update a data source. The programming model summarizes the entire functionality of ADO.

The programming model suggests an object model—the set of objects that correspond to and implement the programming model. Objects possess methods—which perform some operation on data—and properties—which either represent some attribute of the data or control the behavior of some object method.

Associated with objects are events—which are notifications that some operation has occurred, or is about to occur.

Basic ADO Programming Model

ADO provides the means for you to perform the following sequence of actions:

Connect to a data source. Optionally, you can ensure that all changes to the data source occur either successfully or not at all.

Specify a command to gain access to the data source, optionally with variable parameters, or optionally optimized for performance.

Execute the command.

If the command causes data to be returned in the form of rows in a table, store the rows in a cache that you can easily examine, manipulate, or change.

If appropriate, update the data source with changes from the cache of rows.

Provide a general means to detect errors (usually as a result of making a connection or executing a command).
Typically, you will employ all these steps in the programming model. However, it is worth noting that ADO is flexible enough that you can do useful work by executing just part of the model. For example, you could store data from a file directly into a cache of rows, then use ADO resources solely to examine the data.

(The source of this document were took from MSDN Library January 2000)

2. About the wizard

It look like the wizard but it just 2 importance step that you need to understand, first select the connection type and second choose the database. To understand this follow this step:

1. Create a new blank project in vb6.
2. Go to Project menu and click on Component submenu.
3. When the Component dialog box appear scroll to Microsoft ADO Data Control 6.0 (OLEDB) and checked it.
4. An ADO data control appear on the tool box and click and draw it on your form.
5. When an ADO data control appear on the form right click on it and choose ADODC Property.
6. From the property dialog box of ADO data control in the "Source of Connection" frame you will see three option. The first one is "Use Data Link file" the second one is "Use ODBC data source name" and the third one is "Use connection string".
7. Choose the third option (Use connection string) and click the build button.
8. You will see the Data Link Property dialog box appear with a difference type of data that you want to connect. If you want to connect to SQL server database you can click on "Microsoft OLE DB Provider for SQL Server" and click on "Next >>" button. The next tab of property dialog box appear with 3 step for you to follow. The first one is "Select or enter the server name:" click on the drop down list to choose the server name that contain SQL database install on it. The second step is "Enter information to log on server", you can choose two option (SQL or Windows authenication). This example I prefer Windows authenication. And the third step is select database on the server. I choose master database (just an example, master database is the core database of SQL server. You should not modified to it in case of necessary). Then click on "Test connection" button. If you see message box said that "Test connection successed." then it is working properly.
9. Click on OK button twice and you will see the connection string in the text box "Provider=SQLOLEDB.1;Integrated Security=SSPI; Persist Security Info=False;Initial Catalog=master;Data Source=APOCEDC".

Learn from here try to customize the code with any database that you create in SQL server. Then you will see how it is working.

Hope it is work!!! and wish you good luck in programming!!! Rose | [Rose]

APO-CEDC
Save Children Norway-Cambodia Office

Re: success

RamBhatt8-Sep-04 3:44

Murtuza Husain Miyan Patel

8-Sep-04 3:44

Thanks a lot sir. i got the way and it is really useful. now i can make a Library program which can be used in a network than in a stand alone system. a long way to go i know. again thanks.

How to use

Murtuza Husain Miyan Patel5-Sep-04 4:08

5-Sep-04 4:08

Hello there
Can anybody tell me how to use a VB6 form application on my Pocket pc.

thanks
murtuza

Re: How to use

Mekong River5-Sep-04 7:22