Posted: 6/25/2011
When I am using the Command and Parameter Editor for an INSERT query can I add a datetime stamp using the command and parameter editor? What would be my parameter source. Then where would I specify datetime.now() or similar, or is this something you can only do through code.
I tried inserting values from my forms along with the datetime for a time stamp, but it didn't work. Then I used the Command and Parameter Editor for my insert query and was able to insert my text box data to my table. Along with that I tried just inserting the date time with my code behind, but I cannot get that to work. That is why I thought there might be a way to do it through the Command and Parameter Editor. Here is the code I was trying to use to add the datetime in my code behind:
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data.SqlClient; using System.Configuration; using System.Data; namespace Testbed { public partial class About : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void Button1_Click(object sender, EventArgs e) { SqlDataSource ds = new SqlDataSource(); ds.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringtestData"].ToString(); ds.InsertCommandType = SqlDataSourceCommandType.Text; ds.InsertCommand = "INSERT INTO TestTable (DateTimestamp) VALUES (@DateTimeStamp)"; ds.InsertParameters.Add("DateTimeStamp", DateTime.Now.ToString()); ExampleTest.Insert(); } } }
Hi,Below are the steps which I did to insert the data successfully in Database:SQL:
USE [Test] GO /****** Object: Table [dbo].[TestTable] Script Date: 06/25/2011 12:30:05 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[TestTable]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](50) NULL, [Date] [datetime] NULL ) ON [PRIMARY] GO
<html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="HtmlForm" runat="server"> <div> <table> <tr> <td> Name: </td> <td> <asp:TextBox ID="NameTextBox" runat="server"></asp:TextBox> </td> </tr> <tr> <td> </td> <td> <asp:Button ID="SubmitButton" runat="server" Text="Insert" onclick="SubmitButton_Click" /> </td> </tr> <tr> <td> </td> <td> <asp:Label ID="MyLabel" runat="server"></asp:Label> </td> </tr> </table> <asp:SqlDataSource runat="server" ID="MySqlDataSource" ConnectionString="Data Source=RAGHAV-PC;Initial Catalog=Test;Persist Security Info=True;User ID=test;Password=test" InsertCommand="INSERT INTO TestTable([Name],[Date]) VALUES (@Name,@Date)" ProviderName="System.Data.SqlClient"> <InsertParameters> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="Date" Type="DateTime" /> </InsertParameters> </asp:SqlDataSource> </div> </form> </body> </html>
protected void SubmitButton_Click(object sender, EventArgs e) { MySqlDataSource.InsertParameters["Name"].DefaultValue = NameTextBox.Text; MySqlDataSource.InsertParameters["Date"].DefaultValue = DateTime.Now.ToString(); MySqlDataSource.Insert(); MyLabel.Text = "Successfully inserted."; }
Posted: 6/26/2011
Thank you! that answer was super thorough!
You can refer this link too http://www.codeasp.net/articles/asp-net/249/sqldatasource-insert-update-and-delete-data
