Problem With Adding A Date Field To A Database When It Is Null

Jun 21, 2005

I am having problems adding a date field to a SQL Server Database from a form in ASP.Net. When I leave the date field blank, it automatically inserts Monday, January 01, 1900. I want it to be null when the expiration date is left blank. Can someone please help me with this? Here's my code for adding information from the table to the database:  '---------------------------------------------         ' name: Button_Click()         '---------------------------------------------        Sub Button_Click( s As Object, e As EventArgs )                        Dim strConnect As String              Dim objConnect As SQLConnection              Dim strInsert As String             Dim cmdInsert As SqlCommand            

             'Get connection string from Web.Config             strConnect = ConfigurationSettings.AppSettings("ConnectionString")

             objConnect = New SqlConnection(strConnect)                            strInsert = "Insert DomainName (ClientID, DomainName, Registrar, ExpirationDate ) Values ( @ClientID, @DomainName, @Registrar, @ExpirationDate )"             cmdInsert = New SqlCommand( strInsert, objConnect)             cmdInsert.Parameters.Add( "@ClientID", dropClient.SelectedItem.Value )             cmdInsert.Parameters.Add( "@DomainName", txtDomainName.Text )             cmdInsert.Parameters.Add( "@Registrar", txtRegistrar.Text )             cmdInsert.Parameters.Add( "@ExpirationDate", txtExpirationDate.Text )

             objConnect.Open()             cmdINsert.ExecuteNonQuery()             objConnect.Close()                                  'Display the results "page"                  DisplayResults() 

        End Sub Here's the code for the form:         <form id="frmDomainNames" method="post" runat="server" onSubmit="return InputIsValid()">         <div align="center">    <table border="0" cellpadding="2" cellspacing="2" width="50%" bgcolor="#330099">     <tr>      <td height="37" colspan="2" align="center" valign="middle" bgcolor="#330099"><font color="white" size="5">Domain Name Information</font></td>      <td>&nbsp;</td>     </tr>              <tr>      <td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr>          Client's Name:</nobr></strong></font></td>      <td colspan="2" valign="top" bgcolor="#e8e8e8">       <p>        <asp:dropdownlist id="dropClient" runat="server" />        </p>      </td>     </tr>    <tr>      <td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr>          Domain Name:</nobr></strong></font></td>      <td colspan="2" valign="top" bgcolor="#e8e8e8">       <p>        <ASP:TextBox id="txtDomainName" runat="server" TextMode="SingleLine" Columns="30" />               </p>      </td>                         <tr>      <td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr>          Registrar:</nobr></strong></font></td>      <td colspan="2" valign="top" bgcolor="#e8e8e8">       <p>        <ASP:TextBox id="txtRegistrar" runat="server" TextMode="SingleLine" Columns="30" />       </p>      </td>     </tr>     <tr>      <td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr>          Expiration Date:</nobr></strong></font></td>      <td colspan="2" valign="top" bgcolor="#e8e8e8">       <p>        <ASP:TextBox id="txtExpirationDate" runat="server" TextMode="SingleLine" Columns="10" />       </p>      </td>     </tr>               

     <TR>      <TD>      </TD>      <TD align="center">       <asp:Button                            Text="Submit"                            OnClick="Button_Click"                            Runat="Server" />      </TD>     </TR>    </table>  </form>  </div>

View 1 Replies


Pass In Null/blank Value In The Date Field Or Declare The Field As String And Convert

Dec 30, 2003

I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.

I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable

mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))

option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.

With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute

Please help

View 6 Replies View Related

How To Use BCP With Null Date Field

May 28, 2008

My tests have shown that if you use BCP to import records with a
date field as " ", it won't work.

Is there a way to get BCP to accept empty date fields?

View 3 Replies View Related

Doing A 'between' On A NULL In A Date Field

Mar 29, 2007

Hi everyone,

I have two tables - caseinfo and steps.

Caseinfo shows when a particular case entered and exited a particular project. If the project hasn't ended yet, then the end date is NULL.

Steps shows the steps the case has gone through and the dates of those particular steps.

I need to join the tables to show the steps the case went through during a particular project, but I'm having trouble with the NULL values in the end dates.

