How to use Convert date statement in cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date

Hi

I am using SQL 2005, VB 2005

I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works fine

string insertSQL;

insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)";

cmdInsert SqlCommand;

cmdInsert=new SqlCommand(insertSQL,conn);

cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text;

My query is how to detail with dates my previous code was

insertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";

I tried the code below but the record doesn't save?

string date = DateTime.Now.ToString("dd/MM/yy");

insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; 

cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();

cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;

cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;

cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;

cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;

Could someone point to me in the right direction?

Thanks in advance

0
yazzy
9/21/2006 7:17:44 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

3 Replies
1552 Views

Similar Articles

[PageSpeed] 36

yazzy:

string insertSQLstring date = DateTime.Now.ToString("dd/MM/yy");

insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)";

cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();

cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;

cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;

cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;

cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;

 

 

Why don't you just directly pass DateTime.Now to the @Date parameter (SqlDbType.DateTime)?


Welcome to my SQL/ASPNET forum for Chinese
http://51up.org/bbs/forumdisplay.php?fid=38
0
Iori_Jay
9/22/2006 3:06:29 AM

Sorry,

I am having a hard time trying to figure out what it is that you are trying to do.  Convert(datetime,@Date,3) does NOTHING.  @Date is already a datetime, and the third parameter isn't valid for datetime to datetime ?conversions?.

I think you are trying to store a date into a datetime column?  Or is the date field a varchar?

If it's a datetime, then change:

insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; 

to:

insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, @Date,@IDStaff)";

and change:

cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;

to:

cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = Now.Date

*OR* change your insert to:

insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, DATEADD(d,0,DATEDIFF(d,0,getdate())),@IDStaff)";

and remove the Date parameter completely (Which is the method I prefer, since I use the SQL Server's clock exclusively).

0
Motley
9/22/2006 4:38:48 PM

Hi Motley

I am trying to store a date into a datetime column. The thrid parameter is to store the date in 'dd/MM/yy' formate (i.e UK date)

Because I have to store date in the above formate, it is causing when I try to insert/update a record.

Sorry I had not included this information.

Speak soon

0
yazzy
9/25/2006 10:44:48 AM
Reply:

Similar Artilces:

Update a date within SQL gives me an error "Can't convert String to Date"
Good afternoon/Morning I am hoping someone can help me with a problem I am having. I am trying to update a date value but I get the convert error. I have tried the following code but with no joy. Can anyone shed some light on what I need to do to enable the converting of a string into Date?private void UpdateSingleItem(string StoredProcedure) { //Connect to stored procedure //Create a DataAdapter, and then provide the name of the stored procedure.SqlDataAdapter MyDataAdapter = new SqlDataAdapter(StoredProcedure, MyConnection); //Set the command type as StoredProcedure.MyData...

Dates, Dates, Dates!
Hello all... I am in the process of building an appointment system, and I am trying to see if I can get some pointers on which way to go on this. The first version of this appointment system was very lo-tech, and it stored all of the possible dates/times in the database. Once a user booked an appointment, their information was logged to that date/time, and then that date/time would no longer show in the list of possible openings. This was great for a temporary fix, but with having to pre-populate all of the dates/times, the system would eventually have to be updated to continue on pa...

Converting Excel "general" date into DateTime(Sql)
Hi,  I have an odd request.... I am importing data from an excel spreadsheet to a sql db. Inside the excel spreadsheet, I have a column with dates in it, I need these dates to be imported as text to the sql DB field(which is a varchar datatype).   The problem is, at the moment, if I insert a date 2006/08/30 it displays it like this in the sql DB: 38959 Is there a way this can be converted back to the date? even possibly before it is stored in the sql DB ? I am currently bulk copying the excel data to sql DB. The code im using is below: Thank you for any help/assistance Dim e...

HELP!! Need to change "Created" date to "Date" for messages
I'm exporting email for a legal request from an account, only for a certain time period and after certain messages have been redacted as they are "closed records" (I'm a local government installation). I'm using the Transend Migrator Forsenic product to export messages from the user's account to a "ereview" account which then I delete the messages not in the time frame requested and then my legal people go though that account and remove "closed records". When everything is ready I then use the Transend product to export the final me...

how do you use the boolean table for "Last Changed Date", with relative dates?
How does one do a query in the boolean table for "Last Changed Date" using relative dates? In particular, I'm looking for a list of open critical bugs that have had no activity in the past 4 hours. I had thought I could do "last changed date" --> "is less than" --> "4h". Best I can tell, I don't think it's possible (with version 2.20rc1). Right now, I've hacked the Search.pm script to make the above work for me. ...

Display Today's date in my "Current Date" parameter field
Hi  I have created a parameter called "Current Date". The type is DateTime.How do I get today's date to show as default in my "Current Date" parameter textbox? In the "Report Parameters" area, I entered =Today for the Non-Queried. I saved the report, did a build and Todays date displayed correctly. When I clicked the "Preview" button, the report automatically ran because theCurrent Date field was completed. However, is their a way to not have the reportrun automatically. In c# u will use System.Datetime.Now for current date in SQL u use...

Updating database date field results in date value of "01/01/1900"
Brand new to this, so please bear with me.I'm using the following code fragment to update a datetime field on a SQL Server 2005 database table:cmd.CommandText = "Update Projects Set EntryDate = " & Convert.ToDateTime(txtEntryDate.Text)cmd.ExecuteNonQuery()The result of the update operation is the the database field contains the value "1900-01-01 00:00:00:000".  This probably means that I passed nulls to SQL; however, I see a valid date in the txtEntryDate field on my web form (i.e., "06/18/2007").  I also did a "Response.write" t...

"Request Date" in "MyRequests" Shows Wrong DateTime
Hi, The "MyRequests" link in the user application, displays the request information like resource, recipient, state of activity, requested by, recipient, request date and comments. Can anyone tell me from where does the user application takes the value for "Request Date:"...? From where does the "Request Date" for the workflow takes its value. Does it from anyone of the below mentioned components...? 1) MySql's Table consisting of Workflow information. 2) The server time where the user application is installed. 3) The server time where the eD...

