We help IT Professionals succeed at work.

MS SQL request INSERT with out use convert

thierry91
thierry91 used Ask the Experts™
on
my INSERT is :

INSERT INTO [SurfControl_WebFilter].[dbo].[_WhereObjList_A] ([Object_ID],[Attrib_Name],[Type],[Attrib_Val_S],[Attrib_Val_N],[Attrib_Val_B]) VALUES
(
'1, int,'
,'Member, varchar(40),'
,'0, int,','ScoutMonitored\Monitored Sites\www.superu-laiz.com, varchar(250),'
,'NULL, int,'
,'NULL, varbinary(255),'
)



table WebFilter is :

CREATE TABLE [dbo].[_WhereObjList_A](
      [Object_ID] [int] NULL,
      [Attrib_Name] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Type] [int] NULL,
      [Attrib_Val_S] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Attrib_Val_N] [int] NULL,
      [Attrib_Val_B] [varbinary](255) NULL
) ON [PRIMARY]

When I whant insert I have ERROR :

Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.

I not want use CONVERT.

where is my error in my request INSERT ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Paul JacksonSoftware Engineer
Top Expert 2011
Commented:
you don't specify datatypes for the values and you don't put quotes around integers try:
INSERT INTO [SurfControl_WebFilter].[dbo].[_WhereObjList_A] ([Object_ID],[Attrib_Name],[Type],[Attrib_Val_S],[Attrib_Val_N],[Attrib_Val_B]) VALUES 
(1, 'Member',0,'ScoutMonitored\Monitored Sites\www.superu-laiz.com',NULL,NULL)

Open in new window

You don't need the data types in your insert statement.

INSERT INTO [_WhereObjList_A] ([Object_ID],[Attrib_Name],[Type],[Attrib_Val_S],[Attrib_Val_N],[Attrib_Val_B]) VALUES
(
1
,'Member'
,0
,'ScoutMonitored\Monitored Sites\www.superu-laiz.com'
,NULL
,NULL
)

Greg

Commented:
you are trying to store all varchar values in table.
so need to change the column data type to varchar or nvarchar.
Top Expert 2011
Commented:
the values shouldn't contain the data type...

and the values should not all be strings

INSERT INTO [SurfControl_WebFilter].[dbo].[_WhereObjList_A] ([Object_ID],[Attrib_Name],[Type],[Attrib_Val_S],[Attrib_Val_N],[Attrib_Val_B]) VALUES
(
1
,'Member'
,0
,'ScoutMonitored\Monitored Sites\www.superu-laiz.com'
,
,
)

e.g for the integers pass numbers
and for a null pass an empty term , unless there is a default in which case use the nullify function