Displaying Stored Procedures using Grid View

B

brwalias

Hi,

using .net 2
sql server 2005

Here is my situation:

I'm passing a variable in the url from a selection on Page A and need
to display the results on the Results page be based on that variable
in the url. I would like that variable to trigger a stored and
display information in formView or Grid View. Whats the best way to
accomplish this?

Page A

This is passing var from grid view:
<asp:HyperLinkField DataTextField="Open Issues"
DataNavigateUrlFields="Ticket Number"
DataNavigateUrlFormatString="results2.aspx?{0}" Target="_blank"
HeaderText="Open Issues" />

Results Page

<asp:FormView ID="FormView1" runat="server" DataKeyNames="Ticket
Number"
DataSourceID="SqlDataSource1"
OnPageIndexChanging="FormView1_PageIndexChanging">
<EditItemTemplate>
Ticket Number:
<asp:Label ID="Ticket_NumberLabel1" runat="server"
Text='<%# Eval("[Ticket Number]") %>'>
</asp:Label><br />
Technician Assigned:
<asp:TextBox ID="Technician_AssignedTextBox"
runat="server" Text='<%# Bind("[Technician Assigned]") %>'>
</asp:TextBox><br />
Issue Description:
<asp:TextBox ID="Issue_DescriptionTextBox"
runat="server" Text='<%# Bind("[Issue Description]") %>'>
</asp:TextBox><br />
Open Date:
<asp:TextBox ID="Open_DateTextBox" runat="server"
Text='<%# Bind("[Open Date]") %>'>
</asp:TextBox><br />
Work Notes:
<asp:TextBox ID="Work_NotesTextBox" runat="server"
Text='<%# Bind("[Work Notes]") %>'>
</asp:TextBox><br />
<asp:LinkButton ID="UpdateButton" runat="server"
CausesValidation="True" CommandName="Update"
Text="Update">
</asp:LinkButton>
<asp:LinkButton ID="UpdateCancelButton"
runat="server" CausesValidation="False" CommandName="Cancel"
Text="Cancel">
</asp:LinkButton>
</EditItemTemplate>
<InsertItemTemplate>
Ticket Number:
<asp:TextBox ID="Ticket_NumberTextBox"
runat="server" Text='<%# Bind("[Ticket Number]") %>'>
</asp:TextBox><br />
Technician Assigned:
<asp:TextBox ID="Technician_AssignedTextBox"
runat="server" Text='<%# Bind("[Technician Assigned]") %>'>
</asp:TextBox><br />
Issue Description:
<asp:TextBox ID="Issue_DescriptionTextBox"
runat="server" Text='<%# Bind("[Issue Description]") %>'>
</asp:TextBox><br />
Open Date:
<asp:TextBox ID="Open_DateTextBox" runat="server"
Text='<%# Bind("[Open Date]") %>'>
</asp:TextBox><br />
Work Notes:
<asp:TextBox ID="Work_NotesTextBox" runat="server"
Text='<%# Bind("[Work Notes]") %>'>
</asp:TextBox><br />
<asp:LinkButton ID="InsertButton" runat="server"
CausesValidation="True" CommandName="Insert"
Text="Insert">
</asp:LinkButton>
<asp:LinkButton ID="InsertCancelButton"
runat="server" CausesValidation="False" CommandName="Cancel"
Text="Cancel">
</asp:LinkButton>
</InsertItemTemplate>
<ItemTemplate>
Ticket Number:
<asp:Label ID="Ticket_NumberLabel" runat="server"
Text='<%# Eval("[Ticket Number]") %>'>
</asp:Label><br />
Technician Assigned:
<asp:Label ID="Technician_AssignedLabel"
runat="server" Text='<%# Bind("[Technician Assigned]") %>'>
</asp:Label><br />
Issue Description:
<asp:Label ID="Issue_DescriptionLabel"
runat="server" Text='<%# Bind("[Issue Description]") %>'>
</asp:Label><br />
Open Date:
<asp:Label ID="Open_DateLabel" runat="server"
Text='<%# Bind("[Open Date]") %>'>
</asp:Label><br />
Work Notes:
<asp:Label ID="Work_NotesLabel" runat="server"
Text='<%# Bind("[Work Notes]") %>'>
</asp:Label><br />
</ItemTemplate>
</asp:FormView>
 
