By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,480 Members | 1,084 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,480 IT Pros & Developers. It's quick & easy.

Insert date 1900-01-01

P: n/a
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)
sqlcomm1.ExecuteNonQuery()

Query is working fine if there is value in the txtOutDate, but if user
leaves the field txtOutdate empty, system insert date 1900-01-01. I want
system to insert null.

How can I achieve that?

Regards
Arek


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 18 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
in c#
txtOutdate.Text == string.empty ? null : txtOutdate.Text

i'm sorry i am not expert in visualbasic.

but i suppose you can make an if
if txtOutdate.Text == string.empty then
str = null
else
str = txtOutdate.Text

I think you should also transform your string in datetime format recognized
by sql.
"Arek" wrote:
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)
sqlcomm1.ExecuteNonQuery()

Query is working fine if there is value in the txtOutDate, but if user
leaves the field txtOutdate empty, system insert date 1900-01-01. I want
system to insert null.

How can I achieve that?

Regards
Arek


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 18 '05 #2

P: n/a
Hi Arek,

It isn't clear whether your data is supposed to be a datetime or a quoted
string. If it is a string and you want to insert a Null if the text is empty,
you can try testing with the function code below. For a datetime, you'll need
a different value. You can try hardcoding the values at first until you see
what type is acceptable.

Let us know how you make out?

Ken

<%@ Page Language="VB" %>
<script runat="server">

Sub Button1_Click(sender As Object, e As EventArgs)
label1.text="INSERT INTO tblTasks (idTask, outdate) VALUES ('" &
IDTask.text & "'," & CheckNull(txtOutdate.Text) & ")"
End Sub

Function CheckNull(strInVal as object) as string
if isNothing(strInVal) then
return "Null"
end if

if strInval="" then
return "Null"
end if

' Everything is fine
return " '" & strInval & "' "
end function

</script>
<html>
<head>
</head>
<body>
<form runat="server">
<p>
</p>
<p>
<asp:TextBox id="IDTask" runat="server">MyValue</asp:TextBox>
</p>
<p>
<asp:TextBox id="txtOutdate" runat="server">The value goes
here</asp:TextBox>
</p>
<p>
<asp:Button id="Button1" onclick="Button1_Click" runat="server"
Text="Button"></asp:Button>
</p>
<p>
<asp:Label id="Label1" runat="server">Label</asp:Label>
</p>
<!-- Insert content here -->
</form>
</body>
</html>
"Arek" wrote:
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)
sqlcomm1.ExecuteNonQuery()

Query is working fine if there is value in the txtOutDate, but if user
leaves the field txtOutdate empty, system insert date 1900-01-01. I want
system to insert null.

How can I achieve that?

Regards
Arek


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 18 '05 #3

P: n/a
I am not a not an expert in C#, but shouldn't that be DBNull.Value?

Regardless, it is a lot cleaner using parameters...

Dim sqlcomm1 As New SqlCommand("INSERT INTO tblTasks (idTask, outdate)
VALUES (@ID, @OutDate)", conn)

sqlcomm1.Parameters.Add("@ID", SqlDbType.Int).Value = CInt(IDTask.text)

If Not txtOutdate.Text = String.Empty Then
sqlcomm1.Parameters.Add("@OutDate", SqlDbType.DateTime).Value =
CDate(txtOutdate)
Else
sqlcomm1.Parameters.Add("@OutDate", SqlDbType.DateTime).Value =
DBNull.Value
End If

sqlcomm1.ExecuteNonQuery()

HTH,
Greg

"Psycho" <Ps****@discussions.microsoft.com> wrote in message
news:BE**********************************@microsof t.com...
in c#
txtOutdate.Text == string.empty ? null : txtOutdate.Text

i'm sorry i am not expert in visualbasic.

but i suppose you can make an if
if txtOutdate.Text == string.empty then
str = null
else
str = txtOutdate.Text

I think you should also transform your string in datetime format
recognized
by sql.
"Arek" wrote:
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)
sqlcomm1.ExecuteNonQuery()

Query is working fine if there is value in the txtOutDate, but if user
leaves the field txtOutdate empty, system insert date 1900-01-01. I want
system to insert null.

How can I achieve that?

Regards
Arek


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 18 '05 #4

P: n/a
should have been...
sqlcomm1.Parameters.Add("@OutDate", SqlDbType.DateTime).Value =
CDate(txtOutdate.Text)

"Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message
news:Oi**************@TK2MSFTNGP10.phx.gbl...I am not a not an expert in C#, but shouldn't that be DBNull.Value?

Regardless, it is a lot cleaner using parameters...

Dim sqlcomm1 As New SqlCommand("INSERT INTO tblTasks (idTask, outdate)
VALUES (@ID, @OutDate)", conn)

sqlcomm1.Parameters.Add("@ID", SqlDbType.Int).Value = CInt(IDTask.text)

If Not txtOutdate.Text = String.Empty Then
sqlcomm1.Parameters.Add("@OutDate", SqlDbType.DateTime).Value =
CDate(txtOutdate)
Else
sqlcomm1.Parameters.Add("@OutDate", SqlDbType.DateTime).Value =
DBNull.Value
End If

sqlcomm1.ExecuteNonQuery()

HTH,
Greg

"Psycho" <Ps****@discussions.microsoft.com> wrote in message
news:BE**********************************@microsof t.com...
in c#
txtOutdate.Text == string.empty ? null : txtOutdate.Text

i'm sorry i am not expert in visualbasic.

but i suppose you can make an if
if txtOutdate.Text == string.empty then
str = null
else
str = txtOutdate.Text

I think you should also transform your string in datetime format
recognized
by sql.
"Arek" wrote:
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)
sqlcomm1.ExecuteNonQuery()

Query is working fine if there is value in the txtOutDate, but if user
leaves the field txtOutdate empty, system insert date 1900-01-01. I want
system to insert null.

How can I achieve that?

Regards
Arek


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


Nov 18 '05 #5

P: n/a
I was thinking about using parameters and converting my query to stored
procedure. The thing is that this is just part of the query and query
itself is big. So I was trying to avoid this.
Anyway, my outdate is in datetime format.
I will try to use proposed here solutions and I will post whatever
worked for me...:)

Regards
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 18 '05 #6

P: n/a
Ok,

I finally use query with parameters and then check
If Not TxtOutDate.Text = "" Then
sqlcomm1.Parameters.Add("@TxtOutDate",
SqlDbType.SmallDateTime).Value = TxtOutDate.Text
Else
sqlcomm1.Parameters.Add("@TxtOutDate",
SqlDbType.SmallDateTime).Value = DBNull.Value
End If

This is working for me.

When I tried to use CDate or Cint, or if not txtOutdate.text =
String.Empty, system was giving me errors.

Thank you for your help
Arek

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 18 '05 #7

P: n/a
Ok,

I finally use query with parameters and then check
If Not TxtOutDate.Text = "" Then
sqlcomm1.Parameters.Add("@TxtOutDate",
SqlDbType.SmallDateTime).Value = TxtOutDate.Text
Else
sqlcomm1.Parameters.Add("@TxtOutDate",
SqlDbType.SmallDateTime).Value = DBNull.Value
End If

This is working for me.

When I tried to use CDate or Cint, or if not txtOutdate.text =
String.Empty, system was giving me errors.

Thank you for your help
Arek

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 18 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.