Table Field Set To Not Allow Nulls

21 Nov 2007 4:12 PM

JimHeavey

I have a table with 2 keys, which are of course identified as not being
allowed to be null. I have created a screen in which has a number of fields
identified for selection criteria, and the user has the freedom to identify
which fields it will use to make their selection on. Sometimes they will
enter criteria for the key fields and sometimes they will not.

I have use VS2005 to generate my dataset and my dataAdapter. I have bound
the data adapter to a DataView and bound the selection criteria to the text
boxes on the web form. The problem is when the user does not specify any
criteria for the "key" fields which can not be null, an execption is thrown
for the key field which is blank telling me the key field can not be null.
How do I get around this?

21 Nov 2007 8:12 PM

Brad Roberts

I have encountered something like this before. Unfortunately, the default
values for the SQL tables are not propogating to the dataset with the
SelectCommand.

My work-around was as follows. I hope I can explain it clearly enough in
relatively few words.

Immediately after the DataSet has been created, before the DataAdapter has
been used to fill a DataTable with data, run the Select query once to get the
schema of the query results into the dataset/datatable.

example,

SQLDataAdapter.SelectCommand = cmd
SQLDataAdapter.FillSchema(ADataset, SchemaType.Source, "ResultsTable")

then something like....

Dim df As DataColumn
For Each df In ADataset.Tables("ResultsTable").Columns
            df.AllowDBNull = True
            If df.ColumnName = "MyColumnName" Then
                df.DefaultValue = ""
            End If
Next

--With the schema in place before the SelectCommand is executed the results
will go into the previously defined DataTable, which has a default value for
the field that is not to have a null.

--Then something like
      SQLDataAdapter.Fill(ADataset, "ResultsTable")

--
Brad

"Software is like melted pudding..."