August 30, 2004 at 3:23 am
Hello
I am writing a code in vb6. I want to know the best way to connect to sql server and do the select query.
thanks for helping a newbie.
August 31, 2004 at 6:17 am
The following brings up the standard Windows connection dialog and returns a Connection object (You need Microsoft OLEDB Service Component and Microsoft ActiveX data Objects in your project references) :
Dim l_objDataLink As MSDASC.DataLinks
Dim l_conConnection As adodb.Connection
Dim l_aConnectionData() As String
m_cConnectionString = ""
Set l_objDataLink = New MSDASC.DataLinks
l_objDataLink.hwnd = WindowHandle ' Sets the parent window
Set l_conConnection = l_objDataLink.PromptNew
If l_conConnection Is Nothing Then ' User chose 'Cancel'
Set l_objDataLink = Nothing
Exit Sub
End If
The .ConnectionString property of l_conConnection can be stored in the registry or an ini file - don't hard code it!
There are many ways to pass queries to SQL Server, either as command text or by executing stored procedures - I prefer the latter. The following is a simple function to return user data from a stored procedure :
Public Function GetUsers(UserName As String) As adodb.Recordset
' Returns a recordset containing user details.
' If a username is not specified, returns details of all users.
Dim l_cmdCommand As adodb.Command
Dim l_rsUserList As adodb.Recordset
On Error GoTo GetUsersErr
Set l_rsUserList = New adodb.Recordset
Set l_cmdCommand = New adodb.Command
With l_cmdCommand
.ActiveConnection = m_conConnection ' A module level variable
.CommandType = adCmdStoredProc
.CommandText = "usp_GetUserData"
.Parameters.Append .CreateParameter("@Username", adVarChar, adParamInput, 127, UserName)
Set l_rsUserList = .Execute
End With
Set GetUsers = l_rsUserList
Set l_cmdCommand = Nothing
Set l_rsUserList = Nothing
Exit Function
August 31, 2004 at 6:51 am
My first bit of advise is to put as much of the query in SQL server as possible to limit the amount of code that you have to write in VB6.
In SQL Server, create either a view or a stored procedure that contains the actual query you want.
In VB6
make a reference to the ADODB library
Declare a connection object (dim cn as new ADODB.Connection)
Declare a recordset (Dim RS as new ADODB.Recordset)
To make your life easy with the connection string creation (for the connection object), go to your desktop, and create a brand new text file called connection. Then change the extension to UDL - so name it connection.UDL. It will change icon to be a table and a computer. Double click that, and go through the wizard to buiold your connection string. Whenm done, cloe the wizard, and open the fild connection.UDL with notepad. Copy and paste the connection sting it created into the VB6 code
something like:
cn.open "server=blah, blah, blah"
open the record set
rs.open "select * from view",cn <---refer to the conenction object
now populate your control by iterating through the record set, or set the source of a grid to the recordset.
There are also wizards in VB6 that will walk through all of this for you. Granted it creates some bulky code at time,s but it is damn easy and quick.
regards,
Michael
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply