Monday, March 26, 2012

return @@identity for another function

What I'm trying to do is provide a solution where users can upload an image and a description, to a database, so I'm trying to insert the title and description then return the @.@.identity for the image upload function which will name the image like this
image_23.jpg (23 being the @.@.identity) resize it and save it to specified directory

I cant seem to get the identity to return to my script.
This is my SP

CREATE PROCEDURE SP_Insertad
(
@.catid int,
@.subcatid int,
@.areaid int,
@.uid int,
@.adtitle varchar(255),
@.addescription varchar(1000)
)

AS
Insert Into Tbl_ad
(ad_title, ad_description,ad_area,ad_ui_id,ad_active,ad_date,ad_ct_id,ad_sc_id,ad_location)
VALUES
(@.adtitle,@.addescription,@.areaid, @.uid, 0,convert(varchar, GETUTCDATE(), 101), @.catid, @.subcatid, 1)

select @.@.identity
return
GO

I tested in query analyser, and it works fine, so It must be my code. this is my function

Sub Insert_pic(sender as object, e as eventargs)

Dim catid = Request.form("ddcats")
Dim subcatid = Request.form("subcatrad")
Dim adtitle = Request.Form("txttitle")
Dim AdDescription = Request.form("txtdescription")
Dim uid = getUID(Context.User.Identity.Name)
Dim areaid = Request.form("ddarea")
SQLConnect = new SqlConnection(ConfigurationSettings.Appsettings("mydB"))

SQLCommander = New SQLCommand("SP_INSERTad", SQLConnect)

SQLCommander.Commandtype = Commandtype.StoredProcedure

SQLCommander.Parameters.add("@.adtitle", adtitle)
SQLCommander.Parameters.add("@.addescription", addescription)
SQLCommander.Parameters.add("@.catid", catid)
SQLCommander.Parameters.add("@.subcatid", subcatid)
SQLCommander.Parameters.add("@.uid", uid)
SQLCommander.Parameters.add("@.areaid", areaid)

'// this section not working right, it wont write return id

Dim paramreturn as SQLParameter
paramreturn = SQLCommander.Parameters.Add("ReturnValue", SQLDBType.Int)
ParamReturn.Direction = ParameterDirection.ReturnValue

response.write(SQLCommander.Parameters("ReturnValue").Value)

SQLConnect.open()
SQLCommander.ExecuteNonQuery()
SQLConnect.close()

End sub

Can anybody see anything I missing? I appreciate any imputYour existing code will work by making the following changes:

1) Take out the following lines:


Dim paramreturn as SQLParameter
paramreturn = SQLCommander.Parameters.Add("ReturnValue", SQLDBType.Int)
ParamReturn.Direction = ParameterDirection.ReturnValue

2) Make the following change to the SQLCommander.ExecuteNonQuery() line:


Dim returnVal As Integer = SQLCommander.ExecuteScalar()

See the following link about the ExecuteScalar() method:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlcommandclassexecutescalartopic.asp|||you should say "return @.@.ID..." rather than "select"|||i changed it to
...
AS
Insert Into Tbl_Ads
(ad_title, ad_description,ad_area,ad_ui_id,ad_active,ad_date,ad_ct_id,ad_sc_id,ad_location)
VALUES
(@.adtitle,@.addescription,@.areaid, @.uid, 0,convert(varchar, GETUTCDATE(), 101), @.catid, @.subcatid, 1)

return @.@.identity
GO

I think the break down is here -
Dim paramreturn as SQLParameter
paramreturn = SQLCommander.Parameters.Add("ReturnValue", SQLDBType.Int)
ParamReturn.Direction = ParameterDirection.ReturnValue

response.write(SQLCommander.Parameters("ReturnValue").Value)

it's not writing to page. Stumped|||ghost opz ,
that worked, I dont know why or how but That did the trick.

thanks a million!

striker

No comments:

Post a Comment