Hi, I would like to ask a question about returning the column value(s) returned by stored procedure in c#. I have created the code but somehow I encountered an error saying: ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll Additional information: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Here is my code public void Display(string pdesc, string psdesc, string psize, string category, ListView lv) { cn.con.Open(); queryString = "execute dbo.sp_RunningBalance @desc, @sdesc, @cat, @size"; …

Member Avatar
Member Avatar
+0 forum 1

Hi, CREATE PROCEDURE `FindDuplicateExpenses`(IN `strExpenseDate` DATE, IN `strProfile` VARCHAR(50)) NO SQL SQL SECURITY INVOKER SELECT PD.Code, PD.ExpenseAuthorizedRefAmount, PD.FullName, PD.ExpenseDate, PD.ExpenseNatureID, PD.Ignore_Expense, PD.Profile FROM tblExpenses AS PD, (SELECT Profile, ExpenseAuthorizedRefAmount, FullName, ExpenseDate, ExpenseNatureID FROM tblExpenses GROUP BY Profile, ExpenseAuthorizedRefAmount, FullName, ExpenseDate, ExpenseNatureID HAVING COUNT(*) > 1) AS SUB1 WHERE PD.ExpenseAuthorizedRefAmount = SUB1.ExpenseAuthorizedRefAmount AND PD.FullName = SUB1.FullName AND PD.ExpenseDate = SUB1.ExpenseDate AND PD.ExpenseNatureID = SUB1.ExpenseNatureID AND PD.ExpenseAuthorizedRefAmount >0 AND PD.Profile = strProfile ORDER BY PD.ExpenseAuthorizedRefAmount, PD.FullName strProfile can be blank, so if it is blank then this **AND PD.Profile = strProfile** should not be included in the where clause, How do I …

Member Avatar
Member Avatar
+0 forum 7

hello! i have shifted my application from mssql server 2000 to mssql server 2005 , now the prob is the command object is not returning the recordset if command text is stored procedure , here is my code please check it and guide me through errors . [ICODE] dim cmdsp as command dim rs as new ADODB.recordset set cmdsp = new commadn cmdsp.activeconnection = cn cmdsp.commandtext = "MyStoredProcedure" cmdsp.commandtype = adcmdstoredproc set rs= cmdsp.execute 'here i got an error the state of rs is zero , but if i used this code at mssql server 2000 then it will return …

Member Avatar
Member Avatar
+0 forum 2

I have a storedProcedure in SQL-Server that I am using to delete duplicates from one of the tables. This storedprocedure makes use of a cursor. I tried to create the same storedprocedure in microsoft access by just replacing the 'CREATE PROCEDURE' with 'CREATE PROC' but it didn't seem to work. Can anyone provide some workaround? Here is the SQL- storedprocedure:- [CODE] ALTER PROCEDURE [dbo].[csp_loginfo_duplicates] AS BEGIN SET NOCOUNT ON; declare @minrowid bigint declare @empid nvarchar(15) declare @dtpunched datetime declare @count tinyint declare curDuplicate cursor for select empid,dtpunched,count(*),min(row_id) from loginfo group by empid,dtpunched having count(*)>1 open curDuplicate fetch next from curduplicate …

Member Avatar
Member Avatar
+0 forum 1

I have a drop down menu on a page where the user can select between, All, House, Unit and Rural which send the selection to a stored procedure which access' the database and returns the information with the selection eg. House from the category column. It all works great when you select House, Unit or Rural and the trouble I am having is when you select All it returns no information. I'm sure there is something very simple that I am missing but your help would be great. Here is the stored procedure code: [CODE] set ANSI_NULLS ON set QUOTED_IDENTIFIER …

Member Avatar
Member Avatar
+0 forum 2

I'm new to stored procedures and, really anything about sql beyond the basic SELECT/INSERT/etc... so this is probably just something I don't understand but my problem is that my stored procedure seems to only return the last row of the result set (i.e. if it should return 10 items it only shows the last one (number 10)). When I run the stored procedure in the query analyzer it runs without any errors but it only displays the last item; here's the procedure (this is MS SQL Server 2008R2 if that makes any difference): [CODE]USE [NISUSInvyControl] GO /****** Object: StoredProcedure [dbo].[NISUS_GetEDIDocTypes] …

Member Avatar
Member Avatar
+0 forum 3

