Insert Stored Procedure With Error Check And Transaction Function

Jul 21, 2004

Hi, guys
I try to add some error check and transaction and rollback function on my insert stored procedure but I have an error "Error converting data type varchar to smalldatatime" if i don't use /*error check*/ code, everything went well and insert a row into contract table.
could you correct my code, if you know what is the problem?

thanks

My contract table DDL:
************************************************** ***

create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);


My insert stored procedure is:
************************************************** *****

create proc sp_insert_new_contract
( @contractDate[smalldatetime],
@tuition [money],
@studentId[char](4),
@contactId[int])
as

if not exists (select studentid
from student
where studentid = @studentId)
begin
print 'studentid is not a valid id'
return -1
end

if not exists (select contactId
from contact
where contactId = @contactId)
begin
print 'contactid is not a valid id'
return -1
end
begin transaction

insert into contract
([contractDate],
[tuition],
[studentId],
[contactId])
values
(@contractDate,
@tuition,
@studentId,
@contactId)

/*Error Check */
if @@error !=0 or @@rowcount !=1
begin
rollback transaction
print ‘Insert is failed’
return -1
end
print ’New contract has been added’

commit transaction
return 0
go

View 1 Replies


ADVERTISEMENT

Gridview / SqlDataSource Error - Procedure Or Function &<stored Procedure Name&> Has Too Many Arguments Specified.

Jan 19, 2007

Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure  in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.

View 9 Replies View Related

Is The Transaction Context Available Within A 'called' Stored Procedure For A Transaction That Was Started In Parent Stored Procedure?

Mar 31, 2008

I have  a stored procedure 'ChangeUser' in which there is a call to another stored procedure 'LogChange'. The transaction is started in 'ChangeUser'. and the last statement in the transaction is 'EXEC LogChange @p1, @p2'. My questions is if it would be correct to check in 'LogChange' the following about this transaction: 'IF @@trancount >0 BEGIN Rollback tran' END Else BEGIN Commit END.
 Any help on this would be appreciated.

View 1 Replies View Related

ASP/SQL Stored Procedure/ASP Function Error

Jul 20, 2007

Hey I have the following Stored Procedure


CREATE PROCEDURE spGetOrderCount
(
@search varchar(1000) = default
)

AS

SET NOCOUNT ON

/* Setup search string */
IF (@search <> '')
BEGIN
SET @search = 'WHERE' + @search
END


/* Create a temporary table */
CREATE TABLE #TempTable
(
row int IDENTITY,
totalCount int
)

/* Insert the search results into query */
EXEC
(
'INSERT INTO #TempTable([totalCount])' +
'SELECT COUNT(*) AS totalCount ' +
'FROM tblOrders' + @search
)

/* Extract the wanted records from the temporary table */
SELECT[totalCount],
RecordsLeft =
(
SELECT COUNT(*)
FROM #TempTable TI
)
FROM#TempTable

SET NOCOUNT OFF

RETURN
;
GO



And then the following function which specifies the where clause of the statement


function getOrderCount(strDate, strStatusList)
getOrderCount = 0
dim objRS, objSP, strWhereClause

if isDate(strDate) and len(strStatusList) > 0 then
'# Filter on order status



'# Filter on date clause
strWhereClause = "(tblOrders.orderDate >= " & sqlServerDate(strDate) & ")"

'#GET order count
Set objSP = SQLGetProcedure("spGetOrderCount")
SQLSetProcedureParam objSP, "search", strWhereClause

Set objRS = SQLExecuteProcedure(objSP)

if not objRS.eof then
getOrderCount = objRS("totalCount")
end if

'# Free resources
deleteRecordset(objRS)
deleteObject(objSP)
end if

end function


When I do this together I get the following error on my ASP Page

Microsoft OLE DB Provider for SQL Server error '80040e14'

Line 1: Incorrect syntax near '.'.

/sigma_eircommobdispatch/server/database.asp, line 235

And the print out of the resulting string from the function is

(tblOrders.orderDate >= CONVERT(DATETIME, '2007-7-13', 102))


If i remove the Where clause the statement works fine..

Any ideas

View 1 Replies View Related

Error When Attempting To Insert Record Using Stored Procedure

Jun 21, 2006

I am trying to save user data to a sql table but keep getting the following error;
Unable to cast object of type 'System.Boolean' to type 'System.Data.SqlClient.SqlParameter'.
I am using VWD Express Edition with .NET 2.0 and a SQL 2000 database. The code that generates the error is as follows;
Public Function chkUser(ByVal strUser As String) As Stringdim MyConnection As SqlConnectionDim MyCommand As SqlCommandDim ReturnString As StringDim params As SqlParameterDim SelectCmd As String = "wm_CheckUser"MyConnection = New SqlConnection(myConnectionString)MyCommand = New SqlCommand(SelectCmd, MyConnection)MyCommand.CommandType = CommandType.StoredProcedure
Tryparams = MyCommand.Parameters.Add("@parUser", SqlDbType.VarChar, 100).Value = strUserMyCommand.Connection.Open()MyCommand.ExecuteNonQuery()ReturnString = "Record Exists!"Catch Exp As SqlExceptionReturnString = ReturnError(Exp.Number, "Users", Exp.Message)End TryMyCommand.Connection.Close()Return ReturnString
End Function
Basically, I'm traying to check to see if a user id already exists in the database before saving the data the user entered. If the email address entered by the user is already in the database I want a message to be shown to the user. If the email address does not exist then the data entered by the user is saved and the form goes to step two (2) of the user registration process.
Any help with this would be greately appreciated. I can't seem to see what is wrong here. Please someone help.
Thanks,
Jaime

View 7 Replies View Related

DB Engine :: Error Trying To Execute BULK INSERT In Stored Procedure

May 16, 2015

At my customer's site they get this error trying to run a stored procedure I wrote that does BULK INSERT.

-2147217900
[Microsoft ODBC SQL Server Driver][SQL Server] You do not have permission to use the bulk load statement.
upImportFromICPMSRaw 'GSADC1CompanyInstrumentOutputFilesICPMSNew185367.csv', tblFromICPMSRaw

The customer has SQL Server 2008 R2 Express installed

The connection string to the database works on everything else and it is the sa account with password

On my own development system with SQL Server 2008 R2 Standard, it works perfectly OK.

View 5 Replies View Related

How To Check If An Insert Stored Proc Succeeded

Mar 5, 2007

Hi,
How would I check if an insert via stored proc succeeded? Here's the proc I'm using:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[showtube_addNewSUser]
-- Add the parameters for the stored procedure here
@UserId uniqueidentifier,
@FirstName nvarchar(32),
@LastName nvarchar(32),
@DescShort nvarchar(256),
@DescLong ntext,
@ClanID uniqueidentifier
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
Insert into dbo.Users (UserId, FirstName, LastName, DescShort, DescLong, ClanID)
values (@UserId, @FirstName, @LastName, @DescShort, @DescLong, @ClanID);
END


 
 
I tried adding a Return @@RowCount before the END statement, but it always seems to return -1. Could someone tell me what I'm doing wrong? Thanks.

View 3 Replies View Related

How To Check Whether Stored Procedure Is Being Used

Apr 14, 2008

Hi,

I'm using some security scanning software which looks for vulnerabilities in the database. It tells me that some stored procedure are should not be given public permission.
How do i know whether the stored procedures are being used by someone or last used on which date? Is there any way to find out?

At the same time, how do i check the permission of stored procedure on ms sql 2005? Thanks

View 3 Replies View Related

How To Check Stored Procedure?

May 2, 2004

Hello, everyone:

I have some stored procedures that run on SQL2K(SP3) /WIN2K. Sometimes I modify them using ALTER PROCEDURE statements. How to check if they are changed after ALTER statements run? Thanks.

ZYT

View 4 Replies View Related

Stored Procedure To Check Different Criterias

Aug 6, 2007

I've a stored procedure which retrieves based on different criterias. I added one more critieria - to display a column based on a range of values. The values are @OriginalMin and @OriginalMax. I declared the variables and gave the conditions. But still set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
ALTER procedure [dbo].[USP_Account_Search_Mod]@OriginalMin DECIMAL=0
,@OriginalMax DECIMAL=0
AS
DECLARE
@CRI8 VARCHAR(500)SELECT
@CRI1=''
SET @CRI8='AND OriginalBalance >=@OriginalMin AND OriginalBalance<=@OriginalMax'
SELECT @Criteria = ......+ @CRI8
When I execute this stored procedure, I get the following error message.
SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(3)) AND Customer = '00001'AND OriginalBalance >=@OriginalMin AND OriginalBalance<=@OriginalMax UNION SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(3)) AND Customer = '00001'AND OriginalBalance >=@OriginalMin AND OriginalBalance<=@OriginalMaxORDER BY NAME ASC
Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OriginalMin'.
Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OriginalMin
Could someone tell what's wrong with the procedure? For convenience, I've included only the latest critieria I added.

View 2 Replies View Related

How To Check For Nulls With-in A Stored Procedure

May 24, 2008

I have a Stored Procedure as followsUSE [MyDataBase]
GO
/****** Object: StoredProcedure [dbo].[SPLogins] Script Date: 05/24/2008 21:58:50 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
Create  PROCEDURE [dbo].[SPLogins]
(
@LoginName varchar(50), @LoginD int output,
)
AS
Select @LoginID = LoginID From MyDatabase Where (LoginName Is not Null) and @LoginName = LoginName
 
I am trying to check for a null value in the Stored Procedure.  I dont' get an error, but it doesn't catch the "null" and gives an error when there is a null value
What Is the correct wayt to go about it.

View 3 Replies View Related

How To Check Stored Procedure Status

Dec 18, 2003

How to check status(valid/invalid) for all stored procedure in my DB...

View 14 Replies View Related

Check If A Stored Procedure Exists

Mar 14, 2007

Hi All.

Can anyone tell me what the syntax to check and see if a stored procedure exists and if it doesnt to create it is?

Thanks people.

View 8 Replies View Related

SqlDataSource.SelectParameters Causing Procedure Or Function Stored Procedure Has Too Many Arguments Specified.

Sep 12, 2006

 Hi everybody,   I am having trouble how to fixed this code. I am trying to supply the parameterinside a stored procedure with a value, and displays error message shown below. If I did not supply the parameter with a value, it works. How to fix this?Error Message:Procedure or function <stored proc name> has too many arguments specified.Thanks,den2005 
Stored procedure:

Alter PROCEDURE [dbo].[sp_GetIdeaByCategory]
@CatId <span class="kwd">int</span> = 0
AS
BEGIN
SET NOCOUNT ON;

Select I.*, C.*, U.* From Idea I inner join IdeaCategory C on I.CategoryID = C.IdeaCategoryID inner join Users U on I.UserID = U.UserID Where I.CategoryID = @CatId Order By LastModifiedDate Desc
End


oDataSource.ConnectionString = constr;
oDataSource.SelectCommand = storedProc;<span class="cmt">//storedproc - sp_GetIdeaByCategory</span>
oDataSource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
oDataSource.SelectParameters.Add(<span class="st">&quot;@CatId&quot;</span>, catId);
gdvCategories.DataSourceID = oDataSource.ID;

gdvCategories.DataBind(); &lt;&lt;--- Error occured here


 

View 1 Replies View Related

Procedure Or Function 'stored Procedure Name' Expects Parameter Which Was Not Supplied

Mar 26, 2007

Has anyone encountered this before?
Procedure or Function 'stored procedure name' expects parameter '@parameter', which was not supplied.
It seems that my code is not passing the parameter to the stored procedure.
When I click this hyperlink:
<asp:HyperLink
ID="HyperLink1"
Runat="server"
NavigateUrl='<%# "../Division.aspx?CountryID=" + Eval("CountryID")%>'
Text='<%# Eval("Name") %>'
ToolTip='<%# Eval("Description") %>'
CssClass='<%# Eval("CountryID").ToString() == Request.QueryString["CountryID"] ? "CountrySelected" : "CountryUnselected" %>'>
</asp:HyperLink>
it is suppose to get the country name and description, based on the country id.
I am passing the country id like this.
protected void Page_Load(object sender, EventArgs e)
{
PopulateControls();
}
private void PopulateControls()
{
string countryId = Request.QueryString["CountryID"];
if (countryId != null)
{
CountryDetails cd = DivisionAccess.GetCountryDetails(countryId);
divisionNameLabel.Text = cd.Name;
divisionDescriptionLabel.Text = cd.Description;
}
}
To my app code like this:
public struct CountryDetails
{
public string Name;
public string Description;
}
public static class DivisionAccess
{
static DivisionAccess()
public static DataTable GetCountry()
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetCountry";
return GenericDataAccess.ExecuteSelectCommand(comm);
}
public static CountryDetails GetCountryDetails(string cId)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetCountryDetails";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@CountryID";
param.Value = 2;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
CountryDetails details = new CountryDetails();
if (table.Rows.Count > 0)
{
details.Name = table.Rows[0]["Name"].ToString();
details.Description = table.Rows[0]["Description"].ToString();
}
return details;
}
 
As you can see I have two stored procedures I am calling, one does not have a parameter and the other does. The getcountry stored procedure returns the list of countries in a menu that I can click to see the details of that country. That is where my problem is when I click the country name I get
Procedure or Function 'GetCountryDetails' expects parameter '@CountryID', which was not supplied
Someone please help!
 
Thanks Nickdel68

View 5 Replies View Related

Stored Procedure To Check Date Range

Nov 10, 2006

Hi guys,I have written a stored procedure to check for date range, say if the user enters a value for 'city-from' , 'city-to', 'start-date' and end-date, this stored procedure should verify these 2 dates against the dates stored in the database. If these 2 dates had already existed for the cities that they input, the stored procedure should return 1 for the PIsExists parameter. Below's how I constructed the queries:  1 ALTER PROCEDURE dbo.DateCheck
2 @PID INTEGER = -1 OUTPUT,
3 @PCityFrom Char(3) = '',
4 @PCityTo Char(3) = '',
5 @PDateFrom DATETIME = '31 Dec 9999',
6 @PDateTo DATETIME = '31 Dec 9999',
7 @PIsExists BIT = 1 OUTPUT
8 AS
9
10 CREATE TABLE #TmpControlRequst
11 (
12 IDINTEGER,
13 IsExistsCHAR(1)
14 )
15 /*###Pseudo
16 1. Check the Date From and Date To
17 -- select all the value equal to parameter cityFrom and cityTo
18 -- insert the selection records into tmp table
19 --*/
20 INSERT INTO #TmpControlRequst
21 (ID, IsExists)
22 SELECT ID,
23 IsExists = CASE WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
24 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo
25 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1
26 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
27 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo
28 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1
29 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
30 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo
31 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1
32 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
33 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo
34 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1
35 ELSE 0 END
36 FROM RequestTable
37 WHERE ID <> @PID
38 AND CityFrom = @PCityFrom
39 AND CityTo = @PCityTo
40
41 --======== FINAL RESULT
42 -- For tmp table:-
43 -- isExists = 1 ==> date lapse
44 -- isExists = 0 ==> date ok
45 -- if count for (isExists = 1) in tmp table is > 0 then return 1 and data not allow for posting
46 SELECT @PIsExists = CASE WHEN COUNT(*) > 0 THEN 1
47 ELSE 0 END
48 FROM #TmpControlRequst
49 WHEREIsExists = 1
50
51 SELECT @PIsExists
52 --=========
53
54 DROP TABLE #TmpControlRequst
55
56 --=========
57 RETURN(0)However, when I run this stored procedure, 'PIsExists' would always return -1. I am positive that the values that I passed in, had already existed in the database. Any idea what might be causing this problem? Thanks in advance

View 6 Replies View Related

Select And Check A Boolean Value With Stored Procedure?

Apr 10, 2008

 Hi all,How do you select and check a boolean value within a stored procedure? I don't know if this is correct or not:DECLARE @CheckStatus bitSELECT @CheckStatus = PREF_STATUS FROM  tblPrefsWHERE [PREF_ID] = @PREF_IDIF (@CheckStatus IS FALSE) -- DO SOMETHINGIF (@CheckStatus IS TRUE) -- DO SOMETHING But I'm not sure if what I've used (IS FALSE/TRUE) is the correct way? "PREF_STATUS" is a bit column in my table.Thanks. 

View 4 Replies View Related

SQL 2012 :: Check If Particular Stored Procedure Is Recompiling Or Not

Mar 23, 2014

I want to know, Is it possible to check a particular stored procedure is recompiling or not?

View 7 Replies View Related

How To Check When A Stored Procedure Was Last Called/executed

Jul 23, 2005

HiOur SQL server has a lot of stored procedures and we want to get somecleaning up to be done. We want to delete the ones that have been notrun for like 2-3 months. How exactly will i find out which ones todelete. Enterprise manager only seesm to give the "Create Date"How exactly can I find the last called date ! I guess you could write aquery for that ! but how ???P.S I dont want to run a trace for 1 months and see what storedprocedures are not being used.

View 7 Replies View Related

Query - Check For A String In Stored Procedure

Jul 23, 2005

Hi,I would like to check if a string value exist in a string in sqlserver stored procedure, e.g.set @testString = 'this is my test document.'if (@testString contains 'test')begin.....endHow do I do this in sql server stored procedure?Thanks,June...

View 1 Replies View Related

Store Procedure Insert Into Function

Aug 10, 2014

I have 2 tables

First Table is CUSTOMERS with the columns (CustomerId, CustomerName) Second Table is LICENSES with the columns (LicenseId, Customer) The column customer from the second table is the CustomerId from the First table

I wanted to create a store procedure that insert values into table 2

Insert into Licenses (Customer) Values( Same As CustomerId)

HOW i can get this data from the other table?

View 11 Replies View Related

Calling A Stored Procedure Or Function From Another Stored Procedure

Mar 2, 2007

Hello people,

When I am trying to call a function I made from a stored procedure of my creation as well I am getting:

Running [dbo].[DeleteSetByTime].

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.TTLValue", or the name is ambiguous.

No rows affected.

(0 row(s) returned)

@RETURN_VALUE =

Finished running [dbo].[DeleteSetByTime].

This is my function:

ALTER FUNCTION dbo.TTLValue

(

)

RETURNS TABLE

AS

RETURN SELECT Settings.TTL FROM Settings WHERE Enabled='true'

This is my stored procedure:

ALTER PROCEDURE dbo.DeleteSetByTime

AS

BEGIN



SET NOCOUNT ON



DECLARE @TTL int

SET @TTL = dbo.TTLValue()



DELETE FROM SetValues WHERE CreatedTime > dateadd(minute, @TTL, CreatedTime)

END

CreatedTime is a datetime column and TTL is an integer column.

I tried calling it by dbo.TTLValue(), dbo.MyDatabase.TTLValue(), [dbo].[MyDatabase].[TTLValue]() and TTLValue(). The last returned an error when saving it "'TTLValue' is not a recognized built-in function name". Can anybody tell me how to call this function from my stored procedure? Also, if anybody knows of a good book or site with tutorials on how to become a pro in T-SQL I will appreciate it.

Your help is much appreciated.

View 6 Replies View Related

Stored Procedure In Database X, Executes Stored Procedure In Database Y, Wrapped In Transaction?

Jul 20, 2005

Is it possible to execute a stored procedure in one database, which thenitself executes a stored procedure from another database? We have decide tosplit our data into a tree structure (DB1) and data blobs (DB2) (we areusing MSDE and we have a 2gb limit with each DB so we've done it this wayfor that reason). I would like to, say, execute a stored procedure in DB1,passing in the data blob and other details, DB1 will create a tree node inDB1 and then add the blob record to DB2. DB1 will wrap in a transaction ofcourse, as will DB2 when it adds the blob. Is this possible?

View 1 Replies View Related

Stored Procedure And Transaction

Jun 22, 2006

I have a stored procedure that registers a user. It also checks whether an email or username exists before registering them.Does this need to be in a transaction to prevent duplication of email/username? The data is highly sensitive and should not be duplicated at all.I tried writing methods in .net like CheckEmail() and CheckUsername() but that probably wont protect against duplicates if 2 people submit at the same time on a busy server. So the next best thing is a stored procedure but should it be in a transaction or not is what I'm wondering.Thanks again,you guys are great!

View 2 Replies View Related

Stored Procedure And Transaction

Nov 10, 2006

I have a function in which I use a stored procedure and a transaction.I use the following code, but I get the exception e (last try-catch): This SqlTransaction has completed; it is no longer usable.(btw, the SendEmail function currently returns False, so the transaction should ALWAYS roll back)    Public Shared Function SaveGBEntry(ByVal myGBEntry As GBEntry, ByVal Language As String) As Boolean        Dim bSuccess As Boolean = False        Dim MyConnection As SqlConnection = GetConnection()        Dim cmd As New SqlCommand("spNewGBEntry", MyConnection)        Dim fBeginTransCalled As Boolean = False        Dim myTrans As SqlTransaction        Try            MyConnection.Open()            myTrans = MyConnection.BeginTransaction()            cmd.CommandType = Data.CommandType.StoredProcedure            cmd.Transaction = myTrans            fBeginTransCalled = True            Dim UserCodeOwner As Integer = UserFunctions.GetUserCode(myGBEntry.UserNameOwner)            Dim OwnerEmail As String = UserFunctions.GetUserEmail(myGBEntry.UserNameOwner)            cmd.Parameters.Add(New SqlParameter("@UserCodeSender", myGBEntry.UserCodeSender))            cmd.Parameters.Add(New SqlParameter("@GBText", myGBEntry.Text))            cmd.Parameters.Add(New SqlParameter("@GBUpdateDate", myGBEntry.UpdateDate))            cmd.Parameters.Add(New SqlParameter("@UserCode", UserCodeOwner))            cmd.ExecuteNonQuery()            myTrans.Commit()
            If MailFunctions.SendEmail(myGBEntry.UserNameOwner, OwnerEmail, ConfigurationManager.AppSettings("emailINFOname"), ConfigurationManager.AppSettings("emailINFOAddress"), True, "NewGBEntry", Language) Then                bSuccess = True                fBeginTransCalled = False            End If        Catch ex As Exception            GlobalFunctions.ReportError("GUESTBOOKDAL:SaveGBEntry", ex.Message)        Finally            If fBeginTransCalled Then                Try                    myTrans.Rollback()                Catch e As System.Exception                    GlobalFunctions.ReportError("GUESTBOOKDAL:SaveGBEntry", e.Message)                End Try            End If            MyConnection.Close()        End Try        Return bSuccess    End Function

View 1 Replies View Related

Transaction On Stored Procedure

Oct 17, 2007

hello,
 i'm executing a select statement inside the BEGIN TRANSACTION block on a Stored Procedure.  my question is, does it lock the table?  actually, i'm getting the maximum value of a field and i don't want other users to read until the transaction is done.  do i still need to use tablockx to manually lock the table or does the transaction handles the locking?
please help
thanks!   
 

View 2 Replies View Related

Transaction Through Stored Procedure

Jul 20, 2005

i have to update two tables from ASP pages with same data but i wantthat both of them should be updated at one time. If either of them isnot updated then my transaction should roll back.I want this thing tobe in a stored procedure. so that i have to write an execute statementonly on the ASP page and pass the parameters.Looking forward for ur replyDEEPAK

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

HELP! Stored Procedure And Transaction Problems

Aug 21, 2006

I have a stored procedure I want to use in a transaction...I have no idea where the problem is, since I dont receive an error...but tblMessages and tblUsersAndMessages are not updated..(hence something is wrong ;))...it might be the sp or just my code...anyway..here's all the data:STORED PROCEDUREset ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo
ALTER PROCEDURE [dbo].[spNewMessage]@MessageID int,@UserIDSender uniqueidentifier,@MessageTitle nvarchar(50),@MessageContent text,@MessageType int,@UserID uniqueidentifier
ASBegin Set NoCount on DECLARE @WhateverID INT 
INSERT INTO tblMessages(UserIDSender,MessageTitle,MessageContent,MessageType)VALUES (@UserIDSender,@MessageTitle,@MessageContent,@MessageType)
SET @WhateverID=SCOPE_IDENTITY()
INSERT INTO tblUsersAndMessages(MessageID,UserID)VALUES (@WhateverID,@UserID)
End
 
TABLE DEFINITIONtblUsersAndMessages                                            allow nullsMessageID   int                     falseUserID           uniqueidentifier falseNew              bit                  false               *default set to ((1)) 
tblMessages                                               allow nullsMessageID       int                     false         *PKUserIDSender    uniqueidentifier falseMessageTitle       nvarchar(50)     trueMessageContent text                  trueSentDateTime    datetime           false      * default set to (getdate())MessageType       int                  false
THE CODE   Public Shared Function SendMessage(ByVal SenderName As String, ByVal To As String), ByVal MessageTitle As String, ByVal MessageContent As String, ByVal MessageType As String) As Boolean        Dim bSuccess As Boolean        Dim MyConnection As SqlConnection = GetConnection()        Dim cmd As New SqlCommand("spNewMessage", MyConnection)        Dim fBeginTransCalled As Boolean = False        Dim myTrans As SqlTransaction        Try            MyConnection.Open()            myTrans = MyConnection.BeginTransaction()            cmd.CommandType = Data.CommandType.StoredProcedure            cmd.Transaction = myTrans            fBeginTransCalled = True
            Dim UserIDSender As Guid = UserFunctions.GetUserID(SenderName)            Dim UserIDReceiver As Guid = UserFunctions.GetUserID(To)
            'create message            cmd.Parameters.Add(New SqlParameter("@UserIDSender", UserIDSender))            cmd.Parameters.Add(New SqlParameter("@MessageTitle", MessageTitle))            cmd.Parameters.Add(New SqlParameter("@MessageContent", MessageContent))            cmd.Parameters.Add(New SqlParameter("@MessageType", CInt(MessageType)))            'userid receiver            cmd.Parameters.Add(New SqlParameter("@UserID", UserIDReceiver))
            cmd.ExecuteNonQuery()            myTrans.Commit()            bSuccess = True            fBeginTransCalled = False        Catch ex As Exception        Finally            If fBeginTransCalled Then                Try                    myTrans.Rollback()                Catch e As System.Exception                End Try            End If            MyConnection.Close()        End Try        Return bSuccess    End Function

View 3 Replies View Related

Using Multiple Transaction In Stored Procedure?

Oct 15, 2014

I want to use 3 transactions in a single stored procedure.First it should update the value and then if the 1st transaction executed successfully then 2nd transaction should start,if 2nd trans executed successfully then 3rd trans should execute.After that only i want to commit all the transactions that is 1,2,3.If any of the trans fails the other shouldn't execute.How do i do this?

USE [recruit]
GO
/****** Object: StoredProcedure [dbo].[Import] Script Date: 10/15/2014 17:13:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[Import]
@CustId int

[code].....

View 2 Replies View Related

Function Vs Stored Procedure

May 1, 2004

I know this is a stupid question (actually, maybe its not..?)

They seem to be identical in some ways, but not available to the outside world. what are some differences?

View 3 Replies View Related

Function From Stored Procedure

Nov 22, 2007

Hi Everyone,

i want to create a function in Stored procedure and then call this function
that returns true or false from a stored procedure .
is this possible?

please help me if you know something that can help me.

thanks

Tvin

View 2 Replies View Related







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