A

Alexey Smirnov

Hi,

using .net 2
sql server 2005

Here is my situation:

I'm passing a variable in the url from a selection on Page A and need
to display the results on the Results page be based on that variable
in the url. I would like that variable to trigger a stored and
display information in formView or Grid View. Whats the best way to
accomplish this?

Page A

This is passing var from grid view:
<asp:HyperLinkField DataTextField="Open Issues"
DataNavigateUrlFields="Ticket Number"
DataNavigateUrlFormatString="results2.aspx?{0}" Target="_blank"
HeaderText="Open Issues" />

Results Page

<asp:FormView ID="FormView1" runat="server" DataKeyNames="Ticket
Number"
DataSourceID="SqlDataSource1"
OnPageIndexChanging="FormView1_PageIndexChanging">
<EditItemTemplate>
Ticket Number:
<asp:Label ID="Ticket_NumberLabel1" runat="server"
Text='<%# Eval("[Ticket Number]") %>'>
</asp:Label><br />
Technician Assigned:
<asp:TextBox ID="Technician_AssignedTextBox"
runat="server" Text='<%# Bind("[Technician Assigned]") %>'>
</asp:TextBox><br />
Issue Description:
<asp:TextBox ID="Issue_DescriptionTextBox"
runat="server" Text='<%# Bind("[Issue Description]") %>'>
</asp:TextBox><br />
Open Date:
<asp:TextBox ID="Open_DateTextBox" runat="server"
Text='<%# Bind("[Open Date]") %>'>
</asp:TextBox><br />
Work Notes:
<asp:TextBox ID="Work_NotesTextBox" runat="server"
Text='<%# Bind("[Work Notes]") %>'>
</asp:TextBox><br />
<asp:LinkButton ID="UpdateButton" runat="server"
CausesValidation="True" CommandName="Update"
Text="Update">
</asp:LinkButton>
<asp:LinkButton ID="UpdateCancelButton"
runat="server" CausesValidation="False" CommandName="Cancel"
Text="Cancel">
</asp:LinkButton>
</EditItemTemplate>
<InsertItemTemplate>
Ticket Number:
<asp:TextBox ID="Ticket_NumberTextBox"
runat="server" Text='<%# Bind("[Ticket Number]") %>'>
</asp:TextBox><br />
Technician Assigned:
<asp:TextBox ID="Technician_AssignedTextBox"
runat="server" Text='<%# Bind("[Technician Assigned]") %>'>
</asp:TextBox><br />
Issue Description:
<asp:TextBox ID="Issue_DescriptionTextBox"
runat="server" Text='<%# Bind("[Issue Description]") %>'>
</asp:TextBox><br />
Open Date:
<asp:TextBox ID="Open_DateTextBox" runat="server"
Text='<%# Bind("[Open Date]") %>'>
</asp:TextBox><br />
Work Notes:
<asp:TextBox ID="Work_NotesTextBox" runat="server"
Text='<%# Bind("[Work Notes]") %>'>
</asp:TextBox><br />
<asp:LinkButton ID="InsertButton" runat="server"
CausesValidation="True" CommandName="Insert"
Text="Insert">
</asp:LinkButton>
<asp:LinkButton ID="InsertCancelButton"
runat="server" CausesValidation="False" CommandName="Cancel"
Text="Cancel">
</asp:LinkButton>
</InsertItemTemplate>
<ItemTemplate>
Ticket Number:
<asp:Label ID="Ticket_NumberLabel" runat="server"
Text='<%# Eval("[Ticket Number]") %>'>
</asp:Label><br />
Technician Assigned:
<asp:Label ID="Technician_AssignedLabel"
runat="server" Text='<%# Bind("[Technician Assigned]") %>'>
</asp:Label><br />
Issue Description:
<asp:Label ID="Issue_DescriptionLabel"
runat="server" Text='<%# Bind("[Issue Description]") %>'>
</asp:Label><br />
Open Date:
<asp:Label ID="Open_DateLabel" runat="server"
Text='<%# Bind("[Open Date]") %>'>
</asp:Label><br />
Work Notes:
<asp:Label ID="Work_NotesLabel" runat="server"
Text='<%# Bind("[Work Notes]") %>'>
</asp:Label><br />
</ItemTemplate>
</asp:FormView>
Click to expand...

