Visual Basic Forms over Data Video Series Samples

NEW POST: Any Questions?

johnnygoodey — Fri, 22 Aug 2008 04:51:01 GMT

Hi Beth

Thanks for this series. Really helped a lot.

I can't seem to select a dataset project as referred to in your "#18 | How Do I: Use DataSets in an N-Tier Application?" when the dataset project is in a solution folder. Is there a way around this or am I doing something wrong.
As soon as I move the dataset project to the root folder of the solution, it works.

Thanks

Johnny

NEW POST: Any Questions?

BethMassi — Wed, 13 Aug 2008 16:52:07 GMT

Hi Darh,

It sounds like you need to create a parameterized query on your TableAdapter. Here's some information on how to do that: http://blogs.msdn.com/bethmassi/archive/2007/05/25/creating-a-parameterized-query.aspx

You can also check the MSDN library: http://msdn.microsoft.com/en-us/library/ms171905.aspx

HTH,
-B

NEW POST: Any Questions?

darh — Tue, 12 Aug 2008 02:12:41 GMT

Hi Beth,

Many, many thanks for this series. You've really helped me get through some major sticking points in my learning curve. I hope you can point me past my next one, even if it's just were to go RTFM. My apologies if I missed this in the videos.

When I set my application to do something like:
Me.CustomerTableAdapter.Fill(Me.CustomerLookupDataSet.Customer)
Me.OrderDetailTableAdapter.Fill(Me.OrdersDataSet.OrderDetail)
Me.OrdersTableAdapter.Fill(Me.OrdersDataSet.Orders)
it pulls ALL the records from the database. In my case I'm displaying data for one customer, plus all that customer's orders. I'm using a combo box to select the particular customer to display. Rather than pulling all the data at once, I need to trigger the select and fill the form once the customer is selected. (FYI, an added complication is that I'm using a product called QODBC to access QuickBooks data.)

I've tried setting up a separate lookup data source for just customer names & Ids, then binding the selected value to my complete customer data source, but can't get it to work. It should, shouldn't it? Or do I need to go the event programming route?

Thanks,

darh

NEW POST: Sample DBs in SQLServer 2005 -- OMS.mdf has no log in the code avalable for download

Seasoned — Sun, 20 Jul 2008 00:38:21 GMT

Seasoned wrote:
The office manager database sample or OMS.mdf has a missing log file in the sample code downloaded. Where can it be downloaded from?

Nevermind. OMS.ldf is not needed. Using sqlcmd, the OMS.mdf file can be attached without the log file. SQL Server simply creates it for you.

NEW POST: Any Questions?

Seasoned — Sat, 19 Jul 2008 22:58:23 GMT

Sample DBs in SQLServer 2005 -- OMS.mdf has no matching log in the code available for download
First post: Seasoned wrote: The office manager database sample or OMS.mdf has a missing log file in the sample code downloaded. Where can it be downloaded from?

Nevermind. OMS.ldf is not needed. Using sqlcmd, the OMS.mdf file can be attached without the log file. SQL Server simply creates it for you.

NEW POST: Sample DBs in SQLServer 2005 -- OMS.mdf has no matching log in the code available for download

Seasoned — Sat, 19 Jul 2008 22:52:21 GMT

The office manager database sample or OMS.mdf has a missing log file in the sample code downloaded. Where can it be downloaded from?

Nevermind. OMS.ldf is not needed. Using sqlcmd, the OMS.mdf file can be attached without the log file. SQL Server simply creates it for you.

NEW POST: Sample DBs in SQLServer 2005 -- OMS.mdf has no log in the code avalable for download

Seasoned — Sat, 19 Jul 2008 22:51:30 GMT

The office manager database sample or OMS.mdf has a missing log file in the sample code downloaded. Where can it be downloaded from?

NEW POST: Sample DBs in SQLServer 2005

BethMassi — Tue, 15 Jul 2008 21:27:43 GMT

You can run the samples with SQL Server 2005 as well.

NEW POST: Trouble with Delete in multi-table form (video 3)

BethMassi — Tue, 15 Jul 2008 21:26:05 GMT

Hi coleifer,

The video uses the Data Source Wizard to automatically create the update, insert and delete statements. If you use the toolbar (BindingNavigator) you can add and delete the parent rows and if you want to delete the child rows, just select the row header and press the delete key. Under the covers the controls are bound to a BindingSource and you can programatically call AddNew or Delete on the parent or child bindingSource to add or remove rows from the DataSet. When you call Update on the tableAdapters later, the UpdateCommand, InsertCommand and DeleteCommand are executed against the databasse. To look at those commands select the TableAdapter in the Dataset designer and look at the properties.

These videos will help explain these concepts better:
http://msdn.microsoft.com/en-us/vbasic/bb725824.aspx
http://msdn.microsoft.com/en-us/vbasic/bb725826.aspx
http://msdn.microsoft.com/en-us/vbasic/cc138241.aspx

