RSS

Enter Null Values for DateTime From Asp.net To SQL Server database

25 Aug

introduction about Problem:

Solution 1

Inserting a null value to the DateTime Field from asp.net (C#,vb.Net)  in SQL Server is one of the most common issues giving various errors.And Problem To Handle.After Verious Website Visit and No Help find then resolve this Issue and want to share this code for everyone. Even if one enters null values the value in the database is some default value as 1/1/1900 12:00:00 AM.

The Output of entering the null DateTime based on the code would in most cases have errors as:

  • String was not recognized as a valid DateTime.
  • Value of type ‘System.DBNull’ cannot be converted to ‘String’.

Or No Error but DataTime entered in Database would be as 1/1/1900 12:00:00 AM
So lets write the code to enter null values in the DataBase.

The Db Image for data type of Date time  is as follows:

when you use Enterprise library  and you make different classes like BO,BLL,DAL Classes with property .

DAl Class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using System.Data.SqlTypes;

public class DALRegisrationOnline
{

public  string PassportNo{get;set;}
public  string VisaNo{get;set;}
public SqlDateTime VisaStartDate { get; set; }

public SqlDateTime VisaEndDate { get; set; }
public  string Nationality{get;set;}

}

That is the Function to Pass Stored Procedure

public static int InsertUpdateRegisrationInfo(DALRegisrationOnline RegInfo)
{
Database db = DatabaseFactory.CreateDatabase();
object[] parameters = new object[] { RegInfo.PassportNo, RegInfo.VisaNo, RegInfo.VisaStartDate, RegInfo.VisaEndDate, RegInfo.Nationality };
DbCommand cmd = db.GetStoredProcCommand(“SP_InsertUpdateRegistrationInfo”, parameters);
// return db.ExecuteNonQuery(cmd);
return (int)db.ExecuteScalar(cmd);

}

AspDotNet CS Class

DALRegisrationOnline reg = new DALRegisrationOnline();

System.Data.SqlTypes.SqlDateTime sqldatenull;
sqldatenull = SqlDateTime.Null;

///End Img Upload
if (txtVisaStDate.Text != “”)
{
reg.VisaStartDate = Convert.ToDateTime(txtVisaStDate.Text);
}
else
{

reg.VisaStartDate = sqldatenull;//DateTime.Now; //Convert.ToDateTime(“0000:00:00 00:00:00”);

}
if (txtVisaEndDate.Text != “”)
{
reg.VisaEndDate = Convert.ToDateTime(txtVisaEndDate.Text);
}
else
{

reg.VisaEndDate = sqldatenull;// DateTime.Now;// Convert.ToDateTime(“0000:00:00 00:00:00”);

}

int _RegistrationID = DALRegisrationOnline.InsertUpdateRegisrationInfo(reg);

after insertion the preview of db


The other Tutorial which help you…………………

Namespaces used

    System.Data.SqlClient/ System.Data.OleDb

  • System.Data.SqlTypes
  • Code for System.Data.SqlClient

C#

string
sqlStmt ;
string conString ;
SqlConnection cn =
null;
SqlCommand cmd =
null;
SqlDateTime sqldatenull ;
try
{
sqlStmt = “insert into Emp (FirstName,LastName,Date) Values (@FirstName,@LastName,@Date) “;
conString = “server=localhost;database=Northwind;uid=sa;pwd=;”;
cn =
new SqlConnection(conString);
cmd =
new SqlCommand(sqlStmt, cn);
cmd.Parameters.Add(
new SqlParameter(“@FirstName”, SqlDbType.NVarChar, 11));
cmd.Parameters.Add(
new SqlParameter(“@LastName”, SqlDbType.NVarChar, 40));
cmd.Parameters.Add(
new SqlParameter(“@Date”, SqlDbType.DateTime));
sqldatenull = SqlDateTime.Null;
cmd.Parameters[“@FirstName”].Value = txtFirstName.Text;
cmd.Parameters[“@LastName”].Value = txtLastName.Text;
if (txtDate.Text == “”)
{
cmd.Parameters [“@Date”].Value =sqldatenull ;
//cmd.Parameters[“@Date”].Value = DBNull.Value;
}
else
{
cmd.Parameters[“@Date”].Value = DateTime.Parse(txtDate.Text);
}
cn.Open();
cmd.ExecuteNonQuery();
Label1.Text = “Record Inserted Succesfully”;
}
catch (Exception ex)
{
Label1.Text = ex.Message;
}
finally
{
cn.Close();
}

VB.NET

Dim sqlStmt As String
Dim
conString As String
Dim
cn As SqlConnection
Dim cmd As SqlCommand
Dim sqldatenull As SqlDateTime
Try
sqlStmt = “insert into Emp (FirstName,LastName,Date) Values (@FirstName,@LastName,@Date) ”
conString = “server=localhost;database=Northwind;uid=sa;pwd=;”
cn =
New SqlConnection(conString)
cmd =
New SqlCommand(sqlStmt, cn)
cmd.Parameters.Add(
New SqlParameter(“@FirstName”, SqlDbType.NVarChar, 11))
cmd.Parameters.Add(
New SqlParameter(“@LastName”, SqlDbType.NVarChar, 40))cmd.Parameters.Add(New SqlParameter(“@Date”, SqlDbType.DateTime))
sqldatenull = SqlDateTime.Null
cmd.Parameters(“@FirstName”).Value = txtFirstName.Text
cmd.Parameters(“@LastName”).Value = txtLastName.Text
If (txtDate.Text = “”) Then
cmd.Parameters(“@Date”).Value = sqldatenull
‘cmd.Parameters(“@Date”).Value = DBNull.Value
Else
cmd.Parameters(“@Date”).Value = DateTime.Parse(txtDate.Text)
End If
cn.Open()
cmd.ExecuteNonQuery()
Label1.Text = “Record Inserted Succesfully”
Catch ex As Exception
Label1.Text = ex.Message
Finally
cn.Close()
End Try

Code for System.Data.SqlClient.

C#

string sqlStmt;
string conString ;
OleDbConnection cn =
null ;
OleDbCommand cmd =
null ;
try
{
sqlStmt = “insert into Emp (FirstName,LastName,Date) Values (?,?,?) “;
conString = “Provider=sqloledb.1;user id=sa;pwd=;database=northwind;data source=localhost”;
cn =
new OleDbConnection(conString);
cmd =
new OleDbCommand(sqlStmt, cn) ;
cmd.Parameters.Add(
new OleDbParameter(“@FirstName”, OleDbType.VarChar, 40));
cmd.Parameters.Add(
new OleDbParameter(“@LastName”, OleDbType.VarChar, 40));
cmd.Parameters.Add(
new OleDbParameter(“@Date”, OleDbType.Date));
cmd.Parameters[“@FirstName”].Value = txtFirstName.Text;
cmd.Parameters[“@LastName”].Value = txtLastName.Text;
if ((txtDate.Text == “”) )
{
cmd.Parameters[“@Date”].Value = DBNull.Value;
}
else
{
cmd.Parameters[“@Date”].Value = DateTime.Parse(txtDate.Text);
}
cn.Open();
cmd.ExecuteNonQuery();
Label1.Text = “Record Inserted Succesfully”;
}
catch (Exception ex)
{
Label1.Text = ex.Message;
}
finally
{
cn.Close();
}

VB.NET

Dim
sqlStmt As String
Dim
conString As String
Dim
cn As OleDbConnection
Dim cmd As OleDbCommand
Try
sqlStmt = “insert into Emp (FirstName,LastName,Date) Values (?,?,?) ”
conString = “Provider=sqloledb.1;user id=sa;pwd=;database=northwind;data source=localhost”
cn =
New OleDbConnection(conString)
cmd =
New OleDbCommand(sqlStmt, cn)
cmd.Parameters.Add(
New OleDbParameter(“@FirstName”, OleDbType.VarChar, 40))cmd.Parameters.Add(New OleDbParameter(“@LastName”, OleDbType.VarChar, 40))cmd.Parameters.Add(New OleDbParameter(“@Date”, OleDbType.Date))cmd.Parameters(“@FirstName”).Value = txtFirstName.Text
cmd.Parameters(“@LastName”).Value = txtLastName.Text
If (txtDate.Text = “”) Then
cmd.Parameters(“@Date”).Value = DBNull.Value
Else
cmd.Parameters(“@Date”).Value = DateTime.Parse(txtDate.Text)
End If
cn.Open()
cmd.ExecuteNonQuery()
Label1.Text = “Record Inserted Succesfully”
Catch ex As Exception
Label1.Text = ex.Message
Finally
cn.Close()
End Try

Add your Comment…..


Advertisements
 
Leave a comment

Posted by on August 25, 2010 in ASP Dot Net C#

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: