StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Conversion from type 'DBNull' to type 'String' is not valid.

RE: Conversion from type 'DBNull' to type 'String' is not valid.

StrataFrame Team — Thu, 20 Sep 2007 08:53:25 GMT

Glad we helped out, even if it was indirectly. That's the whole reason we went with forum based support, so that people could peruse the topics and get some of their questions answered without having to ask them :)

Conversion from type 'DBNull' to type 'String' is not valid.

Ricardo Quartier — Thu, 20 Sep 2007 08:53:25 GMT

Hello.. this is what I am doing now... Im trying to pupulate the combo box, but i'm gettin this error "Conversion from type 'DBNull' to type 'String' is not valid.". The funny thing is that I have a a text box that loads from the same BO and loads the same field, and it's working..

PS - I dont have any null info on the "nome_clube" field on the table.

Any Clues.. anyone?

RE: Conversion from type 'DBNull' to type 'String' is not valid.

Edhy Rijo — Wed, 19 Sep 2007 14:24:37 GMT

I would like to thank Ricardo, Ben and Trent (I hope I did not miss anybody else in this thread) for this great thread.

As a novice user of SF I am reviewing the previous posts, and then I found this one and by looking at the dedication of the SF developers to guide Ricardo to the correct path and even going the extra mile to provide detail code on how to accomplish Ricardo's request, confirmed that I made the right choice of becoming a member of the StrataFrame family.

I printed out this thread and was able to accomplish the same filtering of a data in a combo based on the selection of a previous combo and it worked the first time and without having to post a request for help.

Once again, thanks to the SF team!

RE: Conversion from type 'DBNull' to type 'String' is not valid.

Trent L. Taylor — Thu, 30 Mar 2006 15:29:31 GMT

Good, I'm glad you got it. Let me know if you have any questions.

RE: Conversion from type 'DBNull' to type 'String' is not valid.

Ricardo Quartier — Wed, 29 Mar 2006 17:14:49 GMT

it runs now, Im trying to learn.. thanks Trent

RE: Conversion from type 'DBNull' to type 'String' is not valid.

Trent L. Taylor — Mon, 27 Mar 2006 15:04:56 GMT

Got it...thanks.

RE: Conversion from type 'DBNull' to type 'String' is not valid.

Ricardo Quartier — Sun, 26 Mar 2006 17:54:19 GMT

OK, I've sent to u... thanks

RE: Conversion from type 'DBNull' to type 'String' is not valid.

Trent L. Taylor — Sat, 25 Mar 2006 11:36:40 GMT

In the city combo box, how do you have the PopulationDataSourceSettings setup? Ricardo, could you just ZIP up your Visual Studio solution and send it to me? I can fix the problem and then send it back to you so you can see it and then you will know how to use it in the future. Just send it to support@strataframe.net and I will keep my eye out for it. :)

RE: Conversion from type 'DBNull' to type 'String' is not valid.

Ricardo Quartier — Fri, 24 Mar 2006 20:30:17 GMT

Hi, now runs.. but the combobox of cities does not load... :w00t:

Should i change other part of the code?

RE: Conversion from type 'DBNull' to type 'String' is not valid.

Trent L. Taylor — Thu, 23 Mar 2006 16:55:42 GMT

Ricardo,

Your fill method is wrong on the cities. Try this instead:

Public Sub FillCityByState(ByVal StatePrimaryKey As Integer)
Me.FillDataTable("SELECT * FROM tb_cidade WHERE cidade_estado_id = " & StatePrimaryKey.ToString())

The code you had referenced its own property which was causing the error you described. Try this and let me know how it goes. :cool:

RE: Conversion from type 'DBNull' to type 'String' is not valid.

Ricardo Quartier — Thu, 23 Mar 2006 14:44:50 GMT

Thanks Rob that helped me to understand, but im still gettin' an error when i try to run... (so i must done something worng i guess)

This is what i done so far:

1 - On my cities BO, called (CidadesBO)

Public Sub FillCityByState(ByVal StatePrimaryKey As Integer)
Me.FillDataTable("SELECT * FROM tb_cidade WHERE cidade_estado_id = " & EstadosBO.EstadosBOFieldNames.estado_id_cod.ToString())

'ESTADOSBO is the BO of states
End Sub

2 - On the Clubs form, called (frmCadClubes)

2.1 - City Combo

Private Sub cboCidade_ListPopulating(ByVal e As MicroFour.StrataFrame.UI.ListPopulatingEventArgs) Handles cboCidade.ListPopulating
e.Parameters(0).Value = EstadosBO.EstadosBOFieldNames.estado_id_cod

'ESTADOSBO is the BO of states
End Sub

2.2 - State Combo

Private Sub cboEstado_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboEstado.SelectedValueChanged
cboCidade.Requery()
End Sub

3 - I set the the Initialization as you told me.

But when i run, the code stops on this "requery line" and gives this message:

{"Invalid column name 'estado_id_cod'."} but this is the primary key of the table states, and that repeats on the field called "cidade_estado_id" on the cities table, so this is the link.

On the pic Im sending a diagram :w00t: maybe give some clue.

