Member Avatar for srky

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
  • 910 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by rohand
Member Avatar for srky

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

Member Avatar for PsychoCoder

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

Member Avatar for srky

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 because: Fixed formatting
Member Avatar for rohand

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.

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.