Calling from classic ASP to a SQL Server stored procedure that has a binary datatype input parameter

Question

I have to call a stored procedure in a SQL Server database from classic asp (VBScript)

The problem is that one of the parameters of the stored procedure is of type "binary(1)". I have looked at the VBscript data type constants for ADO and it seems that the equivalence for SQL binary types is AdBinary, but I don't know how to deal with this kind of data type from classic ASP. I have tried to set the value but I get wrong type errors. Is it possible in classic ASP to convert a numeric or a string value to binary data?

Invoking the same procedure using a query string like "Exec Procedure..." seems to work just concatenating the value for the binary parameter, but I have no idea of how to do the same using the ADODB.Command object.

Maybe someone have faced this situation before?

Dim bvalue 
Dim Conn
Dim objCommand
Dim RS
Set Conn = CreateObject("ADODB.Connection")
Conn.Open CONNECTION_STRING
Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = Conn
objCommand.CommandText = "GetBinaryId"
objCommand.CommandType = adCmdStoredProc
objCommand.NamedParameters = True
bvalue = 15
objCommand.Parameters.Append objCommand.CreateParameter("@Id",adBinary,adParamInput,1,bvalue) 

 set RS = objCommand.Execute

Also useful resource - Data Type Mapping best ADO constant guide I've found on the web. — user692942
Ok, updated with code, but it is just a standard call to a stored procedure, the problem is the binary input parameter — Alejandro Martin
@MCND The OP is already setting the .Value property using the .CreateParameter() method, .Value is set by the 5th argument. — user692942
@AlejandroMartin Your passing an Int and it's expecting Binary data. It would be easier to pass the value as an adinteger in your Stored Procedure` and just use CONVERT(VARBINARY(MAX), @Id). Save yourself a lot of grief trying to convert to Binary using VBScript. — user692942

Control Freak Control Freak · Accepted Answer · 2014-10-06T15:16:15

bvalue must be a binary type.

I would check the type of data you have in bvalue by using btype = varType(bvalue) and see what you get, it should be 17 for byte.

If it's not 17, then you need to make it 17.

You can use this function to do that:

'Stream_StringToBinary Function
'2003 Antonin Foller, http://www.motobit.com
'Text - string parameter To convert To binary data
'CharSet - charset of the Text - default is "us-ascii"

Function Stream_StringToBinary(Text, CharSet)
  Const adTypeText = 2
  Const adTypeBinary = 1

  'Create Stream object
  Dim BinaryStream 'As New Stream
  Set BinaryStream = CreateObject("ADODB.Stream")

  'Specify stream type - we want To save text/string data.
  BinaryStream.Type = adTypeText

  'Specify charset For the source text (unicode) data.
  If Len(CharSet) > 0 Then
    BinaryStream.CharSet = CharSet
  Else
    BinaryStream.CharSet = "us-ascii"
  End If

  'Open the stream And write text/string data To the object
  BinaryStream.Open
  BinaryStream.WriteText Text


  'Change stream type To binary
  BinaryStream.Position = 0
  BinaryStream.Type = adTypeBinary

  'Ignore first two bytes - sign of
  BinaryStream.Position = 0

  'Open the stream And get binary data from the object
  Stream_StringToBinary = BinaryStream.Read
End Function

Then pass it to your command like this instead Stream_StringToBinary(bvalue,"")

Calling from classic ASP to a SQL Server stored procedure that has a binary datatype input parameter

1 Answers