Please be patience :D

RE: Conversion from type 'DBNull' to type 'String' is not valid.

Trent L. Taylor — Thu, 23 Mar 2006 12:54:27 GMT

Rob is exactly right, I really don't have anything to add. Let me know if you need more detail. ;)

RE: Conversion from type 'DBNull' to type 'String' is not valid.

Robert Linton — Thu, 23 Mar 2006 12:39:35 GMT

Hi Ricardo,

"MyStates" is used as an example, you will want to substitute that with the name of the StateBO in your code. The line, e.Parameters(0).Value = YourStateBO.State_PrimaryKey is getting the current primary key from the State BO and assigning it to the query parameter.

Hope that helps,

Rob

RE: Conversion from type 'DBNull' to type 'String' is not valid.

Ricardo Quartier — Thu, 23 Mar 2006 12:09:54 GMT

Yo Trent, this line:

e.Parameters(0).Value = MyStates.State_PrimaryKey

I inserted here: (note - cboCidade is my combo of cities)

Private Sub cboCidade_ListPopulating(ByVal e As MicroFour.StrataFrame.UI.ListPopulatingEventArgs) Handles cboCidade.ListPopulating

e.Parameters(0).Value = MyStates.State_PrimaryKey

End Sub

It keeps giving this error: (Name 'MyStates' is not declared.) should it be one of my controls ? One of the comboboxes? CityBO?

RE: Conversion from type 'DBNull' to type 'String' is not valid.

Trent L. Taylor — Wed, 22 Mar 2006 17:44:32 GMT

Right now you are populating each combo box using all rows in the respective table. This will obviously not work very well for tables with a lot of records. You only need to show the cities that belong to state when populating the cities combo, and then you need to only load the available states for the selected country.

I assume that your database knows these relationships. I am going to talk about the city combo, but the same will apply to the states. In your table, your city data structure should know the state to which it belongs. So you would need to create a Fill method on the BO that looks something like this:

Public Sub FillCityByState(Byval StatePrimaryKey As Integer)
Me.FillDataTable("SELECT * FROM cities WHERE city_state = " & StatePrimaryKey.ToString())
End Sub

Next, you will want to change the PopulationDataSourceSettings of the city combo to use the above method. The one thing that you need to is specify the parameter StatePrimaryKey when the combo loads. To do this, capture the ListPopulating event on the city combo box. Inside that event, place code similar to this:

e.Parameters(0).Value = MyStates.State_PrimaryKey

Now you need to tell the city combo to reload when the state changes. So in the SelectedValueChanged or the SelectedIndexChanged event of the State combo box, place the following code:

MyCityCombo.Requery()

This will force the cities to repopulate based on the new state selection.

Once last thing you can do to make sure that the object populate in the correct order since they rely on one another, is set the InitializationPriority. This will ensure that order they are loaded when instantiating the form will happen in the proper order. Place a value like this on each combo:

Country Combo Initialization Priority: 25
State Combo Initialization Priority: 26
City Combo Initialization Priority: 27

Let me know if you have any questions.

RE: Conversion from type 'DBNull' to type 'String' is not valid.

Ricardo Quartier — Wed, 22 Mar 2006 15:44:21 GMT

Yo, last "question" and a diferent help :D

Now I have those 3 combos, like i said before, the thing is that they are not really "very" dependent. So this way I can chooseto set a club on any country (this is allright) but then when i drop down the states, it shows the states of all countries, anyway, this one is agressive, when i drop down the cities, it show thousands of cities from all around the world. :w00t:

My database is kind simple (4 tables right now)

Clubs
-------
clubId
countryId
stateId
cityId

Contries
--------
countryId
description

State
-------
stateId
description

Cities
-------
cityId
description

Is there any way of strataframe to handle this, "make then dependent"? I use to do this on aspx pages.. using a comand on a query, that concatenate the sql and the value of the parent combo, like:

"select * from states where country = ' & cboCountries.selectedvalue = 25 & '"

Something like that... then i put the code on the event change of the combo and on the form load to bring right the first record.

Any great clues? a "how to" will be great, i won't lie (if that does not take so much time from u).

Best Regards,

Ricardo

RE: Conversion from type 'DBNull' to type 'String' is not valid.

Trent L. Taylor — Wed, 22 Mar 2006 08:22:20 GMT

Glad to hear it ;)

RE: Conversion from type 'DBNull' to type 'String' is not valid.

Ricardo Quartier — Wed, 22 Mar 2006 00:02:17 GMT

Yo Trent, all okidoki. Thanks

Now i have 3 combos... Contry, State and City

RE: Conversion from type 'DBNull' to type 'String' is not valid.

Trent L. Taylor — Mon, 20 Mar 2006 10:34:30 GMT

Ricardo,

This will work similar as the example I showed you previously. Follow the steps below:

1. Select the country combo box
2. Click the PopulationDataSourceSettings
3. Choose the Country Business Object
4. Choose the method that will Fill the BO with the countries
5. Add the country name as the display column
6. Add {0} to the format column
7. Pick the primary key of the Country table for the Value Member
8. Click OK
9. Make sure that the PopulationType of the combo is set to BusinessObject
10. On the same combo box, select the BusinessObject property to the instance of the Clubes on the form
11. Set the BindingField to the Country field in the Clubes table.

That should be it. Let me know if you have any questions.

RE: Conversion from type 'DBNull' to type 'String' is not valid.

Ricardo Quartier — Fri, 17 Mar 2006 17:58:48 GMT

Hi everything u said was correct, but i still did not inserted the clubs combobox in the clubs form, but.. it was going to show the number of the country (PK) coming from the countries table.. i guess

So, u got it...

Thanks ....

RE: Conversion from type 'DBNull' to type 'String' is not valid.

Trent L. Taylor — Wed, 15 Mar 2006 10:05:05 GMT

Just to verify what you are attempting to do before I send you some code. You have a combo box that you want to populate with the countries which is bound to a field in the clubs table, correct? But when you bind and navigate right now it is showing a number (i.e. Primary Key or something) rather than the country description. Also, I assume that your country database has a primary key and that primary key is tied to the clubs country field, correct?

Country Table Club Table
Primary Key - Integer ----- ClubCountry - Integer
Description - String

The simple table above is just to verify that I understand your data structure and what you are attempting to do. Please advise and let me know if I understand your situation. Thanks.

RE: Conversion from type 'DBNull' to type 'String' is not valid.

Ricardo Quartier — Tue, 14 Mar 2006 20:58:05 GMT

Hi, Trent... me again.. now I can load comboboxes on all forms.. that makes me very happy... but can u please teach me how to save the data from a diferent table to the first?

Example: I have a form 1 to insert data from Clubs (soccer), then i load all the textboxes, from the table called (tb_clube), to insert, edit.. and been filled by a BO called "clubeBO", all okidoki like the example you guys made on the tutorial.

Then I inserted a new BO called "paisBO" (means country), and made the same fill method. If i put a combobox to load like u teach me, it brings all the countries... But the thing is that I want to insert on the clubs form this countries comboBox, to load the data from the coutries table and insert the value field (a primary key called pais_id_cod) on the country field on the clubs table.. and somehow, when i navigate the records, see the country that belongs to that team... instead of the value.... like 1, 2, 3....

Am I been anoying? :D, not sure if it's even possible... but i guess it is... not sure how with strataframe... either...

If u got time, and nothing better to it... please drop a few lines.. (of code too) :cool:

Best Regards...

Ricardo

RE: Conversion from type 'DBNull' to type 'String' is not valid.

Trent L. Taylor — Mon, 13 Mar 2006 12:44:33 GMT

Glad to help ;)

RE: Conversion from type 'DBNull' to type 'String' is not valid.

Ricardo Quartier — Mon, 13 Mar 2006 01:15:56 GMT

Awesome ! Thanks, Trent !

RE: Conversion from type 'DBNull' to type 'String' is not valid.

Trent L. Taylor — Fri, 10 Mar 2006 17:42:27 GMT

Ricardo,

Since you are trying to popualte a combo from a business object, you should use the PopulateionDataSourceSettinngs on the combo box itself. Also you will need to change the population method from manual to BusinessObject on the combo box also. The code sample you showed above is actually not going to work for two reasons. First, you are populating the business object twice. Once in the ParentFormLoading of the combo box and again in the load of the business object itself.

Secondly, the reason you are still getting the error is because the DataSource, DisplayMember, and ValueMember of the combo box were never set which tells the combo how to populate and interact with the bound data. So you need to do one of two things:

Manual Population
1. Remove the Me.ClubesBO1.FillTop100() from the ParentFormLoading event of the business object itself. Leave the other in the combo boxes ParentFormLoading.
2. Make the combo box ParentFormLoading event look like this:

Private Sub ComboBox1_ParentFormLoading() Handles ComboBox1.ParentFormLoading

Me.ClubesBO1.FillTop100()

'-- Set the display member
Me.ComboBox1.DisplayMember = "[MyDisplayFieldName]"
Me.ComboBox1.ValueMember = "[MyValueMemberField - Usually a Primary Key Field]"
Me.ComboBox1.DataSource = Me.ClubesBO1.CurrentDataTable

End Sub

End Class

Replace the display and value member fields with the names of the fields you wish to use.

Better Solution - PopulationDataSourceSettings
1. Remove all of calls to ClubesBO1.FillTop100 from all methods.
2. Through the form designer, navigate to the combo box.
3. In the property sheet, navigate to the PopulationDataSourceSettings
4. Click the "..." button to the right of the value
5. When the editor appears, set the Business Object Type to "ClubesBO"
6. Set the Method to Execute to "FillTop100()"
7. Add the fields that you wish to have displayed in the combo box at the bottom left of the editor
8. To tell the combo box how to use those selected display fields, set the Display Member Format String. For example, if you only added a single display member field, the format string will look like this: {0}
9. Pick the value Member field. This is the fields that will be bound to the data.
10. Click OK.
11. Set the PopulationType property on the combo box to Business Object.
12. Save your changes and run the form. This should get you going. ;)

Let me know if you have any questions.