Update And Delete Stored Procedures Not Working

Feb 24, 2006

I grouped everything together so you see it all. I'm not getting any errors but nothing is happening. I had this working and then I converted to Stored Procedures and now it's not.

CREATE PROCEDURE UpdateCartItem
(
@itemQuantity int,
@cartItemID varchar
)
AS
UPDATE CartItems Set pounds=@itemQuantity
WHERE cartItemID=@cartItemID
GO

<asp:Button CssClass="scEdit" ID="btnEdit" Runat="server" Text="Update" CommandName="Update"></asp:Button>

    Sub dlstShoppingCart_UpdateCommand(ByVal s As Object, ByVal e As DataListCommandEventArgs)
        Dim connStr As SqlConnection
        Dim cmdUpdateCartItem As SqlCommand
        Dim UpdateCartItem
        Dim strCartItemID As String
        Dim txtQuantity As TextBox
        strCartItemID = dlstShoppingCart.DataKeys(e.Item.ItemIndex)
        txtQuantity = e.Item.FindControl("txtQuantity")
        connStr = New SqlConnection(ConfigurationSettings.AppSettings("sqlCon.ConnectionString"))
        cmdUpdateCartItem = New SqlCommand(UpdateCartItem, connStr)
        cmdUpdateCartItem.CommandType = CommandType.StoredProcedure
        cmdUpdateCartItem.Parameters.Add("@cartItemID", strCartItemID)
        cmdUpdateCartItem.Parameters.Add("@itemQuantity", txtQuantity.Text)
        connStr.Open()
        cmdUpdateCartItem.ExecuteNonQuery()
        connStr.Close()
        dlstShoppingCart.EditItemIndex = -1
        BindDataList()
    End Sub

____________________________________________________________

CREATE PROCEDURE DeleteCartItem
(
@orderID Float(8),
@itemID nVarChar(50)
)
AS
DELETE
FROM CartItems
WHERE orderID = @orderID AND itemID = @itemID
GO


<asp:Button CssClass="scEdit" ID="btnRemove" Runat="server" Text="Remove" CommandName="Delete"></asp:Button>

    Sub dlstShoppingCart_DeleteCommand(ByVal s As Object, ByVal e As DataListCommandEventArgs)
        Dim connStr As SqlConnection
        Dim cmdDeleteCartItem As SqlCommand
        Dim DeleteCartItem
        Dim strCartItemID
        strCartItemID = dlstShoppingCart.DataKeys(e.Item.ItemIndex)
        connStr = New SqlConnection(ConfigurationSettings.AppSettings("sqlCon.ConnectionString"))
        cmdDeleteCartItem = New SqlCommand(DeleteCartItem, connStr)
        cmdDeleteCartItem.CommandType = CommandType.StoredProcedure
        cmdDeleteCartItem.Parameters.Add("@cartItemID", strCartItemID)
        connStr.Open()
        cmdDeleteCartItem.ExecuteNonQuery()
        connStr.Close()
        dlstShoppingCart.EditItemIndex = -1
        BindDataList()
    End Sub

View 2 Replies


ADVERTISEMENT

SQL 2012 :: Generate Stored Procedures For Select / Insert / Update / Delete On Certain Tables?

Apr 3, 2015

Is there a way in SQL server that can generate stored procedures for select, insert, update, delete on certain tables?

View 4 Replies View Related

How To Use Update/delete Feature Of VS 2005 For Working With A SQL Server Table.

May 1, 2008

 Hi all,
i m using VS 2005 and I have to  display records with feature of INSERT / DELETE ITEMS But when i connect to Sql Server Database and select * from columns but here when clicking the "Advance" button , i do not get "Advance Sql generation Option "  highlighted. Instead , it is turned off. i.e
 The Following options are not highlighting
------ Generate Insert, Update, Delete  statements
------ use optimistic concurrency
Plz guide me anyone..... is anything wrong with our VS 2005 software installed?
Bilal

View 4 Replies View Related

SqlParameters Not Working With Stored Procedures

Dec 31, 2006