The SqlDataSource Control has a SelectParameter section where you can
specify parameters for your query. There is the QueryStringParameter
section used to bind the values of the query string to a parameter
used in a parameterized query.

So, your code would be as the follows

<asp:SqlDataSource ID="SqlDataSource1"
SelectCommand="SELECT .... WHERE state=@state"
....>
<SelectParameters>
<asp:QueryStringParameter Name="state" QueryStringField="state" />
</SelectParameters>
</asp:SqlDataSource>

Where:

Name is the name of @parameter
QueryStringField is the key section of the QueryString

Note, usage of the QueryStringParameter does not perform any
validation of input from the client and the real best way to
accomplish this is the code-behind model.

The QueryStringParameter does not validate the value passed by the
query string in any way; it uses the raw value. In
 
A

Alexey Smirnov

The QueryStringParameter does not validate the value passed by the
query string in any way; it uses the raw value. In- Hide quoted text -
Click to expand...

Too quick on send button: The QueryStringParameter does not validate
the value passed by the query string in any way; it uses the raw
value. In most cases you can validate the value of the
QueryStringParameter before it is used by a data source control by
handling an event, such as the Selecting, Updating, Inserting, or
Deleting event exposed by the data source control you are using. If
the value of the parameter does not pass your validation tests, you
can cancel the data operation by setting the Cancel property of the
associated CancelEventArgs class to true.

http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.querystringparameter(VS.80).aspx
 
B

brwalias

Page A:

This is a hyper link which represents ticketid generated from sql that
the user will click on to view row. once clicked, a new window needs
to open with additional details from that row stored in a stored
procedure.

<asp:HyperLinkField DataTextField="Open Issues"
DataNavigateUrlFields="Ticket Number"
DataNavigateUrlFormatString="results2.aspx?ticketid={0}"
Target="_blank" HeaderText="Open Issues" />

--------------------------

var seems to pass as i have it printing on the results page just to
confirm. I have also added the details view as follows:

<asp:DataList ID="DataList1" runat="server" DataKeyField="Ticket
Number" DataSourceID="SqlDataSource1">

<ItemTemplate>
Ticket Number:
<asp:Label ID="Ticket_NumberLabel" runat="server"
Text='<%# Eval("[Ticket Number]") %>'>
</asp:Label><br />
Technician Assigned:
<asp:Label ID="Technician_AssignedLabel"
runat="server" Text='<%# Eval("[Technician Assigned]") %>'>
</asp:Label><br />
Issue Description:
<asp:Label ID="Issue_DescriptionLabel"
runat="server" Text='<%# Eval("[Issue Description]") %>'>
</asp:Label><br />
Open Date:
<asp:Label ID="Open_DateLabel" runat="server"
Text='<%# Eval("[Open Date]") %>'>
</asp:Label><br />
Work Notes:
<asp:Label ID="Work_NotesLabel" runat="server"
Text='<%# Eval("[Work Notes]") %>'>
</asp:Label><br />
<br />
</ItemTemplate>
</asp:DataList><asp:SqlDataSource ID="SqlDataSource1"
runat="server" ConnectionString="<%$
ConnectionStrings:webhelp2ConnectionString %>"
SelectCommand="sp_dticket"
SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:QueryStringParameter Name="ticketid"
QueryStringField="ticketid" />
</SelectParameters>
</asp:SqlDataSource>

