My Sql Stored procedure problem

Hi!!
I'm getting an error and I can't understand why is this happening...
I have an stored procedure which receives a parameter to delete the database row corresponding to that key-parameter. In the GridView_RowCommand I'm setting up the datasource in the e.CommandName == "Delete" option     

this.SqlDataSourceGridView.DeleteCommandType = SqlDataSourceCommandType.StoredProcedure;
this.SqlDataSourceGridView.DeleteCommand = "spDeleteGridView";

Then in the GridView_RowDeleting event, I'm preparing datasourceDeleteParameters with the Key that I want to delete in my database and call the         

this.SqlDataSourceGridView.Delete ( );

The row is deleted in the database but, and here is the problem, there is something that before it got error and throws me that:

El procedure or funcion 'spDeleteGridView' waits the parameter '@PARAM', that what not expected.

I must add the information that debugging GridView_RowDeleting it ends normally and calls the GridView_RowDeleted...

Any help??

Thank you!!!

Hi All,

am using sql server 2005,

am writing a stored procedure to which i need to pass some parameters. (like company name,minimum revenue,maximum revenue,min emplotees,max employees etc..)

in the stored procedure i need to check the parameter value

ex: i need to check if the max revenue is containing the ">" or not

But am unable to write the stored procedure with the above requirement.It is giving error

CREATE PROCEDURE CompanyAdvSrch
(
--am passing all these values to the stored procedure
    
    @companyName    VARCHAR(250),
    @CurrentPage    VARCHAR(50),
    @type            VARCHAR(50),
    @country        VARCHAR(50),
    @state            VARCHAR(50),
    @city            VARCHAR(50),
    @industry        VARCHAR(50),
    @minrevenue        VARCHAR(50),
    @minemp            VARCHAR(

hi.

I want to make my gridview enable for custom paging and sorting. I started to follow a tutorial and write the Stored procedure.

But my stored procedure seems not to be working. here it is:

ALTER PROCEDURE [dbo].[USP_getStudentsEmail]
(
    @sortExpression nvarchar(100),
    @startRowIndex int,
    @maximumRows int,
    @receiver nvarchar(38)
)
AS


IF LEN(@sortExpression) = 0
    SET @sortExpression = 'SendDate'


DECLARE @sql nvarchar(4000)
SET @sql = 'Select *
From
(
	Select m.Body,m.Subject,m.SendDate,m.SenderProfile,m.Receiver AS Receiver,m.ID,s.FirstName,ROW_NUMBER() OVER (ORDER BY '+ @sortExpression + ') AS RowRank 
	from Message m INNER JOIN StudentProfile s ON m.Sender=s.UserId
) AS OrderedEmails
            WHERE     m.Receiver= '+ CONVERT(nvarchar(38), @receiver) + 'AND RowRank > ' + CONVERT(nvarchar(10), @startRowIndex) +
                ' AND RowRank <= (' + CONVERT(nvarchar(10), @startRowIndex) + ' + '
                + CONVERT(nvarchar(10), @maximumRows) +')'


EXEC sp_executesql @sql

I tried to run this by the following line

exec USP_getStudentsEmail 'SendDate',1,1,'5ed19f91-eeb7-4c6a-b564-98c8dd83e18c'

but it says  

Incorrect syntax near 'd19f91'-

can anybody help

Hi people!

OPEN SYMMETRIC KEY PasswordFieldSymmetricKey
DECRYPTION BY CERTIFICATE PasswordFieldCertificate;
  SELECT
  [UserId],
  [AccountNumber],
  [Login],
  CONVERT(varchar, DecryptByKey(EncryptedPassword)) AS 'Password'
  FROM [User]
  WHERE ([Login] = @Login)
        AND (CONVERT(varchar, DecryptByKey(EncryptedPassword)) = @Password);
CLOSE SYMMETRIC KEY PasswordFieldSymmetricKey;

All works correctly but if i change the "Date and Time Properties" on my machine i receive the next error

(0 row(s) affected)
Msg 15315, Level 16, State 1, Procedure user_login, Line 111
The key 'PasswordFieldSymmetricKey' is not open. Please open the key before using it.

I recently moved a .net site from one machine to another, now for some reason one of the stored procedures is throwing an exception when attempting to insert! 

Exception Details: System.Data.SqlClient.SqlException: An explicit value for the identity column in table 'dbo.tbl_Events' can only be specified when a column list is used and IDENTITY_INSERT is ON

BTW, the column in question does have the identity set to Yes in management studio

I was using originally SQL 2005, now its on SQLexpress 2008

stored procedure:

ALTER

I have stored proc where i insert some value from openxml but it is not inserting that xml data. Below is my stored proc.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[SP_MKTG_SERVICE_INSERT_NEW]
@ID AS INT,
@STATUS AS NVARCHAR(20),
@USERNAME AS NVARCHAR(50),
@DATEOFVISIT AS DATETIME,
@SUMMARY AS NVARCHAR(500),
@HOMESERVICE AS NVARCHAR(5),
@EXPIRYDATE AS DATETIME,
@SERVICELIST XML
AS
 SET NOCOUNT ON

BEGIN TRAN
IF EXISTS(SELECT * FROM NEW_DTL_SERVICE WHERE PARTNERID=@ID)
BEGIN
	DELETE FROM NEW_DTL_SERVICE WHERE PARTNERID=@ID
END

IF (@@ERROR <> 0)
BEGIN
	ROLLBACK TRAN
	RETURN
END
		DECLARE @IDOC INT
		EXEC SP_XML_PREPAREDOCUMENT @IDOC OUTPUT,@SERVICELIST
		INSERT INTO NEW_DTL_SERVICE
		(PARTNERID,[SERVICENAME],[TYPE],DISCOUNT,COMMISSION,CONDITION)
		SELECT @ID,SERVICENAME,TYPE,DISCOUNT,COMMISSION,CONDITION
		FROM OPENXML(@IDOC,'/NEWDATASET/TABLE1',2)
		WITH (SERVICENAME NVARCHAR(50),TYPE NVARCHAR(50),DISCOUNT NVARCHAR(50),COMMISSION NVARCHAR(50),CONDITION NVARCHAR(500))
		EXEC SP_XML_REMOVEDOCUMENT @IDOC
IF (@@ERROR <> 0)
BEGIN
	ROLLBACK TRAN
	RETURN
END
EXEC TIEDUPPARTNER @STATUS,@ID

IF (@@ERROR <> 0)
BEGIN
	ROLLBACK TRAN
	RETURN
END
COMMIT TRAN

and following is my xml which i generate to insert.

 Send Email from SQL Server Express Using a CLR Stored Procedure 
  
Problem
One of the nice things about SQL Server is the ability to send email using T-SQL.  The downside is that this functionality does not exist in SQL Server Express.  In this tip I will show you how to build a basic CLR stored procedure to send email messages from SQL Server Express, although this same technique could be used for any version of SQL Server.

Solution
If you have not yet built a CLR stored procedure, please refer to this tip for what needs to be done for the initial setup.