Adding the parameters dynamicaly to SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Adding the parameters dynamicaly to SP

Author

Topic

Swati Jain
Posting Yak Master

139 Posts

Posted - 2007-10-27 : 07:53:44

In our website we have created the reportengine which passes the parameters dynamicaly means whatever parameters are provided in Stored procedure is automaticaly binded to grid from where values are passed to sp as follows
.Actualy this code is written by some other developer
If Not ParameterTable Is Nothing Then
RecordCount = ParameterTable.Rows.Count
If RecordCount >= 1 Then
For Index = 0 to (RecordCount - 1)
ParameterRow = ParameterTable.Rows(Index)
Select Case ParameterRow(1)
Case "231"
RecordType = "SQlDbType.NVarChar"
Case "60"
RecordType = "SQlDbType.Money"
Case "61"
RecordType = "SQlDbType.DateTime"
Case "62"
RecordType = "SQlDbType.Int"
Case "59"
RecordType = "SQlDbType.Float"
Case "104"
RecordType = "SQlDbType.Bit"
Case Else
RecordType = "SQlDbType.Int"

End Select
myCommand.Parameters.Add(ParameterRow(0), RecordType).Value = (ParameterRow(2))

When Sp is executed with link<A href="Main.aspx?ReportID=1075443551&ReportName=RptAPandARComparative&ReportDescription=RptAPandARComparative"
target="main">

I get the error that nvarchar can not be converted int

Why is is So?
Is anybody having same type of experiance
In above link I got reportid as queries
select id from sysobjects where name='RptAPandARComparative'

Kristen
Test

22859 Posts

Posted - 2007-10-27 : 07:59:36

"I get the error that nvarchar can not be converted int"

You have a string value which is not a valid integer, and you are trying to pass it as a parameter of type Integer.

Suggest you put a debug statement in the code so you can see the actual value(s) you are trying to process, and the target datatype.

Kristen

Swati Jain
Posting Yak Master

139 Posts

Posted - 2007-10-27 : 08:18:22

quote:
Originally posted by Kristen

"I get the error that nvarchar can not be converted int"

You have a string value which is not a valid integer, and you are trying to pass it as a parameter of type Integer.

Suggest you put a debug statement in the code so you can see the actual value(s) you are trying to process, and the target datatype.

Kristen

I tried all datatypes still I got the same error with sp as well as code

Kristen
Test

22859 Posts

Posted - 2007-10-27 : 10:23:15

So what did the debug output show you was

a) the [string] value you are atempting to store
b) the datatype of the target parameter
?

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-27 : 10:47:24

your "RecordType" variable should not be a string, it should be the correct data type as defined by the method you are calling. It's all right there in the intellisense and in the MSDN docs.

so, instead of code like this:

dim RecordType as string
RecordType = "SQlDbType.Float"

you should have code like this:

dim RecordType as SqlDbType
RecordType = SQlDbType.Float

You should always use OPTION STRICT ON to avoid these run-time errors, since if you mix data types incorrectly as you are, it will tell you at compile time. Also, note that you are not setting "record type", but a "data type", so a better variable name should be "DataType".

Either way, this look like a really convoluted way of doing something that should be very simple and straightforward. Why are these parameters stored in some random table somewhere? What does that gain?

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources