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


Similar Messages:

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

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

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

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

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

ADO.NET :: How To Define Date Output Parameters For A Stored Procedure

Oct 15, 2010

I have a question. How do we define date output parameters for a stored procedure?

before you answer,I should mention that I am not referring to the sql syntax definition ,which is @mydate datetime output.

This is ok,what i am asking is HOW DO I SPECIFY IT IN LINQ WHEN I NEED TO CALL THE STORED PROCEDURE IN THE SERVER SIDE CODE. I am repeating that I do I am not referring to the sql definition .

View 1 Replies

DataSource Controls :: Stored Procedure - How To Get Date Within 12 Months

May 21, 2010

I am trying to add a condition where a section of my stored procedure will run if it is 12 months or less. Right now I have it set as

if @dtDate <= '5/21/2010' but I know that won't work because I want it to dynamically get today's date each day. Does any know how I would do this.

View 2 Replies

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

DataSource Controls :: Declaring Today's Date In A Stored Procedure?

Apr 6, 2010

Can someone tell me the syntax to declare today's date in a stored procedure? I've searched around and can't come up with a clear answer.

I'm trying to declare today's date in the stored procedure and then compare it to a date in a database?

Is it as simple as the following or am i way off?

[Code]....

View 5 Replies

Web Forms :: How To Give Conditions In Stored Procedure When From And To Date Are Not Mandatory

Apr 27, 2016

If the from date is given then i have to retreive all the values above from date .IF to date is given have to retrieve all the values below Todate.else between from and to date how it will make changes

Alter PROCEDURE uspSearchTransaction
@txtTID varchar(50) = null ,
@txtMID varchar(50) = null,
@txtCardNo varchar(50) = null,
@txtFromDate DateTime = null,

[Code] ....

View 1 Replies

DataSource Controls :: Writing Stored Procedure To Include Date Range?

Jun 22, 2010

Im building on a transaction website that gives the user a few choices for them to choose when they want to view their transactions history

They can choose:

1.Current Month
2. Last 1 month and current month
3. Last 2 month and current month
4. Select date range

How do I include this variable in the stored procedure?

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

SQL Server :: Have A Stored Procedure Execute Another Stored Procedure During Time Period?

Jan 28, 2011

I could probably figure this out if I tried to, but I have been working so long on code, I'm a little fried

I have a stored procedure, and I want to execute another stored procedure during a time period of lets say 1/1/2011 to 12/31/2011

How Would I accomplish this?

View 4 Replies







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