--------------------------------------------

Stored procedure i'm using:

Declare @ticketid as varchar (20)
Declare @Technician as varchar (20)


set @Technician=(SELECT email

FROM dbo.tech INNER JOIN dbo.job_ticket ON tech.client_id =
job_ticket.assigned_tech_id WHERE job_ticket.job_ticket_id =Cast
(@ticketid as varchar (20)))

SELECT job_ticket.job_ticket_id 'Ticket Number', @Technician
'Technician Assigned',job_ticket.subject 'Issue Description',
report_date 'Open Date',tech_note.note_text 'Work Notes'

FROM job_ticket INNER JOIN dbo.tech_note ON tech_note.job_ticket_Id =
job_ticket.job_ticket_id WHERE job_ticket.job_ticket_id =Cast
(@ticketid as varchar (20))


the query works as it should in sql but once i bring it into vs web
developer the browser gives me errors. It just need the details view
on the results page to pull the sp from sql based off the var being
passed in the url.

Thanks again,

Bryan
 
B

brwalias

Too quick on send button: The QueryStringParameter does not validate
the value passed by the query string in any way; it uses the raw
value. In most cases you can validate the value of the
QueryStringParameter before it is used by a data source control by
handling an event, such as the Selecting, Updating, Inserting, or
Deleting event exposed by the data source control you are using. If
the value of the parameter does not pass your validation tests, you
can cancel the data operation by setting the Cancel property of the
associated CancelEventArgs class to true.

http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.qu...
Click to expand...



Page A:

This is a hyper link which represents ticketid generated from sql that
the user will click on to view row. once clicked, a new window needs
to open with additional details from that row stored in a stored
procedure.

<asp:HyperLinkField DataTextField="Open Issues"
DataNavigateUrlFields="Ticket Number"
DataNavigateUrlFormatString="results2.aspx?ticketid={0}"
Target="_blank" HeaderText="Open Issues" />

--------------------------

var seems to pass as i have it printing on the results page just to
confirm. I have also added the details view as follows:

<asp:DataList ID="DataList1" runat="server" DataKeyField="Ticket
Number" DataSourceID="SqlDataSource1">

<ItemTemplate>
Ticket Number:
<asp:Label ID="Ticket_NumberLabel" runat="server"
Text='<%# Eval("[Ticket Number]") %>'>
</asp:Label><br />
Technician Assigned:
<asp:Label ID="Technician_AssignedLabel"
runat="server" Text='<%# Eval("[Technician Assigned]") %>'>
</asp:Label><br />
Issue Description:
<asp:Label ID="Issue_DescriptionLabel"
runat="server" Text='<%# Eval("[Issue Description]") %>'>
</asp:Label><br />
Open Date:
<asp:Label ID="Open_DateLabel" runat="server"
Text='<%# Eval("[Open Date]") %>'>
</asp:Label><br />
Work Notes:
<asp:Label ID="Work_NotesLabel" runat="server"
Text='<%# Eval("[Work Notes]") %>'>
</asp:Label><br />
<br />
</ItemTemplate>
</asp:DataList><asp:SqlDataSource ID="SqlDataSource1"
runat="server" ConnectionString="<%$
ConnectionStrings:webhelp2ConnectionString %>"
SelectCommand="sp_dticket"
SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:QueryStringParameter Name="ticketid"
QueryStringField="ticketid" />
</SelectParameters>
</asp:SqlDataSource>

--------------------------------------------

Stored procedure i'm using:

Declare @ticketid as varchar (20)
Declare @Technician as varchar (20)


set @Technician=(SELECT email

FROM dbo.tech INNER JOIN dbo.job_ticket ON tech.client_id =
job_ticket.assigned_tech_id WHERE job_ticket.job_ticket_id =Cast
(@ticketid as varchar (20)))

SELECT job_ticket.job_ticket_id 'Ticket Number', @Technician
'Technician Assigned',job_ticket.subject 'Issue Description',
report_date 'Open Date',tech_note.note_text 'Work Notes'

