my requirement is like i want to insert data to table after caliculation like table name salaryhike columns salaryinput,20%hike, 25%hike and 30%hike salaryinput 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.
View 4 RepliesI have stored proc where i insert some value from openxml but it is not inserting that xml data. Below is my stored proc.
[Code]....
[Code]....
[Code]....
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:
[code]....
I have a problem described below
Set @StartMonth = Aug
Set @EndMonth = Jan
Set @StartYear = 2009
Set @EndYear = 2010
I need to insert records into one new table based on the above informations
Month name should go from StartMonth to EndMonth and Year also should insert like below
MonthName Year
Aug 2009
Sep 2009
Oct 2009
Nov 2009
Dec 2009
Jan 2010
2) when I pass again
Set @StartMonth = Dec
Set @EndMonth = Feb
Set @StartYear = 2009
Set @EndYear = 2010
It should check for the records already in the database and should not insert the duplicate record. Based on the above case it should insert only one record
MonthName Year
Feb 2010
3) I need to also calculate number of days in the month except sat and sunday ..
Like Jan2010 we have 21 days except Saturday and Sunday
Is there any logic through which we calculate the number of days.?
I have a database table as follows:
[Code]....
This table receives data from my web application via a stored procedure, snippet pasted below:
[Code]....
In my quote.aspx page, I have a wizard control that collects numerous data points. In one of the wizard steps, I have 20 textbox controls for PartNumbers and 20 textbox controls for respective Quantity.
Question:How do I write a for..each loop that checks for values in my Part Number and Quantity fields and inserts them via my SQLDataSource?
I have used in line queries for inserting and retrieving data from database. How should i use stored proceduress
Insertion code
string strSQL1 = "select * from cust_details";
DataSet ds = new DataSet();
SqlConnection m_conn;
SqlDataAdapter m_dataAdapter;
m_conn = new SqlConnection(conn);
[Code] ....
Retrieval code
try {
SqlConnection conn3 = new SqlConnection(conn);
String q1;
//string ddl = DropDownList1.SelectedItem.ToString();
q1 = "select * from Product where ID ='" + DropDownList1.SelectedItem.ToString() + "'";
SqlCommand cmd = new SqlCommand(q1, conn3);
[Code] .....
is there a function for adding days to a date value within a stored procedure?If I had a field with a start date and a field for duration which would be how many days the even lasts....like if I wanted something like this:TableName.StartDate, DateAdd(TableName.StartDate, TableName.Duration) as EndDate, etc
View 4 RepliesI could probably figure this out if I tried to, but I have been working so long on code, I'm a little fried
I have a stored procedure, and I want to execute another stored procedure during a time period of lets say 1/1/2011 to 12/31/2011
How Would I accomplish this?
I have created stored procedure and student database and also asp.net application for asp.net page but it could not found stored procedure what is the mistake actually I don't no
View 7 Replieshow can I create stored procedure and write my select statement in it, I know how to create dataset then put type stored procedure and assign it to the sp ... what I want is writing my sp... how can I make it ?
check the following code, this is what I want to write put I don't know where or how !
CREATE STORED PROCEDURE SP_CATEGORY
@CATEGORY VARCHAR(30)
AS
BEGIN
SELECT LATIN_NAME, ENGLISH_NAME, ARABIC_NAME, CATEGORY
FROM FLORA, CATEGORY_LIST
WHERE FLORA.CATEGORY=CATEGORY_LIST.CATEGORY_NAME AND CATEGORY_LIST.CATEGORY_NAME IN (SELECT * FROM SPLITLIST(@CATEGORY, ','))
END
where can I write this code ?!
I am trying to create a stored procedure that filters data from sql server by date but i get this error:
Conversion failed when converting datetime from character string.
my stored procedure is like:
[Code]....
the parameters are passed to the stored procedures as dateTime variables from c# code...
I am trying to create the following stored procedure in sql server Lat and Lng are the parameters being passed from c# code behind .But I am not able to create this stored procedureit indicates with error saying undefined column name Lat,Lng
CREATE FUNCTION spherical_distance(@a float, @b float, @c float)
RETURNS float
AS
BEGIN
RETURN ( 6371 * ACOS( COS( (@a/@b) ) * COS( (Lat/@b) ) * COS( ( Lng/@b ) - (@c/@b) ) + SIN( @a/@b ) * SIN( Lat/@b ) ) )
END
sqlda.SelectCommand.CommandText = "select *, spherical_distance( Lat, 57.2958, Lng) as distance
from business
[code]...
I've a table (named NoAccent) with a colum 'Title'. I need to write a store procedure so that if any special characters found in the title it should update (I'll set the sql agent to run the procedure in a specific time interval) e.g.
[Code]....
How to write Stored Procedure for Insert Data & Execute it in MS SQL?
Some websites:
[URL]
Using vs.net 2005 and sql server 2005.
Hope this makes sense: I need to create a stored procedure that I am going to use as a datasource for a crystal report in vs.net.
The issue is that the data is stored in 2 different kinds of databases, one is sql server and the other is plb files.
I have an odbc connections created for each separately however I need to make one query that joins data fields from both the sql server db tables and the plb db, how do I do this in one query that I will use in a stored procedure?
This is surareddy. i nead some small clarification in the "Stored Procedure"
how to convert the oracle Stored Procedure to sqlserver2005/2008 Stored Procedure.
right now i am enhancing the project that project already developed the oracle Stored Procedure. now our company is using sqlserver 2005/2008.
how to convert the Oracle Stored Procedure to sqlserver 2005 Stored Procedure
Initially, I have tried to use stored procedure. But I changed my mind and preferred to call sql query in codebase with command text. However, it stills tries to find initially-called stored procedure (which is neither called or exists).I think that it is related caching. But I tried it with different browsers it did not work.What might be the reason?
View 4 RepliesI'm trying to use a stored procedure in my codebehind insted of a sqldatasource to insert some data, and return the identity fields. But I keep getting tripped up on the output paramaters. What am I missing?
My code behind:
[Code]....
And, here's my stored procedure
Create PROCEDURE [dbo].[123]
(
@MatterToCloneID varchar(15),
@ClonedMatterID int OUTPUT,
@ClientIDOUT int OUTPUT,
@ExistingClientIDOUT int OUTPUT
)
AS
BEGIN
DECLARE
@ExistingClientID int,
@PendingClientID int,
@BillingAddress varchar(200),
@BillingCity varchar(200),
@BillingState varchar(100),
@BillingZipCode varchar(20),
@BillingCountry varchar(200)
SELECT @ExistingClientID = ExistingClientID, @PendingClientID = PendingClientID,
@BillingAddress = BillingAddress, @BillingCity = BillingCity, @BillingState = BillingState, @BillingZipCode = BillingZipCode, @BillingCountry = BillingCountry
FROM tNewMatters
WHERE MatterID = @MatterToCloneID
Set @ClientID = @EClientID
Set @PendingClientID = @PendingClientID
Set @BillingAddress = @BillingAddress
Set @BillingCity = @BillingCity
Set @BillingState = @BillingState
Set @BillingZipCode = @BillingZipCode
Set @BillingCountry = @BillingCountry
INSERT into tNewMatters
(MatterName, ClientID, PendingClientID, BillingAddress, BillingCity, BillingState, BillingZipCode, BillingCountry)
Values
('Enter Client Name', @ExistingClientID, @PendingClientID, @BillingAddress, @BillingCity, @BillingState, @BillingZipCode, @BillingCountry)
Select @ClonedMatterID = scope_identity()
Select @ClientIDOUT = @ClientID
Select @ExistingClientIDOUT = @ExistingClientID
END
We have a page where you can insert a record. On the page we have validation to prevent duplicate IP addresses from being submitted for the same location. Now on the procedure side i have a condition that is checked, if you supply data that already exists for a location, then it will be updated. If its not then its inserted.. this all works fine.. issue now that i noticed is that we have a table that is part of the process, if you select say 3 from a drop down for number of emails, then when you submit the page, i have a loop for each of those emails and they are inserted into the email table as individual records.. works great also.. BUT here is my problem.. if i submit say an existing set of data and this time i provide say 1 email vs 3 from the previous time, i need to handle that in the procedure, so that the 3 are deleted and only 1 is now inserted and linked to that record..
Hope this makes sense..
Since im doing a foreach loop based on that dropdown selection, now when i look at my table im getting only 1 record in the email table... since the logic is within the procedure, when the page calls the procedure, it finds tha the record exists, deletes all the records, then only inserts the last record.
Example.
I submit the page with 3 emails today.. everything works and goes in as expected, there are 3 emails and record is created without issues..
Tomorrow someone else comes in from my team and decides to enter the same record data, thats not a problem, because there is an update statement if it already exists.. BUT this time they entered only 2 emails.. the 2 new emails are inserted / updated in the table, but the 3rd email is still in there.. its not removed.. so i tried to add a if exists condition to my procedure, but since the procedure is called in my foreach loop, the first loop sees the records, deletes them and inserts the first email, then on the 2nd foreach loop it deletes that last entry and inserts the next, so by the time it goes thru it has deleted everything it inserted and only captures the last record..
Just tried it now.. and since there was already 1 record in the table, i chose to add 4.. the end result should be 4, but i now have 5 records in there.. the first one and the 4 new ones..
[Code]....
i have a stored procedure with this values and i need just to make a button that will send something to "Equip" column,how to do it? write the values on my webpage and make a button to exec the procedu
i want data to drop down list using stored procedure .
However im not able to do that.
Does anybody if it is possible that a stored procedure returns rows which is the result of the execution of another sp? Something like..
[Code]....
i want to return output parameter from 1 storeprocedure. into another stored procedure.
View 7 RepliesServer Error in '/staff' Application. Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'. Source Error:
[Code]....
Stack Trace:
[Code]....
Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.1
The following stored procedure updates the value in LeaveTransaction table.
[code].,...
My Question here:
I have written the above trigger,which would update the value for the corresponding records in another table ie) LeaveCumulative table. The trigger is not updating the value in the designated rows in the leavecumulative table. Whereas when I excute the same update command separately in the Query Editor window the trigger is working fine.