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.
LVL 1
AleksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
What is the EXACT error message (in full please)?
What is the SQL code of the SP?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MlandaTCommented:
Try this:
sp_updateattachment.Parameters.Append sp_updateattachment.CreateParameter("@Visible", 901, 1,1,Iif(lcase(sp_updateattachment__Visible)="true" or sp_updateattachment__Visible="1", 1, 0))
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

I don't remember if the IIF function is available in Classic ASP... In case it isn't, just add this function to your page:
Function IIf(i,j,k)
	If i Then IIf = j Else IIf = k
End Function

Open in new window

0
AleksAuthor Commented:
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

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

AleksAuthor Commented:
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

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Iif(lcase(sp_updateattachment__Visibleindividual)="true" orsp_updateattachment__Visibleindividual="1"

In SQL Server the True bit value is passed without the double-quote marks, and numbers are also passed without double quote marks.  Not sure about ASP.
0
MlandaTCommented:
@Jim you are right. The ="1" there is just a part of my check for the input parameter. The Iif statement there is supposed to return either 0 or 1. If the value being checked is true or 1, then the Iif will return a 1, otherwise it returns a 0. This is the value that gets passed into the Bit column.

@Paul... Sorry. The ')'  error was due to the missing space after the OR. Question... What data type is 901? These are the values values...
'---- ParameterDirectionEnum Values ----
adParamUnknown = &H0000
adParamInput = &H0001
adParamOutput = &H0002
adParamInputOutput = &H0003
adParamReturnValue = &H0004

'---- CommandTypeEnum Values ----
adCmdUnknown = 0
adCmdText = &H0001
adCmdTable = &H0002
adCmdStoredProc = &H0004

'---- CursorTypeEnum Values ----
adOpenForwardOnly = 0
adOpenKeyset = 1
adOpenDynamic = 2
adOpenStatic = 3

'---- LockTypeEnum Values ----
adLockReadOnly = 1
adLockPessimistic = 2
adLockOptimistic = 3
adLockBatchOptimistic = 4

'---- DataTypeEnum Values ----
adEmpty = 0
adTinyInt = 16
adSmallInt = 2
adInteger = 3
adBigInt = 20
adUnsignedTinyInt = 17
adUnsignedSmallInt = 18
adUnsignedInt = 19
adUnsignedBigInt = 21
adSingle = 4
adDouble = 5
adCurrency = 6
adDecimal = 14
adNumeric = 131
adBoolean = 11
adError = 10
adUserDefined = 132
adVariant = 12
adIDispatch = 9
adIUnknown = 13
adGUID = 72
adDate = 7
adDBDate = 133
adDBTime = 134
adDBTimeStamp = 135
adBSTR = 8
adChar = 129
adVarChar = 200
adLongVarChar = 201
adWChar = 130
adVarWChar = 202
adLongVarWChar = 203
adBinary = 128
adVarBinary = 204
adLongVarBinary = 205

Open in new window

I think you can either use adBoolean or adInteger
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
there is no native IIF function in vbscript...

MlandaT is on the right track, you're using the wrong data type definition in your sp definition, it should be adBoolean(11) or adBinary(128), but NOT adInteger
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.