Anyone can help me why is this not working?
My function is
public void updateSupplierInformation(string FirstLetter, string Vendor, string SegmentTeam, string VendorRank, string ScrubOwner)
{
SqlCommand myCommand = new SqlCommand("sp_LAST_UpdateSupplier", myConnection);
 
myCommand.CommandType = CommandType.StoredProcedure;
myConnection.Open();
SqlParameter myParam;
myParam = myCommand.Parameters.Add(new SqlParameter("@FirstLetterS", SqlDbType.VarChar, 1));
myParam.Direction = ParameterDirection.Input;
myParam.Value = FirstLetter;
myParam = myCommand.Parameters.Add(new SqlParameter("@VendorS", SqlDbType.VarChar, 30));
myParam.Direction = ParameterDirection.Input;
myParam.Value = Vendor;
myParam = myCommand.Parameters.Add(new SqlParameter("@SegTeamS", SqlDbType.VarChar, 30));
myParam.Direction = ParameterDirection.Input;
myParam.Value = SegmentTeam;
myParam = myCommand.Parameters.Add(new SqlParameter("@VendorRankS", SqlDbType.VarChar, 30));
myParam.Direction = ParameterDirection.Input;
myParam.Value = VendorRank;
myParam = myCommand.Parameters.Add(new SqlParameter("@ScrubOwnerS", SqlDbType.VarChar, 30));
myParam.Direction = ParameterDirection.Input;
myParam.Value = ScrubOwner;
 
int i =myCommand.ExecuteNonQuery();
 
myConnection.Close();
}
My Stored procedure is:
ALTER PROCEDURE sp_LAST_UpdateSupplier
@FirstLetterS varchar(1),
@VendorS varchar(30),
@SegTeamS varchar(30),
@VendorRankS varchar(30),
@ScrubOwnerS varchar(30)
AS
UPDATE [gntm_user].LAST_Supplier_Ownership
SET First_Letter =@FirstLetterS,Seg_Team =@SegTeamS,Vendor_Rank =@VendorRankS,
Scrub_Owner =@ScrubOwnerS
WHERE Vendor=@VendorS
 
RETURN
 
When I debug on any of the myParam value it displays "@FirstLetterS,@VendorS" etc. not the value which is being passed in the function which is FirstLetter,Vendor.
Can anyone help me on this please? Thanks in advance

View 2 Replies View Related

Working Woth Stored Procedures

May 6, 2007

