Board index » delphi » INSERT INTO command with dbExpress and MS SQL Server 2000

INSERT INTO command with dbExpress and MS SQL Server 2000

I have the following code:

  sql.CommandText := 'INSERT INTO User1 (Code, Description) ' +
    'VALUES (:Code, :Description)';
  sql.Params.ParamByName('Code').DataType := ftString;
  sql.Params.ParamByName('Description').DataType := ftString;

where sql is a TSQLDataSet, Code is defined as VARCHAR(12) and Description
is defined as VARCHAR(50).

I run the following code:

  sql.ParamByName('Code').AsString := 'TEST';
  sql.ParamByName('Description').AsString := 'This is a test';
  sql.ExecSQL;

The row inserts correctly, but when I look at it in SQL Server Enterprise
Manager, I see a little square (non-printable character, it would seem) at
the end of each of the strings in the row (but only when I actually click in
the cell).  Trying to issue the following statement:

SELECT * FROM User1 WHERE Code = 'TEST'

fails to return any data.  If I enter the row data directly into SQL Server
Enterprise Manager, then I don't see the little square at the end of each
string field, and the query works correctly.

My question is, what do I need to do?  Change the data type of the parameter
to something else?  Set a property on the connection or dataset?  Something
else?

-Eric Harmon

 

Re:INSERT INTO command with dbExpress and MS SQL Server 2000


Andy,

Lovely :-(  I suspected it was an ASCII 0, but when I tried to use Delphi to
search for 'ABC'#0, it wasn't happy <g>  All right, I guess I'll need to go
to QC and report this.  This one is {*word*193}....

-Eric

Quote
"Andy Mackie" <amac...@citynetworks.co.uk> wrote in message

news:3d85fd5e$1@newsgroups.borland.com...
Quote
> "Eric Harmon" <Eric.Har...@tpx.turbopower.com> wrote in message
> news:3d85f49f$1@newsgroups.borland.com...
> > The row inserts correctly, but when I look at it in SQL Server
Enterprise
> > Manager, I see a little square (non-printable character, it would seem)
at
> > the end of each of the strings in the row (but only when I actually
click
> in
> > the cell).

> I see a character with ASCII value of 0 getting added to the end of the
> strings. I used this to check:

> SELECT
>   Code,
>   Description,
>   ASCII(SUBSTRING(Code, Len(Code), 1)),
>   ASCII(SUBSTRING(Description, Len(Description), 1))
> FROM User1

> Looks like a bug to me.

> Regards,
> Andy Mackie.

Re:INSERT INTO command with dbExpress and MS SQL Server 2000


Quote
"Eric Harmon" <Eric.Har...@tpx.turbopower.com> wrote in message

news:3d85f49f$1@newsgroups.borland.com...

Quote
> The row inserts correctly, but when I look at it in SQL Server Enterprise
> Manager, I see a little square (non-printable character, it would seem) at
> the end of each of the strings in the row (but only when I actually click
in
> the cell).

I see a character with ASCII value of 0 getting added to the end of the
strings. I used this to check:

SELECT
  Code,
  Description,
  ASCII(SUBSTRING(Code, Len(Code), 1)),
  ASCII(SUBSTRING(Description, Len(Description), 1))
FROM User1

Looks like a bug to me.

Regards,
Andy Mackie.

Re:INSERT INTO command with dbExpress and MS SQL Server 2000


Reported as QC #2369

-Eric

Re:INSERT INTO command with dbExpress and MS SQL Server 2000


Quote
Eric Harmon wrote...
> Lovely :-(  I suspected it was an ASCII 0, but when I tried to use Delphi to
> search for 'ABC'#0, it wasn't happy <g>  All right, I guess I'll need to go
> to QC and report this.  This one is {*word*193}....

Yes, it surely is {*word*193}. dbExpress SQL driver does add #0 at the end.
Using original SQL statement, if you do a select

SELECT * FROM User1 WHERE Code = 'TEST'+Char(0)

than you'll get your data back. This needs to be fixed ASAP IMHO.
--
Delphi Guy

Re:INSERT INTO command with dbExpress and MS SQL Server 2000


Delphi Guy,

Quote
> SELECT * FROM User1 WHERE Code = 'TEST'+Char(0)

Well, that may work, but the actual data is corrupted after a fashion, so it
sure isn't a solution <g>

-Eric

Re:INSERT INTO command with dbExpress and MS SQL Server 2000


Quote
> Well, that may work, but the actual data is corrupted after a fashion,
so it
> sure isn't a solution <g>

Certainly not <g>. I was trying to confirm #0 being added to the end of
the varchar() field.
--
Delphi Guy

Re:INSERT INTO command with dbExpress and MS SQL Server 2000


Bug  is addressed and should be available in the update soon.

T.Ramesh.

Quote
"Eric Harmon" <Eric.Har...@tpx.turbopower.com> wrote in message

news:3d85f49f$1@newsgroups.borland.com...
Quote
> I have the following code:

>   sql.CommandText := 'INSERT INTO User1 (Code, Description) ' +
>     'VALUES (:Code, :Description)';
>   sql.Params.ParamByName('Code').DataType := ftString;
>   sql.Params.ParamByName('Description').DataType := ftString;

> where sql is a TSQLDataSet, Code is defined as VARCHAR(12) and Description
> is defined as VARCHAR(50).

> I run the following code:

>   sql.ParamByName('Code').AsString := 'TEST';
>   sql.ParamByName('Description').AsString := 'This is a test';
>   sql.ExecSQL;

> The row inserts correctly, but when I look at it in SQL Server Enterprise
> Manager, I see a little square (non-printable character, it would seem) at
> the end of each of the strings in the row (but only when I actually click
in
> the cell).  Trying to issue the following statement:

> SELECT * FROM User1 WHERE Code = 'TEST'

> fails to return any data.  If I enter the row data directly into SQL
Server
> Enterprise Manager, then I don't see the little square at the end of each
> string field, and the query works correctly.

> My question is, what do I need to do?  Change the data type of the
parameter
> to something else?  Set a property on the connection or dataset?
Something
> else?

> -Eric Harmon

Other Threads