I have the following code:
************************************************** *************************
Dim CommandText as String = "INSERT INTO ftsolutions.dbo.position
(client,dateposted) VALUES ( @client,@dateposted)"
Dim objCmd as New SqlCommand(CommandText,objConn)
with objCmd.Parameters
.Add("@client",SqlDbType.Char,50).value = "the companies next test"
.Add("@dateposted",SqlDbType.datetime).value = dateposted.text
end with
objCmd.ExecuteNonQuery
************************************************** **************************
My problem is that if the dateposted.text is blank, then I get an error
saying date is invalid. If I take out the dateposted line all together (as
well as in the insert statement), it uses todays date - which is what I want
as I have getdate() as the default.
How do I use a parameter and allow it to be null so it takes the default?
Thanks,
Tom. 5 2138
You probably can use DBNull.Value if you want to specify "Null".
--
Girish Bharadwaj http://msmvps.com/gbvb
"tshad" <ts**********@ftsolutions.com> wrote in message
news:OB**************@TK2MSFTNGP11.phx.gbl... I have the following code:
************************************************** ************************* Dim CommandText as String = "INSERT INTO ftsolutions.dbo.position (client,dateposted) VALUES ( @client,@dateposted)"
Dim objCmd as New SqlCommand(CommandText,objConn)
with objCmd.Parameters .Add("@client",SqlDbType.Char,50).value = "the companies next test" .Add("@dateposted",SqlDbType.datetime).value = dateposted.text end with
objCmd.ExecuteNonQuery
************************************************** ************************** My problem is that if the dateposted.text is blank, then I get an error saying date is invalid. If I take out the dateposted line all together
(as well as in the insert statement), it uses todays date - which is what I
want as I have getdate() as the default.
How do I use a parameter and allow it to be null so it takes the default?
Thanks,
Tom.
"Girish Bharadwaj" <gi*****@mvps.org> wrote in message
news:O9**************@TK2MSFTNGP14.phx.gbl... You probably can use DBNull.Value if you want to specify "Null".
But how would I handle that with the Parameters statement?
I want to allow the user to put a date in, but if he doesn't I want to use
getdate(), which is the default on the field.
Tom. -- Girish Bharadwaj http://msmvps.com/gbvb "tshad" <ts**********@ftsolutions.com> wrote in message news:OB**************@TK2MSFTNGP11.phx.gbl... I have the following code: ************************************************** ************************* Dim CommandText as String = "INSERT INTO ftsolutions.dbo.position (client,dateposted) VALUES ( @client,@dateposted)"
Dim objCmd as New SqlCommand(CommandText,objConn)
with objCmd.Parameters .Add("@client",SqlDbType.Char,50).value = "the companies next test" .Add("@dateposted",SqlDbType.datetime).value = dateposted.text end with
objCmd.ExecuteNonQuery ************************************************** ************************** My problem is that if the dateposted.text is blank, then I get an error saying date is invalid. If I take out the dateposted line all together
(as well as in the insert statement), it uses todays date - which is what I want as I have getdate() as the default.
How do I use a parameter and allow it to be null so it takes the default?
Thanks,
Tom.
with objCmd.Parameters
.Add("@client",SqlDbType.Char,50).value = "the companies next test"
if dateposted.text <> "" then
.Add("@dateposted",SqlDbType.datetime).value = dateposted.text
else
.Add("@dateposted",SqlDbType.datetime).value = getdate()
End if
end with
"tshad" wrote: "Girish Bharadwaj" <gi*****@mvps.org> wrote in message news:O9**************@TK2MSFTNGP14.phx.gbl... You probably can use DBNull.Value if you want to specify "Null".
But how would I handle that with the Parameters statement?
I want to allow the user to put a date in, but if he doesn't I want to use getdate(), which is the default on the field.
Tom.
-- Girish Bharadwaj http://msmvps.com/gbvb "tshad" <ts**********@ftsolutions.com> wrote in message news:OB**************@TK2MSFTNGP11.phx.gbl... I have the following code: ************************************************** ************************* Dim CommandText as String = "INSERT INTO ftsolutions.dbo.position (client,dateposted) VALUES ( @client,@dateposted)"
Dim objCmd as New SqlCommand(CommandText,objConn)
with objCmd.Parameters .Add("@client",SqlDbType.Char,50).value = "the companies next test" .Add("@dateposted",SqlDbType.datetime).value = dateposted.text end with
objCmd.ExecuteNonQuery ************************************************** ************************** My problem is that if the dateposted.text is blank, then I get an error saying date is invalid. If I take out the dateposted line all together
(as well as in the insert statement), it uses todays date - which is what I want as I have getdate() as the default.
How do I use a parameter and allow it to be null so it takes the default?
Thanks,
Tom.
"vinay" <vi***@discussions.microsoft.com> wrote in message
news:E3**********************************@microsof t.com... with objCmd.Parameters .Add("@client",SqlDbType.Char,50).value = "the companies next test" if dateposted.text <> "" then .Add("@dateposted",SqlDbType.datetime).value = dateposted.text else .Add("@dateposted",SqlDbType.datetime).value = getdate() End if end with
That will work fine. But I was curious why I get an error that says "String
was not recognized as a valid DateTime." if the value is blank. I would
have thought the program would have seen the type as SqlDbType.datetime and
the field as blank and set it to Null, which would have allowed Sql Server
to use the default value (getdate()).
Thanks,
Tom
"tshad" wrote:
"Girish Bharadwaj" <gi*****@mvps.org> wrote in message news:O9**************@TK2MSFTNGP14.phx.gbl... > You probably can use DBNull.Value if you want to specify "Null".
But how would I handle that with the Parameters statement?
I want to allow the user to put a date in, but if he doesn't I want to use getdate(), which is the default on the field.
Tom.
> > -- > Girish Bharadwaj > http://msmvps.com/gbvb > "tshad" <ts**********@ftsolutions.com> wrote in message > news:OB**************@TK2MSFTNGP11.phx.gbl... >> I have the following code: >> > ************************************************** ************************* >> Dim CommandText as String = "INSERT INTO ftsolutions.dbo.position >> (client,dateposted) VALUES ( @client,@dateposted)" >> >> Dim objCmd as New SqlCommand(CommandText,objConn) >> >> with objCmd.Parameters >> .Add("@client",SqlDbType.Char,50).value = "the companies next test" >> .Add("@dateposted",SqlDbType.datetime).value = dateposted.text >> end with >> >> objCmd.ExecuteNonQuery >> > ************************************************** ************************** >> >> My problem is that if the dateposted.text is blank, then I get an >> error >> saying date is invalid. If I take out the dateposted line all >> together > (as >> well as in the insert statement), it uses todays date - which is what >> I > want >> as I have getdate() as the default. >> >> How do I use a parameter and allow it to be null so it takes the >> default? >> >> Thanks, >> >> Tom. >> >> > >
An empty string is not the same as a NULL value in SQL and SQLServer won't
automatically convert from an empty string to NULL, nor will it be able to
parse an empty string into a DateTime value. NULL is a special value that can
be stored in a DateTime column as long as the database schema specifies that
the column is NULLable. It sounds like your DateTime column may not be
NULLable, but you have a default value specified to set the value to
GetDate() when a NULL value is specified.
By the way, you could check for an empty string in the stored procedure
instead of in your VB code, then any code that uses the SProc could avoid
having the conditional statement. Also, if you're going to allow an empty
date field, I'd recommend going the extra distance and stripping out any
whitespace before checking for an empty string. However, I think the best
way, if the default value in the database is today's date, would be to just
fill in the text box with today's date and put a RequiredFieldValidator and a
RegularExpressionValidator on the text box. Then you don't need the check in
your code any more.
"tshad" wrote: "vinay" <vi***@discussions.microsoft.com> wrote in message news:E3**********************************@microsof t.com... with objCmd.Parameters .Add("@client",SqlDbType.Char,50).value = "the companies next test" if dateposted.text <> "" then .Add("@dateposted",SqlDbType.datetime).value = dateposted.text else .Add("@dateposted",SqlDbType.datetime).value = getdate() End if end with
That will work fine. But I was curious why I get an error that says "String was not recognized as a valid DateTime." if the value is blank. I would have thought the program would have seen the type as SqlDbType.datetime and the field as blank and set it to Null, which would have allowed Sql Server to use the default value (getdate()).
Thanks,
Tom
"tshad" wrote:
"Girish Bharadwaj" <gi*****@mvps.org> wrote in message news:O9**************@TK2MSFTNGP14.phx.gbl... > You probably can use DBNull.Value if you want to specify "Null".
But how would I handle that with the Parameters statement?
I want to allow the user to put a date in, but if he doesn't I want to use getdate(), which is the default on the field.
Tom.
> > -- > Girish Bharadwaj > http://msmvps.com/gbvb > "tshad" <ts**********@ftsolutions.com> wrote in message > news:OB**************@TK2MSFTNGP11.phx.gbl... >> I have the following code: >> > ************************************************** ************************* >> Dim CommandText as String = "INSERT INTO ftsolutions.dbo.position >> (client,dateposted) VALUES ( @client,@dateposted)" >> >> Dim objCmd as New SqlCommand(CommandText,objConn) >> >> with objCmd.Parameters >> .Add("@client",SqlDbType.Char,50).value = "the companies next test" >> .Add("@dateposted",SqlDbType.datetime).value = dateposted.text >> end with >> >> objCmd.ExecuteNonQuery >> > ************************************************** ************************** >> >> My problem is that if the dateposted.text is blank, then I get an >> error >> saying date is invalid. If I take out the dateposted line all >> together > (as >> well as in the insert statement), it uses todays date - which is what >> I > want >> as I have getdate() as the default. >> >> How do I use a parameter and allow it to be null so it takes the >> default? >> >> Thanks, >> >> Tom. >> >> > >
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Leonardo Almeida |
last post by:
Hi,
I have a table with the follow fields :
ID - Int
Date - Datetime
I need to make a simple query to result the records between to dates
with a single ID.
|
by: Michael Albanese |
last post by:
I am building an application to report on-the-job injuries
and incidents. There are a lot of Date fields, some of
which are optional and can be left blank by the user. I
have allowed Nulls on...
|
by: peashoe |
last post by:
I have an asp page that uses a calendar.js (pop-up) file to add an
exact date format in the text field (txtDDate). My problem is I need
some javascript that sets an alert that does not allow them...
|
by: Vern |
last post by:
I'm using the Microsoft Enterprise Data Access block to call the stored
procedure. One of the values the stored procedure returns is the stop date.
When the stop date is null, the program crashes....
|
by: Arek |
last post by:
Hey,
I am inserting values in the table:
Dim sqlcomm1 As SqlCommand = New SqlCommand("INSERT INTO tblTasks
(idTask, outdate) VALUES ('" & IDTask.text & "','" & txtOutdate.Text &
"')", conn)...
|
by: darrel |
last post by:
I have the following right now to enter a date into SQL getting the data
from some pull down menus:
-------------------------------------------------
dim dateCCJApprovedDate as DateTime
if...
|
by: Øyvind Isaksen |
last post by:
I have a page with an optional integer-field, and one asp:calendar control.
I use a stored procedure to save the data in SQL Server.
When all fields contains data, the code works great! But if the...
|
by: Stevienashaa |
last post by:
Hello
I'm using Access 2003, and I have a query (written in SQL) which has
two parameters and asks the user for two dates. This has been working
fine. Today I modified the query, removing the...
|
by: jans78 |
last post by:
Appreciate if you all can help me to solve my Crystal Report problems
First, I create some parameters and one of the parameters is Date. I set the parameter for the date is String.
For example :...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |