ADO.NET :: Linq To SQL Stored Procedure Returning Out Parameter "ref"

Dec 18, 2010

I always have this problem returning an out parameter from sql server using linq. So I have to use other means may be dataset. The following is the stored procedure: ALTER PROCEDURE [dbo].[InsertIntoTestProfile]

View 3 Replies


Similar Messages:

DataSource Controls :: Returning String Value Using Stored Procedure And Linq

Feb 7, 2010

I am having problems returning a string value through a stored procedure/linq to a label. Everything works fine if I return an integer value but I cant get a string value to bind to a label. I get the error message

Microsoft.VisualBasic.CompilerServices.Conversions.ParseDouble(String Value,
NumberFormatInfo NumberFormat) +201
Microsoft.VisualBasic.CompilerServices.Conversions.ToInteger(String Value) +66
[InvalidCastException: String "System.Data.Linq.SqlClient.SqlPr" to 'Integer' is invalid‚]

This is the stored procedure
ALTER PROCEDURE test
AS
Select ProductName
From Products

This is the vb page
Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim db As New DataClassesDataContext
Dim product As Integer = db.test.ToString
Label1.Text = product
End Sub

This is the aspx page
<form id="form1" runat="server">
<asp:Label ID="Label1" runat="server" Text=""></asp:Label>
</form>

View 1 Replies

DataSource Controls :: Returning Single String Value Using Linq And Stored Procedure

Jun 21, 2010

I can return single integer values when using stored procedures with Linq but I cant get single string values returned.

View 2 Replies

ADO.NET :: Stored Procedure Returning ObjectResult

Feb 20, 2011

I am having issues with the stored procedure returning an ObjectResult and then how do I return this is a list of type List.

View 2 Replies

Returning Date From Stored Procedure In VB.Net?

Jun 4, 2010

I want to execute a method on VB.Net to return a date which is in the stored procedure. I tried using ExecuteScalar but it doesnt work it retruns error

'Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query'

below is the code

Public Function GetHolidaydate(ByVal struserID as String) As DateTime
Dim objArgs1 As New clsSQLStoredProcedureParams
objArgs1.Add("@userID", Me.Tag)
objArgs1.Add("@Date", 0, 0, ParameterDirection.Output)
Return (CDate(ExecuteScalar(clsLibrary.MyStoredProcedure.GetHolidayDate, objArgs1)))
End Function

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

SQL Server :: Stored Procedure Not Returning Desire Value

Sep 28, 2010

I have simple stored procedure to check if there is any email address in the database entered by user or not. My SP should return 1 when user enters a correct email. However, it is returning 0 or -1.

View 3 Replies

ADO.NET :: Returning Status Message From Stored Procedure

Dec 28, 2010

I am having problem in returning a status message (success or failure) from my sql server sp here is the code:
[Code]....

"Status" is an output parameter which is supposedly getting the message from sp and we are not able to get its value since
"dbCommand1.Parameters["@status"].Value" returns NULL everytime!
SP is available on request.

View 2 Replies

ADO.NET :: Stored Procedure Returning Multiple Ref Cursors

Nov 22, 2010

Is it possible to have a stored procedure return multiple ref cursors to a .Net application? I tried this, but it didn't work.

<code>
While odr.Read()
dtUserData.Load(odr, LoadOption.OverwriteChanges)
End While
odr.NextResult()
While odr.Read()
dtUserRoles.Load(odr, LoadOption.OverwriteChanges)
End While
</code>

View 1 Replies

SQL Server :: Playing With Stored Procedure And Returning Value To Parm

Nov 9, 2010

I been trying to get a simple stored procedure called and get its returned value back to a parm. In this case I am trying the simplest one I can make to get it to work and the I can build form there. I have tried to do this about 100 times and I try try and say heck with it and do it with out using a stored procedure and move on but I would really like to figure this out. I created a stored procedure called ReqLineItemTotal. It looks like this

USE [OnBillPROD]
GO
/****** Object: StoredProcedure [dbo].[ReqLineItemTotal]
Script Date: 11/09/2010 14:08:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Author: Bill Blair
-- Create date: 11-9-2010
-- Description: ReqLineItemTotal
-- ALTER PROCEDURE [dbo].[ReqLineItemTotal]
-- Add the parameters for the stored procedure here
@SessionIDNo nvarchar(50) = 0

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here select
(select SUM(dbo.TempReqDetail.Amount)
from dbo.TempReqDetail,dbo.tblReqAmountTypes
where dbo.tblReqAmountTypes.AmountType = dbo.TempReqDetail.AmountType and
dbo.tblReqAmountTypes.NumericOpperator = '+' and
dbo.TempReqDetail.SessionID = @SessionIDNo) -
(select SUM(dbo.TempReqDetail.Amount)
from dbo.TempReqDetail,dbo.tblReqAmountTypes
where dbo.tblReqAmountTypes.AmountType = dbo.TempReqDetail.AmountType and
dbo.tblReqAmountTypes.NumericOpperator = '-' and
dbo.TempReqDetail.SessionID = @SessionIDNo) AS LineItemTotal
END

From what I understand I should be getting my total back as LineItemTotal. So I want to call it pass it my parm of @SessionIDNo and have its returned value set to my @LineItemTotal parm. Trying to use these stored proc's always seems like way more work than they are worth but perhaps if I ever get the hang of it would make since to me.

View 6 Replies

DataSource Controls :: Stored Procedure Returning Error?

May 24, 2010

Im trying use a storedprocedure where depending on the status of the room if ='Vacant' And checkIn =date and the result will be put in a gridview, but i kept returning an error that says, "Conversion failed when convertng datetime from character string". Heres the code.

Caller:

[Code]....

Stored Procedure:

[Code]....

GridviewFiller:

[Code]....

View 7 Replies

DataSource Controls :: Stored Procedure And Function Returning Same Shape

Jan 26, 2010

I have problem with a stored procedure and function that return the same shape, but as they are different methods LINQ won' t let me cast between the two. Please see code for the two SPROCs below (This is actually simplified to represent the problem, so please don't spend lots of time explaining alternative ways of writing the function/sproc.)

Table Valued Function
"getCategories()"
SELECT categoryName, categoryID FROM categories
SPROC "getUnusedCategories"
SELECT categoryName, categoryID FROM CategoryInstances cI
FROM dbo.SMAN_getCategories(@SiteMANID, @SectionID) c
LEFT JOIN @EntityResultsTable er

View 2 Replies

ADO.NET :: Scalar Entity Framework Stored Procedure Returning Scope_Identity()?

Nov 30, 2010

am new to entity frame work and having a hell of time building a stored procedure that insertsa value into a table using a Scalar stored procedure and returning the new ID which is a Unique Identifier. I am trying

[code]....

View 1 Replies

DataSource Controls :: Eliminate Returning Of Empty Table From Stored Procedure

Jul 12, 2010

I have a Procedure which should return only 1 Record on selecting record according to Priority.

BEGIN
DECLARE @Accounts nvarchar(MAX)
SELECT @Accounts = ISNULL([map_user_modules].[Accounts], '') FROM [map_user_modules]
WHERE [map_user_modules].[id_user] = 231 AND [map_user_modules].[id_module] = 5
IF ((@Accounts = '') OR (@Accounts = 'All'))
SELECT @Accounts = null

BEGIN
Select @@ROWCOUNT as Priority2
END
IF @@ROWCOUNT = 1
BEGIN
Return
END

BEGIN
Select @@ROWCOUNT as Priority3
END
IF @@ROWCOUNT = 1
BEGIN
Return
END

BEGIN
Select @@ROWCOUNT as Priority4
END
IF @@ROWCOUNT = 1
BEGIN
Return
END

In the above statement Suppose we take a scenario where Select @@ROWCOUNT as Priority3 is executed, I get two tables returned, One is the Table returned by Select @@ROWCOUNT as Priority3 and the other is the Table returned by Select @@ROWCOUNT as Priority2 which has no Records. How can I eliminate the Empty table that returns no records.

View 1 Replies

DataSource Controls :: Stored Procedure Returning Null But Executes At SQL Server

Jul 1, 2010

I am using
SqlHelper.ExecuteDataset(MyConnectionString, CommandType.StoredProcedure, "GetInformation", new SqlParameter("@ID", ID));

In my stored Procedure, I am doing a lot of calculation with joins and it took 19 sec to execute at my sql Server 2008. My application had been working perfectly for 5 days but now as database increases, I found a problem that my connection is breaking at that point. At Sql Server, it execute fine but when I add a breakpoint and watch the stored procedure is returning null.

View 2 Replies

Forms Data Controls :: Returning Single String Result Through Stored Procedure

Jun 22, 2010

I am trying to return single string result through a stored procedure and Linq. This works fine when I test the stored procedure but it is not returning the value to the page. The value returned is always 0 which is the return value. This is the stored procedure:

ALTER PROCEDURE Login
@CustomerName nvarchar(50),
@Password nvarchar(50) output
As
Select @Password=Password
From Customers
Where Customers.CustomerName=@CustomerName

This is the vb
ALTER PROCEDURE Login
@CustomerName nvarchar(50),
@Password nvarchar(50) output
As
Select @Password=Password
From Customers
Where Customers.CustomerName=@CustomerName is the code created by the designer

This is the designer function
<FunctionAttribute(Name:="dbo.Login")> _
Public Function Login(<Parameter(Name:="CustomerName", DbType:="NVarChar(50)")> ByVal customerName

As String, <Parameter(Name:="Password", DbType:="NVarChar(50)")> ByRef password As String) As IntegerDim result
As IExecuteResult = Me.ExecuteMethodCall(Me, CType(MethodInfo.GetCurrentMethod,MethodInfo), customerName, password)
password = CType(result.GetParameterValue(1),String)
End Function

View 4 Replies

C# - Modify Stored Procedure Parameter Value?

Feb 4, 2011

My Stored procedure takes one parameter @IDs and it has values in it like '1,2,3'. I want to get rid of these single quotes. How can I do it? Like I want just 1,2,3 and NOT '1,2,3'. How do I modify this parameter value inside my SP?

View 3 Replies

Passing A Parameter To Stored Procedure In C#?

Mar 23, 2011

When I try the following:

[Code]....

I get an error saying No overload for Add method takes 2 arguments. HOw would i pass the PaycheckID in then as a paramter?

View 2 Replies

How To Specify Parameter Value For Stored Procedure In SqlDataSource

Mar 16, 2011

Being new to using the declarative syntax of SqlDataSource I am trying to figure out a way to set the value of a parameter to a stored procedure. I have a Client_ID that is passed via the Request Object and I need to set the Client_ID before the stored procedure of the SqlDataSource is executed.

Does the stored procedure parameter have to be predefined in the ASPX markup or can it be added dynamically in the code-behind? Would anyone have an example that demonstrates the SqlDataSource markup with a stored procedure and parameter as well as setting that parameter value in code-behind?

View 1 Replies

SQL Server :: How To Add An Output Parameter To A Stored Procedure

Oct 7, 2010

how do I add an output parameter to a Stored Procedure that returns a code or string to the calling app

View 1 Replies

Way To Displaying SQL Stored Procedure Output Parameter Value

Jul 30, 2010

I am having some trouble with the below code snippet. It is a stored procedure which executes on load that has an output parameter which should display in a label. Familiar which Vb however not so much in C#.

View 2 Replies

SQL Server :: Stored Procedure - Specify The Database As A Parameter?

Jan 26, 2011

I have an application with 40 or so separate databases that share a number of identical table schemas. Each is for a separate agency that performs functions identical (nearly) to the others. I have a number of reports that involve complex queries that each agency is required to produce. There is a "top-level" agency that would like to run the same reports but have the data consolidated for ALL agencies. I have tried having the query generated simply re-produced for each sub-agency by using a "UNION" ..

SELECT .... FROM [Agency].[dbo].[Table] ... UNION SELECT ... FROM [Next Agency].[dbo].[Table]....

This works for a few agencies selected/included but when they try to select all agencies or more than a few, it fails because the query is too large. I have tested a stored procedure:

CREATE PROC Test @DB AS
SELECT ... FROM [@DB].[dbo].[Table]...

Thinking I can build a smaller query by having up to 40 items of "EXEC Test (db) UNION EXEC Test (next db)" That also fails.... won't permit the database specifier as a parameter (plus, I don't know if I can do a UNION on the EXEC results).

View 9 Replies

SQL Server :: Add More Values For One Parameter In Stored Procedure?

Jan 9, 2011

i made stored procedure like that

select AssetCode ,Description
,EquipmentID from [FATMS].[fnMaintenanceHeaderRead](@ContextUser,@ContextClient,@ContextLang,@ContextApp,@ContextOrg,'1',@fnXmlParamMaintenanceHeader)
MH
where MH.Status
IN
( @Status)

when i put three values for parameter (@status) not work but one value work

how to put three values please to parameter @status when excecute that stored procedure

EXEC @return_value = [FATMS].[spMaintenanceReport]
@ContextUser = N'4806',
@ContextClient = 1,
@ContextLang = N'en-US',
@ContextApp = N'FATMS',
@ContextOrg = 23,
@ActionCode = N'1',
@MaintenanceHeaderId='',
@SearchText='',
@EntryType='0',
@Status=N' '
SELECT 'Return Value' = @return_value

View 12 Replies

MVC :: Linq To SQL Using Stored Procedure

Feb 15, 2010

I am trying to re-write an existing asp.net website using MVC with Linq to SQL. A page we have is used to insert new contracts and uses usp_contract_insert to insert a new contract into t_contract as well as t_project. On my dbml file i have dragged t_contract on and then I try to drag usp_contract_insert on top of that and I get the error :- Microsoft Visual Studio

One or more selected database objects return a schema that does not match the schema of the target data class. Nothing has been added to the designer. The stored procedure returns the id of the last project which was inserted. I don't quite follow this and unsure how to go about hooking my Controller up using Linq to SQL to call this stored procedure

View 7 Replies







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