FROM job_ticket INNER JOIN dbo.tech_note ON tech_note.job_ticket_Id =
job_ticket.job_ticket_id WHERE job_ticket.job_ticket_id =Cast
(@ticketid as varchar (20))


the query works as it should in sql but once i bring it into vs web
developer the browser gives me errors. It just need the details view
on the results page to pull the sp from sql based off the var being
passed in the url.

Thanks again
 
A

Alexey Smirnov

the query works as it should in sql but once i bring it into vs web
developer the browser gives me errors. It just need the details view
on the results page to pull the sp from sql based off the var being
passed in the url.
Click to expand...

The code you have posted here a T-SQL, do you mean that it is created
as a stored procedure in the database?

CREATE PROCEDURE ...

What is the "sp_dticket"?
 
B

brwalias

The code you have posted here a T-SQL, do you mean that it is created
as a stored procedure in the database?

CREATE PROCEDURE ...

What is the "sp_dticket"?
Click to expand...

Yes you are correct, we created sp_dticket as a stored procedure in
the database.
 
B

brwalias

Yes you are correct, we created sp_dticket as a stored procedure in
the database.
Click to expand...

here is the stored procedure:

Stored procedure i'm using:

Declare @ticketid as varchar (20)
Declare @Technician as varchar (20)

set @Technician=(SELECT email

FROM dbo.tech INNER JOIN dbo.job_ticket ON tech.client_id =
job_ticket.assigned_tech_id WHERE job_ticket.job_ticket_id =Cast
(@ticketid as varchar (20)))

SELECT job_ticket.job_ticket_id 'Ticket Number', @Technician
'Technician Assigned',job_ticket.subject 'Issue Description',
report_date 'Open Date',tech_note.note_text 'Work Notes'

FROM job_ticket INNER JOIN dbo.tech_note ON tech_note.job_ticket_Id =
job_ticket.job_ticket_id WHERE job_ticket.job_ticket_id =Cast
(@ticketid as varchar (20))
 
A

Alexey Smirnov

Yes you are correct, we created sp_dticket as a stored procedure in
the database.
Click to expand...

Well, but the following code does then?

Declare @ticketid as varchar (20)
Declare @Technician as varchar (20)

when you have the following code:

<asp:QueryStringParameter Name="ticketid"
QueryStringField="ticketid" />

you mean that the ticketid has to be the input parameter of the stored
procedure. And here as I see you do "Declare @ticketid", it means you
create a new parameter inside(?) the stored procedure

Please post the complete code of the sp_dticket here (starting CREATE
PROCEDURE...)
I want to look on it.

Thanks!
 
A

Alexey Smirnov

here is the stored procedure:

Stored procedure i'm using:

Declare @ticketid as varchar (20)
Declare @Technician as varchar (20)

set @Technician=(SELECT email

FROM dbo.tech INNER JOIN dbo.job_ticket ON tech.client_id =
job_ticket.assigned_tech_id WHERE job_ticket.job_ticket_id =Cast
(@ticketid as varchar (20)))

SELECT job_ticket.job_ticket_id 'Ticket Number', @Technician
'Technician Assigned',job_ticket.subject 'Issue Description',
report_date 'Open Date',tech_note.note_text 'Work Notes'

FROM job_ticket INNER JOIN dbo.tech_note ON tech_note.job_ticket_Id =
job_ticket.job_ticket_id WHERE job_ticket.job_ticket_id =Cast
(@ticketid as varchar (20))- Hide quoted text -
Click to expand...

Bryan, sorry it is not the stored procedure :)

The real stored procedure should look like

---------------------------------------------
CREATE PROCEDURE dbo.sp_dticket
(
@ticketid as varchar(20)
)
AS

Declare @Technician as varchar (20)

set @Technician=(SELECT email

FROM dbo.tech INNER JOIN dbo.job_ticket ON tech.client_id =
job_ticket.assigned_tech_id WHERE job_ticket.job_ticket_id =Cast
(@ticketid as varchar (20)))

