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

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

beginner with a parameter problem

Posted By:      Posted Date: October 27, 2010    Points: 0   Category :ASP.Net

I am passing the table name from a previous page through a session parameter.

When the datasource tries to run the select command with @SQLTable I get the error below.

using @SQLTable causes the following error

Exception Details: System.Data.SqlClient.SqlException: Must declare the table variable "@SQLTable".


here is the code, (there is no code behind)
01.<asp:GridView ID="GridView1" runat="server" DataSourceID="SQLTableData">  
02.        </asp:GridView>  
03.        <asp:SqlDataSource ID="SQLTableData" runat="server"    
04.            ConnectionString="<%$ ConnectionStrings:PhilConnectionString %>"    
05.            SelectCommand="select * from @SQLTable">  
06.            <SelectParameters>  
07.                <asp:SessionParameter Name="SQLTable" type="String" SessionField="SQLTableName" />  
08.            </SelectParameters>  
09.        </asp:SqlDataSource>  


View Complete Post

More Related Resource Links

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

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 with passing parameter values to stored proceedure from asp.net c#

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

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

Having problem accessing multi-choice parameter in SQL Query in Report.

Hi, I have a report with a multi-choice input parameter. My report contains a dataset that uses CHARINDEX on this multichoice parameter. The dataset query is in text, not in stored procedure. When I run the report I get "the charindex requires 2-3 arguments the reason being that the SQL is run as follows (You can see the multi-choice list screws up the string: exec sp_executesql N'Select test.Region [Region], test.Location [Location], nvarchar3 [Year], nvarchar4 [StatisticType], nvarchar5 [StatisticType2], ntext2 [Detail], float1 [Amount]   from [WSS_Content].[dbo].[AllUserData] UD   inner join [WSS_Content].[dbo].[AllLists] AL on AL.tp_ID = UD.tp_ListId and AL.tp_Title=''Statistics''   left outer join   (       Select UD.tp_id [ID],nvarchar1 [Region],     nvarchar3 [Location]   from [WSS_Content].[dbo].[AllUserData] UD   inner join [WSS_Content].[dbo].[AllLists] AL on AL.tp_ID = UD.tp_ListID and AL.tp_Title=''Regions''   where UD.tp_ListId = AL.tp_ID   and UD.tp_ListId = AL.tp_ID   and UD.tp_DeleteTransactionId = 0x0   and tp_IsCurrentVersion = 1   ) test on test.id = UD.int1   where UD.tp_ListId = AL.tp_ID   and UD.tp_ListId = AL.tp_ID   and UD.tp_DeleteTransactionId = 0x0   and tp_IsCurrentVersion = 1  &n

Problem with Report parameter inside SSRS Report Viewer webpart with deployment from one server to a

Hi all,I've got this problem.me and my team have developed a lot of reports by using a SSRS configured in Sharepoint Integration Mode.For accessing these report we are using the Sql Server 2008 Report Viewer Webpart.One of its parameter is the Report ur that is an absolute url so the problem is when I move our solution from server1 to server2 I have to re-editing all pages where I've added the Report Viewer webpart for updating the report url with the new servername otherwise it's impossible to view the report.Is there any official or unofficial way to solve this problem ?many thanks

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

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

Problem in parameter list while executing the report in sharepoint



I have created one SSRS 2005 report with 3 dependent parameters ,they are BusinessUnit,Account,Project.In SSRS solution file when i click a specific BU name ,then at the same time Account parametr refreshes and shows the corresponding account names and the same for project.

I deployed the report in to sharepoint 2007,that time the account parameter filed is not refreshing paralleling when i click a BU and it shown as inactive.Only when i click the apply parameter acoount fild shows the corresponding account names.Please help me to avoid the issue.Thanks in advance.


PrimaryKey Increment and Numbering problem (beginner)


I am new to Web Developer and SQL Express. My Question is about the numbering problem of Primary Key Index


<<Table Definition>>

                         [ColumnName]      [DataType]

(Primary Key)    ID                            int

                         Name                      nvarchar(150)


:set ID as the Primary Key,  set ID's property 

   Identity Specification = Yes

   (Is Identity)               = Yes

   Identity Increment     = 1

   Identity Seed             = 1


:I typed the data into the table through Web Developer

<<Show Table Data>>


Problem with the layout of the parameter panel


I work on several server or vm, and did not have this problem before until this new vm.  For all my reports, the parameter section of the reports is not displayed properly.  It usually displays the parameters on a grey background, in this case it's totally white.  Also, the section containing the arrows to go to a page, the zoom, the find/next, the refresh...has the same problem also.  Where can I look to solve this?



sharepoint SSRS parameter problem



I had created ssrs report in SSRS 2008 based on sharepoint list in SHarepoint 2007

I had created parameter of EMPGroup from another dataset.

Paarmeter datatype is text.

When I use that parameter to filter the report in tablx properties as

columnname  in parametername  it shows  data on running report


columnname LIKE parametername it shows  error on running report


Thanks & Regards, Vyankatesh Mujumdar

Problem with CLR Assemblies(Beginner)!!




I'm trying to execute an sp which is calling a CLR assembly and on execution, i get the following error:


An error occurred in the Microsoft .NET Framework while trying to load assembly id 65537. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:

System.IO.FileLoadException: Could not load file or assembly 'emailserviceclr, Version=, Culture=neutral, PublicKeyToken=null' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)


problem accesing output parameter from datalayer



in order to allow my design to keep recently inserted record on a formview i decided to use an output parameter.
Currently I have a data layer helper and a class that allows my table to have crud operations.

I have modified my insert stored procedure to the following:

ALTER PROCEDURE [dbo].[InsertContratos]
 @Contrato int,
 @Persona int,  
     @FechaLegalizacion datetime,
 @Vendedor smallint,
 @Programa smallint,
        @numero char(3),
       @Referencia1Nombre varchar(50)='',
 @Referencia1Apellido varchar(50)='',
 @Referencia1Telefono varchar(20)='',
 @Referencia2Nombre varchar(50)='',
 @Referencia2Apellido varchar(50)='',
 @Referencia2Telefono varchar(20)='',
        @Observaciones Text='',
        @Anulado char(1)='N',
 @ContratoInsertado int OUTPUT 


INSERT INTO contrato
 contnume ,
 contfere ,
 contfele ,

Problem to group radio button across gridview rows

In general no one can easily group or make a single selection from radio button list inside gridview rows. There are a lot of way to make a single or unique selection from list. Here i want to share how i can address this problem in the most easiest way. Googling the problem most of the cases i saw the grouping in horizontal way. Thats why i tried to group the radio buttons in vertical manner. Let i have a requirement like below:

Regrading Sharpoint 2010 problem and solutions

We can write your question and problem on this blogger.you get ans.use.
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