Seeking Correct Syntax To Pass A NULL Value For A Datetime Field From An Asp Page To SQL

Jan 13, 2006


I've been searching the web for quite some time to resolve the problem
of "1/1/1900" returning in a datetime field in SQL that resulted from a
blank (not NULL) value being passed to it through an ASP page.

The solution is that a NULL value needs to passed to SQL from ASP.
Thats fine...I understand the why the problem is happening and the
solution around it. HOWEVER, I can't seem to get the proper syntax to
work in the ASP page. It seems no matter what I try the "1/1/1900"
still results. Below are a few variations of the code that I have
tried, with the key part being the first section. Does anyone have any
cDateClosed = ""
If(Request.Form("dateClosed")= "") Then
cDateClosed = (NULL)
end if

sql="UPDATE rfa SET "&_
"dateClosed='"& cDateClosed &"', "&_
"where rfaId='"& Request.Form("RFAID")&"'"

cDateClosed = ""
If(Request.Form("dateClosed") <> "") Then
cDateClosed = (NULL)
end if

sql="UPDATE rfa SET "&_
"dateClosed='"& cDateClosed &"', "&_
"where rfaId='"& Request.Form("RFAID")&"'"

cDateClosed = ""
If(Request.Form("dateClosed")= "") Then
cDateClosed = NULL
end if

sql="UPDATE rfa SET "&_
"dateClosed='"& cDateClosed &"', "&_
"where rfaId='"& Request.Form("RFAID")&"'"
Thanks in advance!!!!

View 7 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 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

Inserting NULL Into Datetime Field

Aug 17, 2007

I have a datetime field in a database which I am programmatically inserting values into. The field can be null or not null. However, I am having problems inserting NULL as part of my SQLCommand.
The value is pulled from a text box (linked to a calendar extender) and when I select a value it is being inserted fine. If I just leave the box blank though, I want the field to be set to NULL. I have tried adding in ,DBNULL.Value, as part of my VALUES(…) string, but this throws an exception. I Have tried just inserting ‘’ but that also throws an exception (“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value?), so I don’t know how I can insert this value when the field is blank? 
Can anyone shed some light please? Thanks

View 2 Replies View Related

Cant Insert NULL To DateTime Field

Jun 5, 2006

Hi I'm using DetailView and I have a text box which show the date. I have formated the date as shortDate {0:d} format. I want to insert/update null if that text box is empty.
I have tried putting null value in my Update statement in sqlDataSource. And I'm getting error saying can't convert varchar to smalldatetime.
If I don't set null value as above, some large date (01/01/2033) has been inserted in my database.
Can anyone help me?

View 5 Replies View Related

Null Datetime Field Contains 1-Jan-1900

Feb 10, 1999

I have defined fields in a table as datetime fields and allow nulls. SQL 6.5 is placing 1-Jan-1900 in all of these fields causing me to have to filter out these dates when retrieving a recordset.
How do I get rid of this?


View 1 Replies View Related

Sql SELECT Returning NULL On DateTime Field

Aug 8, 2006

Hi there ;
This Problem is goin to make me crazy!
I've got a table with couple of fields, 2 of them are DateTime, in Sql Express 05 .
I used 2.0 to insert datetime values, directly,  using sth like DateTime.Now.ToString() .
i use 2 selects as follows :
1)select * from X where  Date1='8/9/2006 11:17:41 AM'
2)select * from X where  Date2='8/9/2006 11:17:41 AM'
#1 is OK, but the second one returns a row with all columns set to NULL .
(X is the name of the table)
Thanks in advance

View 6 Replies View Related

How To Replace DateTime Field With Null Value In SQL 2005 Server

Apr 7, 2006

How to replace DateTime field with null value in SQL 2005 server
I create a stored procedure aa, It works well, but sometimes I hope to replace CreateDate field with null value,I don't know how to doIt seems that datetime type is not null value
create aa  @CreateDate DatetimeAsUpdate cw set CreateDate=@CreateDate

View 3 Replies View Related

Mssql Won't Find NULL Values In Datetime Field??

Feb 13, 2007


I have a really simple query which i can't figure out why its not working. I have a table called 'ADMIN' which has a datetime field called 'date_edited'. Because the majority of records have never been edited, i have allowed null values and they are filled with 'NULL' in each record. How ever, when i try:


I get no records, but i can see and know i have hundreds! I know i'm doing somthing really stupid, but for life of me can't figure it out! :eek:


View 10 Replies View Related

Conditional Execution With Null Checking For DateTime Field

Mar 11, 2008

There was a similar question to mine a few time ago about conditional execution, but I'm having some problems with it to which I couldn't find any answer, could anyone help?

My condition is checking if a DateTime from a DB table is null. I use the Execute SQL Task to retrieve the record (SELECT TOP 1 * FROM myTable WHERE conditions = TRUE), then I map 2 fields to their respective global variables.

Then I created the green arrow connector and used a precedence expression based on sucess. I used the expression IsNull(@variable). For some reason, even if the @variable comes as null - I checked the info in the DB - it still comes out as '1999-11-30'

I've looked around and the IsNull method can be used to also change the value in the event of it being null, could it be changing my variables value to this even if I didn't pass that value as a parameter to the function? If so, how else can I test it for null? I've tried "== null", "= null" and even ".equals(null)" - which I know is absurd, but I'm running out of options here...

Could you enlighten me, please???
Thanks in advance!

View 4 Replies View Related

DateTime.Now, An Allowed Null Field, And My Insert Function From My Dataset

Jun 6, 2008

I have a table adapter for one of my SQL2005 tables, and in two different fields I accept a date time. Now 99% of the times, new rows to this table will be filled out using DateTIme.Now(), as a Time Stamp is what I'm going for.
Here is the line of code in question...cops_current_data_adapter.Insert(ProductOrder, Convert.ToInt16(Session["StationId"].ToString()),
PartNumber, DateTime.Now, DateTime.Now, Convert.ToInt16(qty), 0);
 The second DateTime.Now is the one that can be null, and it's throwing a formatting error everytime I try and drop it in there. It's a FormatException, and there's not much more to the example except unhelpful tips like be careful when conveting a string to a dateTime, which I'm not doing. Needless to say for the code to compile, and then throw a Format error at runtime is a bit frustraiting.
 Any suggestions would be most appreciated

View 1 Replies View Related

Incorrect Syntax Near The Keyword CONVERT When The Syntax Is Correct - Why?

May 20, 2008

Why does the following call to a stored procedure get me this error:

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'CONVERT'.

Code Snippet

EXECUTE OpenInvoiceItemSP_RAM CONVERT(DATETIME,'01-01-2008'), CONVERT(DATETIME,'04/30/2008') , 1,'81350'

The stored procedure accepts two datetime parameters, followed by an INT and a varchar(10) in that order.

I can't find anything wrong in the syntax for CONVERT or any nearby items.

Help me please. Thank you.

View 7 Replies View Related

I Need To Pass Data Entered /created On The First Page To The Next Page And Populate The Next Page With Some Data From The Fir

Nov 28, 2006

Hello I have a project that uses a large number of MS Data access pages created in Access 2003 and runs on MS SQL2005.

When I am on lets say my client, (first page in a series) data access page and I have completed the fields in the (DAP), I am directing my users to the next step of the registration process by means of a hyperlink to another Data access page in the same web but in a linked or sometimes different table.

I need to pass data entered /created on the first page to the next page and populate the next page with some data from the first page / table. (like staying on the client name and ID when i go to the next page)

I also need the first data access page to open and display a blank or new record. Not an existing record. I will also be looking to creata a drop down box as a record selector.

Any pointers in the right direction would be appreciated.
I am some what new to data access pages so a walk through would be nice but anything you got is welcome. Thanks Peter

View 2 Replies View Related

Correct Syntax

Sep 25, 2006

HelloI have having trouble displaying some simple columns in ascending order.I know that the database is populated and I can get the more complex code to work if I display like this: SELECT FName, LName, Town, '<a href="' + url + '">' + Site + '</a>' as LinkFROM Names_DBWHERE FName = 'Tom' And url like 'http:%'ORDER BY LName ASCBut I need a simpler view but I can't get it to workI  have tried this:SELECT FName, LNameFROM Names_DBORDER BY  LName ASCAnd thisSELECT FName, LNameFROM Names_DBORDER BY  LName ASC; And This:SELECT FName, LNameFROM Names_DBORDER BY  LName 'ASC' What is wrong with this syntax?ThanksLynn

View 2 Replies View Related

What Is The Correct Syntax For Anything

Mar 15, 2007

I have a query and I need to check to see if a field is occupied, i.e., it can have anything in it, i just want to see if something is there... this is what I want, but of course, anything isn't the right word here...