Hai, I Have an sp like this: [CODE]ALTER procedure [dbo].[SP_ClassInsertion] @ClassType varchar(50), @ClassTypeId varchar(50), @ClassName varchar(50), @SchoolId varchar(50), @ClassID varchar(50), @BatchId varchar(50), @Userid uniqueidentifier, @ClassCategoryId varchar(50), @SchoolClassCategoryId varchar(50) as declare @STATUS int begin if exists(select Class_Id from SCH_Class where Class_Name=@ClassName and School_Id=@SchoolId and Is_Valid=1) begin set @STATUS=0 end else begin insert into SCH_Class (Class_Id,Class_Type_Id,Batch_Id,School_Id,Class_Name,Created_Date,Updated_Date,Created_By,Updated_By,Is_Valid) values (@ClassID,@ClassTypeId,@BatchId,@SchoolId,@ClassName,GetDate(),GetDate(),@Userid,@Userid,1) set @STATUS=1 return @STATUS end [/CODE] Can u please help me to execute this sp and get the return value from it. Regards, sreevidya

Member Avatar
Member Avatar
+0 forum 7

Hi Guys, I have imported an excel document into MySQL to tidy up and make better use of the data but I am having problems running an update. All tables used as examples below; [U]Tables[/U] asset_register phone sim team The asset_register contains the following cols; [CODE] id INT, serialNo VARCHAR, simNo INT, team VARCHAR [/CODE] The problem is that I want to create a team table and the reference the teamId in the asset_register table. Does that make sense? Instead of storing the actual team name i want a foreign key to the teams table. The problem lies in updating …

Member Avatar
Member Avatar
+0 forum 1

hi, i have an application that feeds data into a table of a database - table name is XTABLE716C6DC3. now i need a stored procedure that takes and calculates data from that dable anytime the table is changed and store the results in a certain format into a new table - lets call that table XTABLEOVERVIEW the calculations i need to be done are the following: totals for all entries sorted by date - sum of the column FLT_REVENUE_E48E42AD sorted by date which is stored in column DT_STARTDATE_318FA6EE code should look somewhat like this: [CODE]SELECT DT_STARTDATE_318FA6EE,SUM(CONVERT(DECIMAL(10,2),FLT_REVENUE_E48E42AD)) FROM dbo.XTABLE716C6DC3 GROUP BY …

Member Avatar
Member Avatar
+0 forum 18

In my application some stored procedures are not working properly in current database of SQL Server. I have taken backup of database 2 weeks ago and all stored procedures are working in backup database. I have checked the properties of current database and backup database both are same. After restoring the backup all procedure are working properly but in current database some procedures are not working. Could anybody can help me to solve this problem. Thanks in advance. Joseph

Member Avatar
Member Avatar
+0 forum 3

Hello, im new to stored procedure and never really used C. For a project i have to write stored procedures and execute them in C. I have written my stored procedures in MySql Workbench and I have Visual Basic 2010 express and Visual studio 2008 installed. How do i execute a stored procedure in C.....in either one of these programs?? thnx in advance...jenny

Member Avatar
Member Avatar
+0 forum 1

I'm using a linux shared hosting account with godaddy and I can't seem to get stored procedures working correctly with my MySQL database. I can create them from within phpMyAdmin fine. I can also create them in MySQL workbench fine. However, whenever I try to call the stored procedures either from inside the SQL section of phpmyadmin or from a php page, I get an error: "PROCEDURE databaseName.ProcedureName can't return a result set in the given context". The database recognizes that the procedure exists. Also, this is how I am creating the procedure: USE `databaseName`; DROP procedure IF EXISTS `ProcedureName`; …

Member Avatar
Member Avatar
+0 forum 1

Hi I am working on a procedure to log the users and times of our reports. Our reports are all executed from stored procedures. The generic logging proc which will be placed in the 30+ report procedures. I'm having a problem with getting the start and end times for the report. I know I can get both times by putting the current time in a variable before I execute the report and doing the same afterwards. This is however a last option. Currently, I call the proc before I execute a report, that way it gives me the start time …

Member Avatar
Member Avatar
+0 forum 1