If I join the tables so that the step date is between the start and end dates of the project, then I get no step information for the cases where the end date is NULL (that is, where the project hasn't ended yet).

Does anybody have any ideas?

Here are my tables, the query that shows the main idea (with the wrong result), and my expected results.

Thank you for reading.

--- create sample data
set dateformat ymd

declare @caseinfo table (caseid int, startdate smalldatetime, enddate smalldatetime)

insert @caseinfo
select 10, '2006-12-23', '2006-12-27' union all
select 20, '2006-12-23', NULL union all
select 30, '2006-12-23', NULL union all
select 40, '2007-1-15', '2007-3-4'

declare @steps table (caseid int, stepnumber int, stepdate smalldatetime)

insert @steps
select 10, 1, '2006-12-24' union all
select 10, 2, '2007-1-3' union all
select 10, 3, '2007-2-5' union all
select 20, 1, '2006-12-26' union all
select 20, 2, '2007-1-7' union all
select 20, 3, '2007-1-9' union all
select 30, 1, '2007-1-14' union all
select 40, 1, '2007-1-23' union all
select 40, 2, '2007-3-2' union all
select 40, 3, '2007-4-16'

--- the main idea (with the wrong results)

select *
from @caseinfo c
left join @steps s on s.caseid = c.caseid and s.stepdate between c.startdate and c.enddate

--- expected result

declare @expresult table (caseidexp int, startdateexp smalldatetime, enddateexp smalldatetime, stepnumberexp int, stepdateexp smalldatetime)

insert @expresult
select 10, '2006-12-23', '2006-12-27', 1, '2006-12-24' union all
select 20, '2006-12-23', NULL, 1, '2006-12-26' union all
select 20, '2006-12-23', NULL, 2, '2007-1-7' union all
select 20, '2006-12-23', NULL, 3, '2007-1-9' union all
select 30, '2006-12-23', NULL, 1, '2007-1-14' union all
select 40, '2007-1-15', '2007-3-4', 1, '2007-1-23' union all
select 40, '2007-1-15', '2007-3-4', 2, '2007-3-2'

select *
from @expresult

View 1 Replies View Related

Date Field Cannot Be Null!

May 30, 2006

Visual Basic 2005 Professional Edition:

I have an SQL database table that includes a BirthDate field. I would like to have this field as optional when adding a record, but, SQL insists on throwing an exception if the field is null.

View 6 Replies View Related

How To Insert Null Into Date Field?

Dec 11, 2007

hi all!I have a task, for example, to create a record for bill. I have table which represents this bill entity (Bill_ID, Amount,  CreationDate, ExposureDate, PaymentDate)In table definition date fields allow null. I would like to create bill, which means insert record:  (new_bill_id, 1000, 2007.12.11, null, null) But it couses exception. Smth like: System.Data.SqlTypes.SqlTypeException, date should be not null.  How could I do it?Please advice!    

View 5 Replies View Related

Check For Null In Repeater Date Field

Jan 27, 2008

I'm about ready to pull my hair out.  I have a repeater control, and a date field.  If the field is a valid date, I'll use it to calculate and display the person's age.  Otherwise, I want to display "N/A".  My problem is trying to determine if the date field is null or not.I'm using SQL Server 2005 which allows Nulls in the date field, and for many records I don't have a birth date.  It makes sense for these records to leave the date Null.  This is my code:Protected Sub Repeater1_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles Repeater1.ItemDataBound    If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then        Dim LabelIcon As Label = CType(e.Item.FindControl("LabelIcon"), Label)        Dim LblAge As Label = CType(e.Item.FindControl("LblAge"), Label)        Dim inmate As WAP.prisonmembersRow = CType(CType(e.Item.DataItem, System.Data.DataRowView).Row, WAP.prisonmembersRow)        If System.IO.File.Exists(Server.MapPath("~/images/picts/" & inmate.FILE2 & ".jpg")) Then            LabelIcon.Visible = True        End If        If inmate.DATE_OF_BIRTH Is DBNull.Value Then            If IsDate(inmate.DATE_OF_BIRTH) Then                LblAge.Text = "Age: N/A"            Else                LblAge.Text = "Age: " & GetBirthdate(inmate.DATE_OF_BIRTH)            End If        End If    End IfEnd Sub How can I resolve this?Diane 

View 7 Replies View Related

Setting A Date/time Field To Null

Mar 2, 2005


I am using a Ms-Access DS which is accessed by a website's server-side scripts.

What I would like to do is set an existing record's date/time field to null. I have tried to simply alter its value by not including any data within the sharps (##), however that did not work.

How can I accomplish this?

Thank you!


View 2 Replies View Related

How To Check Null Or '' In Date Field In Expression And ...............

Aug 16, 2006

I have a column say 'ActivationDate' which is a (database timestamp [DT_DBTIMESTAMP]) which I want to replace with an expression in derived columns

The condition is if 'ActivationDate' field is null or '' then 'Null' else 'ActivationDate'

I am struggling to write this condition. Without condition i.e. at present it saves the value in this database '1753-01-01 00:00:00.000'.
In the preview the 'ActivationDate'field does not show any thing so I recon it is either null or ''

View 6 Replies View Related

Adding Column Where Adding Year To Date In Date Format

Aug 6, 2013

What is the syntax for adding a column where you are adding a year to a date in a date format? For example adding a column displaying a year after the participation date in date format?

View 1 Replies View Related

Updating Database Date Field Results In Date Value Of 01/01/1900

Jun 18, 2007

Brand new to this, so please bear with me.I'm using the following code fragment to update a datetime field on a SQL Server 2005 database table:cmd.CommandText = "Update Projects Set EntryDate = " & Convert.ToDateTime(txtEntryDate.Text)cmd.ExecuteNonQuery()The result of the update operation is the the database field contains the value "1900-01-01 00:00:00:000".  This probably means that I passed nulls to SQL; however, I see a valid date in the txtEntryDate field on my web form (i.e., "06/18/2007").  I also did a "Response.write" to display the txtEntryDate and it looks Okay.Can someone tell me what I'm doing wrong?Thanks!Using Visual Web Developer 2005 Express.

View 1 Replies View Related

Searching A Database Field For Null Value

Feb 27, 2008

I am building a website in C# for a university project, and would like to search a table (Member) for a field (UserName) using a session variable Session["sUserName"]. If that field is null, then I would like to insert that session variable into the field to start to create a new user. However, I am getting errors saying that I am using invalid expression terms. My code is;
//Create the Command, passing in the SQL statement and the ConnectionString queryString = "SELECT UserName FROM Member WHERE (UserName = @myUsername); ";
SqlCommand cmd = new SqlCommand(queryString, sqlConn);cmd.Parameters.Add(new SqlParameter("@myUsername", Convert.ToString(Session["sUserName"])));
//If UserName is null, display confirmation, else display errorif (UserName == null) ;
{UserNameCheckLabel.Text = "Username okay";
String queryString = "INSERT INTO Member (UserName) VALUES(@myUsername); ";SqlCommand cmd = new SqlCommand(queryString, sqlConn); cmd.Parameters.Add(new SqlParameter("@myUsername", Convert.ToString(Session["sUserName"])));
{UserNameCheckLabel.Text = "That username is in use";
I have a feeling I should be checking the database for the UserName, but I'm not sure whether to put this in the SELECT statement part or as a method... I would be most grateful for any advice!
Many thanks,

View 7 Replies View Related

How To Insert Null Values Into A DataBase Field

May 9, 2005

I have a function that updates a table in sql server
If a field is left blank I want the database field set to Null. I use:sqlCmd.Parameter.Add("@somefield, someintvalue) to pass all my values to the database.
if someintvalue is null I want to set @somefield to dbnull
How can I do this? I get errors if I submit a null integer when there is a foreign key constraint. What is the best way to handle this? should I just make all parameters objects? so that I can set them to DBNull.Value?
Also on a side note, when you are populating fields from a datatable, is there a better way to set the values (i.e. of a textbox) than cheking for DBNull before assigning?

View 2 Replies View Related

ASP Returning Null Sized Field From MS SQL Database

Jul 20, 2005

We currently have an SQL db running on a web server.One of these fields is a large(ish) amount of text data – up to 400characters – and has been cast variously as varchar, nchar and texttype to overcome a problem. The problem appears to be in retrievingthe data via ASP. I understand that ASP can handle string data of thissize so I am okay there.When the records are retrieved from the db, the data string length =0.I know the data is there because I have written a Delphi data managerwhich interrogates the db and shows all records and their contents.So if ASP can handle strings this size and the data is there, why do Iget a data length of zero bytes returned when I interrogate the recordset?Whichever way I cast this field I get the same result.I know the code is sound as it works locally through a MS SQL serveron my PS.Anyone have this problem or know what's causing it? I have logged asupport call with my hosting company, but they haven't replied as yetand I am stuck on an urgent project.Any suggestions?CheersGrant

View 1 Replies View Related

How To Pass Null Value To The Database If A Form Field Is Empty?

May 31, 2004

How can I pass into the database (@User_fax = null) if the fax form field is empty, from a command type Stored Procedure? For example:

Dim CmdUpdate As New SqlCommand("Form2_NewUser", strConnection)
CmdUpdate.CommandType = CommandType.StoredProcedure

CmdUpdate.Parameters.Add("@User_fax", SqlDbType.char, 9)
CmdUpdate.Parameters("@User_fax").Value = fax.Text()


And, the stored procedure inside Sql server:

USE market1
ALTER PROC Form2_NewUser
@User_id bigint, @User_fax char(9),...


SET User_fax = @User_fax, ...

WHERE User_id = @User_id

Thank you,

View 4 Replies View Related

Passing Null Date Value To Database

Oct 31, 2004

how can i pass null value to database? date is not required field in my database. i can pass default date but i think default date is not good in my case as it is DOB of a customer.

View 19 Replies View Related

Adding Null And Non-null Columns Together ----??

Nov 27, 2000

I have two columns A (which allows nulls) and B( which does not allow nulls).
How can I add the contents of columns A and B SO THAT I DO NOT GET A NULL RESULT WHEN A IS NULL.

The result of A+B concatanation will be stored in a column, C.

Appreciate your help

View 10 Replies View Related

What Default Date To Use In Database Datetime Field

Aug 4, 2006

We want to add a default date to our database tables. Looking at other database samples people use all sorts of dates to add as default date e.g. 1/1/1997 or the getdate() function.
Is it good practice to set a default date and what should the default date be????

View 2 Replies View Related

Adding A New Field To A Table With Image Field

Aug 24, 2006

I have a concern about adding a new field to a table with image field - which is huge.
Will there be a problem with some databases, where they have a hard time locating data correctly after such a large field?
Previously this happened to me, and what was advised to put all the big fields at the end of the table.

View 1 Replies View Related

Adding In A Whole Dataset Into A Table - Instead Of Field By Field

Feb 29, 2008

does anyone know if there is a way, or perhaps a custom toolbox control that is already developed that allows you to drag an entire dataset into a table, instead of pulling everything in field by field...?

was just curious.


View 3 Replies View Related

How Shoud I Assign Database Field To Get Date And Time Automatically

Feb 11, 2006

I would like to get more information about using detail view together with auto date/time.
I am designing user input form as follow.
tid      : uid     : tool    : priority: reticle: status: remarks: request time :   <- to autogenetrate current timeInsert Cancel
How to use date/time function inside detailed view, insert template.

View 2 Replies View Related

Adding Values With Null

Jul 8, 2002


I want to perform the following select statement

select (sum(ColA) + sum(ColB) + sum(ColC)) / 3
from tableA
Where ....

I want to add the sum of the columns and then divide it by a constant. The problem I am having is there are a lot of nulls in the database that I am working on (i.e. one of the values in the ColB is Null, which makes the result of the entire expession null).

How do I get around this. I need to add the values of the columns?

Any help would be appreciated.



View 3 Replies View Related

Adding A Column With Not Null

Sep 4, 2007

Hi All,
I've one table named tableAB. in that i've added one new column with not null option in the enterprise manager. then i've generated the script, and run the script in client database. because already data is there, it is not accepting to put null value in the new column. so the is missing. anyway backup is there with me.

what is the solution for this....

thanks in advance

View 10 Replies View Related

How To Add Date Field And Time Field (not Datetime Field )

May 4, 2006

Good morning...

I begin with SQL, I would like to add a field that will be date like 21/01/2000.

Actually i find just "datetime" format but give me the format 21/01/2000 01:01:20.

How to do for having date and time in two different field.

Sorry for my english....


A newbie

View 3 Replies View Related

Problem - Adding 2 Columns And Getting A NULL Result

Oct 25, 2006

In a query in SQL server 2005 I have a column LastName + ', ' + FirstName.All works fine except when a LastName or FirstName = NULL then the result is NULL.How do I get around this?ThanksA Newbie to SQL.  

View 1 Replies View Related

Adding Column With Null Values Between Two Columns

Mar 22, 2012

If i have a table with Col1,Col2,Col4, and Col5, how can I create and add a Col3 with null values? The format would be varchar.

View 13 Replies View Related

Adding A Not Null Column To Replicated Table

Apr 23, 2007


I'm merge replicating a SQL Server 2005 database (publisher) to SQL Compact databases (subscribers) on mobile devices. I understood that I could add a "not null" column to a replicated table on the server as long as I specified a default value, but it seems this is not possible. I ran the following script on the server database:

ALTER TABLE Activity ADD ActivityRequiresProject bit not null default(0)

which executed OK. When I went to synchronize the db on the mobile device I got the following error:

Alter table only allows columns to be added which can contain null values. The column cannot be added to the table because it does not allow null values.
The SQL statement failed to execute. If this occurred while using merge replication, this is an internal error. If this occurred while using RDA, then the SQL statement is invalid either on the PULL statement or on the SubmitSQL statement. [ SQL statement = alter table "Activity" add "ActivityRequiresProject" bit not NULL constraint "DF__Activity__Activi__4A47DDAE" default ( ( 0 ) ) ]

Does anyone know if this is a valid error? Is is possible to add a not null column with default, and if not how do I update the schema on a replicated database?



View 12 Replies View Related

How To Convert Datetime Field To A Date Field So Excel Recognize It As Data Type

May 17, 2015

I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE" .

How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?

I tried:

**all of the above returned text objectes in excel and not date objects.

View 3 Replies View Related

Problem Adding NOT NULL Column To Replicated Table

Jan 15, 2003

I'm new to replication and am trying to determine the best approach to add a column (NOT NULL with no DEFAULT) to a replicated table. The only success I have had is if I do the following:

Delete entire Subscription
Delete entire Publication
Add column to table
Create new Publication
Create new Subscription
Run SnapShot

The problem with this approach is that each step affects the entire database and not just the modified table. I think it is inefficient to redo replication for a simple object change. What am I missing? Is there a way to only replicate the changes made to the one table without having to run a SnapShot for the entire publication?
Keep in mind the column must be defined as NOT NULL and cannot have a Default.

Thanks, Dave

View 8 Replies View Related

Informix Date Type Field To SQL Server Datetime Field Error

Oct 17, 2007

I am trying to drag data from Informix to Sql Server. When I kick off the package
using an OLE DB Source and a SQL Server Destination, I get DT_DBDATE to DT_DBTIMESTAMP
errors on two fields from Informix which are date data timestamp part

I tried a couple of things:

Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed.

Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them
as datetime but it failed.

What options do I have that will work?

View 1 Replies View Related

Adding A Field In SQL

Apr 18, 2006

I need to add a field to a query which i have created in SQL Server, i have done this in Access no problem and it works fine but when icopy the SQL code from Access to SQL it just falls over. This is my code in Access...


Any suggestions would be much appreciated.


View 2 Replies View Related

Create Date Field From Substring Of Text Field

Jul 20, 2005

I am trying to populate a field in a SQL table based on the valuesreturned from using substring on a text field.Example:Field Name = RecNumField Value = 024071023The 7th and 8th character of this number is the year. I am able toget those digits by saying substring(recnum,7,2) and I get '02'. Nowwhat I need to do is determine if this is >= 50 then concatenate a'19' to the front of it or if it is less that '50' concatenate a '20'.This particular example should return '2002'. Then I want to take theresult of this and populate a field called TaxYear.Any help would be greatly apprecaietd.Mark

View 2 Replies View Related

Adding A New Field To A View!!!

Aug 30, 2000

I am trying to add a field on a view which should be query-able.
Please what is the best way to go about it. HELP PLEASE!!!

View 1 Replies View Related

Copyrights 2005-15, All rights reserved