SELECT job_ticket.job_ticket_id 'Ticket Number', @Technician
'Technician Assigned',job_ticket.subject 'Issue Description',
report_date 'Open Date',tech_note.note_text 'Work Notes'
FROM job_ticket INNER JOIN dbo.tech_note ON tech_note.job_ticket_Id =
job_ticket.job_ticket_id WHERE job_ticket.job_ticket_id =Cast
(@ticketid as varchar (20))

GO
---------------------------------------------

You should execute this code to create the procedure.

Once created you can test it as

EXEC sp_dticket 'yourtestticketid'
 
B

brwalias

Bryan, sorry it is not the stored procedure :)

The real stored procedure should look like

---------------------------------------------
CREATE PROCEDURE dbo.sp_dticket
(
@ticketid as varchar(20)
)
AS

Declare @Technician as varchar (20)

set @Technician=(SELECT email

FROM dbo.tech INNER JOIN dbo.job_ticket ON tech.client_id =
job_ticket.assigned_tech_id WHERE job_ticket.job_ticket_id =Cast
(@ticketid as varchar (20)))

SELECT job_ticket.job_ticket_id 'Ticket Number', @Technician
'Technician Assigned',job_ticket.subject 'Issue Description',
report_date 'Open Date',tech_note.note_text 'Work Notes'
FROM job_ticket INNER JOIN dbo.tech_note ON tech_note.job_ticket_Id =
job_ticket.job_ticket_id WHERE job_ticket.job_ticket_id =Cast
(@ticketid as varchar (20))

GO
---------------------------------------------

You should execute this code to create the procedure.

Once created you can test it as

EXEC sp_dticket 'yourtestticketid'
Click to expand...

Alexey,

That didn't appear to work. When i passed the variable in the url
nothing was displayed on the results page; not even an error. maybe
i'm not capturing the var correctly? Its easy to get the details view
to display a simple select query but this query seems to be giving me
more problems than expected. This third party schema we're working
with is far from forgiving.
 
B

brwalias

Bryan, sorry it is not the stored procedure :)

The real stored procedure should look like

---------------------------------------------
CREATE PROCEDURE dbo.sp_dticket
(
@ticketid as varchar(20)
)
AS

Declare @Technician as varchar (20)

set @Technician=(SELECT email

FROM dbo.tech INNER JOIN dbo.job_ticket ON tech.client_id =
job_ticket.assigned_tech_id WHERE job_ticket.job_ticket_id =Cast
(@ticketid as varchar (20)))

SELECT job_ticket.job_ticket_id 'Ticket Number', @Technician
'Technician Assigned',job_ticket.subject 'Issue Description',
report_date 'Open Date',tech_note.note_text 'Work Notes'
FROM job_ticket INNER JOIN dbo.tech_note ON tech_note.job_ticket_Id =
job_ticket.job_ticket_id WHERE job_ticket.job_ticket_id =Cast
(@ticketid as varchar (20))

GO
---------------------------------------------

You should execute this code to create the procedure.

Once created you can test it as

EXEC sp_dticket 'yourtestticketid'
Click to expand...

Alexey,

Please disregard my last post, it's working correctly! Thank you very
much for your time and patience working with me. Your help was
greatly appreciated.

Take care,

Bryan
 
A

Alexey Smirnov

Alexey,

Please disregard my last post, it's working correctly! Thank you very
much for your time and patience working with me. Your help was
greatly appreciated.

Take care,

Bryan- Hide quoted text -

- Show quoted text -
Click to expand...

You are very welcome
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Master-Details with GridView and FormView and Paging 0
FormView not updating 1
ASP.NET FormView Money Data Type Binding Problem with 2 Decimal Points.. 0
enter key causing postback and then errors 5
how do i insert userid in formview 6
GridView default column not enough for columns with alot of data 1
Update method always gets old values from a form view 4
need modal help 1

Top