Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Mar 14, 2000

Data Access Dreams with VB's Data Environment! (Part 1)

By Anthony Loera

Download & Install Codec(128 KB) to see Video(1,772 KB) if slow, save video to hard drive first, then run!


Hi, last night I was data dreaming. Yes, dreaming that all the data in the world was at my disposal with only one key. And that this one key could open any data door imaginable, and that this one key could be used by any 10 year old out there...

Ok, so I woke up, and although it was only a dream, I could not help but to think how the Visual Basic Data Environment Object could evolve into such a key...  


How can you use this super simple data access object? Specially if your only 10 years old? Lets find out! This project will have you using 1 DataEnvironment Object quickly doing such things as... 

  • Connecting to SQL Server 7(1 of MANY Databases) 
  • Viewing a table
  • Executing a stored procedure
  • Executing a Query on the fly
  • Executing a stored procedure with a parameter
  • Returning a recordset
  • and whatever else I can think of... 

You'll need:

1 Visual Basic 6 Application

1 SQL 7 Server

1 set of hands (to pinch yourself, when you start thinking that you've got to be dreaming...)


Great, now let's start:

 

First make sure you have a table, or view in your SQL 7 Server that you want to chart. For those of us that have no data available, open the 'Query Analyzer' and copy-paste & execute the following:

 

(For those who did the chart project, this is actually the same table and data)

---Start SQL commands---

use pubs

if exists (select * from sysobjects where id = object_id(N'[dbo].[DataTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DataTable]
GO

CREATE TABLE [dbo].[DataTable] (
[Data_Date] [datetime] NOT NULL ,
[Data_Value] [int] NOT NULL ,
[Data_Name] [char] (20) NOT NULL 
) ON [PRIMARY]
GO

INSERT [dbo].[DataTable] VALUES ('01/01/1999', 10, 'SuperGuyToy')
INSERT [dbo].[DataTable] VALUES ('02/01/1999', 15, 'SuperGuyToy')
INSERT [dbo].[DataTable] VALUES ('03/01/1999', 20, 'SuperGuyToy')
INSERT [dbo].[DataTable] VALUES ('04/01/1999', 13, 'SuperGuyToy')
INSERT [dbo].[DataTable] VALUES ('05/01/1999', 7, 'SuperGuyToy')
INSERT [dbo].[DataTable] VALUES ('06/01/1999', 18, 'SuperGuyToy')

INSERT [dbo].[DataTable] VALUES ('07/01/1999', 19, 'SuperGuyToy')
INSERT [dbo].[DataTable] VALUES ('08/01/1999', 24, 'SuperGuyToy')
INSERT [dbo].[DataTable] VALUES ('09/01/1999', 26, 'SuperGuyToy')
INSERT [dbo].[DataTable] VALUES ('10/01/1999', 32, 'SuperGuyToy')
INSERT [dbo].[DataTable] VALUES ('11/01/1999', 38, 'SuperGuyToy')
INSERT [dbo].[DataTable] VALUES ('12/01/1999', 42, 'SuperGuyToy')

---End SQL commands---

 

Ok, now that we all have some data in SQL 7, we can open Visual Basic 6 and start getting ready to pinch yourself...

 


Visual Basic 6:

 

When you open visual basic, you'll have lot's of projects to choose from like in the picture below:

Don't worry if you don't have all of these, all you really need to open is the "Standard EXE" Project.

  • Open the "Standard EXE" project.

Ok, now that you have selected the Standard exe project, lets look for the DataEnvironment Object and add it! First go to your components section:

 

  Component & Data Environment selection:

1-) under components we will look for the "Controls" Tab on the top of this window and select it, and look for the Microsoft DataGrid control.

2-) Then under components we will look for the "Designers" Tab on the top of this window and select it, and select the Data Environment designer.

 

 

Lastly we will add the Data Environment to the project by selecting it from the Project dropdown menu as shown:

 

Woohoo!! Now we have just added the Data Environment to our project!! You'll notice that it will be listed with your first form:

 

Ok, now that we have selected all the items that we need, Lets place 4 command buttons on the Form, a textbox, and our DataGrid Control:

This is Great! your form is now looking good, and we can start connecting our Data Environment object to the SQL Server!!


Connecting our Data Environment:

 

Here a new VB person is probably thinking something along these lines... "How do we connect? Do we use a crazy looking connection string? And how do I know what all that connection string gibberish means? What the heck am I going to do? Why does this have to be soooo difficult?? This is a nightmare..."

 

But don't worry, It's easy to connect your data environment. How? I'll show you!

 

First double click on your DataEnvironment:

 

Here a window will pop up and show you the items for your Data Environment. Right click on "Connection1" and select it's properties:

 

 

1-) In the provider properties, select the provider for SQL Server and click on the next button, this takes you to "Connection"

2-) In the Connection properties, type in your server name, your user name, password, and the database that you have your data in. After words just click to test the connection. Guess what... your finished!

You have just made a connection to SQL SERVER 7!

Where is the crazy looking connection string? What Happened???

The connection string is still in there, but the data environment took care of the details! This is just too easy!

Now what do we do?  Well I want to be able to see the data on my DataTable. To do this I have to give the Data Environment a command, or create a command for it.


Adding a command:

To Add a command we simply go back to the Data Environment Window we used to get into the connection1 properties, here we click on the "Add command" button:

Notice that command1 is placed below connection1. We will be using this new object to tell the Data Environment what we want to do.

Now right click the new command1 object, and select properties:

Under the command1 properties we will do the following:

Under database object, select "Table". Under object name, select your SQL Server Table Name, "dbo.DataTable", you finished you're command!

That's it, you have just added a command that tells SQL Server that you want to see the Table "dbo.DataTable".

--- and the crowd is silent ---

You better pinch yourself, what you still don't believe it? You say you want to see if it's for real? You want to see it in action? You want to see the Data? Well...   ok.


Binding the Data Grid:

 

Let's put the command to work, and we'll use the data grid to view the results. The first thing to do is attach the Data grid to this command, here's how you do it:

 

First select the datagrid on your form, the go to the control properties window:

First, Under DataSource, select "DataEnvironment1"

Second, under DataMember, select "Command1", that's it you are done!!

 

That's it. you have attached the command to the data grid! What? You still don't see data? You want data? Where the heck is the data? How do I see the data?

 

Well this is super simple... just run your program, and pinch yourself...


So where's the code? And what do I do with these command buttons? How do I execute stored procedures and all that?

 

This first part was to get your feet wet with the Data Environment Object.

I'll be putting in the next part in a few days!

 

Happy Data Dreaming!

 

About Me:

 

Anthony Loera has been goofing around VB for more than 5 years. Has coded for Bell Atlantic, & Americatel Telecom companies. Currently contracted on a couple projects for a small unknown company called Microsoft, and doing a side project for another small pharmaceutical company, which would kill him if it's name was ever divulged. ;)



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date