469,327 Members | 1,159 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,327 developers. It's quick & easy.

Connect to SQL DB and edit the data

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

Jul 17 '05 #1
5 8639
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
Jul 17 '05 #2
Jason,

Thanks for your reaction.

Merry Christmas and a happy 2004

Tino
"Jason Keats" <jk****@melbpcDeleteThis.org.au> wrote in message
news:bs**********@possum.melbpc.org.au...
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

Jul 17 '05 #3
Jason,

I read your post with interest and have filed the text away for a rainy day.

I only have access to MS Access for my database apps which at the moment tend to
be local to the PC and are merely training exercises. Can I use your approach
for remote Access databases or will I have to invest in other database
technologies?

Thanks,

Ade

Jul 17 '05 #4
Adrian Birkett wrote:
Jason,

I read your post with interest and have filed the text away for a
rainy day.

I only have access to MS Access for my database apps which at the
moment tend to be local to the PC and are merely training exercises.
Can I use your approach for remote Access databases or will I have to
invest in other database technologies?

Thanks,

Ade


If you're talking about the database being on a LAN, then all you have to do
is use a connection string like:

sDatabase = App.Path & "\data\" & "NWind.mdb"

gsCnn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & sDatabase
However, if by "remote" you mean over the internet, then look at "MS
Remote":

http://www.able-consulting.com/ADO_Conn.htm

Hope this helps.
Jul 17 '05 #5
Jason Keats wrote:
Adrian Birkett wrote:
Jason,

I read your post with interest and have filed the text away for a
rainy day.

I only have access to MS Access for my database apps which at the
moment tend to be local to the PC and are merely training exercises.
Can I use your approach for remote Access databases or will I have to
invest in other database technologies?

Thanks,

Ade


If you're talking about the database being on a LAN, then all you
have to do is use a connection string like:

sDatabase = App.Path & "\data\" & "NWind.mdb"

gsCnn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
sDatabase
However, if by "remote" you mean over the internet, then look at "MS
Remote":

http://www.able-consulting.com/ADO_Conn.htm

Hope this helps.


Sorry, I just noticed the dumb example I gave for a LAN connection string.
I generally use a mapped network drive to locate the database, eg
M:\MyDatabases\MyDB.mdb (which, by the way, I read from an INI file). You
could, however, connect to your data access DLL on the "server" using DCOM.

HTH
Jul 17 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by shank | last post: by
5 posts views Thread by Mike L | last post: by
5 posts views Thread by Daniel Bass | last post: by
1 post views Thread by Daniel Loose | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.