connecting VB form to SQL Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

connecting VB form to SQL Server

Author

Topic

seefrank
Starting Member

3 Posts

Posted - 2002-03-04 : 16:47:17

Hi All,
Im wondering if any one knows of a website or has any advice on how to get started making a VB form that connects to a SQL server database. All things being as simple as possible for a beginner.
Thanks in advance
Casey

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-04 : 17:33:53

Here's the steps -

Step 1.
Create or open a vb project - you will be given Form1. Place a button "cmd1" and text box "text1" onto your form. Go to the code window for "cmd1".

Step 2.
From the Project menu, choose references, and turn on Microsoft ActiveX Data Objects 2.6 (or an earlier version)

Step 3.
Paste in the following code: (there are many variations....)


Dim cnDB as ADODB.Connection
Dim rst as ADODB.Recordset
Dim sProvider as String
Dim sSQL as String

'Specifics
sProvider = "Provider=sqloledb;Driver=SQL Server;Server=SERVER_NAME;Database=DB_NAME;Trusted Connection=yes;"
sSQL = "Select name from sysobjects"

'instantiate objects
Set cndb = New ADODB.Connection
cndb.Open sProvider

'send SQL
Set rst = New ADODB.Recordset
rst.Open sSQL, cnDB

'get data from recordset object
Do While NOt rst.EOF
text1.text = rst.Fields("name")
rst.MoveNext
Loop

'Close objects
rst.Close
cnDB.Close

'release memory
set rst = Nothing
Set cndb = Nothing

Step 4. You'll need to modify the sProvider value substituting in your SERVER_NAME and DB_NAME. I've also assumed you'll be using integrated security under NT - which assumes you have permissions to connect as you. If this is not the case- get back to me.

Step 5. Change the SQL to be whatever you want - ie "select COLUMN from TABLE where OTHER_COLUMN = VALUE " etc etc

Note - I've iterated through the recordset to show you how it's done. When you run this code - you'll only see the last record - but hey - if you've got that far you should be happy!!!