Hi,
I am working on VS 2005 and I am using the grid view with Stored procedure the case is ..I want the grid to view all the supplier records by default .. but if the user specified a supplier name and/or type the grid should onlyview the relevant records..
What i did is .. I have created a text box (to enter the supplier name) and a drop down list (to choose the type)
** The problem is the grid does not appear unless I choose and fill the text box and drop down list and it only works with the type notwith the name ((does not match both parameters))
Can you please help I am stuck !!--------------------------------
My procedure is as following:
ALTER PROCEDURE p_test
@SupName nvarchar(200)='',@TypeID int= 0
AS
declare @SQL_Text varchar(8000)set @SQL_text = 'select * from dbo.v_supplier' +' where 1 = 1'
if rtrim(ltrim(@SupName)) <> ''set @SQL_Text = @sql_text + ' and name = '''+ @SupName +''''
if @TypeID <> ''set @SQL_Text = @sql_text + ' and type_id = '+ CONVERT(varchar, @TypeID)
if CONVERT(varchar, @TypeID)= '0'set @SQL_Text = @sql_text
BEGIN
exec(@sql_text)
END
----------------------------------------
My grid parameters is as following:
<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSourceSup"></asp:GridView>
<asp:SqlDataSource ID="SqlDataSourceSup" runat="server" ConnectionString="<%$ ConnectionStrings:con %>" SelectCommand="p_test" SelectCommandType="StoredProcedure">
<SelectParameters><asp:ControlParameter ControlID="txtSup" Name="SupName" PropertyName="Text" Type="String" DefaultValue="" /><asp:ControlParameter ControlID="drpType" Name="TypeID" PropertyName="SelectedValue" DefaultValue = 0 Type="Int32" /></SelectParameters>
</asp:SqlDataSource>

View 4 Replies View Related

Adding Delete To Stored Procedures

Aug 31, 2006

I have a stored procedure that Inserts data from one table to the next, I need to add a delete statement to it. I jusually just use the delete option in Access 2003 but I have decided it would be easier to just delete from original table through the sp, only problem is I dont rememeber how to incorporate it into my SP, although at one time I did have it in there then I took it out


REATE PROCEDURE InsertTerms
AS
INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION] ( [TM #],
[FirstName],
[LastName],
[SocialSecurityNumber],
[DateHired],
[Status],
[Title],
[DepartmentName],
[Pictures])

SELECT a.TM#, a.FirstName, a.LASTNAME, a.SSN#, a.HIREDATE, a.STATUS, a.JOBTITLE, a.DEPT#, a.PICS
FROM EmployeeGamingLicense AS a
WHERE a.STATUS = 'TERMINATED'
IF @@Error <> '0'
RETURN


GO

View 4 Replies View Related

Is There An Easy Way To Delete Stored Procedures?

Mar 21, 2007

Hi All,

In SQL server 2000 enterprise manager you were able to select multiple stored procedures and drop them all in one go. In 2005 all the stored procs are listed differently (in a tree view) and you cannot therefore select multiple SP for deleteing. Is there another way to accomplish this?

Danny

View 3 Replies View Related

Debugging Stored Procedures With Visual Studio.net Has Stopped Working

Mar 17, 2004

Hi, I used to be able to debug stored procedures via Visual Studio.net 2003. However, this has stopped working. It does not produce an error just simply doesn't work anymore i.e. the breakpoints are by-passed.
I have the correct settings in the Debug configuration section. If any-one knows how to rectify this your help would be appreciated.
I have thought about re-installing the remote debugging functionality on the server. However, our Visual Studio.net discs are with a developer who is away at present.

Thanks in advance
Lee

View 2 Replies View Related

Transfer SQL Server Objects Task Not Working With Stored Procedures

Sep 17, 2007



I'm unable to copy my Stored Procedures from one database to another. I'm using mixed mode authentication. I have set CopyAllStoredProcedures to True, DropObjectsFirst to True and CopySchema to True.


Nothing gets copied. I have followed many web sites that say Transfer SQL Server Objects Task is broken. Is this true and I should give up?

Also, I'm on SQL 2005 SP2 which appears to be the latest and I assume is the update for SSIS? yes ?

Thanks for any help

[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "ERROR : errorCode=-1073548784 description=Executing the query "CREATE PROCEDURE [dbo].[del_Admin_RemoveContractorFromContract] @ContractID int, @ContractorID int AS DELETE FROM CONTRACTOR_CONTRACTS WHERE CONTRACT_ID = @ContractID AND CONTRACTOR_ID = @ContractorID DELETE FROM CONTRACTOR_USER_CONTRACTS WHERE CONTRACT_ID = @ContractID AND CONTRACTOR_ID = @ContractorID " failed with the following error: "There is already an object named 'del_Admin_RemoveContractorFromContract' in the database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".
Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

View 4 Replies View Related

Update Stored Proc Not Working

Jan 12, 2005

I'm trying to run a UPDATE stored proc to allow my users to update records that are in a datagrid. What the update proc looks like is as follows:

Proc sp_UpdateRecords
@fname nvarchar(30), @lname nvarchar(30), @address1 nvarchar(50), @address2 nvarchar(50), @CITY nvarchar(33), @ST nvarchar(10), @ZIP_OUT nvarchar(5), @ZIP4_OUT nvarchar(4), @home_phone nvarchar(22), @autonumber int
AS
UPDATE NCOA20040603 SET fname=@fname, lname=@lname, address1=@address1, address2=@address2, CITY=@CITY, ST=@ST, ZIP_OUT=@ZIP_OUT, ZIP4_OUT=@ZIP4_OUT, home_phone=@home_phone
WHERE autonumber=@autonumber

The message I'm getting is as follows:

Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index


What could be the problem

Any ideas are appriciated -- Thanks in advance
RB

View 5 Replies View Related

Update Using Stored Procedures

Feb 20, 2008

Hi,
I wonder if anyone knows any good web tutorials or books where I can learn how to make update using sprocs (in ASP.NET and MSSQL environment).
Thanks 

View 6 Replies View Related

Update Stored Procedure Not Working When Called From C#

Jul 11, 2007

OK, I have been raking my brains with this and no solution yet. I simply want to update a field in a table given the record Id. When I try the SQL in standalone (no sp) it works and the field gets updated. When I do it by executing the stored procedure from a query window in the Express 2005 manager it works well too. When I use the stored procedure from C# then it does not work:
 1. ExecuteNonQuery() always returns -1 2. When retrieving the @RETURN_VALUE parameter I get -2, meaning that the SP did not find a matching record.
So, with #1 there is definitely something wrong as I would expect ExecuteNonQuery to return something meaningful and with #2 definitely strange as I am able to execute the same SQL code with those parameters from the manager and get the expected results.
Here is my code (some parts left out for brevity):1 int result = 0;
2 if (!String.IsNullOrEmpty(icaoCode))
3 {
4 icaoCode = icaoCode.Trim().ToUpper();
5 try
6 {
7 SqlCommand cmd = new SqlCommand(storedProcedureName);(StoredProcedure.ChangeAirportName);
8 cmd.Parameters.Add("@Icao", SqlDbType.Char, 4).Value = newName;
9 cmd.Parameters.Add("@AirportName", SqlDbType.NVarChar, 50).Value = (String.IsNullOrEmpty(newName) ? null : newName);
10 cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
11 cmd.Connection = mConnection; // connection has been opened already, not shown here
12 cmd.CommandType = CommandType.StoredProcedure;
13 int retval = cmd.ExecuteNonQuery(); // returns -1 somehow even when RETURN n is != -1
14 result = (int)cmd.Parameters["@RETURN_VALUE"].Value;
15
16 }
17 catch (Exception ex)
18 {
19 result = -1;
20 }
21 }

 And this is the stored procedure invoked by the code above:1 ALTER PROCEDURE [dbo].[ChangeAirfieldName]
2 -- Add the parameters for the stored procedure here
3 @Id bigint = null,-- Airport Id, OR
4 @Icao char(4) = null,-- ICAO code
5 @AirportName nvarchar(50)
6 AS
7 BEGIN
8 -- SET NOCOUNT ON added to prevent extra result sets from
9 -- interfering with SELECT statements.
10 SET NOCOUNT ON;
11
12 -- Parameter checking
13 IF @Id IS NULL AND @Icao IS NULL
14 BEGIN
15 RETURN -1;-- Did not specify which record to change
16 END
17 -- Get Id if not known given the ICAO code
18 IF @Id IS NULL
19 BEGIN
20 SET @Id = (SELECT [Id] FROM [dbo].[Airports] WHERE [Icao] = @Icao);
21 --PRINT @id
22 IF @Id IS NULL
23 BEGIN
24 RETURN -2;-- No airport found with that ICAO Id
25 END
26 END
27 -- Update record
28 UPDATE [dbo].[Airfields] SET [Name] = @AirportName WHERE [Id] = @Id;
29 RETURN @@ROWCOUNT
30 END

 As I said when I execute standalone UPDATE works fine, but when approaching it via C# it returns -2 (did not find Id).

View 2 Replies View Related

Stored Procedure For Insert / Update And Delete

Nov 26, 2013

How could I possibly write a SP for Insert, Update and Delete for the below tables under this condition. On some conditions they need to be queried, initially for Insert, first the data should be inserted to PROFILES table and then to ROLES table. When inserting, if an entry is new then it should be added to both the tables. Sometimes when I make an entry which is already present in PROFILES table, then in this case the value should be added to the ROLES table. And a delete query to delete rows from both the table.

CREATE TABLE PROFILES(
USER_ID varchar(20) UNIQUE NOT NULL,
Name varchar(40) NULL,
Address varchar(25) NULL

[code]...

View 5 Replies View Related

T-SQL (SS2K8) :: One Stored Procedure To Do Add / Update / Select And Delete

Apr 29, 2015

I would like to know if it is possible to build one SP to perform all the functions (Add, Update, delete and Select) and then use this in my code instead of making one SP per action. I know this is possible but the update part throws me a little. I used an online example to explain where I fall short on the subject.

USE [SomeTable]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MasterInsertUpdateDelete]

[Code] ....

So using this as the Stored Procedure, how would I update a records Salary alone by the ID without having to use all the information with just the salary being the new value?

View 8 Replies View Related

Make A Copy Of SQL DB And Update/change Stored Procedures

Oct 10, 2006

Can I make a copy of my development database DEV on same SQL SERVER machine, rename it to TEST and stored procedures to be updated automatically for statements likeUPDATE [DEV].[dbo].[Company]SET [company_name] = @company_nameto becomeUPDATE [TEST].[dbo].[Company]SET [company_name] = @company_namein order not to edit each individual stored procedure for updating it ?

View 2 Replies View Related

General Stored Procedure, For Insert, Update, Select, Delete?

May 7, 2007

Hi All,
As known its recommended to use stored procedures when executing on database for perfermance issue. I am thinking to create 4 stored procedures on my database (spSelectQuery, spInsertQuery, spUpdateQuery, spDeleteQuery)
that accept any query and execute it and return the result, rather than having a number of stored procedures for all tables? create PROCEDURE spSelectQuery
(
@select_query nvarchar(500)
)
as
begin

exec sp_executesql @select_query, N'@col_val varchar(50) out', @col_val out


end
 
Is this a good approach design, or its bad???
 
Thanks all

View 5 Replies View Related

Should Insert, Update And Delete Stored Procs Be Wrapped In Transaction?????

Feb 7, 2008

I have looked at the membership and roles stored procs from Microsoft and noticed that most of them are wrapped into a transaction. Ok some of the stored procs updated more than one table in which case it makes sense to wrap the code into a transaction. Our stored procs are a little simpler and insert, update or delete only one table for the most part. My question is: What is good practice, should I wrap my stored procs in transactions or because I am only updating one table leave it the way it is, see sample below: Please advise, newbie
ALTER PROCEDURE [dbo].[syl_Category_Insert] @CategoryName nvarchar(64), @LanguageID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRYINSERT INTO [syl_Categories]
VALUES(
@CategoryName,
@LanguageID)
 SELECT SCOPE_IDENTITY() AS [CategoryID]
RETURNEND TRY
BEGIN CATCH
--Execute LogError_Insert SP EXECUTE [dbo].[syl_LogError_Insert];
--Being in a Catch Block indicates failure.
--Force RETURN to -1 for consistency (other return values are generated, such as -6).RETURN -1
END CATCH
END

View 2 Replies View Related

Should Insert, Update And Delete Stored Procs Be Wrapped In Transaction?????

Feb 7, 2008

I have looked at the membership and roles stored procs from Microsoft and noticed that most of them are wrapped into a transaction. Ok some of the stored procs updated more than one table in which case it makes sense to wrap the code into a transaction. Our stored procs are a little simpler and insert, update or delete only one table for the most part. My question is: What is good practice, should I wrap my stored procs in transactions or because I am only updating one table leave it the way it is, see sample below: Please advise, newbie

ALTER PROCEDURE [dbo].[syl_Category_Insert]
@CategoryName nvarchar(64), @LanguageID int

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

BEGIN TRY
INSERT INTO [syl_Categories]

VALUES(

@CategoryName,

@LanguageID)


SELECT SCOPE_IDENTITY() AS [CategoryID]

RETURN
END TRY

BEGIN CATCH

--Execute LogError_Insert SP
EXECUTE [dbo].[syl_LogError_Insert];

--Being in a Catch Block indicates failure.

--Force RETURN to -1 for consistency (other return values are generated, such as -6).
RETURN -1

END CATCH

END

View 3 Replies View Related

Spaces Problem On Multi Update Delete Stored Procedure

Feb 5, 2008


spaces problem on multi update delete stored procedure
on the the insert i get it like this


58830803, 55329429, 308962604, 55696314, 309128726



as you see only the first empid=58830803 is without spaces
how to fix this
my code




Code Snippet
DECLARE @empid varchar(500)
set @empid ='58830803, 55329429, 308962604, 55696314, 309128726'
DELETE FROM [Table_1]
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0




TNX

View 4 Replies View Related

Writing Insert And Update Stored Procedures For Normalized Schemas?

May 25, 2006

I have a database schema that has an Address table used to store addresses for different entities such as Customers and Employees. I want to reuse the same Address record between different Customers and Employees without duplicating any address information. I'm not sure what the best approach might be.

Should have I have seperate stored procedures on the Address table that update and insert new addresses, where each Address record remains immutable once created? (So the update stored procedure actually creates a new Address record if the data changes). These stored procedures would then be invoked by business logic and used in tandem with stored procedures that act on Customers and Employees to ensure that no address records are duplicated.

Or should I create a view on a Customer joined with Address, and similarily with Employee and Address, and have stored procedures that act on these views and ensure that no Address records are duplicated. Should I use instead of triggers to override the behavior of insert and update on the view to achieve these?

I'm rather lost as to what direction I should take. Any help would be much appreciated, thanks!

View 1 Replies View Related

How To Update Hard Coded Database References In Stored Procedures ?

Feb 6, 2008

Hi There,

Our company deals with financial education and typically has 9 different databases which have some cross referenced stored procedures. Every time we replicate Production database into TEST and DEV environments, we had to manually update the database references in Stored procedures. and it usually takes atleast a week and until then all the dev and test work has to wait.

Hence, I wanted to write a script, Here the code below.


-- These two variables must contain a valid database name.
DECLARE @vchSearch VarChar(15),
@vchReplacement VarChar(15)

SET @vchSearch = 'Search'
SET @vchReplacement = 'Replacement'
/*
-- Select the Kaplan Database Names in the Current Server
*/

DECLARE @tblDBNames TABLE (vchDBName VarChar(30))
INSERT INTO
@tblDBNames
SELECT
Name
FROM
MASTER.DBO.SYSDATABASES
WHERE
Has_DBAccess(Name)=1
And Name IN ( 'DB_DEV', 'DB_TEST', 'DB_PROD', 'WEBDB_DEV', 'WEBDB_TEST', 'WEBDB_PROD' , 'FINDB_DEV', 'FINDB_TEST', 'FINDB_PROD')

--SELECT * FROM @DBNames

IF @vchSearch NOT IN (SELECT vchDBName FROM @tblDBNames)
BEGIN
PRINT 'Not a Valid Search DB Name'
GOTO Terminate
END
IF @vchReplacement NOT IN (SELECT vchDBNAME FROM @tblDBNames)
BEGIN
PRINT 'Not a Valid Replacement DB Name'
GOTO Terminate
END

-- We have Valid DB Names, lets proceed...
--USE @vchReplacement

SET @vchSearch = '%' + @vchSearch + '..%'
SET @vchReplacement = '%' + @vchReplacement + '..%'

-- Get Names of Stored Procedures to be altered
DECLARE @tblSProcNames TABLE (vchSPName VarChar(100))

INSERT INTO
@tblSProcNames
SELECT
DISTINCT so.Name
FROM
SYSOBJECTS so
INNER JOIN SYSCOMMENTS sc
ON sc.Id = so.Id
WHERE
so.XType='P'
AND sc.Text LIKE @vchSearch
ORDER BY
so.name

-- Now, the table @tblSprocNames has the names of stored procedures to be updated.
-- And we have to Some HOW ?!! grab the stored proc definition and use REPLACE() to
-- update the database reference
-- Then, use cursors to loop through each stored proc and upate the reference



Now, I have got stuck how to extract the body of a stored procedure into a variable.


Please Help.... I dont want spend weeks of time in the future to do this work manually.

Madhu

View 24 Replies View Related

I Have One Stored Procedure For Insert, Update And Delete Operations, I Need Automatically Logged

May 27, 2007

I have one stored procedure for insert, update and delete operations, I need automatically logged the insert,update and delete operations.
How can I set auto logged mechanism and how can I access the logs in c#?
Thanks

View 1 Replies View Related

Fastest Way To Delete Hundreds Of Table Triggers And Hundreds Of Stored Procedures?

Jul 20, 2005

How can i delete all user stored procedures and all table triggers very fastina single database?Thank you

View 17 Replies View Related

Oracle Stored Procedures VERSUS SQL Server Stored Procedures

Jul 23, 2005

I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!

View 11 Replies View Related

The Multi Delete &&amp; Multi Update - Stored Procedure Not Work Ok

Feb 4, 2008

the stored procedure don't delete all the records
need help



Code Snippet
DECLARE @empid varchar(500)
set @empid ='55329429,58830803,309128726,55696314'
DELETE FROM [Table_1]
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0




TNX

View 2 Replies View Related

ASP Update Method Not Working After A MSDE To MSSQL 2005 Expess Update

Oct 20, 2006

The Folowing code is not working anymore. (500 error)

Set objRS = strSQL1.Execute
strSQL1 = "SELECT * FROM BannerRotor where BannerID=" & cstr(BannerID)
objRS.Open strSQL1, objConn , 2 , 3 , adCmdText
If not (objRS.BOF and objRS.EOF) Then
objRS.Fields("Exposures").Value =objRS.Fields("Exposures").Value + 1
objRS.update
End If
objRS.Close

The .execute Method works fine

strSQL1 = "UPDATE BannerRotor SET Exposures=Exposures+1 WHERE BannerID=" & cstr(BannerID)
objConn.Execute strSQL1

W2003 + IIS6.0

Pls advice?

View 1 Replies View Related

ExecuteNonQuery - Add Working/Update Not Working

Jan 7, 2004

I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...

This was my test:


Dim cmd As New SqlCommand("pContact_Update", cn)
'Dim cmd As New SqlCommand("pContact_Add", cn)

Try
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text
[...etc more parameters...]
cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = ContactId

cn.Open()
cmd.ExecuteNonQuery()

Label1.Text = "done"
cn.Close()

Catch ex As Exception
Label1.Text = ex.Message
End Try


When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.

I have looked at the stored procedures and the syntax is correct according to SQL Server.

Please I would appreciate any advice...

View 2 Replies View Related

Delete Sql Not Working.

Nov 8, 2004

hi

win 2k and xp
excel 2k
sqlserver version 7
the code below execute but when i query the table, the data is still in there. can anyone help?

Sub UPDATED_DELETE()

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
dim MyDate As Date

MyDate = Format(Date, "MM/DD/YYYY")

Set conn = New ADODB.Connection
Set cmd = New ADODB.Command

conn.ConnectionString = "ODBC=SQL Server;DSN=LOGCALL_TABLE;UID=richard;APP=Microsoft ® Query;WSID=RICHARD;Trusted_Connection=Yes"
conn.ConnectionTimeout = 30
conn.Open

Set cmd.ActiveConnection = conn

cmd.CommandText = "DELETE FROM LOGCALL_TABLE WHERE LOGCALL_TABLE.OpenCall like 'X' AND LOGCALL_TABLE.StopTime like '" & Format(Range("I" & CStr(ActiveCell.Row)).Value, "HH:MM:SS") & "' AND LOGCALL_TABLE.EndTime like '" & Format(Range("J" & CStr(ActiveCell.Row)).Value, "HH:MM:SS") & "' AND LOGCALL_TABLE.ClientName like '" & Range("B" & CStr(ActiveCell.Row)).Value & "' AND LOGCALL_TABLE.Representative like '" & Range("C1").Value & "' and LOGCALL_TABLE.DateOnCall like '" & Date & "';"
cmd.Execute
conn.Close
End Sub

View 4 Replies View Related

Stored Procedures 2005 Vs Stored Procedures 2000

Sep 30, 2006

Hi,



This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.



Thank you in advance for any help on this matter



View 1 Replies View Related

Multiple Row Delete Not Working

Jun 13, 2008

The method I wrote to delete records is working if there's one record, but not for more than one. This method takes a string, and I've examined what is passed in and everything looks ok, but then no error occurs where there's more than one record, but no delete occurs either.
Here's what I see in my Trace.Warn statement:  Delete From Photo_TBL where PhotoID IN ('223,224')
So the sql looks fine but I can't figure out why it's not working. Here's my method for deleting. Can you see what might be wrong? Thanks
  public void PerformDeletion(string photoID)
{
//Response.Write(photoID);

// Set up SqlCommand, connection to db, sql statement, etc.
SqlCommand DeleteCommand = new SqlCommand();
DeleteCommand.Connection = DBConnectionClass.myConnection;
DeleteCommand.CommandType = CommandType.Text;

// Store Primary Key photoID passed here from DeleteRows_Click
// in a parameter for DeleteCommand
SqlParameter DeletePrimaryKeyParam = new SqlParameter();
DeletePrimaryKeyParam.ParameterName = "@PhotoID";
DeletePrimaryKeyParam.Value = photoID.ToString();

// Insert new parameter into command object
DeleteCommand.Parameters.Add(DeletePrimaryKeyParam);

// Delete row, open connection, execute, close connection
DeleteCommand.CommandText = ("Delete From Photo_TBL where PhotoID IN ('" + photoID + "')");
Trace.Warn(DeleteCommand.CommandText);
// DeleteCommand.Connection.Close();
DeleteCommand.Connection.Open();
DeleteCommand.ExecuteNonQuery();
DeleteCommand.Connection.Close();



// Call BindData so GridView is binded and most recent changes appear
BindData();

View 1 Replies View Related

Delete Request IS NOT WORKING!

Dec 21, 2007

Hello,

My delete record from database table below is not working, do anyone here know why delete is not working.







Code Snippet

Private Sub BindingNavigatorDeleteItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorDeleteItem.Click

Dim conn As New SqlClient.SqlConnection(My.Settings.HACS)
Dim strSQL As String
Dim cmd As New SqlCommand
strSQL = ("DELETE FROM CUSTOMER WHERE CUSTOMER_ID =" & Me.IDTextBox.Text)


If conn.State <> ConnectionState.Open Then conn.Open()

If MessageBox.Show("Are you sure you want to delete the current record?", "Confirm Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question, _
MessageBoxDefaultButton.Button2) = Windows.Forms.DialogResult.OK Then

cmd = New SqlCommand(strSQL, conn)

End If

conn.Close()

End Sub

Thanks.

View 4 Replies View Related

Delete Statement With Parameters Not Working

Jun 13, 2008

When I debug my code I see the string going into the parameter correclty, but the the delete statement doesnt work and I'm not sure why. Does this look ok?  // Set up SqlCommand, connection to db, sql statement, etc.
SqlCommand DeleteCommand = new SqlCommand();
DeleteCommand.Connection = DBConnectionClass.myConnection;
DeleteCommand.CommandType = CommandType.Text;

// Store Primary Key photoID passed here from DeleteRows_Click
// in a parameter for DeleteCommand
SqlParameter DeletePrimaryKeyParam = new SqlParameter();
DeletePrimaryKeyParam.ParameterName = "@PhotoID";
DeletePrimaryKeyParam.Value = photoID.ToString();

// Insert new parameter into command object
DeleteCommand.Parameters.Add(DeletePrimaryKeyParam);

// Delete row, open connection, execute, close connection
DeleteCommand.CommandText = "Delete From Photo_TBL where PhotoID IN (@PhotoID)";
Response.Write(DeleteCommand.CommandText);
// DeleteCommand.Connection.Close();
DeleteCommand.Connection.Open();
DeleteCommand.ExecuteNonQuery();
DeleteCommand.Connection.Close(); 
 

View 9 Replies View Related

SELECT And DELETE Procedures

Nov 30, 2006

Hello, I am moving from Access to SQL and I am trying to create DELETE and a SELECT procedures. I was following the Asp.Net 2.0 Membership, Roles, etc procedures but I am not getting there. Could someone, please, post just the 3 procedures for this example? It would be a start for me to work on ... I have related two tables: <Content> and <ContentLocalized>    <Content>        |----- [ContentId]   Type=UniqueIdentifier   PK        |      [ContentName]    Type=NVarChar(100)        |        |     <ContentLocalized>        |           [ContentLocalizedId]   Type=UniqueIdentifier   PK        | -------> [ContentId]   Type=UniqueIdentifier   FK        |           [ContentCulture]   Type=NVarChar(5)        |           [ContentHtml]   Type=NVarChar(MAX) Basically I want to create 3 stored procedures:    1. SELECT [ContentHtml] Given [ContentName] and [ContentCulture]    2. DELETE a content in <Content> and all its dependencies in <ContentLocalized>        given [ContentName]    3. DELETE a content record in <ContentLocalized> given [ContentName] and [ContentCulture] I allready have an Access Query for (1) but I don't think I should try to use it in SQL: SELECT ContentLocalized.ContentHtml FROM Content INNER JOIN ContentLocalized ON Content.ContentId=ContentLocalized.ContentId WHERE (((ContentLocalized.ContentCulture)=[@ContentCulture]) AND ((Content.ContentName)=[@ContentName])); Thank You Very Much, Miguel

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved