VB – SQL Server (VB ver6)

Topic

New Reply

Hi,

What are the declaration that need to be made in the VB codes in order to access to the SQL Server database? Thanks…

Reply | Quote

Replies

If you want to display data from a SQLServer database, you don’t need to declare anything explicitly. Place a Microsoft ADO Data Control 6.0 (SP4) (aka Adodc) on your form; doing this will automatically set a reference to the appropriate libraries (on my machine, it’s Microsoft ActiveX Data Objects 2.5 Library and Microsoft Data Binding Collection VB 6.0 SP4).

If you want to manipulate data in code, set a reference to the Microsoft ActiveX Data Objects 2.5 Library (the version number may be different) in Project/References…

Reply | Quote

Here’s some sample code for connecting to SQL Server using ADO. This is VBScript (for use in an .ASP page), so you will need to modify the variable declarations to actual var types and change the Server.CreateObject calls:

Dim objConn, objCommand, objRS
‘ Set up connection object and OLEDB string for SQL Server/MyDB database
Set objConn = Server.CreateObject(“ADODB.Connection”)
objConn.Provider = “SQLOLEDB”
objConn.Properties(“Data Source”).Value = “MYDB”
objConn.Properties(“User ID”).Value = “MyPassword”
objConn.Properties(“Password”).Value = “”
‘ Open the connection
objConn.Open

‘ Set up command (query) object
Set objCommand = Server.CreateObject(“ADODB.Command”)
objCommand.ActiveConnection = objConn
objCommand.CommandType = adCmdText
objCommand.CommandText = “SELECT Table1.Field1, Table2.Field1, TAble2.Field2 ” & _
“FROM Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field3 ” & _
“WHERE Table1.Field2 LIKE ‘%Lounge%’ ” & _
“ORDER BY Table2.Field1, Table2.Field2”

‘ Set up recordset and run query
Set objRS = Server.CreateObject(“ADODB.Recordset”)
objRS.CacheSize = 20
objRS.Open objCommand, , adOpenForwardOnly, adLockReadOnly

‘ Check for nothing found before setting up table
If objRS.EOF Then ‘recordset is empty
Response.Write ”

Nothing there, sorry.”
Else ‘lots to type!
‘Do your thing
End If

‘ Clean up
objConn.Close
Set objRS = Nothing
Set objCommand = Nothing
Set objConn = Nothing

Reply | Quote