-3

I have a table in which a column levdat is of DateTime datatype. its value is inserted by a textbox. sometimes it can be null. I want whenever textbox is empty null is inserted into database column. but it is inserting 0001-01-01 00:00:00. my code is

string date3=txt_lev_dat.Text;
if (date3 != "")
{
    if ((!DateTime.TryParse(date3, out datetime)) && (date3 != ""))
    {
        lbl_lev_war.Text = "Incorrect Date";
        lbl_lev_war.ToolTip = "Please Enter Correct Date";
    }
    else
    {
        objprp.p_tchlevdat = Convert.ToDateTime(date3);
    }
}

I tried to enter DBnull.value. but it gives me error. Thanks in advance. My store procedure is

DELIMITER $$

 DROP PROCEDURE IF EXISTS `db_school_management`.`ins_tch`$$

       CREATE PROCEDURE `db_school_management`.`ins_tch`(citycod INT,_statecod INT,_tchfstnam       VARCHAR(50),_tchlstnam VARCHAR(50),_tchfatnam VARCHAR(50),
    _tchmotnam VARCHAR(50),_tchdob DATETIME,_tchgen VARCHAR(5),_tchadd VARCHAR(200),_tchmob VARCHAR(20),_tchphn VARCHAR(20),_tchzipcod VARCHAR(10),
    _tchedu VARCHAR(50),_tchsal INT,_tcheml VARCHAR(50),_tchusrnam VARCHAR(50),_tchpwd VARCHAR(50),_tchjoindat DATETIME,_tchpic VARCHAR(50),_tchlevdat DATETIME,_flag VARCHAR(5))
        BEGIN
    INSERT INTO tbteacher(citycod,statecod,tchfstnam,tchlstnam,tchfatnam,tchmotnam,tchdob,tchgen,tchadd,tchmob,tchphn,tchzipcod,tchedu,tchsal,
    tcheml,tchusrnam,tchpwd,tchjoindat,tchpic,tchlevdat,flag) VALUES(_citycod,_statecod,_tchfstnam,_tchlstnam,_tchfatnam,_tchmotnam,_tchdob,_tchgen,
    _tchadd,_tchmob,_tchphn,_tchzipcod,_tchedu,_tchsal,_tcheml,_tchusrnam,_tchpwd,_tchjoindat,_tchpic,_tchlevdat,_flag);
        END
Improve this question
6
  • "but it gives me error" - which error would that be? Commented Feb 5, 2014 at 5:32
  • does the column accept Null value in MySQL? Commented Feb 5, 2014 at 5:32
  • @MitchWheat Object cannot be cast from DBNull to other types. Commented Feb 5, 2014 at 5:34
  • Yes column accept Null value . Commented Feb 5, 2014 at 5:35
  • 4
    @downvoter can you explain why this question is off topic. some guys here sit only to downvote questions Commented Feb 5, 2014 at 5:36

1 Answer 1

Reset to default
1

You Can use ISNULL Property in MySQL

ISNULL

Like this

ISNULL(levdat,'')

Try this

DELIMITER $$

 DROP PROCEDURE IF EXISTS `db_school_management`.`ins_tch`$$

 CREATE PROCEDURE `db_school_management`.`ins_tch`
     (citycod INT,_statecod INT,_tchfstnam       VARCHAR(50),_tchlstnam VARCHAR(50),_tchfatnam   VARCHAR(50),
    _tchmotnam VARCHAR(50),_tchdob DATETIME,_tchgen VARCHAR(5),_tchadd VARCHAR(200),_tchmob VARCHAR(20),_tchphn VARCHAR(20),_tchzipcod VARCHAR(10),
    _tchedu VARCHAR(50),_tchsal INT,_tcheml VARCHAR(50),_tchusrnam VARCHAR(50),_tchpwd VARCHAR(50),_tchjoindat DATETIME,_tchpic VARCHAR(50),_tchlevdat DATETIME,_flag VARCHAR(5))
 BEGIN
    INSERT INTO tbteacher(citycod,statecod,tchfstnam,tchlstnam,tchfatnam,tchmotnam,tchdob,tchgen,tchadd,tchmob,tchphn,tchzipcod,tchedu,tchsal,
    tcheml,tchusrnam,tchpwd,tchjoindat,tchpic,tchlevdat,flag) VALUES(_citycod,_statecod,_tchfstnam,_tchlstnam,_tchfatnam,_tchmotnam,ISNULL(_tchdob,''),_tchgen,        _tchadd,_tchmob,_tchphn,_tchzipcod,_tchedu,_tchsal,_tcheml,_tchusrnam,_tchpwd,_tchjoindat,_tchpic,ISNULL(_tchlevdat,''),_flag);
 END
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

thanks for answering vignesh, but i have to do it from c#. i am using store procedureto insert the value.
No problem add that line in store procedure.. Mark useful if it helped you
okey, Vignesh I'll try it
else post your stored proc. Ill give you suggestion
OP seems expecting a null go into db field, when text box is empty. ... I want whenever textbox is empty null is inserted into database column. but it is inserting 0001-01-01 00:00:00. ...
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.