Insert date 1900-01-01

A

Arek

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
 
G

Guest

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.
 
G

Guest

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

Greg Burns

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
 
A

Arek

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
 
A

Arek

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
 
A

Arek

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top