T. Wintershoven wrote:
Hello,
Although i write programs in VB for a while now, i've allways used the
datacontrol and MS-Access DBs for data storage and never (and i mean
never) used SQL.
I lookt in many sample codes, red some books but i still can't
connect to a SQL server and edit the data.
Is there someone who is willing to take a little time for me and
explane to me how it works and maby give me some sample code (very,
very basic) on how to connect to a SQL DB and store, edit and remove
records from it.
Preferably, don't use the standard VB datacontrol
I want to learn it so much, but all i do doesn't seem to work.
Many thanks in advance.
Kind regards,
Tino Wintershoven
The Netherlands
Tino,
Here's lesson one...
'References: Microsoft ActiveX Data Objects 2.1 Library (or later)
'Module Code...
Option Explicit
Public gsCnn As String
Sub Main()
Dim i As Integer
Dim sSQL As String
Dim rst As ADODB.Recordset
Const sIP As String = "127.0.0.1"
Const sPort As String = "1433"
Const sDatabase As String = "Northwind"
Const sUID = "sa"
Const sPwd = ""
gsCnn = "Provider=SQLOLEDB;Network Library=DBMSSOCN;"
gsCnn = gsCnn & "Data Source=" & sIP & "," & sPort
gsCnn = gsCnn & ";Initial Catalog=" & sDatabase
gsCnn = gsCnn & ";User ID=" & sUID & ";Password=" & sPwd
sSQL = "SELECT * FROM Suppliers WHERE Country = 'USA'"
Set rst = GetSQL(sSQL)
If Not (rst Is Nothing) Then
With rst
If .BOF And .EOF Then
'no records available
Else
Do Until .EOF
Debug.Print String$(50, "-")
For i = 0 To .Fields.Count - 1
Debug.Print .Fields(i).Name & " : " &
..Fields(i).Value
Next i
.MoveNext
Loop
Debug.Print String$(50, "-")
End If
.Close
End With
Set rst = Nothing
End If
End Sub
Public Function GetSQL(ByVal sSQL As String) As ADODB.Recordset
On Error GoTo errHandler
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = New ADODB.Connection
With cnn
.Mode = ADODB.adModeReadWrite
.ConnectionTimeout = 15
.CommandTimeout = 30
.Open gsCnn
End With
Set rst = New ADODB.Recordset
With rst
Set .ActiveConnection = cnn
.CursorLocation = ADODB.adUseClient
.LockType = ADODB.adLockBatchOptimistic
.CursorType = ADODB.adOpenStatic
.Source = sSQL
'.Properties("Update Criteria").Value = ADODB.adCriteriaAllCols
.Properties("Update Criteria").Value = ADODB.adCriteriaKey 'FORCE
OVERWRITE!
.Open , , , , ADODB.adCmdText
If .State = ADODB.adStateOpen Then 'disconnect
recordset
Set .ActiveConnection = Nothing
Else
Debug.Assert False
End If
End With
Set GetSQL = rst
If Not (cnn Is Nothing) Then
If cnn.State = ADODB.adStateOpen Then cnn.Close
Set cnn = Nothing
End If
Exit Function
errHandler:
' Your choice of error handlers (I use HuntERR)
End Function
All the above does is show you how to connect, retrieve a (disconnected) ADO
recordset and dispaly the data.
Naturally you will want to add functions to Execute an SQL statement (eg to
UPDATE or DELETE a record) and to INSERT a record and return the IDENTITY
value.
You should, of course, wrap up all these functions (once you've written
them) in a Data Access class.
I could supply you with all you need, but then you wouldn't learn anything
:-)
HTH,
Jason.
P.S. Merry Christmas