hi, Am having a problem Inserting into SQL 2008 database using stored procedure from a php webpage. My Stored Procedure is: [code=text] Create PROCEDURE [dbo].[udp_AddContact_Insert] @Contact_ID int, @Client_ID int, @STD varchar(10), @Tel varchar(20), @Mobile varchar(20), @EmailAddress varchar(100), @FirstName varchar(20), @LastName varchar(20), @Title_ID int, @Position_ID int, @User_ID int, @SourceURL varchar(1000), @PositionText varchar(200), @TitleText varchar(50), @NewContact_ID int output AS BEGIN SET NOCOUNT ON IF LEN(@PositionText)>0 BEGIN INSERT INTO dbo.tblPosition (Position) VALUES (@PositionText) SET @Position_ID = @@identity END IF LEN(@TitleText)>0 BEGIN INSERT INTO dbo.tblTitle ([Client_ID], [Title]) VALUES (@Client_ID, @TitleText) SET @Title_ID = @@identity END INSERT INTO [dbo].[tblContact] ([Client_ID] ,[STD] ,[Tel] ,[Mobile] ,[EmailAddress] ,[FirstName] …

Member Avatar
Member Avatar
+0 forum 1

I know, is a very basic question, but I am in the process of re-learning sql stored procedures, and this is what happended: I was practicing with some simple delete / insert routines, when encountered with this: if I ONLY insert records: 1. dog 2. cat 3. t-rex 4. llama all is ok, then I call a delete procedure, passing the colum_ID to identify the record and delete it. This is done correctly and all is OK Then I call a insert procedure once again and the insert happens like this: 1. dog 2. cat 5. snake **now this is …

Member Avatar
Member Avatar
+0 forum 2

Hello DW Forum, i am a new member between you and this is my first post i am building a website mostly uses Stored Procedure, but now i am facing a problem such that i want to call the SP more than one time from the .vb page this is my SQL SP: [ICODE]ALTER PROCEDURE dbo.plnCs @plan int, @level int AS SELECT p.CNo, c.CLevel FROM Plans p, Course c WHERE p.CNo=c.CNo AND p.[Year]=@plan AND c.CLevel=@level RETURN[/ICODE] and this is my VB statements that call the SP: [ICODE]... csrDa.SelectCommand.CommandType = CommandType.StoredProcedure csrDa.SelectCommand.Parameters.AddWithValue("@plan", plan) csrDa.SelectCommand.Parameters.AddWithValue("@level", x)[/ICODE] the values of x range from …

Member Avatar
Member Avatar
+0 forum 8

I'm trying to create login form in C#. I store username and password in SqlServer 2008 express using the stored procedure [CODE]ALTER procedure [dbo].[InsertNewUser] ( @UserName varchar(30), @Password varchar(30) ) as If exists(Select 'True' from Users where UserName = @UserName) begin --This means that the user name is taken select 'User already exists' end else begin --This means that the username is available select 'New user created' Insert into Korisnici (UserName,UserPassword) values (@UserName, @Password) end[/CODE] How to check in C# when the new user is available, and when the username is taken? When the selected username is available do the …

Member Avatar
Member Avatar
+0 forum 3

Hi, I am having problems inserting data into a table using a stored procedure. When I click the button to insert the data nothing happens at all. I added a [B]server.transfer[/B] after the insert to see if it was falling over before the end of the query/insert. My code behind the button to insert the data is as follows... [CODE] Imports System.Data Imports System.Data.SqlClient Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click Dim ConnString As New SqlConnection ConnString.ConnectionString = "Data Source=.\SQLEXPRESS2008;Initial Catalog=mkb;Integrated Security=True" ConnString.Open() Dim SQLCmd As New SqlCommand SQLCmd.CommandText = "usp_insertError" 'name of SP SQLCmd.CommandType …

Member Avatar
Member Avatar
+0 forum 15

[CODE]<?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="Samples.Controllers" schema="I_USER" assembly="Samples" > <sql-query name="PROCEDURE1" callable="true"> <!--<return class="empdetails.cs" />--> <return-scalar column="iusrfname" type="String" /> <return-scalar column="iusrlname" type="String" /> <return-scalar column="iusrlinname" type="String" /> <return-scalar column="iusremailid" type="String" /> <return-scalar column="iusrmobileno" type="String" /> <return-scalar column="iusrrmrks" type="String" /> <!--<return-scalar column="iusrcreated" type="DateTime" />--> call PROCEDURE1(:pr_userid,:iusrfname,:iusrlname,:iusrlinname,:iusremailid,:iusrmobileno,:iusrrmrks)<!--,:iusrcreated)--> </sql-query> </hibernate-mapping>[/CODE]

Member Avatar
+0 forum 0

I have a stored procedure in SQL Server 2008 that follows: USE [Numbers] GO /****** Object: StoredProcedure [dbo].[thenextnum] Script Date: 09/23/2009 15:35:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[thenextnum] @descrip varchar(50) as begin declare @t table (nextseq varchar(100)) update number_definition_table set Current_Number=Current_Number+1 output inserted.prefix_format +(Case WHEN inserted.leading_hyphen = 'NONE' then '' ELSE inserted.leading_hyphen END ) --+ inserted.leading_hyphen +right( replicate('0',inserted.Character_Count) +convert(varchar(50),inserted.Current_Number) ,inserted.Character_Count) +(Case WHEN inserted.trailing_hyphen = 'NONE' then '' ELSE inserted.trailing_hyphen END ) into @t where descriptor = @descrip select nextseq from @t end It needs to get passed in a value from VB.net for the …

Member Avatar
Member Avatar
+0 forum 4

The End.