Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2002
    Posts
    84

    Question Unanswered: char type in MS SQL

    Dear All,

    I am new to MS SQL. One VB application that I need to maintain using it.
    There is one problem reported but I couldn't explain: one field is declared
    as Char (20), but the application seems able to insert a string with length
    even 30. There is no character truncatd when retrieved neither.

    However, I cannot simulate the case in the MSSQL database manager. When I executed a SQL script to insert, I was simply not allowed to do so.

    Any explanation to the sDid anyone encounter same thing before?
    Thanks for any hint.


    Regards

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    My first thought is that you may not be looking in the right place for something (either the column size or the data entered). You are right that normally trying to put 30 characters in a 20 character slot will result in an error. Does the VB script have "on error resume next"? Can you disect the VB script enough to see the insert statement itself? Post that insert statement and the result of sp_help tablename, if you can.

  3. #3
    Join Date
    Apr 2002
    Posts
    84
    Hi,

    Thanks for the help. Below is the piece of codes, while t1_id was declared as CHAR(20) NOT NULL but a string with length 30 could be inserted and retrieved.

    ----------------------------------------------------------------------
    On Error GoTo errors:
    Dim conn As RDO.rdoConnection
    Set conn = mrdoConnection.Connection

    sqlStr = "INSERT INTO t1 (t1_id,t1_name) values ('" & s1 & "', '" & s2 & "')"
    conn.Execute sqlStr

    errors:
    MsgBox err.Description
    If Not conn Is Nothing Then
    conn.RollbackTrans
    conn.Close
    End If
    ----------------------------------------------------------------------

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Must be using the winzip datatype.

    Code:
    create table test1 (col1 char(20))
    
    insert into test1 values
    ('123456789012345678901234567890')
    
    Server: Msg 8152, Level 16, State 9, Line 1
    String or binary data would be truncated.
    The statement has been terminated.
    Even if there was something (connection option of some sort) preventing you from getting an error returned, you would not be able to retrieve the value. When you insert the value, what do you get from this?
    Code:
    select * from t1
    Here, I am assuming that t1 is some sort of permanent entity that is not being dropped and recreated for every run.

Posting Permissions

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