DataBinding a ListBox's SelectedValue to integer field from DataSet gives error

[dtblankenship]

Hello everyone,

I know this question has been asked many times in the forums, and after
spending a few days reading, I am still confused as to the answer.

I have a ListBox (lstBox), SqlConnection (sqlConnection),
SqlDataAdapter (daLookupData), SqlDataAdapter (daData), DataSet
(dsLookupData), and DataSet (dsData), all created via the IDE during
design-time. Here is the design of my simple tables: (An example
since I can't post my real tables, etc.)

LookupDataTable
----------------
LookupDataID - int (identity)
LookupDataText - nvarchar(25)

Data
-----
LookupDataID - int
(Other nvarchar textbox nullable fields)


Each DataAdapter has a command that simply selects * from each table,
respectively.

Here are the properties I've edited for lstBox:

DataMember: LookupDataTable (An existing table)
DataSource: dsLookupData
DataTextField: LookupDataText (nvarchar(25))
DataValueField: LookupDataID (int)

I then went to DataBindings and changed the SelectedValue to be:

DataBinder.Eval(dsData, "Tables[Data].DefaultView.[0].LookupDataID",
"{0:N}")

Here is my Page_Load:

//----------------------------------------------
sqlConnection.Open();
if (!Page.IsPostBack)
{
daLookupData.Fill(dsLookupData);
daData.Fill(dsData);
Page.DataBind();
}
sqlConnection.Close();
//----------------------------------------------

Since there is no data in the Data table, a null value is returned to
the lstBox's SelectedValue. Since that value doesn't exist, the web
application bombs stating:

"Specified argument was out of the range of valid values. Parameter
name: value "

and it's pointing to the Page.DataBind() as the error line;

I know it is the SelectedValue because when I unbind that property, it
all works as intended. I have also attempted changing to the ISNULL
T-SQL recommended on some posts, but there is no -1 or 0 value, so it
still bombs. I am unable to have an extra entry in the listbox--only
those values currently in the LookupDataTable can be shown in the list.

I know there exists a problem where null values are causing web
applications to crash like this, and I have read many ways to fix it,
but none seem to work. I still would like to use databinding to
accomplish the loading of the data into these controls and the setting
of values for each record, but would not mind overloading a function or
creating an event handler for this, but do not know what ones would be
needed. I've also seen code where you check for null to set it to
DBNull.value or an empty string, but where exactly does that code go in
this situation? I have seend DataBinding classes that do not exist in
the web forms, only in Windows forms, so that is out of the question
(unless I'm mistaken).

Basically, if a null value exists, I need nothing to be selected, and
if one does, then I need that one to be selected.

I since added a button to check the current value of this box unbound,
and it seems to be an empty string (''), however, when I try to
ISNULL(...) to a nvarchar empty string, it does not work (yes, I did
change the type in the xsd file).

Has anyone found a solution for this problem while still using
databinding for the datasource AND selectedvalue, and if
events/overloaded functions were used to accomplish this, what ones
should I research? My thought was if I could overload the function
that sets the value at the time of databinding, so I could trap the
error, I could just not set the selected value and continue on,
allowing the next databind to get the value if the next one existed.
Thank you all in advance.

David

 

[Karl Seguin]

David,
I think your problem is that you have fundamentally overcomplicated things.
The reason you are probably confused by the answers you've received is
likely because the people trying to help you have been confused by the
question asked - god knows I am.

I'm not trying to put you down and am willing to work with you to find a
solution, but I think it's important you know that it seems that you are
either overcomplicating your code or that you aren't properly explaining it.

Let me rephrase your question as I understand it, simplified, so that you
might tell me if I'm right or wrong.

(a) you are binding a dataset to a listbox
(b) you are trying to set the selectedValue


Now, it seems to me that your page_load should look like this:

Page_Load
if (!Page.IsPostBack){
dsLookupDate = GetYourDataFromSomeFunction()
lstBox.DataSource = dsLookupData
lstBox.DataMember = "LookupDataTable"
lstBox.DataTextField = "LookupDataText"
lstBox.DataValueField = "LookupDataId"
lstBox.DataBind()
//set the selected value here
}

now, notice that I couldn't have solved your problem because I didn't set
your selected value, which you think is the problem. The reason I didn't is
because I don't understand what the heck you want as the selected value. I
don' t understand what you hope/want this to do: DataBinder.Eval(dsData,
"Tables[Data].DefaultView.[0].LookupDataID", "{0:N}")

I understand that you are binding to LookupDataTable and you want the
selectedValue to be based on something in the Data table, but I don't
understand which LookupDataId you wish to have? The first one in the Data
table? Is your problem when the entire data table has no rows?

Please clarify for me.

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/

Hello everyone,

I know this question has been asked many times in the forums, and after
spending a few days reading, I am still confused as to the answer.

I have a ListBox (lstBox), SqlConnection (sqlConnection),
SqlDataAdapter (daLookupData), SqlDataAdapter (daData), DataSet
(dsLookupData), and DataSet (dsData), all created via the IDE during
design-time. Here is the design of my simple tables: (An example
since I can't post my real tables, etc.)

LookupDataTable
----------------
LookupDataID - int (identity)
LookupDataText - nvarchar(25)

Data
-----
LookupDataID - int
(Other nvarchar textbox nullable fields)


Each DataAdapter has a command that simply selects * from each table,
respectively.

Here are the properties I've edited for lstBox:

DataMember: LookupDataTable (An existing table)
DataSource: dsLookupData
DataTextField: LookupDataText (nvarchar(25))
DataValueField: LookupDataID (int)

I then went to DataBindings and changed the SelectedValue to be:

DataBinder.Eval(dsData, "Tables[Data].DefaultView.[0].LookupDataID",
"{0:N}")

Here is my Page_Load:

//----------------------------------------------
sqlConnection.Open();
if (!Page.IsPostBack)
{
daLookupData.Fill(dsLookupData);
daData.Fill(dsData);
Page.DataBind();
}
sqlConnection.Close();
//----------------------------------------------

Since there is no data in the Data table, a null value is returned to
the lstBox's SelectedValue. Since that value doesn't exist, the web
application bombs stating:

"Specified argument was out of the range of valid values. Parameter
name: value "

and it's pointing to the Page.DataBind() as the error line;

I know it is the SelectedValue because when I unbind that property, it
all works as intended. I have also attempted changing to the ISNULL
T-SQL recommended on some posts, but there is no -1 or 0 value, so it
still bombs. I am unable to have an extra entry in the listbox--only
those values currently in the LookupDataTable can be shown in the list.

I know there exists a problem where null values are causing web
applications to crash like this, and I have read many ways to fix it,
but none seem to work. I still would like to use databinding to
accomplish the loading of the data into these controls and the setting
of values for each record, but would not mind overloading a function or
creating an event handler for this, but do not know what ones would be
needed. I've also seen code where you check for null to set it to
DBNull.value or an empty string, but where exactly does that code go in
this situation? I have seend DataBinding classes that do not exist in
the web forms, only in Windows forms, so that is out of the question
(unless I'm mistaken).

Basically, if a null value exists, I need nothing to be selected, and
if one does, then I need that one to be selected.

I since added a button to check the current value of this box unbound,
and it seems to be an empty string (''), however, when I try to
ISNULL(...) to a nvarchar empty string, it does not work (yes, I did
change the type in the xsd file).

Has anyone found a solution for this problem while still using
databinding for the datasource AND selectedvalue, and if
events/overloaded functions were used to accomplish this, what ones
should I research? My thought was if I could overload the function
that sets the value at the time of databinding, so I could trap the
error, I could just not set the selected value and continue on,
allowing the next databind to get the value if the next one existed.
Thank you all in advance.

David

 

[dblanken]

Hi Karl,

Yes, you are on the right track. I didn't set up the lstBox properties
in code--I did them during design-time using the properties given in
the IDE, which is why there's no need for them to be in the Page_Load.

I was under the impression that you could eliminate the function to get
the dataset since you can have the DataAdapter fill the dataset with
the data as outlined in your command object, which is why I thought
there was no need for a function to return a dataset--again, the
dataset was already generated by the IDE from the DataAdapter.

In my example, I have a lookup table and another table with an ID
corresponding to an item in that lookup table. This is an optional
field and the value could be null, which is where I am having my
problem. If it is null, that's where I get my error in the
SelectedValue. As for what I'm getting, it shouldn't matter--whether
it's the first, last, middle, or even if there is no data, if that ID
field has returned a null value, it crashes.

Thanks for helping.

 

[Karl Seguin]

Dan,
My fictitious function to get the dataset would basically just does what you
did (use the dataAdapter to fill the dataset and return it). The benefit of
the function is that it could be reused more easily in some other part of
the code...say another page which wanted to bind to it....that's symatics
though and I was just trying to establish flow. Same with those other
properties, I figured you were doing it at design time but for illustration
purposes I much rather do it in codebehind so that nothing is assumed (well,
in real world I rather do it there too, but again, symatics).

Still not 100% clear however....I'm understanding that Data only has 1
record (possibly none, or posibly a record with a null Id) or that we are
only interested in the first record.

doesn't something like:

int id;
if (dsLookupData.Tables["Data"].Rows[0]["LookupDataId"] != DbNull.Value){
lstBox.SelectedIndex =
lstBox.Items.IndexOf(lstBox.Items.FindByValue(dsLookupData.Tables["Data"].Ro
ws[0]["LookupDataId"].ToString))
}

work? might have a couple errors in there, but the general idea I'd expect
to work...

This is probably something you can't easily do in design time...even with
databinding expressions (Which I'm thinking might be where you are going
wrong).

Karl

 

[dblanken]

I think I got it. It was a problem with the databinding expression. I
found that you can set it to anything, even a function, as long as it
returns a null or string to the SelectedValue. So I made a function
that is as follows:

public string SelectVal(DataSet ds, string strField)
{
if (ds.Tables[0].Rows[0][strField] != DBNull.Value)
return (ds.Tables[0].Rows[0][strField]).ToString();
return null;
}

Then, my databinding expression for SelectedValue is: SelectVal(dsData,
"LookupTableID")

If it returns null, then nothing is selected, just as it should be; and
if there is a value, it will select it.

Thanks for your help!