HTH,
-B

NEW POST: Sample DBs in SQLServer 2005

silverhalide — Thu, 03 Jul 2008 13:20:04 GMT

Great videos... very helpful.
How can I run the sample DB (like OMS.mdf) in SQLServer 2005? I don't have SQLExpress installed.

NEW POST: Need to populate database programmatically...

Mostly_Lucid — Mon, 30 Jun 2008 12:47:48 GMT

Hello Greg;

Not sure if this is the direction you are going but here are some ideas to maybe research until someone else gets you a more complete answer:

Forgive me if this is too basic or not even in the general ball park of what you are looking for:

Also note that in the code examples a sometimes it is converting my variable names to italics and dropping the underscore character...hope you can still make sense of it?

You could create a custom class for helping to work with the data
Part of that class could be a constructor or method to write the data to the database

I just recently had a project that had to programmatically write to the database (up until that point all my apps pretty much just had the user entering info):

The general process I use is as follows:
1. store a connection string and use that to open a connection to the database useing the .open
a. example: Dim connection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\LOGISTICS.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
b. note that you would replace everything is quotes above with whatever your connection string would be. One way to help come up with your connection string is when you are creating a new datasource open the connection string portion of the info box and you can copy/paste from it.
c. connection.open() -- this uses the connectoin string you created above and tries to open it.
2. once I had an open connection I used the sqldatareader to read the data into memory string variables
3. at that point I am having to create a text file so I am using the textout.write but it does not sound like that is what you are trying to do, so...
4. another section of the program I am needing to write data out to the database - I created a class with properties to store what I needed and methods to write the properties I just set to the database
5. I open a connection the same as above
6. I dim a string as a New SqlCommand()
a: Dim cmdInsertData As New SqlCommand()
7. I use parameters and move data into them
a: Dim prmPackageStatus As New SqlParameter("@Packagestatus", mpackage_status)
b: this will create a variable called prmPackageStatus and put the data stored in the private variable mpacakgestatus into it
c: in my case m_pacakge status happens to be filled in by use of a class but you could get data into it anyway you wanted.
8. Call the connection using your connectoin string and your sqlcommand:
a: cmdInsertData.Connection = connection
9. I then build a string that holds the exact sql command I want to run
a: Dim sqlStatementBuild As String = "insert into Package (Packagestatus) values (@Packagestatus,)"
10. move that statement into your sqlcommand
a: cmdInsertData.CommandText = sqlStatementBuild
11. open a connection, write the data and close the connection: (remember the word connection below is a string varaible holding my connection string)
connection.Open()
cmdInsertData.ExecuteNonQuery()
connection.Close()

I do now if this helps or not, but it is one way to programmatically access a database. Keep in mind that this is my first program to do this so I am sure there are better ways!
As a matter of fact if anybody knows a better way I would love to hear it!

Not sure about the adding columns from the grid view -- if you still need an answer repost here and I would be glad to research it some....it would certainly be useful to know how to do that.

Joe Ruder

NEW POST: Trouble with Delete in multi-table form (video 3)

coleifer — Sun, 29 Jun 2008 19:10:59 GMT

I've been following the videos and in video 3, when there are multiple tables on a single form, the video does not explain how to implement DELETE or INSERT functionality. How does this work?

NEW POST: Need to populate database programmatically...

Greg — Thu, 26 Jun 2008 02:15:12 GMT

Hi There.
I watched the series of videos, and wondered; "How can I add data to the database by automatically by code?" I am using visual studio 2008.

I want to take input from user, generate number sets from this input, then put the number sets into the database. Depending on the input, there could be anywhere from 64 to 4096 individual number sets all refering to the user input.
The user will then be able to manipulate the number sets and add descriptions, names, etc., with the original input as reference (also to be stored in the database)

I also need to know how to add columns (or remove columns) from the grid view depending on the user's input at run time.

Is this even possible?

NEW POST: Any Questions?

omarcb11 — Sun, 15 Jun 2008 00:41:37 GMT

BethMassi wrote:
Hi Omar,

When you specify the join in the select command using the Query builder, it will ask you if it shuold regenerate the commands for insert/update/delete. Just say NO and those commands will remain intact.

HTH,
-B

Hi Beth, I did that but it doesn't asked me that! I have VS 2005 the professional edition

NEW POST: Any Questions?

BethMassi — Fri, 13 Jun 2008 23:52:11 GMT

Hi Omar,

When you specify the join in the select command using the Query builder, it will ask you if it shuold regenerate the commands for insert/update/delete. Just say NO and those commands will remain intact.

HTH,
-B

NEW POST: Any Questions?

omarcb11 — Fri, 13 Jun 2008 16:43:00 GMT

