?
Solved

Retrive result from stored procedure in web application

Posted on 2011-04-19
5
Medium Priority
?
203 Views
Last Modified: 2012-05-11
Hi,

I have created a Stored Procedure  which select some rows and inserts them into a temporary table before the table (tmpCust) gets dropped again.

It is also used by a web service for some others usage.

I would like to receive the content from the SP in a asp.net application but is a little uncertain how?
Lets say that I wanted the content to be displayed in a gridview.

I have made a test page where I have placed a calendar control and a gridview but with no code.

My SP:
 
GO
/****** Object:  StoredProcedure [dbo].[CustomerWebServiceGetByDate]    Script Date: 04/19/2011 20:59:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[CustomerWebServiceGetByDate]
           @InputDate as datetime = '1753-01-01 00:00:000'
  
        
as
--declare @InputDate as datetime
--set @InputDate = '2009-12-31 12:30:000' --'1753-01-01 00:00:000'
--set @InputDate = dateadd(day,-5,getdate())
declare @day as varchar(2)
declare @month as varchar(2)
set @day = datepart(dd,@InputDate)
set @month = datepart(mm,@InputDate)
if len(@day) = 1
begin
           set @day = '0' + cast(@day as char(1))
end
if len(@month) = 1
begin
           set @month = '0' + cast(@month as char(1))
end
set @InputDate = cast(datepart(yyyy,@InputDate) as varchar(4)) + '-' + 
                                             cast(@month as varchar(2)) + '-' +
                                            cast(@day as varchar(2)) + ' 00:00:000'


create table dbo.tmpCust
(CustomerNo varchar(50),
ProductionDate datetime,
DiscontinuedDate datetime,
StopProductionDate datetime)

if @InputDate <> '1753-01-01 00:00:000'
           begin
                      insert into dbo.tmpCust
                      SELECT distinct
                      CustomerNo,
                      ProductionDate,
                      DiscontinuedDate,
                      StopProductionDate
                      FROM Customer
                      Where 
                                 (--ProductionDate <= @InputDate
                                 (ProductionDate <= @InputDate and ProductionDate <> '1753-01-01 00:00:000')
                                 and 
                                 StopProductionDate = '1753-01-01 00:00:000'
                                 and
                                 (DiscontinuedDate >= @InputDate or DiscontinuedDate = '1753-01-01 00:00:000')
                                 )
                      or
                                 (--ProductionDate <= @InputDate
                                 (ProductionDate <= @InputDate and ProductionDate <> '1753-01-01 00:00:000')
                                 and 
                                 StopProductionDate >= @InputDate
                                 and
                                 (DiscontinuedDate >= @InputDate or DiscontinuedDate = '1753-01-01 00:00:000')
                                 )
                      order by CustomerNo
           end
else
           begin
                      insert into dbo.tmpCust
                      SELECT distinct
                      CustomerNo,
                      ProductionDate,
                      DiscontinuedDate,
                      StopProductionDate
                      FROM Customer
                      Where 
                      StopProductionDate <= getdate()
                      order by CustomerNo
           end

select distinct CustomerNo
from dbo.tmpCust
order by CustomerNo

drop table tmpCust

Open in new window

0
Comment
Question by:Madsing
  • 4
5 Comments
 

Author Comment

by:Madsing
ID: 35427987
I have changed the SP to only contain a select statement.

When I then bind it with a gridview and test it I get a Servererror

Login failed for user 'myusername'.   Before I ran it in a browser I tested the query in the "Configure  Data source" wizard with no problem. I was asked to type in my password twice though, but it didn't fail?!

Please help?

0
 

Author Comment

by:Madsing
ID: 35427995
The new SP:

 
USE [SQL2008_640987_merevind]
GO
/****** Object:  StoredProcedure [dbo].[CustomerWebServiceGetByDate2]    Script Date: 04/19/2011 22:11:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[CustomerWebServiceGetByDate2]
           @InputDate as datetime = '1753-01-01 00:00:000'
  
        
as
--declare @InputDate as datetime
--set @InputDate = '2009-12-31 12:30:000' --'1753-01-01 00:00:000'
--set @InputDate = dateadd(day,-5,getdate())
declare @day as varchar(2)
declare @month as varchar(2)
set @day = datepart(dd,@InputDate)
set @month = datepart(mm,@InputDate)
if len(@day) = 1
begin
           set @day = '0' + cast(@day as char(1))
end
if len(@month) = 1
begin
           set @month = '0' + cast(@month as char(1))
end
set @InputDate = cast(datepart(yyyy,@InputDate) as varchar(4)) + '-' + 
                                             cast(@month as varchar(2)) + '-' +
                                            cast(@day as varchar(2)) + ' 00:00:000'



                      SELECT distinct
                      CustomerNo,
                      ProductionDate,
                      DiscontinuedDate,
                      StopProductionDate
                      FROM Customer
					WHERE 
					
					@InputDate <= [StopProductionDate]

Open in new window

0
 
LVL 31

Expert Comment

by:MlandaT
ID: 35428483
SqlConnection conn = new SqlConnection("Data Source=myDBServer;Database=myDB;User ID=myUser; Password=myPassword");

SqlCommand command = new SqlCommand("CustomerWebServiceGetByDate2", conn);

command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@InputDate", SqlDbType.DateTime).Value = Now;

SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();

adapter.Fill(ds);

myGridView.DataSource = ds;
myGridView.DataBind;

Open in new window


If you are using Visual Basic, you can convert this code here: http://www.developerfusion.com/tools/convert/csharp-to-vb/

Login failed for user 'myusername' - Check your connection string. Make sure that the username and password are correct. See here for guidance http://www.connectionstrings.com/sql-server-2005

0
 

Accepted Solution

by:
Madsing earned 0 total points
ID: 35702176
I found the solution. The SP dropped the tables so there was no data for the gridview to display.

Im closing the question here.
0
 

Author Closing Comment

by:Madsing
ID: 35726950
Wrong query syntax in SP
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Integration Management Part 2
Loops Section Overview

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question