and ( = 'anything')

View 7 Replies View Related

Correct Syntax

Feb 25, 2008


This is a Stored proc I am working on
I dont know much about stored procs.
This is what I am trying to achieve
The proc takes two input parameter and returns one outparameter.

This is what I have


CREATE PROCEDURE [dbo].[GetNextAction]

@Code char(10),
@Track varchar(30),
@NextAction ntext output

Declare @ID int;
@ID = Select Sequence from [dbo].[Track] where Code=@Code;
Declare @nextCode varchar;
@nextCode= Select Code from [dbo].[Track] where sequence =(@ID+1);
@NextAction= Select nextAction from [dbo].[CaseStage] where Code=@nextCode;
return @NextAction;


I guess you can understand what I am trying in the proc but the rules and syntax are wrong. Please help me with this.

View 4 Replies View Related

Please Correct Syntax

Dec 3, 2006

hi there

RS2005 - Layout tab

I have a field that says

txtTrackerTrue=Sum(Iif ((Fields!Tracker.Value = Fields!RecvdBy.Value), 1,0 ))

I want another field that says

txtAllTrue=Sum(Iif ((Fields!Tracker.Value, ClosedBy.Value = Fields!RecvdBy, 1,0 ))

obviously this is going to error

how would I do this please


View 19 Replies View Related

What Is Correct Syntax

Oct 19, 2007

select CONVERT(VARCHAR, stdate, 'YYYY') startyear
from tbl_emp

i want output : 2007

How can i get?

View 5 Replies View Related

Correct InsertParameters Syntax

Jan 29, 2008

If someone can show me the correct syntax for the right side of the code below, I'd appreciate it.
srcMoreInfo.InsertParameters("Address").DefaultValue =       DirectCast(cuwMoreInfo.FindControl("txtAddress"), TextBox).Text 

View 11 Replies View Related

Is Syntax Is Correct ? URGENT

Jun 21, 2000

@spname nvarchar(20),
@tblname nvarchar(50)

declare @sqlrun varchar(8000)

set @sqlrun = "CREATE PROCEDURE @sp_name AS CREATE TABLE @tblname
(MsdsId [nvarchar] (5),
IngredId [nvarchar] (2),
Agency [nvarchar] (10),
AgencyText [nvarchar] (4) ,
nName [nvarchar] (40))"

eXEC sp_ExecuteSql @sqlrun

PS: I'm getting this error:

Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 7
Cannot convert parameter '@statement' to ntext/nchar/nvarchar data type expected by procedure.

View 2 Replies View Related

SYNTAX Help, Correct Way To Use LIKE In Statement

Feb 27, 2007

Hi, I'm new to SQL and was wondering if there was an easier way to filter data.

I have two tables -

The first table called Names of Companies has a column named: NAMES

XYZ Company
ABC Limited Liability Company
ZZZ Corporation
KKK Inc.
ABC Inc.

The second table called Keywords has a column named: WORDS


I want to search for all NAMES that contain the WORDS in some form

The results should be:

XYZ Company
ABC Limited Liability Company

Technically, I can get the results I want by manually typing into the SQL statement all the words that appear in the WORDS column.

FROM [Names of Companies]
WHERE [Names of Companies].Names Like "*Company*" Or ([Names of Companies].Names) Like "*limited*"));

But is there a way that I reference the table Keyword instead of typing into the query statement all the words that appear in the column WORD? I have a lot of words to search for.

Can anyone recommend a better way to do this?

Thanks for all your help!

View 8 Replies View Related

Correct Syntax For Dtsrun

May 18, 2006

Somehow I'm not getting dtsrun to work.

The server is local

The name of the package is ImportHosttrncs. It is saved under Local Packages.

There is no password.

Is this syntax correct?

dtsrun /Slocalhost /PImportHosttrncs

View 1 Replies View Related

Correct Syntax For IF EXISTS

Apr 11, 2008

What is the correct syntax for dropping a column if it exists?

Cant seem to get it to work. This is SQL 2005 if it makes a difference.

ALTER TABLE Reports_PI_Recent


View 3 Replies View Related

Correct Syntax For Parameters When Creating SQL Job

Dec 30, 2007

