Hi,
I want to save date into SQL server pls help me how to save this date into server. its give me syntax error that converting datetime to character string. and also I want to save date only in mm/dd/yy format in a sql not including time.
The following code I am using
sub SaveEvent(sender as object, e as EventArgs)
Dim dt As DateTime = DateTime.Parse(eventdate.Text)
lbl.text=dt
dim con as new SQLConnection("server=london-home; Database=tony; uid=rashid2; pwd=test; ")
dim cmd as new SQLCommand("insert into events values('" & dt & "','" & desc.text & "')",con)
con.open()
cmd.executenonquery()
con.close()
end sub
Going thru your code, I am not sure what you are trying to do here. You have dt as a DateTime object. You cannot assign an object to lbl.Text, like in lbl.Text = dt. Now to the date problem in SQL Server. What you are probably trying to do is insert a DateTime value in a char type field in SQL Server table, and thus the reason for error. You have two choices, you either use a DateTime field in SQL Server or you don't. If you don't, you could try using a varchar type, but I am not sure, but if you use DateTime type, there is no way to trim the Time part in the value saved, though you can trim it while displaying it, either in SQL or your front end.
P.S.: Using other types to store datetime in SQL Server is not a good idea.
|||
Actually I get the date in textbox using <asp:TextBox> in this format(mm/dd/yy) and this textbox value I want to save in a SQL database, so regarding this how to save in the SQL.
|||
and i have also a datetime field in a sql database
|||
Which line is it giving you error again? Also, can you please post the exact error message?
|||I got this error and also below the whole code I am using.....
Server Error in '/' Application.
Syntax error converting datetime from character string.
Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.Data.SqlClient.SqlException: Syntax error converting datetime from character string.
The following code is below:
<script runat="server">
sub SaveEvent(sender as object, e as EventArgs)
dim con as new SQLConnection("server=london-home; Database=tony; uid=rashid2; pwd=test; ")
dim cmd as new SQLCommand("insert into events values('" & eventdate.text & "','" & desc.text & "')",con)
con.open()
cmd.executenonquery()
con.close()
end sub
</script>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<style type="text/css">
#display_prod{float:left;}
#prod{margin-top:0;}
</style>
</head>
<body>
<table border="1" align="center" cellpadding="5" cellspacing="0">
<tr>
<td valign="top">
<!--#include file = "header.aspx" -->
<!--#include file = "scroll.aspx" -->
<form id="prod" runat="server" enctype="multipart/form-data">
<table id="display_prod" width="554" border="1" cellspacing="0" cellpadding="0" align="left" height="400">
<tr valign="top">
<td>
<table border="1" align="center" cellpadding="20" cellspacing="0">
<tr>
<td>PRODUCTS DESCRIPTION <asp:Label runat="server" ID="lbl"></asp:Label></td>
</tr>
</table>
<table align="left" id="menu" width="150" border="1" cellpadding="0" cellspacing="0">
<tr align="center">
<td><a href="http://links.10026.com/?link=home.aspx">Main</a></td>
</tr>
<tr align="center">
<td><a href="http://links.10026.com/?link=Events.aspx">Change Password</a></td>
</tr>
<tr align="center">
<td><a href="http://links.10026.com/?link=Comments.aspx">Events</a></td>
</tr>
</table>
<table width="390" border="1" align="center" cellpadding="0" cellspacing="0">
<tr>
<td width="100" >Enter Date :</td>
<td width="243" ><asp:TextBox runat="server" ID="eventdate" BackColor="#FFFFFF" Width="100"></asp:TextBox> mm/dd/yy</td>
</tr>
<tr>
<td>Description :</td>
<td><asp:TextBox runat="server" ID="desc" TextMode="MultiLine" Columns="20" Rows="3" BackColor="#FFFFFF" MaxLength="200"></asp:TextBox> max 200</td>
</tr>
<tr>
<td> </td>
<td>
<asp:Button runat="server" ID="submit" Text="Save" OnClick="SaveEvent"></asp:Button>
<asp:Button runat="server" ID="reset" Text="Reset"></asp:Button>
</td>
</tr>
</table>
</td>
</tr>
</table>
</form>
|||
I am assuming your eventdate.Text is supposed to have date in mm//dd/yy format and you are trying to insert that value in a datetime field in SQL Server table. If this is the case, use DateTime.Parse(eventdate.Text).
|||
Its again give me this error
Syntax error converting datetime from character string.
using this code:
dim cmd as new SQLCommand("insert into events values('" & DateTime.Parse(eventdate.text) & "','" & desc.text & "')",con)
|||
Here are few things I would like to know:
1. What exactly did you try to insert? Post the exact value that you typed in your textbox.
2. What is the structure of your table? What columns are there, in which order and their datatypes?
|||
The value of textbox is "02/11/07"
and below is a query which I use for table.
create table events
(
event_id integer primary key identity(1,1),
event_name varchar(200),
event_date datetime,
)
|||
Heres the first problem. The order in which you are inserting your values is different from the column order. Either name the columns like this INSERT INTO TABLE(Column1, Column2) VALUES(VALUE1, VALUE2) or use the values in order. From your table schema, the date part should be at the end.
|||
Thanks a lot I have solved my problem....
Cheers!
|||
one thing more that when I run this grid its give me show the this type of format 02/11/2007 00:00:00 and I want to display only dd/mm/yy can you pls tell me that how I can change the format?
<asp:DataGrid runat="server" ID="ViewEvents" AutoGenerateColumns="false">
<columns>
<asp:TemplateColumn>
<itemtemplate>
<table id="tt" border="1" cellpadding="5" cellspacing="0">
<tr>
<td>hello hwo r u<asp:Label runat="server" ID="eventname" Text='<%#container.DataItem("event_name")%>' ></asp:Label></td>
<td><asp:Label runat="server" ID="pdesc" Text='<%#container.DataItem("event_date")%>' ></asp:Label></td>
</tr>
</table>
</itemtemplate>
</asp:TemplateColumn>
</columns>
</asp:DataGrid>
|||
See if this helps, change the date format accordingly.
http://peterkellner.net/2006/05/24/how-to-set-a-date-format-in-gridview-using-aspnet-20using-htmlencode-property/
I would also recommend marking the posts as answers if they solved your problem.
