Displaying Stored Procedures using Grid View

[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:HyperLinkF ield DataTextField=" Open Issues"
DataNavigateUrl Fields="Ticket Number"
DataNavigateUrl FormatString="r esults2.aspx?{0 }" Target="_blank"
HeaderText="Ope n Issues" />

Results Page

<asp:FormView ID="FormView1" runat="server" DataKeyNames="T icket
Number"
DataSourceID="S qlDataSource1"
OnPageIndexChan ging="FormView1 _PageIndexChang ing">
<EditItemTempla te>
Ticket Number:
<asp:Label ID="Ticket_Numb erLabel1" 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_Descr iptionTextBox"
runat="server" Text='<%# Bind("[Issue Description]") %>'>
</asp:TextBox><br />
Open Date:
<asp:TextBox ID="Open_DateTe xtBox" runat="server"
Text='<%# Bind("[Open Date]") %>'>
</asp:TextBox><br />
Work Notes:
<asp:TextBox ID="Work_NotesT extBox" runat="server"
Text='<%# Bind("[Work Notes]") %>'>
</asp:TextBox><br />
<asp:LinkButt on ID="UpdateButto n" runat="server"
CausesValidatio n="True" CommandName="Up date"
Text="Update">
</asp:LinkButton>
<asp:LinkButt on ID="UpdateCance lButton"
runat="server" CausesValidatio n="False" CommandName="Ca ncel"
Text="Cancel">
</asp:LinkButton>
</EditItemTemplat e>
<InsertItemTemp late>
Ticket Number:
<asp:TextBox ID="Ticket_Numb erTextBox"
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_Descr iptionTextBox"
runat="server" Text='<%# Bind("[Issue Description]") %>'>
</asp:TextBox><br />
Open Date:
<asp:TextBox ID="Open_DateTe xtBox" runat="server"
Text='<%# Bind("[Open Date]") %>'>
</asp:TextBox><br />
Work Notes:
<asp:TextBox ID="Work_NotesT extBox" runat="server"
Text='<%# Bind("[Work Notes]") %>'>
</asp:TextBox><br />
<asp:LinkButt on ID="InsertButto n" runat="server"
CausesValidatio n="True" CommandName="In sert"
Text="Insert">
</asp:LinkButton>
<asp:LinkButt on ID="InsertCance lButton"
runat="server" CausesValidatio n="False" CommandName="Ca ncel"
Text="Cancel">
</asp:LinkButton>
</InsertItemTempl ate>
<ItemTemplate >
Ticket Number:
<asp:Label ID="Ticket_Numb erLabel" 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_Descr iptionLabel"
runat="server" Text='<%# Bind("[Issue Description]") %>'>
</asp:Label><br />
Open Date:
<asp:Label ID="Open_DateLa bel" runat="server"
Text='<%# Bind("[Open Date]") %>'>
</asp:Label><br />
Work Notes:
<asp:Label ID="Work_NotesL abel" runat="server"
Text='<%# Bind("[Work Notes]") %>'>
</asp:Label><br />
</ItemTemplate>
</asp:FormView>

[Alexey Smirnov]

On May 6, 6:29 pm, brwalias <brwal...@gmail .comwrote:

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:HyperLinkF ield DataTextField=" Open Issues"
DataNavigateUrl Fields="Ticket Number"
DataNavigateUrl FormatString="r esults2.aspx?{0 }" Target="_blank"
HeaderText="Ope n Issues" />

Results Page

<asp:FormView ID="FormView1" runat="server" DataKeyNames="T icket
Number"
DataSourceID="S qlDataSource1"
OnPageIndexChan ging="FormView1 _PageIndexChang ing">
<EditItemTempla te>
Ticket Number:
<asp:Label ID="Ticket_Numb erLabel1" 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_Descr iptionTextBox"
runat="server" Text='<%# Bind("[Issue Description]") %>'>
</asp:TextBox><br />
Open Date:
<asp:TextBox ID="Open_DateTe xtBox" runat="server"
Text='<%# Bind("[Open Date]") %>'>
</asp:TextBox><br />
Work Notes:
<asp:TextBox ID="Work_NotesT extBox" runat="server"
Text='<%# Bind("[Work Notes]") %>'>
</asp:TextBox><br />
<asp:LinkButt on ID="UpdateButto n" runat="server"
CausesValidatio n="True" CommandName="Up date"
Text="Update">
</asp:LinkButton>
<asp:LinkButt on ID="UpdateCance lButton"
runat="server" CausesValidatio n="False" CommandName="Ca ncel"
Text="Cancel">
</asp:LinkButton>
</EditItemTemplat e>
<InsertItemTemp late>
Ticket Number:
<asp:TextBox ID="Ticket_Numb erTextBox"
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_Descr iptionTextBox"
runat="server" Text='<%# Bind("[Issue Description]") %>'>
</asp:TextBox><br />
Open Date:
<asp:TextBox ID="Open_DateTe xtBox" runat="server"
Text='<%# Bind("[Open Date]") %>'>
</asp:TextBox><br />
Work Notes:
<asp:TextBox ID="Work_NotesT extBox" runat="server"
Text='<%# Bind("[Work Notes]") %>'>
</asp:TextBox><br />
<asp:LinkButt on ID="InsertButto n" runat="server"
CausesValidatio n="True" CommandName="In sert"
Text="Insert">
</asp:LinkButton>
<asp:LinkButt on ID="InsertCance lButton"
runat="server" CausesValidatio n="False" CommandName="Ca ncel"
Text="Cancel">
</asp:LinkButton>
</InsertItemTempl ate>
<ItemTemplate >
Ticket Number:
<asp:Label ID="Ticket_Numb erLabel" 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_Descr iptionLabel"
runat="server" Text='<%# Bind("[Issue Description]") %>'>
</asp:Label><br />
Open Date:
<asp:Label ID="Open_DateLa bel" runat="server"
Text='<%# Bind("[Open Date]") %>'>
</asp:Label><br />
Work Notes:
<asp:Label ID="Work_NotesL abel" runat="server"
Text='<%# Bind("[Work Notes]") %>'>
</asp:Label><br />
</ItemTemplate>
</asp:FormView>

The SqlDataSource Control has a SelectParameter section where you can
specify parameters for your query. There is the QueryStringPara meter
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:SqlDataSou rce ID="SqlDataSour ce1"
SelectCommand=" SELECT .... WHERE state=@state"
....>
<SelectParamete rs>
<asp:QueryStrin gParameter Name="state" QueryStringFiel d="state" />
</SelectParameter s>
</asp:SqlDataSour ce>

Where:

Name is the name of @parameter
QueryStringFiel d is the key section of the QueryString

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

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

[Alexey Smirnov]

On May 6, 8:53 pm, Alexey Smirnov <alexey.smir... @gmail.comwrote :

>
The QueryStringPara meter does not validate the value passed by the
query string in any way; it uses the raw value. In- Hide quoted text -

Too quick on send button: The QueryStringPara meter 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
QueryStringPara meter 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/lib...er(VS.80).aspx

[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:HyperLinkF ield DataTextField=" Open Issues"
DataNavigateUrl Fields="Ticket Number"
DataNavigateUrl FormatString="r esults2.aspx?ti cketid={0}"
Target="_blank" HeaderText="Ope n 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="T icket
Number" DataSourceID="S qlDataSource1">

<ItemTemplate >
Ticket Number:
<asp:Label ID="Ticket_Numb erLabel" 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_Descr iptionLabel"
runat="server" Text='<%# Eval("[Issue Description]") %>'>
</asp:Label><br />
Open Date:
<asp:Label ID="Open_DateLa bel" runat="server"
Text='<%# Eval("[Open Date]") %>'>
</asp:Label><br />
Work Notes:
<asp:Label ID="Work_NotesL abel" runat="server"
Text='<%# Eval("[Work Notes]") %>'>
</asp:Label><br />
<br />
</ItemTemplate>
</asp:DataList><a sp:SqlDataSourc e ID="SqlDataSour ce1"
runat="server" ConnectionStrin g="<%$
ConnectionStrin gs:webhelp2Conn ectionString %>"
SelectCommand=" sp_dticket"
SelectCommandTy pe="StoredProce dure">
<SelectParamete rs>
<asp:QueryStrin gParameter Name="ticketid"
QueryStringFiel d="ticketid" />
</SelectParameter s>
</asp:SqlDataSour ce>

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

Stored procedure i'm using:

Declare @ticketid as varchar (20)
Declare @Technician as varchar (20)
set @Technician=(SE LECT email

FROM dbo.tech INNER JOIN dbo.job_ticket ON tech.client_id =
job_ticket.assi gned_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_t icket.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_t icket_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

[brwalias]

On May 6, 11:57 am, Alexey Smirnov <alexey.smir... @gmail.comwrote :

On May 6, 8:53 pm, Alexey Smirnov <alexey.smir... @gmail.comwrote :

The QueryStringPara meter does not validate the value passed by the
query string in any way; it uses the raw value. In- Hide quoted text -

Too quick on send button: The QueryStringPara meter 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
QueryStringPara meter 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/lib...webcontrols.qu...

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:HyperLinkF ield DataTextField=" Open Issues"
DataNavigateUrl Fields="Ticket Number"
DataNavigateUrl FormatString="r esults2.aspx?ti cketid={0}"
Target="_blank" HeaderText="Ope n 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="T icket
Number" DataSourceID="S qlDataSource1">

<ItemTemplate >
Ticket Number:
<asp:Label ID="Ticket_Numb erLabel" 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_Descr iptionLabel"
runat="server" Text='<%# Eval("[Issue Description]") %>'>
</asp:Label><br />
Open Date:
<asp:Label ID="Open_DateLa bel" runat="server"
Text='<%# Eval("[Open Date]") %>'>
</asp:Label><br />
Work Notes:
<asp:Label ID="Work_NotesL abel" runat="server"
Text='<%# Eval("[Work Notes]") %>'>
</asp:Label><br />
<br />
</ItemTemplate>
</asp:DataList><a sp:SqlDataSourc e ID="SqlDataSour ce1"
runat="server" ConnectionStrin g="<%$
ConnectionStrin gs:webhelp2Conn ectionString %>"
SelectCommand=" sp_dticket"
SelectCommandTy pe="StoredProce dure">
<SelectParamete rs>
<asp:QueryStrin gParameter Name="ticketid"
QueryStringFiel d="ticketid" />
</SelectParameter s>
</asp:SqlDataSour ce>

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

Stored procedure i'm using:

Declare @ticketid as varchar (20)
Declare @Technician as varchar (20)
set @Technician=(SE LECT email

FROM dbo.tech INNER JOIN dbo.job_ticket ON tech.client_id =
job_ticket.assi gned_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_t icket.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_t icket_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

[Alexey Smirnov]

On May 7, 11:27 pm, brwalias <brwal...@gmail .comwrote:

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

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 "?

[brwalias]

On May 7, 2:48 pm, Alexey Smirnov <alexey.smir... @gmail.comwrote :

On May 7, 11:27 pm,brwalias<brw [e-mail] wrote:

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.

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 "?

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

[brwalias]

On May 7, 3:00 pm, brwalias <brwal...@gmail .comwrote:

On May 7, 2:48 pm, Alexey Smirnov <alexey.smir... @gmail.comwrote :

On May 7, 11:27 pm,brwalias<brw [e-mail] wrote:

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.

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 "?

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

here is the stored procedure:

Stored procedure i'm using:

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

set @Technician=(SE LECT email

FROM dbo.tech INNER JOIN dbo.job_ticket ON tech.client_id =
job_ticket.assi gned_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_t icket.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_t icket_Id =
job_ticket.job_ ticket_id WHERE job_ticket.job_ ticket_id =Cast
(@ticketid as varchar (20))

[Alexey Smirnov]

On May 8, 12:00 am, brwalias <brwal...@gmail .comwrote:

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

Well, but the following code does then?

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

when you have the following code:

<asp:QueryStrin gParameter Name="ticketid"
QueryStringFiel d="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!

[Alexey Smirnov]

On May 8, 12:10 am, brwalias <brwal...@gmail .comwrote:

>
here is the stored procedure:

Stored procedure i'm using:

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

set @Technician=(SE LECT email

FROM dbo.tech INNER JOIN dbo.job_ticket ON tech.client_id =
job_ticket.assi gned_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_t icket.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_t icket_Id =
job_ticket.job_ ticket_id WHERE job_ticket.job_ ticket_id =Cast
(@ticketid as varchar (20))- Hide quoted text -

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=(SE LECT email

FROM dbo.tech INNER JOIN dbo.job_ticket ON tech.client_id =
job_ticket.assi gned_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_t icket.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_t icket_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 'yourtestticket id'