I'm trying to create a SQL job in SQL Server and am a little unclear about the formatting.Here's a snippet from the stored procedure that creates the job:CREATE PROCEDURE [dbo].[spArchive]     @DB            varchar(30),    @Date        DateTimeAS EXEC msdb.dbo.sp_add_jobstep @job_name = 'ArchiveIncentives'     , @step_id = 1    , @step_name = 'ArchiveAHD'    , @subsystem = 'TSQL'    , @command = 'spArchiveAHD ''@Date'''    , @on_success_action = 3     , @on_fail_action = 2     , @database_name = '@DB'     , @retry_attempts = 1   In this case, the job will be calling this stored procedure:CREATE PROCEDURE [dbo].[spArchiveAHD] (    @dtArchiveBefore DateTime)AS I'm unclear about these lines:    @command = 'spArchiveAHD ''@Date'''    @database_name = '@DB'   Do they look correct to you or should I drop some/all of the apostrophes?Robert W. 

View 5 Replies View Related

Is There Any Correct Syntax For ParameterDirection.Input ?

Apr 3, 2008

 I try to call the storeproc to perform task by allowing only input 6 of length. The syntax over here might not be relevant, im seeking for correct syntax.
Dim connString2 As String = _ConfigurationManager.ConnectionStrings("Local_LAConnectionString1").ConnectionString Using myConnection2 As New SqlConnection(connString2)
Dim test01 As IntegerDim myPuzzleCmd2 As New SqlCommand("GetRandomCode", myConnection2)
myPuzzleCmd2.CommandType = CommandType.StoredProcedure
Dim retLengthParam As New SqlParameter("@Length", SqlDbType.TinyInt, , 6)                 < --------   allow 6 letters in length to be input   
retLengthParam.Direction = ParameterDirection.InputmyPuzzleCmd2.Parameters.Add(retLengthParam) Dim retRandomCode As New SqlParameter("@RandomCode", SqlDbType.VarChar, 30)
retRandomCode.Direction = ParameterDirection.Output
TryDim reader2 As SqlDataReader = myPuzzleCmd2.ExecuteReader()
Catch ex As Exception Response.Write("sp value : " & retRandomCode.Value)
Dim iRandomCode(1) As StringReDim Preserve iRandomCode(1)
iRandomCode(1) = Convert.ToString(retRandomCode.Value)myPuzzleCmd2 = Nothing
Session.Remove("RandomCode") HttpContext.Current.Session("RandomCode") = iRandomCode(1) Response.Write(Session("RandomCode"))
End Try
End Using
StoredProcedure ALTER PROC [dbo].[GetRandomCode]
@Length TINYINT,
@RandomCode VARCHAR(30) OUTPUT
@Chars2BUsed VARCHAR(30),
@iCnt INT,
@CharFound CHAR(1)
SELECT @RandomCode = ''SELECT @iCnt = 1 WHILE @iCnt<=@Length
BEGINSELECT @LookAt = FLOOR((RAND()*LEN(@Chars2BUsed))) + 1
SELECT @CharFound = SUBSTRING(@Chars2BUsed, @LookAt, 1)IF CHARINDEX(@CharFound, @RandomCode)=0
BEGINSELECT @RandomCode = @RandomCode + @CharFound
SELECT @Chars2BUsed = REPLACE(@Chars2BUsed, @CharFound, '')SELECT @iCnt = @iCnt + 1

View 2 Replies View Related

Correct Syntax For This Select In SQL Server?

Jun 22, 2007

This (demo) statement is fine in Access, and so far as I can see, shouldbe OK in SQL Server.But Enterprise Manager barfs at the final bracket. Can anyone helpplease?select sum(field1) as sum1, sum(field2) as sum2 from(SELECT * from test where id < 3unionSELECT * from test where id 2)In fact, I can reduce it to :-select * from(SELECT * from test)with the same effect - clearly I just need telling :-)cheers,Jim--Jima Yorkshire polymoth

View 4 Replies View Related

Can You Please Correct My Syntax For If Statement In Stored Proc

Nov 23, 2004

I am using @strsql to construct the where condition for a select query.
Can you please correct my syntax.

DECLARE @strsql nvarchar(2000)

IF @ProgNO <> '' then
strsql = WHERE ProgNO = @ProgNO
end if

If @ProjNO <> '' then
if strsql <> '' then
strsql = strsql & " and ProjNO =@ProjNO
strsql = wHERE ProjNO =@ProjNO

Thank you very much.

View 9 Replies View Related

Correct Syntax For An Update Stored Procedure

Nov 24, 2005

This is probably a very simple question but i would appreciate some helpwith the correct syntax for and update stored procedureI  have created  user form that allows the user to update the name and address fields in a datatable called  customers based on the input value customer ID =  ( datatable/Customers)customerIDI have got this far and then got lost:Create SP_UpdateCustomer(@customerID, @name, @address)As Update customers ( name, address)Where customerID =  @customerID GOCould anyone tell me what the correct sntax should be.many thanksMartin

View 3 Replies View Related

Correct Syntax For Recordset's Select Method.

Feb 17, 2006

I have a interger stored in x.

I want to use x in a SELECT statement like so :

SELECT * from aTable WHERE A_Column = x

This select statement is then assigned to and passed as a string like :

sql = "SELECT * from aTable WHERE A_Column = x"

How does the x get interpreted correctly ?

View 1 Replies View Related

Correct Syntax Using Variables Inside SQL Queries (SqlCommand)

Mar 30, 2008

I have a test page where I'm using SqlConnection and SqlCommand to update a simple database table (decrease a number).
I'm trying to figure out how to make a number in the database table to decrease by 1 each time a button is being pressed. I know how to update the number by whatever I want to, but I have no idea what the correct syntax is for putting variables inside the query etc. Like "count -1" for instance.
The database table is called "friday" and the one and only column is called "Tickets".
Here's the code behind the button:protected void Button1_Click(object sender, EventArgs e)
{SqlConnection conn;
conn = new SqlConnection("Data Source=***;Initial Catalog=***;Persist Security Info=True;User ID=***;Password=***");
conn.Open();int count = -1;
 SqlCommand cmd = new SqlCommand("select Tickets from friday", conn);
count = (int)cmd.ExecuteScalar();if (count > 0)
 string updateString = @"
update friday
set Tickets = 500" ;   <------ Here I want to set Tickets like "current count -1"SqlCommand cmd2 = new SqlCommand(updateString);
cmd2.Connection = conn;

View 3 Replies View Related

Correct Syntax For Query Using Osql.exe In MSDE Setup

Nov 10, 2003

I have the following .bat file that I call to register users to my MSDE instance...

echo osql -E -S %COMPUTERNAME%OfficiumInstance -Q "sp_grantlogin '" + %COMPUTERNAME% + "ASPNET'"
echo osql -E -S %COMPUTERNAME%OfficiumInstance -d Officium -Q "sp_grantdbaccess '" + %COMPUTERNAME% + "ASPNET'"
echo osql -E -S %COMPUTERNAME%OfficiumInstance -d Officium -Q "sp_addrolemember 'db_owner', '" + %COMPUTERNAME% + "ASPNET'"

My question is how do I correctly append %COMPUTERNAME% as a string to

I have tried using + signs...

"sp_grantlogin '" + %COMPUTERNAME% + "ASPNET'"

But that doesn't work.
I have looked for .bat file tutorials on the web but can't find the correct way to do this.


View 1 Replies View Related

Integration Services :: Correct Syntax To Access A Variable

Sep 30, 2015

I am having trouble finding the correct syntax to access a variable. I have a variable defined in the Variables window: The variable name is formatedDate. The DataType is String.

I am successfully setting the value of the variable in a Execute SQL Task. The SQL is as follows:

SELECT LEFT(CONVERT(VARCHAR, MAX(ReportDate), 120), 10) as formatedDate
from DimReportDates

The Result Set is set to “Single Row” and properly set up. 

No problem so far. I can see with a watch that the variable has the correct value, something like:


Now, in a subsequent step, a Data Flow Task, I want to access the variable. Actualy it is in the SQL statement of a OLE DB source in the Data Flow… I have the following:

Declare @sDate smalldatetime
SELECT @sDate = xxxxx

I have tried several things substituting xxxxx above, but nothing seems to work. One variation was


 Another was

((DT_WSTR, 10) @[User::formatedDate]).

I think I’m close, but just can’t get it. What is the correct syntax.

View 4 Replies View Related

How Can I Print A Field That Is In The Dataset On Each Page And Show The Table Hearder On Each Page?

Apr 16, 2007

How can I print a field that is in the dataset on each page? I added a textbox in the Page Header and use =Fields!ProjectName.value in the value property. I got an error "Fields cannot used in page header and footer."

How can I have the table header shows on each page? Currently if the data goes to the second page, there is no table header.



View 8 Replies View Related

Copyrights 2005-15, All rights reserved