CODEWITHSUNDEEP
jqueryasp.netsql-server
Andreas Lymbouras
Andreas Lymbouras

Reputation: 1035

The best way to add NULL values to the database when my textboxes are empty

The problem was on executeScalar where my sql statement didn't return anything

I have some values in my tables that are type of Int32, others of datetime which are nullable. And what I want is when the user doesn't fill the textbox to insert a null value instead of ""(whitespace) which is incorrect type for my field.

enter image description here

I have tried ideas like:

insertActor.Parameters.AddWithValue("@School2StartYear", String.IsNullOrWhiteSpace(txtSchool2StartYear.Text) ? (object)DBNull.Value : (object)Int16.Parse(txtSchool2StartYear.Text));

I have to do this for my 15 tables(with 30+ fields).

I also seen the .validate() function by jQuery but I didn't tried it at all.

I have also seen this idea: [DisplayFormat(ConvertEmptyStringToNull = true)]

Do I have to do it in Database? When a '' is added convert it to NULL?

So is this the best way to do it? Checking all the textboxes if they are IsNullOrWhiteSpace

Upvotes: 0

Views: 462

Answers (3)

Glen Hughes
Glen Hughes

Reputation: 4822

The exception is probably caused because the result of the statement is null, not because your input values are incorrect. Try splitting apart the execution like this:

object result = insertActor.ExecuteScalar();
newID = Int32.Parse(result.ToString());

I would suspect that result is null, which is causing a NullReferenceException when the ToString method is called.

Is the new id supposed to be coming from a return statement, an output parameter, a select statement?

Upvotes: 1

gilly3
gilly3

Reputation: 91557

I usually just exclude the parameter:

Int16 school2StartYear;
if (Int16.TryParse(txtSchool2StartYear.Text, out school2StartYear) {
    insertActor.Parameters.AddWithValue("@School2StartYear", school2StartYear);
}

Your SQL code would declare the parameter as @School2StartYear smallint = NULL so that if the parameter is excluded, it uses NULL as the default.

Upvotes: 1

praveen
praveen

Reputation: 12271

You can check with IsDBNull

Convert.IsDBNull (txtSchool2StartYear) ? 0:int.Parse (txtSchool2StartYear);

Upvotes: 0

Related Questions