Board index » delphi » Problem w/SQL Insert statement, SQL Server nvarchar datatype, and colons :

Problem w/SQL Insert statement, SQL Server nvarchar datatype, and colons :

Hello,

I am having trouble loading a SQL Server 7.0 table with data.  When an
attempt is made to insert a record in which a colon (:) will be stored
into an nvarchar field, I get the following error - "Arguments are of
the wrong type, are out of acceptable range, or are in conflict with
one another".  I am able to insert the record if I remove the colon
but I wish I didn't have to do that.  Any thoughts?

Here is the failing SQL statement-
INSERT INTO CstabLE VALUES
(2002228,1999,'07',3,1987,'TT','JAYC','J-SERIES','5THWHEEL',770,NULL,'N','CORR:
PER MR MAN & REV. RECORDS','12/20/1996','1UTCD02M0H1AG0054',NULL)

It works if I remove the colon after "CORR"-
INSERT INTO CstabLE VALUES
(2002228,1999,'07',3,1987,'TT','JAYC','J-SERIES','5THWHEEL',770,NULL,'N','CORR
PER MR MAN & REV. RECORDS','12/20/1996','1UTCD02M0H1AG0054',NULL)

Here's the table schema-
Fld Name Type     Size
A_Number int      4
Year     smallint 2
Category nvarchar 2
Seq      smallint 2
Yr       smallint 2
Cls      nvarchar 2
Make     nvarchar 4
Model    nvarchar 23
Style    nvarchar 40
Value    int      4
Override nvarchar 1
Late     nvarchar 1
Note     nvarchar 40
A_Date   smalldatetime 4
VDX      nvarchar 20
DTS_Flag nvarchar 1    

The Note field is the one giving problems.  Thanks for any help you
can give!

-Chris

 

Re:Problem w/SQL Insert statement, SQL Server nvarchar datatype, and colons :


Quote
>I am having trouble loading a SQL Server 7.0 table with data.  When an
>attempt is made to insert a record in which a colon (:) will be stored
>into an nvarchar field, I get the following error - "Arguments are of
>the wrong type, are out of acceptable range, or are in conflict with
>one another".  I am able to insert the record if I remove the colon
>but I wish I didn't have to do that.  Any thoughts?

I suggest you try using a Parameter in your query for this field then setting
the value of the parameter

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:Problem w/SQL Insert statement, SQL Server nvarchar datatype, and colons :


Hello again,

Thank you Brian for the response.  I tried using a parameter, but with
no luck.  Same error is reported.  However, I have had success by
changing the way I am inserting the records.  Rather than composing
Insert statements in my code, I am now just using an ADO Table
component and saying Table1.Append; Table1.FieldByName().asstring =
'whatever', etc... then Table1.post'ing the record that way....Colons
are no longer a problem.

I don't know what the deal is, but at least I'm running...If I had
more time, I'd pursue this further.  Thanks again for your response.

Chris

On Mon, 15 Oct 2001 22:25:22 -0500, Brian Bushay TeamB

Quote
<BBus...@Nmpls.com> wrote:

>>I am having trouble loading a SQL Server 7.0 table with data.  When an
>>attempt is made to insert a record in which a colon (:) will be stored
>>into an nvarchar field, I get the following error - "Arguments are of
>>the wrong type, are out of acceptable range, or are in conflict with
>>one another".  I am able to insert the record if I remove the colon
>>but I wish I didn't have to do that.  Any thoughts?

>I suggest you try using a Parameter in your query for this field then setting
>the value of the parameter

>--
>Brian Bushay (TeamB)
>Bbus...@NMPLS.com

Other Threads