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


Top 5 Contributors of the Month
AndyV
webmaster
SOHINI DASGUPTA
Post New Web Links

Custom paging sorting stored procedure problem

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

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


View Complete Post


More Related Resource Links

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

Cursor problem with Stored Procedure and PHP

  
I'm struggling with accessing the result set of a stored procedure using the SQL Server Driver for PHP 1.1. I've already got a few of them running, but this one is special in that it has an XML input parameter which is parsed into a table variable. This table variable is then used within a select statement. When I execute the SP within Management Studio, it runs fine and returns exactly one result set with multiple rows. But when I execute it from PHP, I get the error message "Executing SQL directly; no cursor. ". I've tried every cursor type out there, the only one that doesn't fail is SQLSRV_CURSOR_FORWARD, but with that I'm not getting any results at all. The original SP is enormous, so I have stripped it down as far as I could so it still throws the error. If I remove the bold part, my PHP code executes fine.   USE [testsite] GO SET  ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[getCrossSellingItems]     @InputIds XML AS     SET NOCOUNT ON ;     DECLARE @tblInputIds TABLE     (         Id INT     );     INSERT INTO @tblInputIds (Id)     (         SELECT         T.Item.value('@Id', 'INT') FROM @InputIds.nodes('/Root/Item') AS T(

custom stored procedure error

  

We have been presenting an issue randomly since we changed our replication to use custom stored procedures for replication.

We have several offices which replicate to our central datacenter, each of this offices have different data corresponding only to their site and we centralize the data from all databases in only one database.

We use custom stored procedures for some tables only for the insert method, but randomly the update method of that table drops and I have to create again. We have been set the replication by using the 'replication support only' method so it only creates the replications sp. All our articles have the create update, insert and delete stored procedures for replication.

We have done traces for this and the replications is generating the drop but it doesn't recreate the procedure. It only occurs for the articles with custom procs for insert.  

The snapshot jobs for each office are disabled.


