Stuck, I pull mulitlple objects from a table and want to display them as a single row

[prosenba]

Need help, I trying to query a table that returns multiple objects (1 column and 3 rows) I want to display the results in one column one row in the datagrid. I actually see a new row in the datagrid for each object returned from the database. Can someone please help display the data in one row, any help would be greatly appreciated.

 

Sub Binddata5()

 

Datagrid5.Visible = "true"

 

Dim myConnection As New SqlClient.SqlConnection()

 

myConnection.ConnectionString = "server=localhost;database=License;user id=ASPNET;password="

 

'First create data reader object

 

Dim sqlStmt As String = "Select Projects.Project_Name from Projects

 

inner join ProjectsXProducts on (ProjectsXProducts.Project_ID=Projects.Project_ID) where ProjectsxProducts.Vendor_ID LIKE '" & DropDownList1.SelectedItem.Value & "'"

 

'Create SQL comand object

 

Dim da As New SqlClient.SqlDataAdapter(sqlStmt, myConnection)

 

Dim ds As New DataSet()

 

da.Fill(ds, "Project_Name")

 

Datagrid5.DataSource = ds.Tables(0).DefaultView

 

Datagrid5.DataBind()

 

myConnection.Close()

 

End Sub

 

<asp:datagrid id="Datagrid5" runat="server" BackColor="#E0E0E0" OnEditCommand="DataGrid_Edit5" OnCancelCommand="DataGrid_Cancel5" OnUpdateCommand="DataGrid_Update5" HorizontalAlign="Center" HeaderStyle-BackColor="#0033ff" HeaderStyle-ForeColor="White" Width="90%" Font-Size="8pt" Font-Name="Verdana" CellSpacing="0" CellPadding="4" GridLines="Both" BorderWidth="1" BorderColor="black" AutoGenerateColumns="false" BorderStyle="Solid">

 

<HeaderStyle ForeColor="White" BackColor="#0033FF"></HeaderStyle>

 

<Columns>

 

<asp:EditCommandColumn ButtonType="PushButton" UpdateText="Update" CancelText="Cancel" EditText="Edit">

 

<ItemStyle BackColor="#D4D0C8"></ItemStyle>

 

</asp:EditCommandColumn>

 

<asp:TemplateColumn ItemStyle-HorizontalAlign="Center" ItemStyle-Wrap="True" HeaderStyle-Font-Bold="True" HeaderStyle-HorizontalAlign="Center" HeaderText="Projects">

 

<ItemTemplate>

 

<%# DataBinder.Eval(container.DataItem, "Project_Name") %>

 

</ItemTemplate>

 

<EditItemTemplate>

 

<asp:ListBox Runat="server" SelectionMode=Multiple ID="Listbox1" DataValuefield="Project_ID" DataTextField="Project_Name" DataSource="<%# GetComponent5() %>" />

 

</EditItemTemplate>

 

</asp:TemplateColumn>

 

</Columns>

 

</asp:datagrid>

[Robby]

You will not be able to acomplish this task with a simple da Fill with a Dataset.

 

I would say use a DataReader, iterate the reslutset and create a Datatable while in the loop.

[prosenba]

I tried something like that, could you look at my code and tell me why the loop doesn't work. I have it commited out since I couldn't fiqure out how to get to work.

Sub Binddata5()
       Datagrid5.Visible = "true"
       Dim myConnection As New SqlClient.SqlConnection()
       myConnection.ConnectionString = "server=10.146.200.38;database=License;user id=ASPNET;password="
       myConnection.Open()
       '       First create data reader object 
       Dim sql_dr As SqlClient.SqlDataReader
       Dim sqlstr As String = "Select Projects.Project_Name from Projects inner join ProjectsXProducts on (ProjectsXProducts.Project_ID=Projects.Project_ID) where ProjectsxProducts.Vendor_ID LIKE '" & DropDownList1.SelectedItem.Value & "'"
       'Create SQL comand object 
       Dim sql_command As New SqlClient.SqlCommand(sqlstr, myConnection)
       'sql_dr = sql_command.ExecuteReader()
       'While sql_dr.Read()
       '    Dim Project_Name As String
       '    Project_Name = sql_dr("Project_Name")
       '    Response.Write(Project_Name)
       'End While
       'sql_dr.Close()
       Datagrid5.DataSource = sql_command.ExecuteReader()
       Datagrid5.DataBind()
       Datagrid5.DataSource.Close()
       myConnection.Close()

Edited November 4, 2003 by Robby

[LostProgrammer]

Are you suret that the query is returning a record set?

Try running the query on the database using query analyzer or enterprise manager.

Also with the LIKE, the way you have it must be an exact match

- there are no wildcard characters in the string.

Why don't you just use = or LIKE '%" & ddl.Value & "%'"

 

-lp

[Robby]

Have you considered using a Datalist instead of a datagrid.

A datalist allows you to span many rows horizontaly.