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>
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 RepliesI 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
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.
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
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 RepliesI 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.
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.
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]....
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
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>
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]....
I can return single integer values when using stored procedures with Linq but I cant get single string values returned.
View 2 RepliesI 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.
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.
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
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 RepliesThis is surareddy. i nead some small clarification in the "Stored Procedure"
how to convert the oracle Stored Procedure to sqlserver2005/2008 Stored Procedure.
right now i am enhancing the project that project already developed the oracle Stored Procedure. now our company is using sqlserver 2005/2008.
how to convert the Oracle Stored Procedure to sqlserver 2005 Stored Procedure
Initially, I have tried to use stored procedure. But I changed my mind and preferred to call sql query in codebase with command text. However, it stills tries to find initially-called stored procedure (which is neither called or exists).I think that it is related caching. But I tried it with different browsers it did not work.What might be the reason?
View 4 RepliesI have a table with 4 columns.The first column is a list of teams.The other three columns specify whether the team member with the particular id is present on any particular day .Say for example in the third row the running Team has 2 and 3 which means team member with the id 2 and 3 are present.Multiple values are seperated by the pipe symbol '|'.I have a Query like this
Select Teamname from tblTeam where Running not like '%|2|%' and Running not like '%|3|%' and Running not like '%|7|%' and Biking not like '%|1|%' and Biking not like '%|4|%' and Biking not like '%|7|%' and shooting not like '%|3|%' and shooting not like '%|4|%' and shooting not like '%|7|%' Basically i am searching the columns with like clause.The search criteria for each column may vary.In the above query i am searching for Teams where Running column does not contain 2,3 and 7,Biking column does not contain 1,4 and 7 and the shooting column does not contain 3,4 and 7. I would like to set a stored procedure where i would be able to pass 3 set of parameters from the code behind(I am using VB.net) for Running column search,Biking column search and the shooting column search.
Can someone tell me writing a simple stored procedure.I have a table with 4 columns.The first column is list of teams.The other three columns specify whether the team member with the particular id is present or not on any particular day .Say for example in the third row the running column has 2 and 3 which means team member with the id 2 and 3 are present.Multiple values are seperated by the pipe symbol '|'.
I'm a novice in .NET programming. I was trying to figure a way to make a MULTI TABLE search via a stored procedure for my website. I've written a SP to select records from one table, which isn't working for some unknown reasons:
[Code]....
Whatever I search for, the datagrid is displaying all records from my table.
I get the fol error: An exception of type 'System.Data.SqlClient.SqlException' Occurred in System.Data.dll but was not handled in user code..Subquery returned more than 1 value. This is not permitted When the subquery Follows =,! =, <, <=,>,> = Or When the subquery is used as an expression.
IF EXISTS (SELECT CustomerId FROM Customers where CustomerId= (SELECT Id FROM @tblCustomers ))
UPDATE Customers
SET Name=(SELECT Name FROM @tblCustomers ), Country=(SELECT Country FROM @tblCustomers )
WHERE CustomerId=(SELECT Id FROM @tblCustomers );
Else
[code]...
I am trying to what i think is an easy task, but so far i have came up empty.
What i want to do is take a repeating table on a form i have and submit the employee IDs to a database using a web service.
I know how to submit them one by one, but i am thinking there is an easier way to do this instead of send the value to the web service, submit thee data, and then go till the last record.
I am currently using this:
<WebMethod(Description:="sends employee id to database")> _
Public Function SendMultiple(ByVal strEmployeeID as string) As String
Dim sqlCon As New SqlConnection()
Dim sqlCmd As New SqlCommand()
sqlCon.ConnectionString = "connection string information"
If sqlCon.State = ConnectionState.Closed Then
sqlCon.Open()
End If
sqlCmd.CommandText = "spInsertMultiple"
sqlCmd.Connection = sqlCon
sqlCmd.CommandType = CommandType.StoredProcedure
Dim Parameter0 As SqlParameter = New SqlParameter("@EmployeeID", strEmployeeID)
Parameter0.Direction = ParameterDirection.Input
sqlCmd.Parameters.Add(Parameter0)
Dim dr As SqlDataReader
dr = sqlCmd.ExecuteScalar
Dim strReturnedValue As String = "hello"
Return strReturnedValue
End Function
I'm using the Autocomplete extender, it works great however I need to be able to display multiple columns from my stored procedure as have in the example below. The user enters a location #, I need it to display the location number and the address formatted, but when the user selects the correct location, I ONLY want the location number to populate my textbox:
1234
Address
City, State Zip
I've had a real hard time trying to figure out how to do this,. Here's my stored procedure and CS code.
[Code]....
Here's my CS:
[Code]....
Just after some advice I have a stored procedure that returns several tables. I then need to loop through the tables and create dynamic gridviews using the data. At the moment this stored proc is called using
[Code]....
I have used an xsd file to populate everthing else, but due to this returning multiple tables i wasn't sure how this works should it just be in its own xsd? as all the oather i just get a datatable where as with this i need a dataset... how this makes sense...
