How to use 2 different stored procedures in Data Reapeater

[meenakshidhar]

Hi All,
I have one Reapeter in my page. I have to use two seperate stored procedures in order to fetch the data from database and then bind it to the data repeater.
My code is given below--

<asp:Repeater id="Repeater1" runat="server">
<HeaderTemplate>
<table border="1">
<tr bgcolor="#C8CCE1">
<th>First Name</th>
<th>Extension No</th>
<th>Mobile No</th>
<th>Email</th>
</tr>
</HeaderTemplate>

<ItemTemplate>
<tr bgcolor="#F5F7FA">
<td><%# DataBinder.Eval(Container.DataItem, "FIRST_NAME") %></td>
<td><%# DataBinder.Eval(Container.DataItem, "LAST_DIGITS") %></td>
<td><%# DataBinder.Eval(Container.DataItem, "?????") %></td> ---(This field is coming from second query i.e. another stored procedure)
<td><%# DataBinder.Eval(Container.DataItem, "RESOURCE_INITIAL") %></td>
</tr>
</ItemTemplate>

<FooterTemplate>
</table>
</FooterTemplate>
</asp:Repeater>

============================================================
Private Sub Page_Load()
If Not Page.IsPostBack Then
Dim objTel As New clsTelephoneList()
Dim DS As New DataSet()

DS = objTel.fnCompany_Listing()
Repeater1.DataSource = DS
Repeater1.DataBind()

End If
End Sub

============================================================
Public Class clsTelephoneList

Public Function fnCompany_Listing()
Dim objConn As New db_Globals()
Dim cmd As New SqlCommand()
Try
cmd = objConn.GTDConn("spTelephone_Listing")
cmd.ExecuteNonQuery()
Dim DA As New SqlDataAdapter(cmd)
Dim DS As New DataSet()
DA.Fill(DS)
Return DS
Catch ex As Exception
Throw New ArgumentException(ex.Message.ToString())
Finally
cmd.Connection.Close()
End Try
End Function
End Class
=============================================================

Public Class db_Globals

Function GTDConn(ByVal vchSPName)
Dim sConnectionString As String = "server=INFO3783;uid=gtd;pwd=gtd;database=GTD"
Dim conn As New SqlConnection(sConnectionString)
Dim cmd As New SqlCommand(vchSPName, conn)
Try
cmd.Connection.Open()
cmd.CommandType = CommandType.StoredProcedure
Return cmd
Catch ex As Exception
Throw New ArgumentException(ex.Message.ToString())
End Try
End Function

End Class
============================================================

Now my problem is to bind 1st, 2nd and 4th field from one stored procedure and 3rd field from another stored procedure. Is this possible using Data Repeater..if not, what will be the right approach to achieve this....

Thanks in Advance,
Meenakshi Dhar

 

[shatch]

You should be able to get this all in one stored procedure, if you post a copy of the two procedures i will take a look. I am not sure if calling two in the same repeater is possible, but even if it is, its probably not a great idea as it would but an additional load on the server...

 

[meenakshidhar]

Stored Procedure 1--

CREATE Procedure sp1
AS
BEGIN
SELECT * FROM Table1
WHERE
COMPANY_ID=52 AND
CONTACT_TYPE='O' AND
RESOURCE_TYPE_ID=1 AND
RESOURCE_ROLE='P'
END
GO

=========================================================
Stored Procedure 2--

CREATE Procedure sp2
AS
BEGIN
SELECT A.CONTACT_TYPE, MAX(A.COL_C) CELL , MAX(A.COL_F), MAX(A.COL_O),
MAX(A.COL_R)FROM
(
SELECT 'CONTACT' CONTACT_TYPE,
COL_C = CASE contact_type WHEN 'C' THEN LAST_DIGITS END,
COL_F = CASE contact_type WHEN 'F' THEN LAST_DIGITS END,
COL_O = CASE contact_type WHEN 'O' THEN LAST_DIGITS END,
COL_R = CASE contact_type WHEN 'R' THEN LAST_DIGITS END
FROM GTD_M_RESOURCE_CONTACT WHERE RESOURCE_ID=193
) A
GROUP BY A.CONTACT_TYPE
END
GO

==========================================================

In both the procedures, "RESOURCE_ID" is the common field.

 

[ca8msm]

What you should do is get all of the information back in one procedure by using a join in your SQL Statement to link both tables. A simple example of this would be:

SELECT TABLE1.FIELD1, TABLE1.FIELD2, TABLE2.FIELD1 
FROM TABLE1, TABLE2
WHERE TABLE1.MYIDENTIFIER = TABLE2.MYIDENTIFIER
AND TABLE1.FIELD3 = 'MYCRITERIA'

but this syntax may change slightly depeding on what database you are using.

I would suggest posting in the relevant SQL forum on Tek-Tips (e.g SQL Server, Oracle, Access etc) and explain what fields are in both tables, how they can be joined and what you want to achieve.

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

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[shatch]

To simplify matters in the stored procedure you could create a view which generates the same output as the SP2 and hen create a simple link on resourceid in sp1 between the 'Table1' and the view. If you created myView then SP1 should read

CREATE Procedure sp1
AS
BEGIN
SELECT Table1.*, myView.* FROM Table1, myView ON table1.Resource_id = myView.childresourceID
WHERE
Table1.COMPANY_ID=52 AND
Table1.CONTACT_TYPE='O' AND
Table1.RESOURCE_TYPE_ID=1 AND
Table1.RESOURCE_ROLE='P'
END
GO

You need to amend the text in the view so that it selects resource_id:
SELECT A.CONTACT_TYPE, MAX(A.COL_C) CELL , MAX(A.COL_F), MAX(A.COL_O),
MAX(A.COL_R)FROM
(
SELECT 'CONTACT' CONTACT_TYPE, 'childResourceID' Resource_ID,
COL_C = CASE contact_type WHEN 'C' THEN LAST_DIGITS END,
COL_F = CASE contact_type WHEN 'F' THEN LAST_DIGITS END,
COL_O = CASE contact_type WHEN 'O' THEN LAST_DIGITS END,
COL_R = CASE contact_type WHEN 'R' THEN LAST_DIGITS END
FROM GTD_M_RESOURCE_CONTACT WHERE RESOURCE_ID=193
) A
GROUP BY A.CONTACT_TYPE

Any problems, let me know...

 

[meenakshidhar]

I m not able to create a view for sp2.
my table structure(GTD_M_RESOURCE_CONTACT)for sp2 is--

Resource_id Contact_Type Last_Digits
---------------------------------------------
193 O 291
193 F 56122778
193 C 98116677989
193 R 34567677