Changing date format in "Date" column
Thunderbird version 1.5.0.9 (20070117) on PC LinuxOS. I have set the System date format to UK (ie dd/mm/yyyy) but can't seem to find out how to change the "Date" column format in TBird to that setting. Can anyone point me to the answer? thanks Gordon wrote: > Thunderbird version 1.5.0.9 (20070117) on PC LinuxOS. I have set the > System date format to UK (ie dd/mm/yyyy) but can't seem to find out how > to change the "Date" column format in TBird to that setting. > > Can anyone point me to the answer? > > thanks this is f...

need a "Date Modified: <date>" control for vb aspx.net 3.5
Sounds simple!  I just want a thing in my footer that says, "Date Modified: <date>" where <date> is the actual date the file was modified. I posted this question in the beginner's area but the suggested solutions do not work. I tried adding a Label control on my page, thus: <asp:Label ID="Label1" runat="server"></asp:Label>then in my masterpage.master.vb I have: Partial Class MasterPage    Inherits System.Web.UI.MasterPage    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System...

Date for Sybase SQL Anywhere 5.5 is "not compatible" with the data type date for Oracle 7.3?
Hello! We have a problem with PowerBuilder dates treatment with different Database type. The data type date for Sybase SQL Anywhere 5.5 is "not compatible" with the data type date for Oracle 7.3. We create a DataWindow for Sybase table that include date fields. This DataWindow executed with Oracle return date error (we recuperate ??/??/??). If we create this DataWindow for Oracle, the date fields are automatically assigned such as DateTime and this object not run in Sybase (remember the different data access: GetItemDate and GetItemDateTime). It's possible t...

How can I return a valid date format when I use a date in "dd-mmm-yyyy" format with US location
When I have Regional setting Location = English (Australia) and Input language = English (Australia) and short date fomat = dd/mmm/yyyy the statement date("01-Jan-2004") returns 1-Jan-2004. When I have Regional setting Location = English (US) and Input language = English (US) and short date fomat = d/m/yyyy the statement date("01-Jan-2004") returns 1/1/1900. How can I get PB8 to return 1/1/2004 when I run the statement date("01-Jan-2004") with US Windows regional settings? Instead of date("29-Jan-2004"), you need to use date(2004, 1, 29). ...

Date Issue
Has anyone run across this problem? I don't remember seeing it prior to 1.0.10: Any date field, i.e. the Expire Date field in the portal settings reverts to the format "8-Oct-2003" and generates the warning "Invalid expiry date!". For example if I go to view the settings for a particular portal, the expire date is in the 'DD-MMM-YYYY' format and won't let me update the settings until I either type a MM-DD-YYYY date in or select one from the calendar. This happens in the module settings as well when there is a date involved. Is this some regional setting I have to...

Date of every "day" between date x and y
Hi everyone, Is there a function to work out the every date of a particular day between two dates? For example:The date of every monday between 01/01/2006 to the 01/12/2006.Would prefer and example if c# is pos, but either is fine.Thank you! DateTime dt = DateTime.ParseExact("01/01/2006", "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture);ArrayList a = new ArrayList(); while ( dt.ToString("MM/dd/yyyy") != "01/12/2006" ){ if ( dt.DayOfWeek == DayOfWeek.Monday )  a.Add(dt);  dt = dt.AddDays(1);} this.Response.Write("<b>Mondays between 01/01/200...

Web resources about - How to use Convert date statement in cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date - asp.net.sql-datasource

Resources last updated: 12/22/2015 7:26:57 AM