Insert or Update SQL Table when Textbox is Empty

[zektheiguana]

I have a web form which has around 30 or so fields, most of which are optional from an SQL standpoint (i.e. table column allows Null). I'm using a dataset and table adapter to generate my Insert and Update. Most of the fields are strings, but there are also Dates and Integers.

What I'm running up against is that Date and Integer fields that are left blank on the web form crash the Insert and Update commands because a blank string can't be converted to a Date or Integer. What needs to happen is that the database just gets a Null inserted into that field. Is there a simple way to do that when a field is left blank? My Insert is below ... Thanks in advance for any tips!

Sub InsertNewIncident()

'declare datatable(adapter/da) and dataset(ds)
Dim dsIncidentNumber As New HMDDataSet.IncidentMasterDataTable 'dataset
Dim daIncidentNumber As New HMDDataSetTableAdapters.IncidentMasterTableAdapter 'datatable

'insert values of the controls into the appropriate database fields
daIncidentNumber.Insert(CDate(txtEntryDate.Text), txtOriginator.Text, txtLoc.Text, txtRegion.Text, CDate(txtIncidentDate.Text), txtIncidentTime.Text, _
ddlTimeZone.SelectedValue, ddlIncidentType.SelectedValue, txtIncidentLocation.Text, txtWeather.Text, txtSpeed.Text, _
txtTraffic.Text, ddlArea.SelectedValue, txtDetail.Text, txtVehicleNo.Text, txtPlateNo.Text, txtHMDNo.Text, _
ddlCompany.SelectedValue, txtCallerName.Text, txtPhone.Text, txtAddress.Text, ddlCallType.SelectedValue, chkCallBack.Checked, _
txtDriverName.Text, txtJobTitle.Text, txtNoOfIncidents.Text, CDate(txtHireDate.Text), CDate(txtBirthDate.Text), _
txtSupervisorName.Text, ddlDriverReaction.SelectedValue, txtDriverComments.Text, CDate(txtCallerContactDate.Text), _
ddlActionTaken.SelectedValue, CDate(txtActionDate.Text), txtMgmtComments.Text, CDate(txtResponseDate.Text))

End Sub

 

[jmeckley]

you need to check each value and try to parse it.

var row = datatable.New[Explicit]Row();

var stringText = StringTextBox.Text;
if(string.IsNullOrEmpty(stringText) == false)
{
   row.StringColumn = stringText;
}

var numberText = NumberTextBox.Text;
int number;
if(int.TryParse(numberText, out number))
{
   row.NumberColumn = number;
}

var dateText = DateTextBox.Text;
DateTime date;
if(DateTime.TryParse(dateText, out date))
{
   row.DateColumn = date;
}

datatable.Add(row);
//have the data table/set accept the changes. which in this case is a new row.

also make sure the data table will not throw on null values.

Jason Meckley
Programmer

faq855-7190
faq732-7259