Passing int parameter to stored procedure question.

Hi all,

I had created a stored procedure "DeleteRow" that can receive a parameter "recordID" from the calling function, however, I received a error msg "Procedure or function deleteRow has too many arguments specified." when run the C# code.
My code is showing below
----------- -------
thisConnection.Open();
SqlParameter param = DeleteRow.Parameters.Add("@.recordI D", SqlDbType.Int, 4);
param.Value = key;
SqlDataReader reader = DeleteRow.ExecuteReader(CommandBeh avior.CloseConnection) //program stop after runing this line
The stored procedure code which is showing below:
--------------------
CREATE PROCEDURE dbo.deleteRow @.recordID INT AS DELETE FROM ShoppingCart WHERE RecordID = @.recordID
Can anyone give me some ideal why this happen.
Thank alot.
wing

I don't know exactly, maybe you made a mistake here:
"@.recordI D",
see the whitespace

|||You should be using ExecuteNonQuery. ExecuteReader is designed to return data, not update.
|||omg, I've overlooked that. :$
You can also use an ExecuteScalar. In contract to ExecuteNonQuery, itwill get the first field of the first row. Usefull when using a returnvalue in a stored procedure...

passing an int parameter

Hello,

This is the way I call report viewer in my asp.net application.

this.ReportViewer1.ServerUrl=serverUrl;

this.ReportViewer1.ReportPath=repName;

this.ReportViewer1.Parameters=Microsoft.Samples.ReportingServices.ReportViewer.multiState.False;

this.ReportViewer1.SetQueryParameter("MyName",myname);

this.ReportViewer1.SetQueryParameter("MyID",myID);

MyName is a string and MyID is an int. Without MyID parameter it is working fine, however, when I add MyID it does not work, so how can I pass an int parameter to reporting services.

Thanks,

Cast its type as an integer

Passing a variable to the UPDATE Command

Hello everybody,

I have a problem in passing a variable into the Update command. This is my stored procedure.

CREATE PROCEDURE UpdateTable @.tsID INT, @.UpdateDesc varchar(1000)
AS

DECLARE @.TableNumber INT
DECLARE @.TableName NVARCHAR(100)
DECLARE @.SQL VARCHAR(100)

/* I have to get the name of the table to be updated by first getting the TableId
( an INT field) from a table TS_SUBTASKS and with that the table name from another table TS_TABLES */

SELECT @.TableNumber = TS_SUBTABLEID FROM TS_SUBTASKS
WHERE TS_SUBITEMID = @.tsID

SELECT @.TableName = TS_DBNAME FROM TS_TABLES
WHERE TS_ID = @.TableNumber

/* Which the Table name in the variable @.TableName i have to set one of its
fields, TS_DESCRIPTION to the value passed into the procedure
(@.UpdateDesc) */

SET @.SQL = 'update ' + @.TableName+ 'SET TS_DESCRIPTION = ' + @.UpdateDesc + ' WHERE TS_ID = ' + @.tsID

EXEC(@.SQL)

GO

--But i get this error

Syntax error converting the varchar value 'update USR_HUMAN_RESOURCESSET TS_DESCRIPTION = newdescription WHERE TS_ID = ' to a column of data type int.

It would be very helpful if somebody could throw light on this.
Thanks,
Krishna Murthy.

You should really rethink your approach of using dynamic SQL and this type of design. It requires lot of work in terms of giving permissions to users directly to update the table and coding the dynamic SQL. It is best to use static SQL wherever possible since it is more secure and easier to manage. The errors you are seeing are a result of incorrecly formed UPDATE statement. I believe it is missing some quotes. You should also consider using sp_executesql since that is easier to parameterize like below and you can avoid some of the SQL injection problems with using EXEC incorrectly:

DECLARE @.SQL nvarchar(1000)
SET @.SQL = N'update ' + QUOTENAME(@.TableName) +
N'SET TS_DESCRIPTION = @.Descr WHERE TS_ID = @.ID'
exec sp_executesql @.SQL, N'@.Descr varchar(1000), @.ID int', @.Descr = @.UpdateDesc, @.ID = @.tsID
|||Thank you Mr. Jayachandran, your correction has helped me a great deal. And coming to your suggestion of not taking this approach, I could not find any other way of doing the same. I shall again be very thankful to you if you can suggest a better way of doing this.

Thanks,
Krishna Murthy.|||I meant that you should avoid writing code that relies on dynamic schema elements. They are difficult to manage and code. In your case, you may want to consider generating some of the SP code during the creation at the time of definition of the row that represents a table. This approach also depends on your application and requirements.