CODEWITHSUNDEEP
c#asp.netsql-server
Eric Mbiada
Eric Mbiada

Reputation: 133

Stop a Null value date inserting in the table when the textbox value is null using C#

I am trying to stop the default value date "01/01/1900"to be entered in the table when the value of the textbox is null.I don't need validation on the form null textbox is ok Thanks

using (SqlConnection con = new SqlConnection(WebConfigurationManager
       .ConnectionStrings["Molecular"].ConnectionString))
{
   con.Open();

   using (SqlCommand sc = new SqlCommand(@"Insert into ClinicalFollowUp  (MBID, Diagnosis, 
          DateLastSeen, DateofDeath ) values(@MBID, Upper(@Diagnosis), 
          Convert (date, @DateLastSeen , 103), Convert (date, @DODeath, 103);", con))
   {
      sc.Parameters.AddWithValue("@MBID", txtMBID1.Text);
      sc.Parameters.AddWithValue("@Diagnosis", txtDiagnosis.Text);

      // Date  Textbox
      sc.Parameters.AddWithValue("@DateLastSeen", txtDateLastSeen.Text);

      // Date  Textbox
      sc.Parameters.AddWithValue("@DODeath", txtDateDeath.Text);
   }
   con.close();
}

Upvotes: 1

Views: 946

Answers (3)

Igor
Igor

Reputation: 62213

  1. You should send the parameters in your command using
    • The correct data type (NOT as string and do not convert them in Sql Server!)
    • Specify the data type of the parameter
    • Specify the length of parameter data types where appropriate. I guessed at your string lengths in the schema, update it accordingly.

  2. Execute the conversion as early as possible in your call stack. Ideally you have a control that is a datetime picker, maybe this can do the conversion for you OR if its a web api then let the serializer deserialize the request to the approriate types.

    const string sqlStatement = @"Insert into ClinicalFollowUp (MBID, Diagnosis, DateLastSeen, DateofDeath ) VALUES(@MBID, @Diagnosis , @DateLastSeen, @DODeath);"

    using (SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["Molecular"].ConnectionString)) using (SqlCommand sc = new SqlCommand( , con)) { con.Open(); sc.Parameters.Add(new SqlParameter("@MBID", SqlDbType.VarChar, 100){Value = txtMBID1.Text}); sc.Parameters.Add(new SqlParameter("@Diagnosis", SqlDbType.VarChar, 2000){Value = txtDiagnosis.Text.ToUpper()});

    // Date  Textbox
sc.Parameters.Add(new SqlParameter("@DateLastSeen", SqlDbType.DateTime){Value = getSqlDate(txtDateLastSeen.Text)});

// Date  Textbox
sc.Parameters.Add(new SqlParameter("@DODeath", SqlDbType.DateTime){Value = getSqlDate(txtDateDeath.Text)});


sc.ExecuteNonQuery();

    }

    // TO DO - validate culture information public static object getSqlDate(string dateTime) { DateTime dt; return !string.IsNullOrEmpty(dateTime) && DateTime.TryParse(dateTime, CultureInfo.InvariantCulture, DateTimeStyles.None, out dt) ? (object) dt : (object) System.DBNull.Value; }

Upvotes: 1

John Wu
John Wu

Reputation: 52250

I'd do it this way, personally.

static public object ToDbNullableDate(this string s)
{
    DateTime d;
    var ok = DateTime.TryParse(s, out d);
    return ok ? d : DbNull.Value;
}

Then in your code:

sc.Parameters.AddWithValue("@DateLastSeen", txtDateLastSeen.Text.ToDbNullableDate());

Upvotes: 2

Nino
Nino

Reputation: 7095

try like this:

if (string.IsNullOrWhiteSpace(txtDateLastSeen.Text)
{
    sc.Parameters.AddWithValue("@DateLastSeen", DbNull.Value);
}
else
{
    sc.Parameters.AddWithValue("@DateLastSeen", txtDateLastSeen.Text);
}

EDIT: IsNullOrWhiteSpace, as suggested, is a bit simpler.

Upvotes: 1

Related Questions