Problem with DateTime column

Hi All,
I faced this strange problem with sql server.
I have datetime column and storing value from asp.net. If the user doesnt enter any date, then we dont want any value to be stored in the database. But when we checked SQL Server, it default takes this value "01/01/1900". When users clicks on edit button, this value is fetched from database and stored in front end.
I dont want this value to be stored in database if i didnt provide value. But in 1 particular page, there are 5 different date columns. I am not sure about how to go ahead. I tried the following way, but again ended up with same problem.
insert into tablename......values (..., txtFromDate.text, txtToDate.text,...).
Since value is not there, '' is sent to database and above said value is stored. i want NULL to be stored in teh database when user didnt specify any value.
i didnt encounter this problem in Oracle.

appreciate your reply.

In the table design window for this table put a check in the column for allow nulls for the datetime field and make sure that you have not set the field with a default value. Also validate the textbox before data insertion or update like so.
if(txtFromDate.Text.Trim(' ') != "")
(
Place your database insertion code.
}
This will make sure that the textbox actually has something in it before you insert the value. Also you should have to convert the value of the textbox to a datetime before the data is inserted like this.
yadayadayada = Convert.ToDateTime(txtFromDate.Text);
I hope that this helps.

|||Check ifthisarticle helps.
|||

hi,
thanks for your reply. I thought of doing it like check the textbox value. But the problem is i am having 5 date control in that particular page.
So the condition for NULL checking, i need to try out with all probability. I believe, in that case, i should use around 25 if statement with each statement checking for all 5 condidtion.
Is there any other way to doing it.
rgds
ramu

|||you can check for nulls at the time of passing the value to the stored proc. you will have only 5 conditions. just before you pass the value to the parameter check for null. if you post some code we can help you out.|||You can just do iif(me.txtDate1.text.length=0,dbNull.value, me.txtDate1.text) if you are using parms. If not iif(me.txtDate1.text.length=0,"NULL", me.txtDate1.text)

Nick|||hi nick
dbnull.value does not seem to work. we need to use a special sqldatetime.null and import the sqltypes namespace for it.|||hi all,
once again, thanks for your reply.
since it was getting complicated and time was main constraint for me, so i tried it in the following way.
If the value is null, i let it get stored as 01/01/1900 itself.
but while fetching record for edit/view, i am checking those date columns for for above. If yes, i display null value, otherwise the stored value is displayed.
the above one temporarily solved my problem.
based on your solution, i think iif will solve my issue.
rgds
ramu|||

ndinakar wrote:

hi nick
dbnull.value does not seem to work. we need to use a special sqldatetime.null and import the sqltypes namespace for it.

Maybe its because of the way its being used? I always pass dbNull.value to my stored procs if the value is blank....

Nick|||It works for all columns except datetime columns. Leaving datetime columns empty will throw errors.

Problem with DataSet or SQL query

I've faced the following problem while forming DataSet. I'd like to ask the following SQL query:

___

select Users.UserID,Users.FirstName,Users.LastName, Users.Email, data.Suffix, data.Unit,data.Telephone, data.IM,

data.Website
from
(select UserID,
max(case PropertyName when 'Suffix' then PropertyValue end)Suffix,
max(case PropertyName when 'Unit' then PropertyValue end)Unit,
max(case PropertyName when 'Telephone' then PropertyValue end)Telephone,
max(case PropertyName when 'IM' then PropertyValue end)IM,
max(case PropertyName when 'Website' then PropertyValue end)Website
from
(
SELECT UserProfile.UserID, UserProfile.PropertyValue, UserProfile.PropertyDefinitionID,

ProfilePropertyDefinition.PropertyName FROM UserProfile,ProfilePropertyDefinition
WHERE UserProfile.PropertyDefinitionID=ProfilePropertyDefinition.PropertyDefinitionID
)table2
group by UserID)
data
inner join Users on Users.UserID = data.UserID
WHERE data.Suffix = 'IT';

__

Nothing happens while fulfilling as well (i.e. if to set up Breakpoint and watch the DataSet condition - it will be empty there). If to change the request to let's say some standart - then DataSet downloading begins - it seems there's something wrong with the request

