.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

problem with passing parameter values to stored proceedure from asp.net c#

Posted By:      Posted Date: September 02, 2010    Points: 0   Category :ASP.Net
hi all, i am facing a problem while passing parameters to a stored proceedure. following is my sql query written for creating this stored procedure:   CREATE proc [dbo].[getstudent]( @wherecolumn nvarchar(2000), @wherevalue nvarchar(2000)) asbegin  declare @sql nvarchar(max) set @sql = 'select * from student2 where ' + @wherecolumn+'='+@wherevalueexec sp_Executesql @sql end   my table contains four columns: And the sql Query For the table is as follows:   CREATE TABLE [dbo].[student2]( [name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [rollno] [int] NOT NULL, [class] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [section] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_student2] PRIMARY KEY CLUSTERED ( [rollno] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]   from page behind I am passing the values to parameter as follows:   SqlCommand command = new SqlCommand("getstudent", c.con); SqlDataAdapter datadapter = new SqlDataAdapter(c.cmd); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@wherecolumn", SqlDbType.NVarChar).Value = "rollno"; command.Parameters.Add("@wherevalue", Sq

View Complete Post

More Related Resource Links

SSRS problem with passing multiple parameter values via URL %2c

Hi, I have a problem with passing multiple parameter values from one report (A) to another report (B).   For example I built a URL string in report (A) with the parameters like &Parameter1=1&Parameter1=2. As soon as I click on that URL the URL parameters look like &Parameter1=1%2c2 and the parameters don’t getting populated on report (B).   If I change the parameter in the URL to &Parameter1=1&Parameter1=2 and execute the URL manual the parameter on report (B) getting populated properly.   Is there a way to disable the coding like %2c (comma) or is there another solution for it?   Thanks

passing object values from server to client problem


I get a message that x_login is not declared.  I added Dim login as String = "123", but that didn't help.  What's the problem?

code behind....

Dim Login = "123"
x_login.Value = loginID


<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="Default.aspx.vb" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<form id="simForm" runat="server" method='post' action='https://test.authorize.net/gateway/transact.dll'>
        <input id="HiddenValue" type="hidden" value="Initial Value" runat="server" />
        <input type='hidden' runat="server" name='x_login' id='x_login' />
        <input type='submit' runat="server" id='buttonLabel' />



Performance problem when passing a "nullable parameter"

Say @startDate and @endDate are my datetime parameters. If users does not want to limit the @startDate for example, he/she sends a null value to @startDate. Same goes for @endDate (and any other parameter). What I like doing: Select * From MyTable Where MyTable.Date >= IsNull(@startDate, MyTable.Date) And MyTable.Date <= IsNull(@endDate, MyTable.Date) The above looks nice as far as coding but when I look at the execution plan I'm surprised to see the my clustered index (that has it's first column as "MyTable.Date") is not used. instead I get an index scan. More frustrating is that the following works with an index seek and is much faster: Select * From MyTable Where MyTable.Date >= @startDate And MyTable.Date <= @endDate So what should I do? am I doing something wrong? I don't want to use dynamic sql because it's generally slower and prone to sql injection (is dynamic sql my only choise)? I don't want to use "if statements" because then i'd have to rewrite my code several times (in this case three combinations but some of my procedures have up to 8 "nullable" parameters). Or maybe there is another way of implementing what I call here "nullable parameters". obviously I preformance is crucial. thanks, Dror

problem for using webservice (sql parameter stored Procedure)

i want to put my database layer in the web service. This works fine BUT there is a problem when i am access these methods GetSPData(Byval arg As SqlComman, argCmdText as string, ByVal argSqlConnection as SqlConnection) As DataTable I want to acces the aforesaid method in my Desktop application and i have to send SqlCommand as parament. When i try to acces this (GetSPData) method the follow exception occured "There was an error generating the XML document." Now the problem is to converting DataTable in xmlDocument Or xml node (i dont know about it very much). Can anyone tell me How to send SqlCommand as parament ?? How to convert DataTable into XmlDocument?? When i try to test this method the following Error occurs " test form is only available for methods with primitive types as parameters. " The following are my Web Service Method code in which i try to Get Data Using Stored Procedure and then convert DataTable into XmlDocument and return. Dim XmlDoc As New XmlDocument Dim ds As New DataSet Dim oDBLibrary As New DataBaseLibrary.DatabaseClass Dim dt As DataTable = oDBLibrary.GetSPData(objCommand1, cmdText, msqlConnection) ds.Tables.Add(dt) Dim sw As New StringWriter ds.Tables(0).WriteXml(sw) sw.Close() Dim tableXml As String = sw.ToString XmlDoc.Load(tableXml) Return XmlDoc  Now can anyone tell me what is wrong with that c

Passing multi values to another multi value parameter

Hi, my requirement is as follows I have a parameter1 in which i populate values from database. the user can select multiple values from parameter1. after the parameter1 i have another parameter2 which is also mutliselection value. i,e depending on the selection of parameter i need to populate remaining values in Parameter2 (which is also a multi selection value). i have splited the values from parameter1 and passed the value of parameter1 (parameter1.value) to parameter2. but i am getting the following error An expression of non-boolean type specified in a context where condition is expected, near ',' incorrect syntax near the keyword 'ELSE' can anyone help me in this Thanks, Rajesh Peethala

Executing an SSIS package from an stored procedure and passing a parameter to it:

Hello all, I'm trying to execute an SSIS package from an stored procedure, using xp_cmdshell. When I execute the package, hard coding the value on the variable, I get rows inserted to my destination tables, which indicates my package pulled the correct data based on the TransactionID parameter. When I execute the package from the stored procedure, the package executes without issues, but I get no rows inserted into my destination tables, which leads me to think it is a data type issue, between the package and the variables I'm using within the stored procedure. The variable TransactionID on the SSIS package, is a string data type, therefore I'm passing it as a varchar from the stored procedure. Am I doing this correctly? Maybe I'm missing something, can you guys see anything wrong? Also, I tried login into the server and executing these 2 commands on the cmd prompt: One with "" quotes between the TransactionID value: dtexec /FILE "D:\SSISPackages\MyOrderCheckProcess\MyOrderCheck_RenewalOrder.dtsx" /CONFIGFILE "D:\SSISPackages\MyOrderCheckProcess\MyOrderCheckProcess_Config.dtsConfig" /CHECKPOINTING OFF  /REPORTING EWCDI  /SET \Package.Variables[User::TransactionID].Properties[Value];"470219"   And also one without quotes between the TransactionID value: dtexec /FILE "D:\SSISPackages\MyOrderCheckProcess\MyOrde

syntax problem passing parameter into Indexing Service Query

Hi everyone, I have the following query which works fine: select OriginalFileName from Document_Entries where EntryType like 'File%' and substring(entry,charindex('file_',entry),LEN(entry)) in (  SELECT filename FROM OPENQUERY(MySearchCat, 'SELECT Directory, FileName FROM SCOPE() WHERE    CONTAINS('' "green" '') ') )  It finds all documents in the document system which contain the word "green" using the index catalog.  My problem is that i need to include this query in a larger stored procedure which accepts a parameter for key words amongst others. I can't work out the syntax to get the @keywords parameter into query. The closest I've come is the following which runs but comes back with "incorrect syntax near keyword 'green'".  The @keywords parameter will contain any key words the user enters.   declare @keywords nvarchar(500) set @keywords='green'   Declare @query nvarchar(max) set @query = ' select OriginalFileName from Document_Entries where EntryType like ''File%'' and substring(entry,charindex(''file_'',entry),LEN(entry)) in (  SELECT filename FROM OPENQUERY(MySearchCat, ''SELECT Directory, FileName FROM SCOPE() WHERE    CONTAINS(''' + @keywords + ''') '')     )  )' exec(@query)   Any ideas? thanks Gus

Rounding problem for money output parameter from stored proc

I have a GridView sourced by a SQL Server stored procedure with several databound fields, including a money column for item values. My stored proc returns the data through a SELECT and everything looks good. The stored proc also has a single output parameter that sums the total value of the displayed data, and this value is displayed in a label above the GridView. The output parameter (defined as money on the SQL Server side) is passed back to the app in good shape, but is rounded when displayed in the DataSource_Selected event handler. So a total of $30,155.22 in SQL Server shows up as $30155 on my aspx.In debugging, I have confirmed that the value leaving SQL Server is correct (30155.2200), and I can see that the value coming IN to the ASP-side event handler is already rounded. What do I need to do here to coerce/force it to show as money/currency?Relevant snippets:Stored Proc:... @onHandValue money OUT...  CREATE TABLE #tempValueOnHand(... valueOnHand money )... set @onHandValue = (select round(sum(valueOnHand), 2) from #tempValueOnHand) set @onHandValue = (select sum(valueOnHand) from #tempValueOnHand)And again, the output parameter @onHandValue is coming back, just already rounded.On the ASP side:    Protected Sub SqlDataSource1_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSourc

Facing problem in Passing Multiple values to a DataSet


Hi all,


I have a report parameter(@rptParameter), which allows only single value.

The Main Data Set query :

Select * From XXXX 

Where Title like @rptParameter+'%'

This is how i'm handling till now.

but now i need to allow users to select multiple values at report param,

and need to generate report based on that.


I'm totally lost, i don't have any idea that hw can i use "like" and "in" at a time in my Data set.


Please help me out.


If you not clear with question, let me know i will give you some example.


Thanks in Advance.


passing values to object properties of action method parameter


I have an action method that accepts a single parm.

That parm is an object with multiple properties. MVC correctly maps values from the querystring
to properties in the parm object by matching property name to querystring value name. 
Problem is, properties in the action method parm object which are themselves objects with
properties, are not assigned their matching values from the querystring. The class object has a
constructor which accepts a single string parameter.

How do I assign to properties of an action method parameter which are themselves class objects?



                <%: Html.ActionLink("Edit", "ProductMasterEdit", 
                       new { ProdKey=item.ProductKey.Value, 
                       ProductKey = item.ProductKey.Value })%> 

    public ViewResult ProductMasterEdit(ProductMaster Item)

  public partial class ProductMaster

    public ProductKey ProductKey
      get { return new ProductKey(this.ProdKey); }
      set { this.ProdKey = value.Value; }


Passing a parameter into a stored procedure from ASP.NET


Via the button1_ click event I retrieve all files in a directory (Example Follows)

protected void Button1_Click(object sender, EventArgs e)



DirectoryInfo di = new DirectoryInfo(@"C:\Test");

foreach (FileInfo filename in di.GetFiles())






I now need to check whether this filename exists as a field in a SQL Server table. For example, if the directory retrieves a file named ExampleFile.txt and does not exist in the (Table = TableFileStore;s Field = FileRetrievals) then I want to insert the filename.

How do I construct a stored procedure to pass the filename(s) into a stored procedure as a parameter? Do I need to also create an array of filenames and pass the array into my stored procedure? Does the stored procedure belong within the foreach loop?

Passing the values from the Deleted virtual table to another Stored Procedure



I am wondering if something can be done.  I have a large database that I would like to implement change tracking on selected tables.  We've come up with a way to do it the least painful way we can, but I do not know if it will work.

I've created a trigger that will pass the table name to a stored procedure.  That stored procedure creates a Tracking table by appending the suffix 'Changes' to the past in table name.  But now how do I get the data from the Deleted table?

I can't do a SELECT INTO or INSERT INTO because the tables have timestamp fields and blobs.  I don't care if I do net get the data from those fields but I don't want to have to enter all the field names besides those two types.  I can make a stored procedure get the column names and types from a system table and dynamically build the SQL.  But I would prefer to do that in one place instead of each trigger.

Basically the problem is how do I get the data from the Deleted table to another stored procedure without explicitly naming all \the fields in the trigger.

If you don't understand what I mean then please ask, I will clarify with examples.



Stored Procedure multile values parameter with integer type



I have created a store procedure with input paramenter @param1 of integer type to return result from a select statement as below:

SELECT * FROM TableABC WHERE columnabc LIKE (@param1)

the columnabc in the WHERE clause is of integer type.

I will like to be able to pass multiple values of integer to @param1, such as @param1 = 1, 2, 3.

Please advise how do I achieve that?



Problem while passing values through query to table



   I'm new to ASP.net programming. I have created a page and while trying to pass the values to the table using the non executable query I get an error. Below is my code and the error I get. Any help is really really appreciated.


 Sub Button_Click(ByVal sender As

Passing values in pages


Hi! Friends..

how to pass the value from one page to another page in asp.net  with out Using State Management Concepts?

Like 1. cookies


        3. QueryStrings

     With out using the above should pass the values...

Sort by gridview SortExpression parameter via Stored Procedure


I have a gridview that calls data via a stored procedure.  I am unable to enable the gridview columns to be sortable. I need to set the parameter in the Stored Procedure, can someone help me with this?

Here is my gridview:

<asp:GridView ID="AllUsersGrid" runat="server" AutoGenerateColumns="False" DataKeyNames="UserName"
                        GridLines="Vertical" Width="900px" DataSourceID="SqlDataSource1" AllowSorting="True"
                        SelectedRowStyle-Height="30px" CellPadding="4" BackColor="White">
                            <asp:TemplateField HeaderText="Full Name" SortExpression="lastname">
                                    <asp:Label ID="DisplayName" runat="server" Text='<%# Eval("firstname").ToString() & " " & Eval("lastname").ToString() %>' />
                            <asp:BoundField HeaderText="User Name" DataField="UserName" />

Crystal Report vs 2003 convert to vs 2008 parameter problem


Hi friend,

I have a project develop with Visual Studio 2003, when i convert the project to Visual Studio 2005 is work well. But when i convert to Visual Studio 2008, the crystal report when have pass parameter will prompt the parameter field to re-type then show the report.

But in this converted project i create a new report and pass the parameter is ok. That means i wan re-do all report @.@??

Does someone can help me solve this problem??

Thank you

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend