DataSource Controls :: ExecuteNonQuery And Stored Procedure?

Jun 28, 2010

Im using above to makes sure that record is inserted to the SQL database before executing another function, but for some reason even if the record is NOT inserted other function has been called.(Other function is t update a Flag)

look at my code below and see where it goes wrong??

Part of the .Net Code
=============================================================
Private Sub GetAndInsert()[code]....

I have a feeling it could be that Rolling back is done in both .NET and SP??

View 2 Replies


Similar Messages:

ADO.NET :: ExecuteNonQuery Using Stored Procedure Will Not Delete Record (SQLDataAdapter)

Sep 6, 2010

I'm using VB.net 2.0 with a SQL server (2005) backend db. I've got a very odd problem. I'm trying to use a stored procedure with a parameter to delete a record from a table. I've tested the stored procedure by running it directly from visual studio and it works fine, but when running it through my application nothing happens. There's no error and it appears to have worked but the record is not deleted.

I've checked permissions on the backend db and giving the network ASPNet account the db owner role has no effect either.

Here's the code, there are 3 layers - the webform code, then the business logic class, then the dataconnection class.

'firstly the original code that is run from the webform when the user selects to delete the record

Partial Public Class NewPerson

Inherits System.Web.UI.Page
Private newConn As New DBConnection
Private currentNewRequest As New NewRequest(newConn)
Protected Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
Dim requestID As Integer = CInt(Me.lblRecordID.Text)
Me.currentNewRequest.newRequestID = requestID
Me.newConn.openConnection()
Me.currentNewRequest.DeleteRecord()
Me.newConn.closeConnection()
End Sub
End Class
'the business logic class that is called to set the parameters etc. The newRequestID integer is set at 19, I have checked this out.
Public Class NewRequest
Private dBConnection As DBConnection
Public newRequestID As Integer
Public Sub New(ByVal dBConnection As DBConnection)
'check params
If dBConnection Is Nothing Then
Throw New ArgumentNullException("dBConnection")
End If
Me.dBConnection = dBConnection
End Sub
Public Sub DeleteRecord()
'This sub deletes a request
With Me.dBConnection
.AddParameter("@newrequestID", SqlDbType.Int, 0, newRequestID)
.ExecNonQuerySP("sp_DeleteNewRequest")
End With
End Sub
End Class
'then the data connection class
Imports System.Data.SqlClient
Public Class DBConnection
Private _ParamList As SqlParameterCollection
Private _MyComm As New SqlCommand
Private _ConnectionStr As String = ConfigurationManager.ConnectionStrings("Conn").ToString
'shared connection
Dim conn As New SqlConnection(_ConnectionStr)
'the parameter is added
Public Sub AddParameter(ByVal ParamName As String, ByVal paramType As Data.SqlDbType, ByVal Paramlength As Int16, ByVal ParamValue As String)
MyComm.Parameters.Add(ParamName, paramType, Paramlength)
If ParamValue = Nothing Or ParamValue = "00:00:00" Or ParamValue = "" Then
_MyComm.Parameters(ParamName).Value = DBNull.Value
Else
_MyComm.Parameters(ParamName).Value = ParamValue
End If
End Sub
'and this is where the stored procedure is supposed to run, no errors are generated
Public Sub ExecNonQuerySP(ByVal SPName As String)
Dim MyAdapter As New SqlDataAdapter(_MyComm)
_MyComm.CommandType = CommandType.StoredProcedure
_MyComm.CommandText = SPName
_MyComm.Connection = conn
If Not _MyTrans Is Nothing Then
_MyComm.Transaction = _MyTrans
End If
Try
_MyComm.ExecuteNonQuery()
Catch ex As Exception
Dim errorMessage As String = ex.GetType.ToString & " " & ex.Message.ToString
System.Web.HttpContext.Current.Session("DBError") = "True"
System.Web.HttpContext.Current.Session("DBEx") = errorMessage
Throw ex
Finally
End Try
End Sub
Public Sub openConnection()
conn.Open()
End Sub
Public Sub closeConnection()
conn.Close()
End Sub
End Class

The stored procedure looks like this:

ALTER PROCEDURE sp_DeleteNewRequest
/*this sp is used to delete a new account request record*/
@newrequestID int
AS
DELETE FROM NewAccountRequest WHERE
NewRequestID = @newrequestID

View 4 Replies

DataSource Controls :: How To Convert The Oracle Stored Procedure To Sqlserver 2005 Stored Procedure

Apr 2, 2010

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

View 4 Replies

DataSource Controls :: Do Not Use Or Call Any Stored Procedure But It Says "Could Not Find Stored Procedure 'xxxxx'?

Dec 9, 2010

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 Replies

DataSource Controls :: Execute A Stored Procedure Within A Stored Procedure?

Jan 18, 2010

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

View 11 Replies

DataSource Controls :: How To Call A Stored Procedure In Another Stored Procedure

May 13, 2010

i want to return output parameter from 1 storeprocedure. into another stored procedure.

View 7 Replies

DataSource Controls :: Call One Stored Procedure In Another Procedure For Search?

May 1, 2010

I want to apply other stored procedure select query on result of first stored procedure.

View 1 Replies

DataSource Controls :: Stored Procedures Are Not Showing Up Correctly In The Stored Procedure Drop Down

Apr 27, 2010

I am using Visual Studio 2008 and SQL Server 2008 and have added a SQL datasource to my form.

When I configure the datasource, I choose my connection string which looks like this in my webconfig:

<connectionStrings>

<add
name="PSFSPRD_ConnectionString"
connectionString="Data

The database I am using has a schema called EQB and as such, my stored procedures are named as EQB.usp_SelectFunds, EQB.usp_SelectAccount, etc.

On the select tab of the Configure Data Source screen, I choose to use a stored procedure. The dropdown shows my stored procedures, however, the schema name does not show up in front of the stored procedures in the drop down. I see only usp_SelectFunds, usp_SelectAccount, etc.

I select one of the stored procedures and when I click TEST, I get the message that the stored procedure is not found. If I instead choose to use a SQL statement instead on the configuration screen and enter EXEC EQB.usp_SelectFunds and click TEST, it works fine.

Why are my stored procedures not showing up correctly in the stored procedure drop down and how can I fix this?

View 3 Replies

DataSource Controls :: To Use Stored Procedure In .net

May 30, 2010

how to use stored procedure in asp.net for for manipulation form sql server (in details).

View 2 Replies

DataSource Controls :: Stored Procedure In .net?

Mar 31, 2010

parameters inside the stored procedure.How to write .

View 6 Replies

DataSource Controls :: Could Not Find Stored Procedure?

Jun 17, 2010

I created a database on SQL Server 2005 developer edition with MS SQL Server Mangement Studio Express. Now I created code that accessess a stored procedure named 'pS_TableData'. However I have one problem the code raises an exception "Could not find stored procedure 'pS_TableData 'Letters''."the code looks like this:

[Code]....

Note I am using (for now) the same connection that I used to create my database so in essence my web site is the administrator. I tried a simple SELECT from a table with the same connection and it raised no exceptions!When I run EXECUTE ps_TableData 'Letters' in SSMS it has no . I also tried EXECUTE ps_TableData 'Letters' as the command paramated for SqlCommand but it had the same effect

View 1 Replies

DataSource Controls :: Stored Procedure Not Returning Value

May 15, 2010

I call this stored procedure with a dataset. It always returns a 0 but should return a 1 or 2. Does anybody know why it isn't returning a 1 or 2.

ALTER PROCEDURE dbo.sp_InsertLinkVote
@LinkId int,
@LinkVoter nvarchar(50),
@LinkVotesDateTime datetime,
@LinkVotesGoodBad bit
AS
/* SET NOCOUNT ON */
 
If EXISTS
(SELECT
*
FROM
LinkVotes
WHERE
LinkId=@LinkId
and
LinkVoter = @LinkVoter )RETURN
1
ELSE
INSERT INTO
dbo.LinkVotes (LinkId,LinkVoter,LinkVotesDateTime,LinkVotesGoodBad)VALUES
(@LinkId,@LinkVoter,@LinkVotesDateTime,@LinkVotesGoodBad)RETURN
2

Here is were a call it in my code behind.
Dim VoteCheck
As
Integer
Dim InsertVote
As
New DataSetStoredProceduresTableAdapters.QueriesTableAdapter
VoteCheck = InsertVote.sp_InsertLinkVote(LinkId, User.Identity.Name, DateAndTime. Now,
True)
'0 is a good vote.
If VoteCheck = 0
Then

It seems to insert and not insert correctly, I just cannot get it to return the correct value.

View 4 Replies

DataSource Controls :: Returning Value From Stored Procedure

Jun 23, 2010

I am developing a page in ASP.Net where I have the user enter a letter into a textbox. They then click a submit button and I all of the userids that start with that letter are to be displayed. So I need the SP to go through the database and match first letters with what was entered. Here is what I have as my code for the SP:

CREATE PROCEDURE dbo.exp2 @testchar Char(1)
declare @flag as char(2)
set @flag = @testchar + '%'
select userid
from ex_database
where userid like @flag

View 6 Replies

DataSource Controls :: Where To Save Stored Procedure

May 25, 2010

So I'm working with SQL Server 2005 and I'm having trouble saving a stored procedure. (I've only used an Apache server before, and for nothing as complex as the project I'm working on)

Nonetheless, the problem is that I don't know where to actually save the stored procedure I create. In my Object Explorer, I go to the database I want to create it in, expand Programmability, right click on Stored Procedures and choose to create a new one. That's all nice, but when I'm ready to save and test it from my webpage, it asks where to save it. This is probably dumb of me, but I don't know where to. I try to save it on the desktop and click/drag to the Stored Procedures folder in my database, but all that does is open it, not actually store it in the database. Maybe I'm getting the point of stored procedures wrong? Let me know what I should do to save it in the right place so I can access it.

View 8 Replies

DataSource Controls :: Verify My Stored Procedure

Mar 18, 2010

I just want to make sure I used SCOPE_IDENTITY() correctly to get the newly made TestimonialID

[Code]....


Is this the proper way to use it?

View 4 Replies

DataSource Controls :: How To Create A Stored Procedure

Jan 15, 2010

I'm trying to create a stored procedure that does two things:1st - Insert a new record in the contactus table.2nd - Gets the last primary key value from table contactus.I've created the following Stored Procedure but this is giving me an error when being executed.

[Code]....

View 5 Replies

DataSource Controls :: Error In SQL Stored Procedure?

May 11, 2010

I have a button which the admin will click on every new acad year to update the student's study level.

I have written a SP to do this process, however, it was working fine. But I faced problem when I did testing.

This is my student update study level stored procedure:

UPDATE Student
SET acadLevel = (CASE WHEN acadLevel < '3' THEN convert(char,acadLevel + 1) ELSE convert(char,'G') end )
WHERE acadLevel between '1' and '4';

Error: Conversion failed when converting the varchar value 'G' to data type int.

View 5 Replies

DataSource Controls :: Stored Procedure .net + DateTIme?

Apr 19, 2010

I have one pretty stupid problem - datetime issue. I've made one stored procedure for making jobs

[Code]....
[Code]....

View 9 Replies

DataSource Controls :: Abt Learning Stored Procedure?

May 29, 2010

can anyone tell me which is the best website to learn stored procedure from?

View 3 Replies

DataSource Controls :: Call Stored Procedure In .Net?

Mar 11, 2010

I have created a stored procedure in Oracle. Now I want to call those procedure in VB.Net

Stored procedure:

[Code]....

How to call this sp in .net?

View 4 Replies

DataSource Controls :: Call A DLL From A Stored Procedure?

May 13, 2010

How to call a DLL from a stored procedure in SQL 2005?

View 3 Replies

DataSource Controls :: Linq And Stored Procedure ?

Mar 2, 2010

I have created one stored Procedure as

[code]...

I am not getting the result with extra column c.I am not getting what is the Problem.In sql server,It's working fine.

View 2 Replies

DataSource Controls :: Get Value Out Of Stored Procedure Within A Cursor?

May 15, 2010

I have a stored procedure that uses a cursor and also uses another stored procedure to return a column from the returned records.

[Code]....

See that part with 'exec dirinfo.dbo.d_searchempybyname @TicketEmail'? It works and returns the records but what I really need is to return a COLUMN from the record and store that column value in a variable which I can use - for example 'PRINT @VariableName'.

View 2 Replies

DataSource Controls :: Run Stored Procedure Using Linq?

Feb 12, 2010

Is it posible to run a stored procedure using linq? I dont mean one that returns a table i just mean one that runs a set of transactions exec sp_MyProc @Var or something?

View 1 Replies

DataSource Controls :: Stored Procedure And Session?

Jun 1, 2010

ALTER PROCEDURE insertdetails2

View 9 Replies







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