I'm checking...If to look with the holp of request master and press "perform", data is reflected in a normal way...But as soon as i press "Bild", emptiness is shown as usual

Please, prompt me what to do

Initial code in the file .aspx.cs:

----------

UserInfo ds =new UserInfo();

UserInfoTableAdapters.UserInformationTableAdapter da =new UserInfoTableAdapters.UserInformationTableAdapter();

da.Fill(ds.UserInformation);

Info.DataSource = ds.UserInformation;

Info.DataBind();

Hello Alag,

Did the query gave the correct result in the SQL Query Analyzer?

If you are using an xsd dataset definition, execute the menu option Preview Data ... to see the xsd dataset works fine.

Jeroen Molenaar.

|||

Result on SQL Query Analyzer is correct.

AND RESULT CORRECT IN "Execute Query" in DataSet Ouery Builder.. But if press "Bild"..((( no correct

|||

Bild sounds like build, but what error message is the compiler reporting when "bildung".

Jeroen Molenaar.

|||

No error. Just nothing displays on the screen .. And as you see that empty DataSet.

Sorry for the poor English. I am from Russia, and the school taught him poorly.

|||

I thought "bild" was German. But your English is much, much better than my Russian!

Are you indeed using an xsd dataset?

Jeroen Molenaar.

PS: Ja nie gavarjoe paroeskie.

|||

I am using "Add New Item" -> "DataSet"

PS. You russian is good)

|||

In that case you are using an xsd dataset file.

When you open the UserInfo dataset and select the datatable UserInformation. Give a right click on the mouse and select the option: Preview data ... preview.

Does it give any result?

Jeroen Molenaar.

|||

Yes, the results show

|||

We are now moving to the asp page. Can you show more of the code you are using?

Jeroen Molenaar.

|||

Default.aspx.cs:

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

publicpartialclass_Default : System.Web.UI.Page

{

protectedvoid Page_Load(object sender,EventArgs e)

{

UserInfo ds =newUserInfo();UserInfoTableAdapters.UserInformationTableAdapter da =new UserInfoTableAdapters.UserInformationTableAdapter();

da.Fill(ds.UserInformation);

Info.DataSource = ds.UserInformation;

Info.DataBind();

}

}

|||

Default.aspx

<%@. Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body style="background-color:#F7F7F7">
<form id="form1" runat="server">
<div>

<asp:DataList ID="Info" runat="server">
<ItemTemplate>
<table width="700" border="0" cellpadding="0" cellspacing="2" style="font-family:Tahoma; background-color:white;">
<tr>
<td style="width:130; background-color:#F7F7F7;"><asp:Image ID="Image1" runat="server" ImageUrl='' /></td>
<td valign="top" style="background-color:#F7F7F7"><table width="100%" border="0" cellpadding="0" cellspacing="0" style="background-color:#F7F7F7">
<tr>
<td style=" font-size: 13px;padding-left: 12px;text-decoration:underline;color:#003366;font-weight:bold; height:30px; background:background.png" >
<asp:Label ID="Label7" runat="server"><%# DataBinder.Eval(Container.DataItem, "FirstName")%></asp:Label>
<asp:Label ID="Label8" runat="server"><%# DataBinder.Eval(Container.DataItem, "LastName")%></asp:Label>
</td>
</tr>
<tr>
<td style="height:4px;"></td>
</tr>
...

<tr>
<td style="height:4px;"></td>
</tr>
</table></td>
</tr>
</table>
</ItemTemplate>
</asp:DataList>
</div>
</form>
</body>
</html>

|||

Looks fine to me. Final piece of code: markup language of the default.asp.

Jeroen Molenaar.

PS: Whoah, that was fast!

|||

I don't understand ((.. what I want? The code above is default.aspx

|||

When I was typing my reply, you already added the markup code from default.aspx. My question to add the code was too late

The problem is propably in the definition of your DataList control on the page, but I don't know exactly. The Data Access/SQL server forum is not a perfect place for this problem.

You better ask your question in another forum about ASP, for example the getting started forum.

Good luck!

Jeroen Molenaar.