Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2006

    Unanswered: can't enter data in field

    I have a field in my table that is varchar:1500:null, but I cannot enter any more that 994 characters. any ideas why?

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Quotes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[Quotes]

    CREATE TABLE [dbo].[Quotes] (
    [QuoteID] [int] IDENTITY (1, 1) NOT NULL ,
    [DateAdded] [datetime] NULL ,
    [CustomerID] [int] NOT NULL ,
    [ProductName] [varchar] (100) NULL ,
    [RepID] [int] NULL ,
    [QuoteNumber] [varchar] (30) NULL ,
    [QuoteDate] [datetime] NULL ,
    [QuoteTerm] [varchar] (10) NULL ,
    [QuoteFOB] [varchar] (15) NULL ,
    [QuoteNAIRep] [varchar] (30) NULL ,
    [QuoteExpiration] [datetime] NULL ,
    [Note] [varchar] (700) NULL ,
    [Comment] [varchar] (1500) NULL ,
    [OrderRequirement] [varchar] (1000) NULL ,
    [Status] [varchar] (1) NULL ,
    [DateClosed] [datetime] NULL ,
    [ProductType] [varchar] (30) NULL ,
    [ImageID] [int] NULL ,
    [CloseMonth] [int] NULL ,
    [CloseYear] [int] NULL ,
    [ClosePercent] [int] NULL ,
    [Segment] [varchar] (50) NULL ,
    [AccountID] [uniqueidentifier] NULL ,
    [ReplacedQuoteID] [int] NULL ,
    [Lead] [varchar] (80) NULL
    ) ON [PRIMARY]

    ALTER TABLE [dbo].[Quotes] ADD
    CONSTRAINT [DF_Quotes_Closed] DEFAULT ('N') FOR [Status]

    CREATE INDEX [idx_RepID] ON [dbo].[Quotes]([RepID]) ON [PRIMARY]

    CREATE INDEX [indx_CustomerID] ON [dbo].[Quotes]([CustomerID]) ON [PRIMARY]

    CREATE INDEX [indx_QuoteID] ON [dbo].[Quotes]([QuoteID]) ON [PRIMARY]

    CREATE INDEX [indx_Status] ON [dbo].[Quotes]([Status]) ON [PRIMARY]

    [Comment] [varchar] (1500) NULL , is where I can't enter more than 994 characters.

    note - I know very little about SQL, I just had the responsibility placed on me at my job. If this is not enough information to go on to throw an idea at me please let me know what else you need.

    Thanks in advance
    Last edited by c0pe; 05-30-07 at 23:54.

  2. #2
    Join Date
    May 2007
    somewhere in dbforums
    No such issue i just ran your script and inserted 1500 characters into the comments column.....also when you define 1500 it will give you a capacity of 1500

    are you using the insert statement or just entering data through enterprise manager???? cuz insert statement worked fine for me....
    insert into quotes (Customerid, comment)
    values (3,'Your comment goes here')

    Also try to find the length of your largest column by using the following script

    select len(comment)
    from quotes

  3. #3
    Join Date
    Oct 2006
    i am entering the text manually. Don't know how to run an insert script.

    I ran the sel script and the longest entry is 1000 chars.

    would you mind telling me how to run the insert script to apply the comment to quote number 070530JB185438?
    Last edited by c0pe; 05-31-07 at 00:47.

  4. #4
    Join Date
    Oct 2006
    I was able to insert the text I need using the insert script you gave, I just had to add more fields to make it apply to the correct quote...

    INSERT INTO Quotes

    (QuoteID, CustomerID, QuoteNumber, Comment)

    VALUES ('35845', '5695', '070530jb185438', 'comment')

    Thanks for the help, it is greatly appreciated.

  5. #5
    Join Date
    May 2007
    somewhere in dbforums
    since quoteid is generated automatically you dont need to insert a value there........ according to your table design then customerid is the only non nullable field which needs to be present...

    hence for testing purpose if you provide a query as
    INSERT INTO Quotes
    (CustomerID, Comment)
    VALUES ('5695', 'comment')
    it would still work...since all other columns are nullable........

    Also to test your case you can insert a very long string in place of 'comment'.... and inside single quotes of course...

    other thing what you can do to test the case is use the update statement
    update quotes
    set comment = 'You comment goes here'
    where customerid = '5695'

    this will update the record which has customerid = '5695'
    i.e. the record which you have just inserted in the previous post

  6. #6
    Join Date
    Dec 2006
    Momence, IL USA
    Here's a function to insert a 1500 character string of x's into your comment field.

    INSERT INTO Quotes
     (customerid, comment)  VALUES  ('1234', REPLACE(SPACE(1500), ' ', 'x'))
    then check the length with the Len function
    Don't Bogart That Squishee!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts