0

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.

3
Contributors
5
Replies
8
Views
7 Years
Discussion Span
Last Post by rohand
0

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

0

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 ;)

0

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.

Edited by mike_2000_17: Fixed formatting

0

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.