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

[Alexey Smirnov]

On May 6, 6:29 pm, brwalias <[e-mail]:

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>

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

[Alexey Smirnov]

On May 6, 8:53 pm, Alexey Smirnov <[e-mail]:

>
The QueryStringParameter 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 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/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:HyperLinkField DataTextField="Open Issues"
DataNavigateUrlFields="Ticket Number"
DataNavigateUrlFormatString="results2.aspx?ticketi d={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

[brwalias]

On May 6, 11:57 am, Alexey Smirnov <[e-mail]:

On May 6, 8:53 pm, Alexey Smirnov <[e-mail]:

The QueryStringParameter 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 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/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:HyperLinkField DataTextField="Open Issues"
DataNavigateUrlFields="Ticket Number"
DataNavigateUrlFormatString="results2.aspx?ticketi d={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

[Alexey Smirnov]

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

>
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 <[e-mail]:

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

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 <[e-mail]:

On May 7, 2:48 pm, Alexey Smirnov <[e-mail]:

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

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=(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))

[Alexey Smirnov]

On May 8, 12:00 am, brwalias <[e-mail]:

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

[Alexey Smirnov]

On May 8, 12:10 am, brwalias <[e-mail]:

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

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'

[brwalias]

On May 7, 3:23 pm, Alexey Smirnov <[e-mail]:

On May 8, 12:10 am,brwalias<[e-mail]:

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 -

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'

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.

[brwalias]

On May 7, 3:23 pm, Alexey Smirnov <[e-mail]:

On May 8, 12:10 am,brwalias<[e-mail]:

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 -

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'

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

[Alexey Smirnov]

On May 8, 12:57 am, brwalias <[e-mail]:

On May 7, 3:23 pm, Alexey Smirnov <[e-mail]:

On May 8, 12:10 am,brwalias<[e-mail]:

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 -

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'

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 -

You are very welcome