Insert Null value into DateTime column in SQL server from ASPX application

Question

srky 0 Newbie Poster

13 Years Ago

cmd1.Parameters.Add("@emp_name1", SqlDbType.NVarChar);
cmd1.Parameters.Add("@join_date1", SqlDbType.DateTime);

cmd1.Parameters["@emp_name1"].Value = name.Text;

System.Data.SqlTypes.SqlDateTime getDate;
//set DateTime null
getDate = SqlDateTime.Null;

if (joindate.Text == "")
{
cmd1.Parameters["@join_date1"].Value = getDate;
}
else
{
cmd1.Parameters["@join_date1"].Value = joindate.Text;
}

I used this code to insert a null value in datetime column of sql server table
but always its giving the error
string is not recognized as a valid datetime

Please help me.

Thanks in Advance.

asp.net

3 Contributors
5 Replies
901 Views
1 Day Discussion Span
Latest Post 13 Years Ago Latest Post by rohand

All 5 Replies

PsychoCoder 6 Light Poster

13 Years Ago

Have you tried using DBNull.Value

PsychoCoder 6 Light Poster

13 Years Ago

Take a stab at this:

if (string.IsNullOrEmpty(joindate.Text))
{
cmd1.Parameters["@join_date1"].Value = DBNull.Value;
}
else
{
cmd1.Parameters["@join_date1"].Value = joindate.Text;
}

And see how that works for you ;)

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

srky 0 Newbie Poster · Answer 1 · 2010-08-18T11:30:26+00:00

But where to use DBNull.Value in my code
Because I used SqlDateTime.Null
Please reply me

srky 0 Newbie Poster · Answer 2 · 2010-08-18T11:46:46+00:00

Actually its giving the error in this statement:

cmdL1.Parameters["@FNOStartDate"].Value =txtfstdt.Text;

My code is like this