Problem with Oracle Stored Procedure/Web Form(C#)

  

Hi!

I receive the following message when trying to insert a record into a table via Web Form(C#):

Error: Invalid operation. Connection closed.

The button event, when clicked, should connect to the database and then call the stored procedure and
insert a new record according to the text input on the Web Form. Hence, returning the new flight id (TID).

Note: Some entries can be null.

Frontend-Code:

{code}

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Add_Flight.aspx.cs" Inherits="Add_Flight" %>   <!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"> <head runat="server"> <title></title> <style type="text/css"> .style1 { width: 89%; } .style2 { height: 23px; } .style3 { width: 360px; } .style4 { width: 137px; } .style5 { height: 23px; width: 137px; } .style6 { width: 95px; } .style7 { height: 23px; width: 95px; } .style8 { width: 200px; } .style9 { width: 129px; } </style> </head> <body> <form id="form1" runat="server"> <div> <table cellspacing="3" class="styl

Pass Multi-Value parameters to Stored Procedure problem

  

Hello

I am trying to pass multi-value parameters to stored procedure to filter data, but seems it does not work.

Stored procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[test]

@StartDate DateTime,
@EndDate DateTime,
@FirstName varchar(8000),
@LastName varchar(8000),
@Location varchar(8000),

AS
BEGIN
SET NOCOUNT ON;
 
WITH cte AS
(
SELECT [Item No_],[Sales Staff],[Location Code],[Date],
[Price],[Quantity],[Item Category Code],[Product Group Code]FROM [Trans Sales Entry]
),
cte2 as
(
SELECT [ID],[First Name],[Last Name] FROM Sta

GridView delete using stored procedure problem (strange)

  

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!!!


Problem with stored procedure.

  

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(

My Sql Stored procedure problem

  

Dear all,

          I am in need to import the Data from .DAT file to my database.when I tried with LOAD DATA INFILE statement ,it's working fine.But my ultimate aim is to do the same with stored procedure  ,it fails and gives an error message 


Script line: 4 LOAD DATA is not allowed in stored procedures


Script line: 4 LOAD DATA is not allowed in stored procedures


This is my Stored procedure


DELIMITER $
DROP PROCEDURE IF EXISTS `paylink`.`Test` $
CREATE PROCEDURE `paylink`.`Test` ()
BEGIN
Load Data INfile 'E:\Collect.dat' into table paylink.te

Custom DB Installation Issue While Run the Stored Procedure Script

  

1. I have created Custom DB Installation by Installar Class
2. I created Stored Procedure Script From DB
3. Copyied Script in sql.txt
4.Created Custom DB Setup and Executed, But I am getting SP script Execution issue, same script is working in QueryAnalyser Execution

Here is My Execution code

Private

 

Sub ExecuteSql(ByVal DatabaseName As String, ByVal S

Custom DB Installation Issue While Run the Stored Procedure Script

  

1. I have created Custom DB Installation by Installar Class
2. I created Stored Procedure Script From DB
3. Copyied Script in sql.txt
4.Created Custom DB Setup and Executed, But I am getting SP script Execution issue, same script is working in QueryAnalyser Execution

Here is My Execution code

Private

 

Sub ExecuteSql(ByVal DatabaseName As String, ByVal S

Need to order stored procedure output based on custom order

  

*Read the whole question before you decide to send this to the chart forum

So here is what I am trying to do:

I have a chart in VB Express (vb.net) that obtains its data from a stored procedure in my SQL Express database. The chart is a stacked column chart and shows events on the X axis. What I need is these events to be displayed in a very specific order in my X Axis.

The problem:

I have been told that I cannot custom order this in the application, but rather I need to change my stored procedure output to get the events in the order I want. It was suggested to me that I should make a new table in my database that has the names of the events in 1 column and the order I want them in, in the other. Then use that to order my output in my stored procedure.

I do not care how I accomplish what I need to accomplish so long as I do indeed accomplish it. Here is some database structure to help you understand my database:

Table: Buyer

BuyerID (PK), ProjectID (FK), Status, Type

There are 12 different statuses, though my stored procedure doesn't know that. These 12 statuses need to be shown in a very specific order. My current stored procedure is as follows:

select Status as Status, Type as Type, count(BuyerID) as Value from Buyer WHERE projectID = @ProjectID AND Status IS NOT NULL AND Type IS NOT NULL GROUP BY Status,Ty

stored procedure based custom conflict resolver truncates data

  

I created a stored procedure based custom conflict resolver in SQL 2005, I return the winning result set and also save that result set to a test table to compare the values.  The values saved to the test table are correct but some of the values saved as the conflict winter are truncated.

 

Example a char(3) filed is updated at the subscriber as '111' and updated at the publisher as '222', in my custom conflict resolver if I use the value from the subscriber the conflict resolver updates the field as  '11 ', if I use the publisher value the conflict resolver updates the field as '22 '.  Now the same records is saved to the test table correctly as either '111' or '222' depending on the logic I used.  So the result set has the correct values, its after the custom conflict resolver is called where the values is somehow truncated.  Has anybody run into this before and what steps can I take to avoid this.

 

Thank you,

Problem with decryption data in stored procedure if local date on my machine was changed

  

Hi people!

Can anybody help me with my problem?

I have one machine (Server 2003 x64 SP2, SQL Server Standard Edition (64-bit) version 9.00.4053.00). Also I have a stored procedure in database that doing a validation of users, and contains the next code:

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.

If i set the current date it will work correctly again.
One strange thing:
if execute the query "SELECT * FROM sys.symmetric_keys" i have the next date of creation for my symmetric keys

2010-11-04 11:18:49.300

And if i set a date less then date of

problem inserting using a stored procedure and identity column.

  

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

@EventID

PROCEDURE [dbo].[proc_EventsAddEdit]int,

@EventName

varchar(200),<

Execute stored procedure on linked DB2 server from MS SQL 2008 SP1 64 bit problem

  
Hi everybody. I am trying to execute stired procedure on linked DB2 server from MS SQL 2008 x64
I installed IBM Access client x64 and on provider tab showed up 3 providers IBMDASQL,IBMDA400,IBMDARLA
I installed linked server as shown on this two links:

1)http://www.mcpressonline.com/database/db2/db2-integration-with-sql-server-2005-part-i-linked-server-enhancements.html
2)http://stackoverflow.com/questions/329051/execute-db2-iseries-stored-procedure-from-a-sql-2005-linked-server

I installed linked server using all this 3 providers

sp_addlinkedserver
    @server=N'DB2',
    @srvproduct=N'DB2 UDB for iSeries',
    @provider=N'IBMDASQL',-- provider for example
    @datasrc=N'ASTEST', -- mydatasource
    @catalog='S65CF29B'
go
exec sp_addlinkedsrvlogin DB2,false,null,'telebank','password'

I can run procedure from the extended stored procedure, but when i try to execute it as shown on the linkes above i get the following error:
Could not execute statement on remote server 'DB2'.


declare
  @branch    as varchar(4),
  @cli   as varchar(6),
  @suffix    as varchar(3),
  @date1     a

Problem in Stored procedure when inserting data through openxml

  

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.

AJAX GridView Paging and Sorting using C# in ASP.Net

  
To enable the GridView Paging and Sorting to update the GridView Data without refreshing the ASP.Net web page AJAX Extension controls are used. UpdatePanel is used to hold the GridView control that triggers the events for the controls placed inside it. By Default UpdatePanel triggers the asynchronous postbacks for its child controls.
Categories: 
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