asked on

Stored procedure syntax

I have a SP that is giving me an error. The first two items are updated fine, but the two fields that are 'bit' type in the database are giving the error.

The value when displayed on the page shows "True" or "False"

I am using ASP and SQL 2008R2

This is my code:

<%

Dim sp_updateattachment__attachment_id
sp_updateattachment__attachment_id = "0"
if(Request("fieldid")    <> "") then sp_updateattachment__attachment_id = Request("fieldid")   

Dim sp_updateattachment__description
sp_updateattachment__description = "0"
if(Request("field1")    <> "") then sp_updateattachment__description = Request("field1")   

Dim sp_updateattachment__dateuploaded
sp_updateattachment__dateuploaded = "01/01/1900"
if(Request("field2")    <> "") then sp_updateattachment__dateuploaded = Request("field2")   

Dim sp_updateattachment__Visible
sp_updateattachment__Visible = "0"
if(Request("field3")    <> "") then sp_updateattachment__Visible = Request("field3")   

Dim sp_updateattachment__Visibleindividual
sp_updateattachment__Visibleindividual = "0"
if(Request("field4")    <> "") then sp_updateattachment__Visibleindividual = Request("field4")   

%>
<%

set sp_updateattachment = Server.CreateObject("ADODB.Command")
sp_updateattachment.ActiveConnection = MM_bluedot_STRING
sp_updateattachment.CommandText = "dbo.BlueDotUpdateAttachmentInfo"
sp_updateattachment.Parameters.Append sp_updateattachment.CreateParameter("@RETURN_VALUE", 3, 4)
sp_updateattachment.Parameters.Append sp_updateattachment.CreateParameter("@attachment_id", 3, 1,4,sp_updateattachment__attachment_id)
sp_updateattachment.Parameters.Append sp_updateattachment.CreateParameter("@description", 200, 1,256,sp_updateattachment__description)
sp_updateattachment.Parameters.Append sp_updateattachment.CreateParameter("@dateuploaded", 135, 1,10,sp_updateattachment__dateuploaded)
sp_updateattachment.Parameters.Append sp_updateattachment.CreateParameter("@Visible", 901, 1,1,sp_updateattachment__Visible)
sp_updateattachment.Parameters.Append sp_updateattachment.CreateParameter("@Visibleindividual", 901, 1,1,sp_updateattachment__Visibleindividual)
sp_updateattachment.CommandType = 4
sp_updateattachment.CommandTimeout = 0
sp_updateattachment.Prepared = true
sp_updateattachment.Execute()

%>

Open in new window

Any ideas ? Is it trying to update the bit field to "True" ? If so how can I make into a "1" so it updates correct ?

Help is appreciated.

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Mlanda T

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aleks

ASKER

Got this error:

Microsoft VBScript compilation  error '800a03ee' 

Expected ')' 

/bluedot/Intranet/Cases/attachments_SPedit.asp, line 67 
sp_updateattachment.Parameters.Append sp_updateattachment.CreateParameter("@Visibleindividual", 901, 1,1,Iif(lcase(sp_updateattachment__Visibleindividual)="true" orsp_updateattachment__Visibleindividual="1", 1, 0))

Open in new window

Aleks

ASKER

I then added a space after the 'or' ... and got this error:

ADODB.Command error '800a0bb9' 

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. 

/bluedot/Intranet/Cases/attachments_SPedit.asp, line 66

Open in new window

SOLUTION