CODEWITHSUNDEEP
asp.netsqlsql-servervb.net
Suresh
Suresh

Reputation: 117

How to Get All Rows in Asp.net

 Dim data As New SqlDataAdapter
        Dim ds As New DataSet
        Dim df As New DataTable
        Dim tr As New HtmlTableRow
        Dim td3 As New HtmlTableCell 
        Dim td4 As New HtmlTableCell 
        Dim dt As New DataTable 
        Dim cblbind As New CheckBoxList 
        Dim strcon As String = ConfigurationManager.ConnectionStrings("KRGCbiz").ConnectionString 
      Dim con As New SqlConnection(strcon)
        Dim CmdString As String = "select Teamid,teamname from team"
        Dim cmd As New SqlCommand(CmdString, con)
        data.SelectCommand = cmd
        data.SelectCommand = cmd
        data.Fill(dt)
        For i As Integer = 0 To dt.Rows.Count - 1
            cmd.Parameters.Clear()
            ds.Clear()
            Dim protyp As Integer = dt.Rows(i).Item("TeamId")
            cmd.Connection = con
            cmd.CommandText = "TeamBindingProc"
            cmd.Parameters.Add("@TeamId", SqlDbType.Int).Value = protyp
            cmd.CommandType = CommandType.StoredProcedure
            data.SelectCommand = cmd
            data.Fill(ds)
            Dim lbl1 As Label = TryCast(table1.FindControl("lbl1"), Label)
            For Each rows As DataRow In ds.Tables(0).Rows
                con.Open()
                cblbind.DataSource = ds.Tables(0)
                cblbind.DataTextField = "Nickname"
                cblbind.DataValueField = "UserId"
                cblbind.DataBind()
                lbl1.Text = dt.Rows(i).Item("TeamName").ToString()
                td3.Controls.Add(lbl1)
                td4.Controls.Add(cblbind)
                tr.Cells.Add(td3)
                tr.Cells.Add(td4)
                table1.Controls.Add(tr)
                con.Close()
            Next
        Next
    End Sub

My procedure is

CREATE procedure TeamBindingProc             
    `@teamid` as int as           
begin
   select 
      UserId, TeamID,
      (select Nickname 
       From [user] 
       where [user].userid = TeamDetails.Userid) as Nickname 
   from 
      TeamDetails 
   where 
      Teamid = `@teamid`
end

Now its working fine... its read all data in the database but display last row only? how to restrict this problem...

Upvotes: 1

Views: 114

Answers (1)

Swagata
Swagata

Reputation: 622

Modify your stored procedure as follows:

CREATE procedure TeamBindingProc             
begin
select UserId,TeamID,(select Nickname From [user] where [user].userid=TeamDetails.Userid)as Nickname from TeamDetails
end

Then remove the line 

cmd.Parameters.Add("@TeamId", SqlDbType.Int).Value = protyp

from your code.

Upvotes: 1

Related Questions