Date Problem

[Guest]

Dear All,

I have a field in the datebase called ReacheIELTSDate with a DataType
"datetime"
with a length 8. I have a ASPX Web Form with several TEXTBOXES to insert
values into the database table. One of the textbox is attached to this date
field . I have a save button which saves the values in the datebase when the
user enters something in the txtdate text box. Thats fine I have no problem
with that. But when the txtdate box is left empty and I press a save button.
It inserts 01/01/1900 in the database table. which I dont want. All i want is
that when the text box is left empty and if i press a save button it should
insert empty value into the database field.

Below is the stored procedure I am using for updating the table
CREATE PROCEDURE UpdateProfessionalExams

@reacheIeltsDate datetime

Update HCP_Personal_Details Set ReacheIELTSDate=@reacheIeltsDate where
Hcp_Id=@hcpid

Below is the code behind the save button:

Dim conProfessionalExams As SqlConnection
Dim cmdUpdateProfession As SqlCommand

conProfessionalExams = New SqlConnection(Application("strConnection"))
conProfessionalExams.Open()
cmdUpdateProfession = New SqlCommand("UpdateProfessionalExams",
conProfessionalExams)
cmdUpdateProfession .CommandType = CommandType.StoredProcedure
cmdUpdateProfession .Parameters.Add("@reacheIeltsDate", txtIELTSDate.Text)
cmdUpdateProfession .Parameters.Add("@hcpid", Session("CurrentHcpId"))
cmdUpdateProfession .ExecuteNonQuery()

 

[bruce barker]

you cannot store a blank in a datetime. you could store a null though.
change your code to test for blank, and if so set the parameter to dbnull,.
on retrieval, if null, set field to blank. you will have to check that the
proc and table support null in the column.

-- bruce (sqlwork.com)


| Dear All,
|
| I have a field in the datebase called ReacheIELTSDate with a DataType
| "datetime"
| with a length 8. I have a ASPX Web Form with several TEXTBOXES to insert
| values into the database table. One of the textbox is attached to this
date
| field . I have a save button which saves the values in the datebase when
the
| user enters something in the txtdate text box. Thats fine I have no
problem
| with that. But when the txtdate box is left empty and I press a save
button.
| It inserts 01/01/1900 in the database table. which I dont want. All i want
is
| that when the text box is left empty and if i press a save button it
should
| insert empty value into the database field.
|
| Below is the stored procedure I am using for updating the table
| CREATE PROCEDURE UpdateProfessionalExams
|
| @reacheIeltsDate datetime
|
| Update HCP_Personal_Details Set ReacheIELTSDate=@reacheIeltsDate where
| Hcp_Id=@hcpid
|
| Below is the code behind the save button:
|
| Dim conProfessionalExams As SqlConnection
| Dim cmdUpdateProfession As SqlCommand
|
| conProfessionalExams = New SqlConnection(Application("strConnection"))
| conProfessionalExams.Open()
| cmdUpdateProfession = New SqlCommand("UpdateProfessionalExams",
| conProfessionalExams)
| cmdUpdateProfession .CommandType = CommandType.StoredProcedure
| cmdUpdateProfession .Parameters.Add("@reacheIeltsDate",
txtIELTSDate.Text)
| cmdUpdateProfession .Parameters.Add("@hcpid", Session("CurrentHcpId"))
| cmdUpdateProfession .ExecuteNonQuery()
|
|