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


Top 5 Contributors of the Month
Sharon Maxwell
Post New Web Links

inserting data after caliculation using stored procedure

Posted By:      Posted Date: September 15, 2010    Points: 0   Category :ASP.Net
 
hi my requirement is like i want to insert data to table after caliculationliketable name salaryhikecolumns salaryinput,20%hike, 25%hike and 30%hikesalaryinput column details  to be input by the user and hike is to be caliculated and inserted with 20%hike, 25%hike and 30%hike are columns of same table.plz help me.thank you very much.


View Complete Post


More Related Resource Links

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.

Inserting rows via stored procedure and under certain conditions

  

I'm using Dynamic Data with Entity Framework in VS2010.

Let's say my table has these fields:

MyTable
ID (PK)
PersonID (FK)
LocationID (FK)
TotalItemsSold

Hypothetical scenario (it's easier for me to explain this way, so just bear with me for now)... But let's say each row in the table represents "how many items were sold by such-and-such employee at such-and-such location," where location and person are foreign keys which are referencing other tables.  Basically, there should be no more than a ONE row which has a particular combination of Person and Location.  Makes sense?

So, when inserting new rows using my Dynamic Data app, the insert form displays editable fields for Person (dropdown), Location (dropdown), and Items Sold (textbox).  How do I prevent users from inserting another row into the table containing an already-existing combination of Person and Location?   How do I displaying useful feedback to them in the event that they DO attempt to do this?

I have several thoughts about this, but since I'm new to Dynamic Data, I'm not sure which way to go.  For example:

Option 1:  Use "cascading dropdowns" approach in the insert form and only pull in the "allowed" combinations of the two dropd

Trying to run a stored procedure from vb code with oracle data provider.

  

Hello,

Here is my SP:

create or replace
PROCEDURE ZGETUSERSSIGNONS (vUid IN VARCHAR2, p_getuserssignon_recordset1 OUT SYS_REFCURSOR) AS
BEGIN
 Open p_getuserssignon_recordset1 for
 SELECT Distinct(Userid), UserPassword, SecurityLevel, ActiveStatus
FROM ZSIGNON
WHERE substr(UserId,1,2) <> vUid
Order By UserId;
END ZGETUSERSSIGNONS;

I would like to run this SP from code and fill a gridview with the result. 

I am not sure how to go about this, as I have found several different examples, other than the one I  think I need.

I am using the oracle data provider and I have an input parameter (vUid, which will equal "zz").

First question. When filling a gridview with a result set from a stored procedure should the recordset OUT be defined as a REFCURSOR (like i did above)? 

Second:

Do you have example code as to how to execute the SP and fill a gridview?  I keep trying different variations of code i've found on the internet without any success other than getting more confused.

(I am using VS 2005, VB).

Thank you.

 

 


Entity Framework 4.0 Dynamic Data - stored procedure

  

I'm using Dynamic Data and have created a custom List.aspx page.  I simply want to populate the grid with a stored procedure.  I've seen some posts about doing this with a LinqDataSource but not an EntityDataSource.  Does someone have an example or a different way of doing this?  Thanks.


can a Workflow access a stored procedure and pass the parameters from the list data to the stored pr

  
The reason that I would like to consider this functionailty is because my table architecture is complicated and I do not want to modify my master table to accept all of this data where some of the data should be normalized into sub tables.  Has anyone see evidence of the stored-procedure parm approach?  Is this best accomplished through VS 2010 or can I do it through SPD? Thanks

No Return Type for a Stored Procedure in a Linq to SQL Data Layer

  
Hello. First, I don't know if this is the right forum section to post this in, but I couldn't fine a more suitable one. Feel free to move this if it's totally worng. I have a weird problem when using LINQ to SQL and Store Procedures with a return value... The problem is that when I drag my SP's onto the "Methods Pane" the property field "Return Type" is set to (None) and greyed out. And here's the most weird thing... I created a test SP yesterday wich returned SCOPE_IDENTITY() and it worked flawlessly. Dragged it onto the "Methods Pane", could play with the return type property (hence it was not greyed out) and everything was working out nice. I used a ISingleResult<T> and could fetch the .ReturnValue... Today I was going to contiune to create the rest of the SP's i needed with a return type but today when I drag my Store Procedures to my Methods Pane, I couldn't access the Return Type property... I even dragged the SAME procedure I used yesterday (wich then worked great) and now I can't get the dbml-layer to notice that there is a return value... I've tried to create new solutions, new Data Layers, used different databases/servers, but still the same problem... Don't really know if it's LINQ or something else that's causing this... This is how part of my SP wich I used yesterday to test looks

ORA-06550, PLS-00306; Error inserting data to Oracle procedure.

  

I have tried to troubleshoot this problem but to no avail. I get error while inserting a big chuck of data to Oracle package containing procedure 'INSCRAPP'. The error message is,

ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'INSCRAPP' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

The C# code is big but I am providing the parameters I am passing,

oraCmd.Parameters.Clear();

                OracleParameter param_fName_in = new OracleParameter("fName_in", OracleType.VarChar);
                param_fName_in.Value = FirstName;
                param_fName_in.Direction = ParameterDirection.Input;
                oraCmd.Parameters.Add(param_fName_in);
                
                OracleParameter param_mi_in = new OracleParameter("mi_in", OracleType.VarChar);
                param_mi_in.Value = MiddleInitial;
                param_mi_in.Direction = ParameterDirection.Input;
                oraCmd.Parameters.Add(param_mi_in);

                OracleParameter param_lName_in = new OracleParameter("lName_in", OracleType.VarChar);
                param_lName_in.Value = LastName;
                param_lName_in.Direction = ParameterDirection.Input;
                oraCmd.Parameters.Add(param_lName_in);

                OracleParameter para

How to write Stored Procedure for Insert Data & Execute it in MS SQL?

  

How to write Stored Procedure for Insert Data & Execute it in MS SQL?


Use a stored procedure to return data for algorithm

  

Hi,

I am looking at using the time series algorithm to predict a products future.

However, I seem to have missed the obvious. How do I return a subset of a tables data as the input to the algorithm?

Can I specifiy a SP to use or do I specify the parameters within DMX?

 

Thanks


Getting data from a stored procedure instead of a table

  

Using linq2sql, I've seen examples of using stored procedures for insert, update and delete but how can I use a stored procedure for select?

Thanks, Simon


Getting data from a stored procedure instead of a table

  

Using linq2sql, I've seen examples of using stored procedures for insert, update and delete but how can I use a stored procedure for select?

Thanks, Simon


help inserting an empty string and a default date thru a stored procedure

  

I want to insert an empty string in a nvarchar field and a default date like a base date in a datetime field inside a stored procedure without passing it the value thru a parameter

how is this done?

INSERT INTO deals_SoldItems (

deals_SoldItems.[TransactionID],

deals_SoldItems.[EmptyStringField],

deals_SoldItems.[BaseDateField]

)

VALUES (

@TransactionID,

"",

"01/01/1900

)

 

this dont work

cheers

 


SSIS DB2 Stored Procedure as a data source

  

Hi,

I have this problem:

In a DTS package I can connect to a DB2 on an AS400 and execute a stored procedure. I can then import this data into a table.

 

I can't do this in SSIS. DTS uses an ODBC connection. I have tried this with SSIS 2008 r2 – I can use a ado.net connection which picks up the ODBC driver.

I pass in the stored proc as a sql command.

I click preview and all the data is there. I click columns and none are defined.

 

Has anybody gotten this to work? It seems like a step back to me- DTS did

Capturing data through a stored procedure as a result of a click event (asp.net)

  

Hello,

I am having trouble capturing data as a result of a click event. Basically, I don't know what code to put and where for the click event.

I need to record information from a text box, into a table in Microsoft SQL Server. Any help would be great.

My stored procedure is below:

/****** Object:  StoredProcedure [dbo].[SearchConcept_SP]    Script Date: 10/23/2010 12:17:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SearchConcept_SP]
@Concept_Name nvarchar(50),
@Keywords char(50)

AS
BEGIN
IF EXISTS(SELECT Concept_Name FROM Concept WHERE Concept_Name = @Concept_Name)
PRINT 'This concept already exists'

ELSE
INSERT INTO Concept VALUES (@Concept_Name, @Keywords)
END


transfer data from a database to another using a stored procedure

  

I need to transfer data  from a database to another using a stored procedure in a C# application. I have some tables in the first database and I need to transfer data from them in others tables from the second database, not all columns but few of them.

How should I do that?

 

Thank you for your time!

 


ucsel

Need assistance obtaining the correct data and layout in a stored procedure

  

So let me start out by describing my tables:

Project Table: ProjectID (PK), Project Name, etc.

Buyer Table: BuyerID (PK), ProjectID (FK), Buyer Type*, Buyer Status**

*There are 3 buyer types, for simplicity lets call them A, B, C

**There are 12 different buyer statuses

In my stored procedure my parameter is ProjectID. What I want to do is count the number of buyers for a project that have each status. The kicker is that I need the buyers in each status (for a particular project) segmented by buyer type (A, B, C). The result would probably look like this:

 

Status             A                B                  C

First                1                2                   1

Second           2                 1    &

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,

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