SqlConnection c1 = new SqlConnection(conString);
                    c1.Open();
                    string q1 = "insert into TerminalMaster(BranchId,BranchName,SubBranchId,SubBranchName,TerminalId,TerminalType,TerminalName,IntCtcl,DBType,StartDate,EndDate,Remarks,AllowBSECash,BSEUniqueId,BSEStartDate,BSEEndDate,BSERemarks,AllowNSECash,NSEUniqueId,NSEStartDate,NSEEndDate,NSERemarks,AllowNseFno,FNOUniqueId,FNOStartDate,FNOEndDate,FNORemarks) values (@BranchId,@BranchName,@SubBranchId,@SubBranchName,@TerminalId,@TerminalType,@TerminalName,@IntCtcl,@DBType,@StartDate,@EndDate,@Remarks,@AllowBSECash,@BSEUniqueId,@BSEStartDate,@BSEEndDate,@BSERemarks,@AllowNSECash,@NSEUniqueId,@NSEStartDate,@NSEEndDate,@NSERemarks,@AllowNseFno,@FNOUniqueId,@FNOStartDate,@FNOEndDate,@FNORemarks)";
                    SqlCommand cmdL1 = new SqlCommand(q1, c1);
                    cmdL1.Parameters.Add(new SqlParameter("@BranchId", SqlDbType.NVarChar, 16));
                    cmdL1.Parameters.Add(new SqlParameter("@BranchName", SqlDbType.NVarChar, 20));
                    cmdL1.Parameters.Add(new SqlParameter("@SubBranchId", SqlDbType.NVarChar, 16));
                    cmdL1.Parameters.Add(new SqlParameter("@SubBranchName", SqlDbType.NVarChar, 20));
                    cmdL1.Parameters.Add(new SqlParameter("@TerminalId", SqlDbType.NVarChar, 15));
                    cmdL1.Parameters.Add(new SqlParameter("@TerminalType", SqlDbType.Int));
                    cmdL1.Parameters.Add(new SqlParameter("@TerminalName", SqlDbType.NVarChar, 20));
                    cmdL1.Parameters.Add(new SqlParameter("@IntCtcl", SqlDbType.NVarChar, 50));
                    cmdL1.Parameters.Add(new SqlParameter("@DBType", SqlDbType.NVarChar, 20));
                    cmdL1.Parameters.Add(new SqlParameter("@StartDate", SqlDbType.DateTime));
                    cmdL1.Parameters.Add(new SqlParameter("@EndDate", SqlDbType.DateTime));
                    cmdL1.Parameters.Add(new SqlParameter("@Remarks", SqlDbType.NVarChar, 300));
                    cmdL1.Parameters.Add(new SqlParameter("@AllowBSECash", SqlDbType.Char, 1));
                    cmdL1.Parameters.Add(new SqlParameter("@BSEUniqueId", SqlDbType.NVarChar, 16));
                    cmdL1.Parameters.Add(new SqlParameter("@BSEStartDate", SqlDbType.DateTime));
                    cmdL1.Parameters.Add(new SqlParameter("@BSEEndDate", SqlDbType.DateTime));
                    cmdL1.Parameters.Add(new SqlParameter("@BSERemarks", SqlDbType.NVarChar, 300));
                    cmdL1.Parameters.Add(new SqlParameter("@AllowNSECash", SqlDbType.Char, 1));
                    cmdL1.Parameters.Add(new SqlParameter("@NSEUniqueId", SqlDbType.NVarChar, 16));
                    cmdL1.Parameters.Add(new SqlParameter("@NSEStartDate", SqlDbType.DateTime));
                    cmdL1.Parameters.Add(new SqlParameter("@NSEEndDate", SqlDbType.DateTime));
                    cmdL1.Parameters.Add(new SqlParameter("@NSERemarks", SqlDbType.NVarChar, 300));
                    cmdL1.Parameters.Add(new SqlParameter("@AllowNseFno", SqlDbType.Char, 1));
                    cmdL1.Parameters.Add(new SqlParameter("@FNOUniqueId", SqlDbType.NVarChar, 16));
                    cmdL1.Parameters.Add(new SqlParameter("@FNOStartDate", SqlDbType.DateTime));
                    cmdL1.Parameters.Add(new SqlParameter("@FNOEndDate", SqlDbType.DateTime));
                    cmdL1.Parameters.Add(new SqlParameter("@FNORemarks", SqlDbType.NVarChar, 300));
                    sqldatenull = SqlDateTime.Null;

                    cmdL1.Parameters["@BranchId"].Value = b;
                    cmdL1.Parameters["@BranchName"].Value = bb;
                    cmdL1.Parameters["@SubBranchId"].Value = s;
                    cmdL1.Parameters["@SubBranchName"].Value = sb;
                    cmdL1.Parameters["@TerminalId"].Value = ttid.Text;
                    cmdL1.Parameters["@TerminalType"].Value = ttype.Text;
                    cmdL1.Parameters["@TerminalName"].Value = tname.Text;
                    cmdL1.Parameters["@IntCtcl"].Value = ic;
                    cmdL1.Parameters["@DBType"].Value = dbs;
                    cmdL1.Parameters["@StartDate"].Value =tstdt.Text;
                    if (string.IsNullOrEmpty(teddt.Text))
                    {
                        cmdL1.Parameters["@EndDate"].Value = DBNull.Value;
                    }
                    else
                    {
                        cmdL1.Parameters["@EndDate"].Value = teddt.Text;
                    }
                    cmdL1.Parameters["@Remarks"].Value = trmks.Text;
                    cmdL1.Parameters["@AllowBSECash"].Value = bopt;
                    cmdL1.Parameters["@BSEUniqueId"].Value = txtbid.Text;
                    cmdL1.Parameters["@BSEStartDate"].Value =txtbstdt.Text;          
                    if (string.IsNullOrEmpty(txtbeddt.Text))
                    {
                        cmdL1.Parameters["@BSEEndDate"].Value = DBNull.Value;
                    }
                    else
                    {
                        cmdL1.Parameters["@BSEEndDate"].Value = txtbeddt.Text; 
                    }
                    cmdL1.Parameters["@BSERemarks"].Value = txtbrmks.Text;
                    cmdL1.Parameters["@AllowNSECash"].Value = nopt;
                    cmdL1.Parameters["@NSEUniqueId"].Value = txtnid.Text;
                    cmdL1.Parameters["@NSEStartDate"].Value = txtnstdt.Text;                
                    if (string.IsNullOrEmpty(txtneddt.Text))
                    {
                        cmdL1.Parameters["@NSEEndDate"].Value = DBNull.Value;
                    }
                    else
                    {
                        cmdL1.Parameters["@NSEEndDate"].Value =txtneddt.Text;
                    }
                    cmdL1.Parameters["@NSERemarks"].Value = txtnrmks.Text;
                    cmdL1.Parameters["@AllowNseFno"].Value = fopt;
                    cmdL1.Parameters["@FNOUniqueId"].Value = txtfid.Text;
                    cmdL1.Parameters["@FNOStartDate"].Value =txtfstdt.Text;                
                    if (string.IsNullOrEmpty(txtfeddt.Text))
                    {
                        cmdL1.Parameters["@FNOEndDate"].Value = DBNull.Value;
                    }
                    else
                    {
                        cmdL1.Parameters["@FNOEndDate"].Value = txtfeddt.Text;      
                    }

                    cmdL1.Parameters["@FNORemarks"].Value = txtfrmks.Text;

                    int i1 = cmdL1.ExecuteNonQuery();
                    if (i1 == 1)
                    {
                        //Response.Write("<script>alert('Records Added Successfully');</script>");
                        msg.Text = "Records Inserted Successfully.";                       
                        bsetbl.Visible = true;
                        nsetbl.Visible = true;
                        fnotbl.Visible = true;
                        btntbl.Visible = true;

                    }
                    else
                    {
                        Response.Write("<script>alert('Error in insertion');</script>");
                        bsetbl.Visible = true;
                        nsetbl.Visible = true;
                        fnotbl.Visible = true;
                        btntbl.Visible = true;
                    }
                    c1.Close();
                    cmdL1.Dispose();