BethMassi wrote:
If you're not allowing the user to overwrite the price per OrderDetail then the detail.Price column doesn't need to exist in the database. You can specify a join in your query to create the column only in the OrderDetail DataTable (this is the client-side entity) and use it as a placeholder on the form.

HTH,
-Beth

Hi Beth and thank you again, yes! I'm not allowing the user to overwrite the price per OrderDetail so I don't need the OrderDetail.Price field, but when I specified the join in the query, the DataSet designer eliminates me the commands(insert, update, delete, that's what I discover) because in the code of the "Function Save" there's no Update statement for the OrderDetailTableAdapter, so what I do for this is that, before I make the join in the query I copy the commands text then I do the join and put the commands text manually, but that throws me an error when I click save.

What can I do so my form could run great? :(

Thank You.

NEW POST: Any Questions?

Mostly_Lucid — Fri, 13 Jun 2008 13:24:35 GMT

Beth,

Thank you for the answer and the explaining code to go along with it. BTW - I recently finished video 15 of yours - it really did a good job of tying together stuff I had already learned from the previous videos. Thanks!

Joe

NEW POST: Any Questions?

BethMassi — Wed, 11 Jun 2008 17:17:25 GMT

Mostly_Lucid wrote:
You have us insert code into the combox1_selectedindexchanged event handler:
Me.ProductBindingSource.Position = Me.ComboBox1.SelectedIndex

What does this code do? My program seems to function the same with or without it.

Hi Mostly_Lucid,

I believe back in VS2005/Fx 2.0 this was necessary because the ListBox and the Combobox did not move the BindingSource's position. And in testing with VS 2008, you'll notice that you get two different position values if you don't set it explicitly in the SelectedIndexChanged event which depending on what you're doing it could cause issues. The BindingSource isn't moving until after the SelectedIndexChanged event fires:

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
MsgBox("Binding Source: " & Me.CustomerBindingSource.Position.ToString)
MsgBox("Combobox: " & Me.ComboBox1.SelectedIndex.ToString)
End Sub

HTH,
-B

NEW POST: Any Questions?

BethMassi — Wed, 11 Jun 2008 16:47:35 GMT

Hi Omar,

Try handling the CurrentChanged event on the ProductBindingSource. Cast the ProductBindingSource.Current to your ProductDataRow and then set the detail row's price column to that on the Product. Something like:

Private Sub ProductsBindingSource_CurrentChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ProductsBindingSource.CurrentChanged
Dim product As ProductsLookupDataSet.ProductRow
product = CType(CType(Me.ProductsBindingSource.Current, DataRowView).Row, ProductsLookupDataSet.ProductRow)

Dim detail As OrdersDataSet.OrderDetailRow
detail = CType(CType(Me.OrderDetailBindingSource.Current, DataRowView).Row, OrdersDataSet.OrderDetailRow)

detail.Price = product.Price

End Sub

If you're not allowing the user to overwrite the price per OrderDetail then the detail.Price column doesn't need to exist in the database. You can specify a join in your query to create the column only in the OrderDetail DataTable (this is the client-side entity) and use it as a placeholder on the form.

HTH,
-Beth

NEW POST: #1 How Do I: Create a Database

Mostly_Lucid — Mon, 09 Jun 2008 13:53:06 GMT

TomLee wrote:
Hi Massi,

First of all, thank you very much for your efforts that sharing your VB programming experience to us. It's make me a rough idea to learn VB in myself.

I'm a newbies to learn VB database program. Could inform us why do you suggest to implement the "Store Procedures" in MDF file? I cannot find the differents in "Video #2How Do I: Connect to a Database"

Besides, in the reportviewer call in video # 14, how do I print out one record in detail (two table data) thr printer?

Lastly, how can I auto generate the number such as 5 digits (0804-0001) in the From like reference number?

Thanks for your help.

Tom

Hello,

I'll take a swing at this - hope it helps:

1. Stored procedures allow you as a programmer to have control over how a database is updated or changed. If you store a procedure to add a record to a table for instance, then you can make sure it is done exactly how you want. Later, when someone programs code to access your table all they have to do is call your procudure and you will be assured that YOUR code is updateing the database, not the standard code that VS inserts into a program. In Beths example the procedure happens to work the same of the generated code, but there is no reason that it has to. She could have had it do several other things if she wanted it to. Another reason is for maintainability - if you need to change something later, you can change it at the database level and it will not "break" any code that access it.

2. Sorry, can't help here

3. If I understand the question correctly: When you go to a tables list of columns and go to the columns properties - you want to turn on the Identity setting. This will also allow you to set a starting number (the seed) and set the increment amount. If you are not wanting it to be handled this way they you would have to simply read in the data you want and programaticly increase the value before writing it back to the database.

Hope this helps,

Joe