Data Access Dreams with VB's Data Environment! (Part 2)
October 16, 2000
Today we are going to look at the great VB Data Environment again. Why? Well because we have yet to do all the nifty things we can do with it! This a wonderfully exciting data object that is just toooo EASY to use! Specially if your only 10 years old! What's so cool about talking to a server when your 10 years old???
Hey have you guys seen this cool game called Allegiance? Yeah, so it's a space war simulator and dozens of gamers can be playing on the internet at the same time!! Did you guys know that SQL Server 7 was used to have every real-time space battle between everybody at the same time? Hey, If you 10 year olds can start communicating with SQL servers, I think we'll have MUCH cooler looking games!!
What? You want e-commerce stuff? Business Analyst stuff? Geez your a boring 10 year old, but I guess VB Data Environment will work for that too...
Ok, so... get ready to drop you're jaw, because were going to be doing some super simple Data Access Dream stuff...
In Part 1 we used the Data Environment to talk to the SQL Server, and command the server to show us a table. What else can you do with this super simple data access object? Specially if your only 10 years old? Lets find out! This project will have you using 1 Data Environment Object to do these things...
The dinky application that we built in Part 1(see part 1 first if ya don't have it!) and the Part 1's data already in SQL Server.
Ok, got part 1's stuff? Great! let's continue then!! Oh yeah you also need:
1 set of hands (to pinch yourself, when you start thinking that you've got to be dreaming...)
Great, now let's start:
Visual Basic 6:
Open the application in visual basic, it will look like the picture below:
Once you load the VB application, you'll notice the Data Environment in our project!
Ok, now that we loaded the application, check your server connection by first double clicking on the "DataEnvironment1" on the window shown above. A new window will open up (see below), here just right click on "Connection1" and select it's properties.
Great, now In the Connection properties, Check your server name, your user name, password, and the database that you have your data in. After everything looks good, just click to test the connection to see if you are properly connected!
This is Great! You have checked your SQL Server connection, and we can start executing a stored procedure using the DataEnvironment object!!
Creating the stored procedure for the project:
First let's create s stored procedure, just open the 'Query Analyzer' and copy-paste & execute the following:
(it creates a simple stored procedure, that will later show us the data, ordered by the 'Data_value')
---Start SQL commands---
---End SQL commands---
Alrighty then, we now have a Stored Procedure in SQL Server! But how the heck do we tell the DataEnvironment Object about it? and How the heck do we use the darn thing? No problema dudes, just HAng in their for the simple answers!!
Adding a Stored Procedure Command:
To Add a Stored Procedure Command we simply go back to the Data Environment Window we used to get into the connection1 properties, first select the "Connection1" to highlight it, then we click on the "Add command" button:
Now right click on your new command ("Command2"), and select properties. Here a window will pop up and show you the items for your command2 properties:
When this is opened, you simply select "Stored Procedure" in the Database object dropdown list, then simply select the name of your brand new stored procedure in the Object Name dropdown!! (This is JUST TOO EASY!!)
You have just made a Stored Procedure command for your DataEnvironment!!!
What!!? That's it? No way, I must have read over some really hard and disgusting details somewhere, where's the sweat and tears? Where's the hard labor? Where's the 8 hour long code debugging?? Well, if your masochistic, you can dive into some really nasty stuff in nightmare land, but not here ok, so stop yer blathering!
Now what do we do? Well I want to be able to use my stored procedure of course! To do this I have to assign this DataEnvironment command to one of my buttons! (O-oh, we are actually going to type a piece of code...and, HEY, their is no fainting allowed!)
Using the command button:
To use the command button we will need to type in some code on it's click event, an easy way to to this is to double click on the button (use command1 button for now...) you want to use. You will then see the "Code Window" pop up, here you would just add the following super easy and cheesy code:
Since the DataGrid control was already assigned to the DataEnvironment object in part one, we only need to tell it to use the stored procedure command we just created, and to refresh the grid. Wahoooo!! Is this easy or WHAT? We are done with this!
REALLY!! We are finished and we have now created the ability to have the application use a stored procedure!! What? You want to check it out for yourself? Ok, run your application, and then press the command1 button! You'll see that the grid changes to reflect our stored procedure!
You are now allowed to pinch yourself !
You Want More???
Ok, but from here on out, we'll be using more and more code! (So beware!) ;)
Here were going to create a way so that you can enter your own SQL statement, and have your SQL Server give you 10 year olds what you want:
How do we do something like this? Is it super hard? Will it harm me? Will the silly rabbit ever eat Trix? Hmmm... all good questions indeed! Hopefully I can provide you with the easy answers you are looking for!
Create SQL Command:
First thing is to create another command, to do this we simply go back to the Data Environment Window we used to get into the connection1 properties, first select the "Connection1" to highlight it, then we click on the "Add command" button:
Next, you select the "SQL Statement" under the command3 properties, and type in a simple SQL statement. (You can't leave the box blank)
That's it, you have just added a command that tells SQL Server that you want to use an SQL Statement. Cool Beans!
Ok, so how is this going to help me do an on the fly Query? Do I have to do this all the time if I need to change this SQL statement? Why do I feel like I'm gong to be coding some more??
Don't worry too much! First of all, since you are only 10, just worry about Saturday morning "Pokemon and the Johto Journeys" for now, I'll help you with the simple code for the command button ... here we go!
Using the command Button for SQL:
To use the command button with the SQL Command we will need to type in some code on it's click event, an easy way to to this is to double click on the button (use command2 button for now...). You will then see the "Code Window" pop up, here you would just add the following simple easy code:
Now, what we told the application to do is this: Tell SQL Server to execute whatever I type in the text1 box. So if you type gibberish in the text1 box, then the server will give you an error, and you'll feel like something is not working. Just remember that you need to use SQL Commands in the text1 box.
Oh, one last thing... change the text1 property called "Multiline" to true, also change the "scrollbars" property to "2-Verticle". That way you can type in really long commands into the text1 box easily.
Ok, guess what!! You are Finished!! You are kicking major but! Now you can easily do Queries On the Fly, and without getting all crazy.
(Way too easy right?!) OK! You want to run it? Go ahead! Just remember to type in a correct SQL statement in the text1 BOX!!!
More? Whew! Ok, ok! here's more...
Wow, a stored procedure with a parameter. This sounds complicated!!! (It's not, but you can sure impress 9 year olds with it!)
Ok first, we create the stored procedure with a parameter, just drop it in Query Analyzer and execute the SQL Stuff below:
(skip this part if you know about parameters)
(This stored procedure would bring back only the data that has a particular Data_value. The parameter '@value' only accepts numbers(integers))
You would normally execute this by typing in " exec my_storedprocedure2 10 " in the Query Analyzer after creating this procedure. The Parameter in this command is the number 10. But you can put in any old number you want here, because the parameter accepts numbers. If it does not bring back any data, it just means that their is nothing with your number in the table. (ok enough of the Basics..)
---Start SQL commands---
---End SQL commands---
Creating the stored procedure command:
(This might feel like Deja-vu for some of you...)
Create another command in the DataEnvironment by clicking the "Add Command" button, and assign it the new Stored Procedure in the new command's properties:
(man this is Too EASY!!!)
Now YOU MUST go and put in something for the value of the parameter, you cannot leave the following part blank. To do this, select the "Parameter" Tab in the Properties window where you assign the stored procedure to the command (see above). Then Select the name of your parameter in the "Parameters" box (see Below) once it's highlighted, just type in any old number in the bottom "Value" box... (I Typed the number 9 here just because I thought 9 is a cool number...that's all)
Now (whew!) you are done telling the DataEnvironment about the parameter. (If you use more than one parameter in a stored procedure, the same thing applies to them.)
Isn't this great? You are almost done with the silly parameter stuff!!
Using the command Button for this Stored Procedure:
To use the command button with this Stored Procedure we will need to type in some code on it's click event, an easy way to to this is to double click on the button (use command3 button for now...). You will then see the "Code Window" pop up, here you would just add the following (Yawn) code:
Now, what we told the application to do is this: Tell SQL Server to execute the new stored procedure with anything I type in the text1 box as the parameter. So if you type gibberish in the text1 box, then the server will give you an error, and you'll feel like something is not working. Just remember that you need to use a proper parameter in the text1 box. (For this Stored procedure you can only enter numbers, maybe in some other stored procedure you can only enter a name, or letters, or something else...)
YOU ARE NOW FINISHED!! Yep, you can now use the DataEnvironment to run a stored procedure with this thing called a parameter!! Why would you use this? Stored procedures are simply faster to run than SQL on the fly!
Well that was super simple... just run your programs wipe the cheeto crumbs off your face, and if you still think your dreaming... pinch yourself.!!
This was the second part of Data Access Dreams with the Data Environment Object.
I hope you all can see why I like this Data Environment Object so much. I hope that the more and more you use it... you'll start noticing and wanting to do other things. Eventually you will start learning how to use other advanced magical objects, like disconnected recordsets, Active X object creation and the MS XML objects. But even if you don't get into these caffeine inducing areas, just knowing how to use the Data Environment Object will give you a good nights rest when you have to whip up some Data Access Application fast.
Happy Data Dreaming!
Anthony Loera has been working with VB for more than 5-6 years. Has coded for Bell Atlantic, Merck pharmaceuticals & Americatel Telecom companies. Currently contracted on a couple projects for a small unknown company called Microsoft, and will be open to accept offers for late November 2000 using advanced VB/SQL on projects or for a PM position.