i found a different way to insert the date or rather not insert the date at all if a certain condition is true. But anyway i have come accross the same problem when i try to update the date field with a blank value. Code:
How do I tell my SQL Server database to insert a NULL value for an empty string. Like if I set a value to null in ASP like this: x = null then it inserts as empty in to the database, how do I get it to insert a reall null value?
I'm working on an element of my site in which users can choose an expiry date for features they create. However, i also want them to have the option to set the feature to be active indefenitely. Currently I am making them choose to add a date/time field to the record, or leave the value of the field as Null.
The problem I have here is that if they choose the keep the feature active indefenitely, I need to keep the date/time field a NULL. However, if they change thier minds and give it an expiry date, and then want to change it back, I have a problem as the field has been populated. If I try to enter a "" value into the date/time field, but when the user goes back to edit they get a 01/01/1900 date instead of N/A.
Trying to make it have a far future date also doesn't work, as when they go back to change they get the year as 01/01/3000 or some such. Basically, what I want to do is add a NULL value again to the date/time field. Can this be done?
I need to deposit and empty dummy value into a date/time field within Access using INSERT. However, I pick up a data type error if I attempt to insert a NULL value for this particular date time field....
What are my options here.....I need the date to come up empty.....what can I do?
I have a form that I submit to an oracle database. Along with the info from the form I want to insert the date it was submitted. How should I go about doing this? I am having problems because it is the wrong format?
I am using a 'date' data type for the field. How can I convert the date into the correct format that will allow me to submit it, I have tried using to_char or to_date, but they give me an error?I tried using this to convert it, but it doesn't work, I am getting a syntax error?submit_date=to_date(date,'YYYY-MM-DD')
i m reading data from a excel file and inserting its fields into access database, everything is fine but i m getting problem when i m insertin the data string into the date/time field of the aceess database,i have use the CDate function but it is not working.what should i do?
I am trying to insert a date into a date field through my website. The date is pre-populated with todays date. I am getting a type mismatch error. The Access DB is setup as a Date/Time Short Date field. Code:
i have a field which accepts the date as dd/mm/yyyy but when i insert the value it becomes mm/dd/yyyy for some date values like 9 april become 4 august.... the database date format is mm/dd/yyyy and i just take the value out and display it as dd/mm/yyyy.. however ...
my update and inserts can't use the same function, cos the function is just display out the server's date as dd/mm/yyyy... how should i add i in correctly? here's my insert and update function[img]images/smilies/bawling.gif[/img] PHP Code:
Dim ddate, ydate, mdateFunction FormatDateDM2MD(sdate) if sdate="" or sdate=" " thenFormatDateDM2MD = "NULL"elseddate=day(sdate)mdate=month(sdate)ydate=year(sdate)sdate= ydate & "/" & mdate & "/" & ddateFormatDateDM2MD = "'"&sdate&"'"end ifEnd Function
i have another problem my date values when its 10 of Nov it comes out as 11/10/2003 instead of 10/11/2003 why? how do i solve it? Code:
i have a field which accepts the date as dd/mm/yyyy but when i insert the value it becomes mm/dd/yyyy for some date values like 9 april become 4 august.... the database date format is mm/dd/yyyy and i just take the value out and display it as dd/mm/yyyy..
however ... my update and inserts can't use the same function, cos the function is just display out the server's date as dd/mm/yyyy... how should i add i in correctly? Code:
I'm trying to insert a date value into MSSQL, the type of the sql filed is: "smalldatetime" and i'm trying to insert a text Variable that looks like this: "19/02/2006".
.... SET update_date='" & Update_Date & "' ...
i get the message:
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value
I have an interesting situation that needs to be addressed ASAP.
I am running a standard SQL Query in ASP to retrieve some rows/columns from a table that's on a SQL Server 2000 database. I put the results of that query into a RecordSet, and then use the GetRows function to push the results that are in the recordset into an array variable. In the query that I am running, 39 columns and 2 rows are returned.
The GetRows function appears to be working - the array variable is being created with the proper dimensions. However, the values of the different "cells" of the array are not showing up correctly. The first and the last column values for each row are correct, yet everything else in between is blank, empty, NULL, or whatever.
In other words: ArrayVariable(0,0) shows up correctly, ArrayVariable(38,0) shows up correctly, but everything in between is null.
I did a test on the RecordSet and discovered that it was the culprit - it wasn't being filled in properly. This is strange, though, because the select query that I run in the ASP code to create the recordset returns perfect results when I run it on the SQL Server 2K database.
I am developing a simple booking system where I had a booking date. The problem is I want to make sure that the user must book 3 days in advanced for certain room.
I have tried to use DateDiff but it seems doesn't work. Actually i just 1 to make sure that the user must book 3 days in advanced from the current date. I am wondering whether there is anything goes wrong in my datediff function. Code:
I have created a table. How do I limit the result of that table to only show the entries that have the "address" filled in. In Access here is the sql coding to acheive the results.
SELECT Dropoffs.City, Dropoffs.StateInitials, Dropoffs.AcceptedItems, Dropoffs.MoreInfo, zipcodes.Address FROM zipcodes, Dropoffs WHERE (((zipcodes.Address) Is Not Null));
How do I set this up in my VBSCRIPT page? Here is my working page so that you can see what I am trying to acheive. When the page comes up you can type 45255 for the zipcode.
I had set up the following 'trouble-shooting' code to determine how a certain value was being 'evaluated' for lack of a better term. The field "status" in this example has no value in it.
<% status = RS("status") If isnull(status) Then response.write("status is null") end if if status = "" Then response.write("status has no value") end if %>
The database field 'status' has no value in it. If I run the above trouble-shooting code, the first statement "status is null" will print out. If I remove the first if statement and use the second if statement 'if status = "" ...', the statement will NOT print indicating that the value <> "".
Questions: What is the difference between something not having a value and NULL?
Most importantly, what is the PROPER way to check for a field not having a value in it? In the past I have always checked for a 'no value' using double quotes.
I'm using the following code to, firstly erase any old logins the user has. Then to check whether the page has been idle for longer than 30 minutes or not. If it has been idle for longer than 30 mins then the user will be required to input their password again. However it's not working! Code:
Microsoft OLE DB Provider for SQL Server (0x80040E2F) Cannot insert the value NULL into column 'ContactID', table 'ka0506a.ka0506a.contacts'; column does not allow nulls. INSERT fails. /submitcontact.asp, line 15
What happened is i used to used MS Access and ContactID is an Autonmuber field, and now my connection is set to MS SQL so it's giving me this error, what's causing this error to happen and how can i change it ?
I have a condition that will run depending if a field in the database is NULL. It will not run at all, and I tried various way to write it but none works so far
'not working If assignee = DBNull Then
'not working If assignee = null Then
'not working If assignee = "" Then
If I write assignee to the page then the value of assignee will write if it has a value and nothing if it is null.
I have a data entry form. If the user submits the form without entering any data, the record should be inserted with null entries for the respective fields. But when i execute the INSERT SQL query with these null values, it gives me an error. This is vat I am doing:
<% ..... address = request.form("address") if request.form("Age")= "" then ageValue="" ..... ..... %> I use these values in SQL query This is the SQL statement when the above is executed " INSERT INTO tblStatistics (Age,address) VALUES (, aaa)" Because of no value for age, the query is not gettin executed. I dont want a 0, I want a nul entry (<null>). Could someone throw some light on how to set the age field to null?
Can someone help with an error? It's a StoreFront store. right after the credit card screen is gives me the error. I get the following:
Microsoft VBScript runtime error '800a005e'
Invalid use of Null: 'replace' /ssl/SFLib/incGeneral.asp, line 85
-------------------------------------------- 84 Function makeInputSafe(str) 85 makeInputSafe=replace(str, "'", "''") 86 End Function -------------------------------------------
i had select the data from the database. The null value are returned. How can i convert the null value to integer so that i can add some value for it?? Actually i try to use both of this statement but there's nothing come out...when i try to selecte the data in the database, the null value are returned
if rs("max_id") = "NULL" then id_no = 1 response.write "id_no = " &id_no& "<br>" end if
and
if rs("max_id") = "" then id_no = 1 response.write "id_no = " &id_no& "<br>" end if
I have 2 questions about SQL server. First, what should i do about allowing NULL values when designing the database? What difference does it make? And if I disallow NULL values, should i enter default values?
Second, when adding new records to database with recordset.addnew, what SELECT statment, cursor, and lock type should i use?
I've always used "SELECT * FROM Table" and adOpenStatic, and adLockOptimistic. This SELECT statement looks very inefficient to me, but I don't know what else to use.
I am updating a table depending on the user input that could be left blank for the numeric as well as the string field. I am getting an error if I do the following:
Dim a = null
sql = "update country set number =" & a
If I do ... sql = "update country set number = null", it works fine.
How can I use the variable to change the value to null.
I have set up a database, myDB, in MS Access. one of my Table, myTable, has columns Col1, Col2, ..., Col10. Depending on some scenarios, I insert some values in some (but not all) of the columns. Now I want to delete those records that their col3 is null or nothing is set.
I used col3= '' and col3=NULL in the below statement objRS.Open "SELECT * FROM myTable WHERE col3 = '' ", myDB, , , adCmdText
but none of them works.How to delete a record based on Null value filtering?