Showing posts with label convert. Show all posts
Showing posts with label convert. Show all posts

Wednesday, March 7, 2012

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

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

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)?

|||

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)";

andchange:

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

|||

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

how to use convert data type from int to char in select statement

when i insert data from outside
i use insert into ....select...
can i use IDENTITY ( seed ,increment ) in my statement.
for example i have table
aaaaa AAA
bbbbb BBB
ccccc CCC

can i insert into new table with data

1 aaaaa AAA
2 bbbbb BBB
3 ccccc CCC
thanks

You can use cast (or convert)

create table test
(
integerValue int
)
insert into test
select 1
union all
select 232525
union all
select 2325
union all
select 525
union all
select 99232525

select integerValue, cast(integerValue as varchar(10)) as varcharValue
from test

integerValue varcharValue

1 1

232525 232525

2325 2325

525 525

99232525 99232525

|||

Well, first, don't change the meaning of a thread...I already answered it as it was originally asked Smile


create table test
(
value char(5),
otherValue char(3)
)
insert into test
select 'aaaaa','AAA'
union all
select 'bbbbb','BBB'
union all
select 'ccccc','CCC'

You can do this:


select identity(int,1,1) as test2Id,
value,
otherValue
into test2
from test

Or if you just wanted numbers, you can do this (in 2005) to get control over the values.


select row_number() over (order by value) as test3Id,
value,
otherValue
into test3
from test
go

|||sorry, i have change my question. can you answer me.