and the error is :

Failed to convert parameter value from a String to a DateTime.

rohand 1 Posting Whiz in Training · Answer 3 · 2010-08-19T13:00:58+00:00

The error is coming up because you are trying to insert text/string value to date time column. See your below statments :

// Here you have defined data type as date time for StartDate and EndDate.
cmdL1.Parameters.Add(new SqlParameter("@StartDate", SqlDbType.DateTime));
cmdL1.Parameters.Add(new SqlParameter("@EndDate", SqlDbType.DateTime));

cmdL1.Parameters["@StartDate"].Value =tstdt.Text; // here, you are trying to store text value to DateTime datatype defined in above red highlighted portion.
if (string.IsNullOrEmpty(teddt.Text))
{
    cmdL1.Parameters["@EndDate"].Value = DBNull.Value;
}
else
{
    cmdL1.Parameters["@EndDate"].Value = teddt.Text;
}

Also in table if you have defined StartDate, EndDate and so on as Varchar or text type then don't use SqlDbType.DateTime for setting command object parameter datatype.

Actually its giving the error in this statement\
cmdL1.Parameters["@FNOStartDate"].Value =txtfstdt.Text;
My code is like this
SqlConnection c1 = new SqlConnection(conString);
c1.Open();
string q1 = "insert into TerminalMaster(BranchId,BranchName,SubBranchId,SubBranchName,TerminalId,TerminalType,TerminalName,IntCtcl,DBType,StartDate,EndDate,Remarks,AllowBSECash,BSEUniqueId,BSEStartDate,BSEEndDate,BSERemarks,AllowNSECash,NSEUniqueId,NSEStartDate,NSEEndDate,NSERemarks,AllowNseFno,FNOUniqueId,FNOStartDate,FNOEndDate,FNORemarks) values (@BranchId,@BranchName,@SubBranchId,@SubBranchName,@TerminalId,@TerminalType,@TerminalName,@IntCtcl,@DBType,@StartDate,@EndDate,@Remarks,@AllowBSECash,@BSEUniqueId,@BSEStartDate,@BSEEndDate,@BSERemarks,@AllowNSECash,@NSEUniqueId,@NSEStartDate,@NSEEndDate,@NSERemarks,@AllowNseFno,@FNOUniqueId,@FNOStartDate,@FNOEndDate,@FNORemarks)";
SqlCommand cmdL1 = new SqlCommand(q1, c1);
cmdL1.Parameters.Add(new SqlParameter("@BranchId", SqlDbType.NVarChar, 16));
cmdL1.Parameters.Add(new SqlParameter("@BranchName", SqlDbType.NVarChar, 20));
cmdL1.Parameters.Add(new SqlParameter("@SubBranchId", SqlDbType.NVarChar, 16));
cmdL1.Parameters.Add(new SqlParameter("@SubBranchName", SqlDbType.NVarChar, 20));
cmdL1.Parameters.Add(new SqlParameter("@TerminalId", SqlDbType.NVarChar, 15));
cmdL1.Parameters.Add(new SqlParameter("@TerminalType", SqlDbType.Int));
cmdL1.Parameters.Add(new SqlParameter("@TerminalName", SqlDbType.NVarChar, 20));
cmdL1.Parameters.Add(new SqlParameter("@IntCtcl", SqlDbType.NVarChar, 50));
cmdL1.Parameters.Add(new SqlParameter("@DBType", SqlDbType.NVarChar, 20));
cmdL1.Parameters.Add(new SqlParameter("@StartDate", SqlDbType.DateTime));
cmdL1.Parameters.Add(new SqlParameter("@EndDate", SqlDbType.DateTime));
cmdL1.Parameters.Add(new SqlParameter("@Remarks", SqlDbType.NVarChar, 300));
cmdL1.Parameters.Add(new SqlParameter("@AllowBSECash", SqlDbType.Char, 1));
cmdL1.Parameters.Add(new SqlParameter("@BSEUniqueId", SqlDbType.NVarChar, 16));
cmdL1.Parameters.Add(new SqlParameter("@BSEStartDate", SqlDbType.DateTime));
cmdL1.Parameters.Add(new SqlParameter("@BSEEndDate", SqlDbType.DateTime));
cmdL1.Parameters.Add(new SqlParameter("@BSERemarks", SqlDbType.NVarChar, 300));
cmdL1.Parameters.Add(new SqlParameter("@AllowNSECash", SqlDbType.Char, 1));
cmdL1.Parameters.Add(new SqlParameter("@NSEUniqueId", SqlDbType.NVarChar, 16));
cmdL1.Parameters.Add(new SqlParameter("@NSEStartDate", SqlDbType.DateTime));
cmdL1.Parameters.Add(new SqlParameter("@NSEEndDate", SqlDbType.DateTime));
cmdL1.Parameters.Add(new SqlParameter("@NSERemarks", SqlDbType.NVarChar, 300));
cmdL1.Parameters.Add(new SqlParameter("@AllowNseFno", SqlDbType.Char, 1));
cmdL1.Parameters.Add(new SqlParameter("@FNOUniqueId", SqlDbType.NVarChar, 16));
cmdL1.Parameters.Add(new SqlParameter("@FNOStartDate", SqlDbType.DateTime));
cmdL1.Parameters.Add(new SqlParameter("@FNOEndDate", SqlDbType.DateTime));
cmdL1.Parameters.Add(new SqlParameter("@FNORemarks", SqlDbType.NVarChar, 300));
sqldatenull = SqlDateTime.Null;
cmdL1.Parameters["@BranchId"].Value = b;
cmdL1.Parameters["@BranchName"].Value = bb;
cmdL1.Parameters["@SubBranchId"].Value = s;
cmdL1.Parameters["@SubBranchName"].Value = sb;
cmdL1.Parameters["@TerminalId"].Value = ttid.Text;
cmdL1.Parameters["@TerminalType"].Value = ttype.Text;
cmdL1.Parameters["@TerminalName"].Value = tname.Text;
cmdL1.Parameters["@IntCtcl"].Value = ic;
cmdL1.Parameters["@DBType"].Value = dbs;
cmdL1.Parameters["@StartDate"].Value =tstdt.Text;
if (string.IsNullOrEmpty(teddt.Text))
{
cmdL1.Parameters["@EndDate"].Value = DBNull.Value;
}
else
{
cmdL1.Parameters["@EndDate"].Value = teddt.Text;
}
cmdL1.Parameters["@Remarks"].Value = trmks.Text;
cmdL1.Parameters["@AllowBSECash"].Value = bopt;
cmdL1.Parameters["@BSEUniqueId"].Value = txtbid.Text;
cmdL1.Parameters["@BSEStartDate"].Value =txtbstdt.Text;
if (string.IsNullOrEmpty(txtbeddt.Text))
{
cmdL1.Parameters["@BSEEndDate"].Value = DBNull.Value;
}
else
{
cmdL1.Parameters["@BSEEndDate"].Value = txtbeddt.Text;
}
cmdL1.Parameters["@BSERemarks"].Value = txtbrmks.Text;
cmdL1.Parameters["@AllowNSECash"].Value = nopt;
cmdL1.Parameters["@NSEUniqueId"].Value = txtnid.Text;
cmdL1.Parameters["@NSEStartDate"].Value = txtnstdt.Text;
if (string.IsNullOrEmpty(txtneddt.Text))
{
cmdL1.Parameters["@NSEEndDate"].Value = DBNull.Value;
}
else
{
cmdL1.Parameters["@NSEEndDate"].Value =txtneddt.Text;
}
cmdL1.Parameters["@NSERemarks"].Value = txtnrmks.Text;
cmdL1.Parameters["@AllowNseFno"].Value = fopt;
cmdL1.Parameters["@FNOUniqueId"].Value = txtfid.Text;
cmdL1.Parameters["@FNOStartDate"].Value =txtfstdt.Text;
if (string.IsNullOrEmpty(txtfeddt.Text))
{
cmdL1.Parameters["@FNOEndDate"].Value = DBNull.Value;
}
else
{
cmdL1.Parameters["@FNOEndDate"].Value = txtfeddt.Text;
}
cmdL1.Parameters["@FNORemarks"].Value = txtfrmks.Text;
int i1 = cmdL1.ExecuteNonQuery();
if (i1 == 1)
{
//Response.Write("<script>alert('Records Added Successfully');</script>");
msg.Text = "Records Inserted Successfully.";
bsetbl.Visible = true;
nsetbl.Visible = true;
fnotbl.Visible = true;
btntbl.Visible = true;
}
else
{
Response.Write("<script>alert('Error in insertion');</script>");
bsetbl.Visible = true;
nsetbl.Visible = true;
fnotbl.Visible = true;
btntbl.Visible = true;
}
c1.Close();
cmdL1.Dispose();
and the error is :
Failed to convert parameter value from a String to a DateTime.

Insert Null value into DateTime column in SQL server from ASPX application

Recommended Answers Collapse Answers

All 5 Replies

Recommended Answers