need help with this sp
-
need help with the sp below.
CREATE Procedure dbo.sp_select_image
(
@iNo int OUTPUT,
@iName nvarchar(50) OUTPUT,
@iSize int OUTPUT,
@iType nvarchar(50) OUTPUT,
@iByteData image OUTPUT,
@iDescription nvarchar(100) OUTPUT,
@iUploadDate datetime OUTPUT,
@iUploadPersonGuid nvarchar(100) OUTPUT,
@iApproved bit OUTPUT,
@iRating int OUTPUT,
@iGuid nvarchar(50)
)
AS
SELECT
@iNo=iNo,
@iName=iName,
@iSize=iSize,
@iType=iType,
@iByteData=iByteData,
@iDescription=iDescription,
@iUploadDate=iUploadDate,
@iUploadPersonGuid=iUploadPersonGuid,
@iApproved=iApproved,
@iRating=iRating
FROM
tblimages
WHERE
iGuid = @iGuid
GO
when I hit 'Check Syntax' button on Store Procedure Properties window I get this error message
Error 409: The assignment operator operation cannot take a image data type as an argument.
This is my table's sql script that used by the sp.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblimages]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblimages]
GO
CREATE TABLE [dbo].[tblimages] (
[iNo] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[iName] [nvarchar] (50) COLLATE Turkish_CI_AS NOT NULL ,
[iSize] [int] NOT NULL ,
[iType] [nvarchar] (20) COLLATE Turkish_CI_AS NOT NULL ,
[iByteData] [image] NOT NULL ,
[iDescription] [nchar] (100) COLLATE Turkish_CI_AS NULL ,
[iUploadDate] [datetime] NOT NULL ,
[iUploadPersonGuid] [nvarchar] (50) COLLATE Turkish_CI_AS NOT NULL ,
[iApproved] [bit] NOT NULL ,
[iRating] [int] NOT NULL ,
[iGuid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Also I have another sp that insert images to the table and that works just fine.
CREATE PROCEDURE [dbo].[sp_insert_image]
(
@iByteData [image],
@iName [nvarchar](50),
@iSize [int],
@iType [nvarchar](20),
@iDescription [nchar](100),
@iUploadPersonGuid [nvarchar](50),
@iGuid [nvarchar](50) OUTPUT
 
AS INSERT INTO [neistersen].[dbo].[tblimages]
(
[iName],
[iSize],
[iType],
[iByteData],
[iDescription],
[iUploadPersonGuid]
)
VALUES
(
@iName,
@iSize,
@iType,
@iByteData,
@iDescription,
@iUploadPersonGuid
)
DECLARE @iNo int
SELECT @iNo=@@IDENTITY
SELECT
@iGuid=iGuid
FROM
tblimages
WHERE
GO
Your help is very much appriciated. Thank you in advance.
Best regards,
-
CREATE Procedure dbo.sp_select_image
(
@iGuid nvarchar(50)
)
AS
SELECT
iNo,
iName,
iSize,
iType,
iDescription,
iUploadDate,
iUploadPersonGuid,
iApproved,
iRating,
iByteData -- Notice is at the end
FROM
tblimages
WHERE
iGuid = @iGuid
GO
I am not sure if you are using ADO or ADO.NET but you can't assign an image datatype to a variable you will have to use recodsets (ADO) or Datareader /Datasets (ADO.NET)
HTH
* Noel -
Thank you Noel,
I think I cannot use them on record set, or I dont know how to use it that way.
Once I get all the parameters on the original sp I will use then on the following code.
Public
Class imagefunctions
Public Function getimage(ByVal iGuid As String) As imagedetails
Dim myimagedetails As imagedetails = New imagedetails
Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("applicationConnectionString"))
Dim myCommand As SqlCommand = New SqlCommand("sp_select_image", myConnection)
' Mark the connection as SPROC
myCommand.CommandType = CommandType.StoredProcedure
Dim parameterNo As SqlParameter = New SqlParameter("@iNo", SqlDbType.Int, 4)
parameterNo.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterNo)
Dim parameterName As SqlParameter = New SqlParameter("@iName", SqlDbType.NVarChar, 50)
parameterName.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterName)
Dim parameterSize As SqlParameter = New SqlParameter("@iSize", SqlDbType.Int, 4)
parameterSize.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterSize)
Dim parameterType As SqlParameter = New SqlParameter("@iType", SqlDbType.NVarChar, 50)
parameterType.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterType)
Dim parameterByteData As SqlParameter = New SqlParameter("@iByteData", SqlDbType.Image)
parameterByteData.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterByteData)
Dim parameterDescription As SqlParameter = New SqlParameter("@iDescription", SqlDbType.NVarChar, 100)
parameterDescription.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterDescription)
Dim parameterUploadDate As SqlParameter = New SqlParameter("@iUploadDate", SqlDbType.DateTime, 8)
parameterUploadDate.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterUploadDate)
Dim parameterUploadPersonGuid As SqlParameter = New SqlParameter("@iUploadPersonGuid", SqlDbType.NVarChar, 50)
parameterUploadPersonGuid.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterUploadPersonGuid)
Dim parameterApproved As SqlParameter = New SqlParameter("@iApproved", SqlDbType.Bit, 1)
parameterApproved.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterApproved)
Dim parameterRating As SqlParameter = New SqlParameter("@iRating", SqlDbType.Int, 4)
parameterRating.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterRating)
Dim parameterGuid As SqlParameter = New SqlParameter("@iGuid", SqlDbType.NVarChar, 50)
parameterGuid.Value = iGuid
myCommand.Parameters.Add(parameterGuid)
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
myimagedetails.imageid = parameterNo.Value
myimagedetails.imagename = parameterName.Value
myimagedetails.imagesize = parameterSize.Value
myimagedetails.imagetype = parameterType.Value
myimagedetails.imagebytedata = parameterByteData.Value
If Not parameterDescription.Value Is Nothing And Not parameterDescription.Value Is System.DBNull.Value Then
myimagedetails.imagedescription =
CStr(parameterDescription.Value).Trim
Else
myimagedetails.imagedescription = ""
End If
myimagedetails.imageuploaddate = parameterUploadDate.Value
myimagedetails.imageuploadpersonGuid = parameterUploadPersonGuid.Value
myimagedetails.imageapproved = parameterApproved.Value
myimagedetails.imagerating = parameterRating.Value
myimagedetails.imageguid = iGuid
Return myimagedetails
End Function
Public Function insertimage(ByVal iname As String, ByVal isize As Integer, ByVal itype As String, ByVal ibytedata() As Byte, ByVal idescription As String, ByVal iuploadpersonguid As String) As String
Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("applicationConnectionString"))
Dim myCommand As SqlCommand = New SqlCommand("sp_insert_image", myConnection)
' Mark the connection as SPROC
myCommand.CommandType = CommandType.StoredProcedure
Dim parameterByteData As SqlParameter = New SqlParameter("@iByteData", SqlDbType.Image)
parameterByteData.Value = ibytedata
myCommand.Parameters.Add(parameterByteData)
Dim parameterName As SqlParameter = New SqlParameter("@iName", SqlDbType.NVarChar, 50)
parameterName.Value = iname
myCommand.Parameters.Add(parameterName)
Dim parameterSize As SqlParameter = New SqlParameter("@iSize", SqlDbType.Int, 4)
parameterSize.Value = isize
myCommand.Parameters.Add(parameterSize)
Dim parameterType As SqlParameter = New SqlParameter("@iType", SqlDbType.NVarChar, 50)
parameterType.Value = itype
myCommand.Parameters.Add(parameterType)
Dim parameterDescription As SqlParameter = New SqlParameter("@iDescription", SqlDbType.NVarChar, 100)
parameterDescription.Value = itype
myCommand.Parameters.Add(parameterDescription)
Dim parameterUploadPersonGuid As SqlParameter = New SqlParameter("@iUploadPersonGuid", SqlDbType.NVarChar, 50)
parameterUploadPersonGuid.Value = iuploadpersonguid
myCommand.Parameters.Add(parameterUploadPersonGuid)
Dim parameterGuid As SqlParameter = New SqlParameter("@iGuid", SqlDbType.NVarChar, 50)
parameterGuid.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterGuid)
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
Return CStr(parameterGuid.Value).Trim
End Function
End Class
Public Class imagedetails
Public imageid As Integer
Public imagename As String
Public imagesize As Integer
Public imagetype As String
Public imagebytedata() As Byte
Public imagedescription As String
Public imageuploaddate As Date
Public imageuploadpersonGuid As String
Public imageapproved As Boolean
Public imagerating As Integer
Public imageguid As String
End Class
And this functions will be called from
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not Request.Params("iGuid").Trim Is Nothing Then
Dim iGuid As String = Request.Params("iGuid").Trim
Dim myif As imagefunctions = New imagefunctions
Dim myimagedetails As imagedetails = myif.getimage(iGuid)
With Page.Response
.Clear()
.ContentType = myimagedetails.imagetype
.AddHeader("content-disposition", "filename=" & myimagedetails.imagename)
.OutputStream.Write(myimagedetails.imagebytedata, 0, myimagedetails.imagesize)
.End()
End With
End If
End Sub
But I will try to place image variable at the end and lets see if it works that way.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply