none
INSERT A DATETIME IN DATABase

    Question

  • hii
    i have a problem,i used to insert date time in the table directly and it works then i don't know what happen and now it doesn't work
    when i try to insert by filling in the table the values it display this message

    The data in row 1 was not committed
    Error source:.Net Sql Client Data Provider
    Error message:cannot convert data type nvarchar to uniqueidentifier
    the statement has been terminated




    and i try to insert by query i write like this

    insert

     

    into bindings values('nablus',1,'6/6/2009 10:00:00 AM','6/6/2009 00:30:00 AM')
    and it give me this error

    Msg 8114, Level 16, State 5, Procedure sp_update_job, Line 0

    Error converting data type nvarchar to uniqueidentifier.

    The statement has been terminated.


    what shull i do?
    what is the write way to insert datetime?

    Tuesday, June 30, 2009 10:13 AM

Answers

All replies

  • oki now i know what is the problem but i don't know why

    the problem is that i do trigger for delete and trigger for inserty for this table

    when i drop them the insert querey work and the insert by filling the datetime work

    why this happen?
    the triggers like this


    stop trigger:

    create trigger stop
    on bindings
    for delete
    as
    begin
    declare @c int
    select @c=count(*) from bindings
    if(@c<=0)
    EXEC msdb.dbo.sp_update_job @job_id=N'planning_second_job',@enabled=0


    end



    start trigger:


    create trigger start
    on bindings
    for insert
    as
    begin
    declare @c int
    select @c=count(*) from bindings
    if(@c=1)
    EXEC msdb.dbo.sp_update_job @job_id=N'planning_second_job',@enabled = 1


    end

    Tuesday, June 30, 2009 10:18 AM
  • Counting the whole table in this trigger is a very bad idea.  Do some research on the EXISTS and NOT EXISTS syntax and use these clauses accordingly.  Doing this count aggregate is liable to cause lock contention that you don't want.
    Kent Waldrop
    Tuesday, June 30, 2009 10:41 AM
  • Mai,

    (Trigger issues aside)

    The original error message states quite clearly that you are trying to insert an NVARCHAR into a UNIQUEIDENTIFIER column.

    Odd, because none of the strings in your posted INSERT statement are NVARCHAR - are you *sure* that is the exact statement that caused the error?

    This highlights one of the reasons why it is bad practice to skip specifying column names in the INSERT statement.

    You should instead write INSERT dbo.Table (Column1, Column2...) VALUES (1, 2...)

    Try also to get into the habit of specfying both the schema and table or view name - dbo.Table rather than Table.  You may avoid unnecessary recompilations that way, it is clearer, and just looks more polished ;c)

    Paul
    Tuesday, June 30, 2009 10:57 AM
  • Please provide the DDL for the BINDINGS table.
    Kent Waldrop
    Tuesday, June 30, 2009 12:01 PM
  • start  datetime 
    duration datetime
    city_name  nchar(10)
    id int

    city_name with id primary key and these are foreien key for other tables




    lsn now it seems that sth wrong happen i don't know i add now and there is error

    insert

     

    into bindings values('nablus',3,'6/6/2009 10:00:00 AM','6/6/2009 00:30:00 AM')

    Tuesday, June 30, 2009 12:07 PM
  • Well, if all of this is true, the problem seems to be that you have no correlation between your VALUES statement and the actual table.  Please re-read Paul's comments about modifying your INSERT statement and see if you can change your INSERT statement into something similar.

    Also, when you submit a post to to request assistance for a specific issue like this post the issue as a QUESTION rather than a DISCUSSION.  "Discussions" are intended for situations in which the person submitting the post is not expecting a definite answer to a specific issue.

    Here you are trying to get help resolving a problem; this should be posted as a question.  I will modify this into a question in a couple of minutes.

     

    Thanks for understanding.

     

    Kent Waldrop


    Kent Waldrop
    Tuesday, June 30, 2009 12:14 PM
  • ( Modified into a question; I will als be modifying some of your previous "discussions" into "Questins". )


    Kent Waldrop
    Tuesday, June 30, 2009 12:16 PM
    Moderator
  • am sorrrry realy but my manager come soo i didn;t replay please am sooory
    Tuesday, June 30, 2009 12:22 PM
  • i want to disscuss it sorry
    Tuesday, June 30, 2009 12:23 PM
  • Please refrain from changing the type to a discussion; this needs to be posted as a question.  You may continue to discuss the issues, but do not change the type back to a "discussion" post.

    EDIT:

    Mai,

    I understand that you are new to the forums and that you have an issue that you want to discuss.  What you want is the reason the forums are here; however, there are specific purposes to the "Question" category and the "Discussion" category.

    The "Discussion" category is intended for more general rhetorical issues that someone wants to discuss about Transact SQL.  As such, the person making the post has not requirement for an answer to solve a specific issue.

    The "Question" category is intended for more narrow, specific issues in which the person making the post is looking for assistance to solve a problem.  This is the case with your particular post here -- you are looking to have an execution error resolved.

    That does not mean that you cannot "discuss" the issue as part of a question.  By all means, do continue the discussion.

     

    Kent

    Tuesday, June 30, 2009 12:25 PM
    Moderator
  • Check Datatype of Your table first column.  Is it Varchar or  uniqueidentifier?.

    May be  datatype of first column is uniqueidentifier.

    Therefore Mention Column name when inserting Record.

    Regards
    Rajoo
    • Proposed as answer by RAJOO JHA Tuesday, June 30, 2009 1:03 PM
    Tuesday, June 30, 2009 12:28 PM
  • am sorrrry realy but my manager come soo i didn;t replay please am sooory
    I'm out!
    Tuesday, June 30, 2009 12:30 PM
  • I can understand wanting to stand down; I hope I didn't blow you out of the water.  If I did, Paul, I'm sorry.


    Kent
    Kent Waldrop
    Tuesday, June 30, 2009 12:49 PM
  • I can understand wanting to stand down; I hope I didn't blow you out of the water.  If I did, Paul, I'm sorry.
    Hey Kent!

    No - it's all good - nothing you did in any way.  
    Though I must confess that I don't know what the phrase "blow you out of the water" means...just not a phrase I have heard before.  I would actually quite like to know what it means... hey so one can learn more than just SQL here eh?
    No I'm just out for the obvious reason (my manager came - $deity help us all).

    Paul

    Tuesday, June 30, 2009 12:55 PM
  • sory for my wrong expression maybe due to weak in expressing


    my table is
    the first column is nchar now nvarchar
    Tuesday, June 30, 2009 1:10 PM
  • I Guess

    "blow you out of the water" = "I fired a shot that sank your boat"
    Kent Waldrop
    Tuesday, June 30, 2009 1:13 PM
  • i didn;t understand did my expression was wrong to say am out
    i didn;t understand
    Tuesday, June 30, 2009 1:16 PM
  • Check Datatype of Your table first column.  Is it Varchar or  uniqueidentifier?.

    May be  datatype of first column is uniqueidentifier.

    Therefore Mention Column name when inserting Record.

    Regards
    Rajoo
    Cheek!  Proposing your own post as an answer!!!  Look up a couple of posts dude - I believe I said it first and better ;c)
    Tuesday, June 30, 2009 1:20 PM
  • I Guess

    "blow you out of the water" = "I fired a shot that sank your boat"
    Kent Waldrop
    Sweet.  Thanks!
    Tuesday, June 30, 2009 1:21 PM
  • i didn;t understand did my expression was wrong to say am out
    i didn;t understand
    Hey Mai - it's nothing personal.  I just choose not to get involved in some circumstances.

    Paul
    Tuesday, June 30, 2009 1:22 PM
  • i want to ask you something

    if i have a start time and duration i want to add them and compare with the time now?what is the best thing to do?


    Tuesday, June 30, 2009 1:26 PM
  • General note to All: for NEW question open a NEW topic. Don't piggyback unrelated questions.
    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Tuesday, June 30, 2009 1:38 PM
    Moderator
  • The problem is in your triggers. The first parameter for the system stored procedure sp_update_job @job_id is of type uniqueidentifier and you are passing nvarchar string. Perhaps you mean to use the @job_name parameter, which is nvarchar.

    http://msdn.microsoft.com/en-us/library/aa260379(SQL.80).aspx
    Plamen Ratchev
    Tuesday, June 30, 2009 3:32 PM
    Moderator
  • yes you are right how should i write this statematent
    i must write it like this

    EXEC msdb.dbo.sp_update_job @job_name=N'planning_second_job',@enabled=0
    Tuesday, June 30, 2009 3:38 PM
  • Yes, this is the correct syntax.
    Plamen Ratchev
    Tuesday, June 30, 2009 3:49 PM
    Moderator