Remove From My Forums

Retrieving Multiple values in select command -VB.NET

Question
0

Sign in to vote

User-1578974752 posted

    cmd.CommandText = "select * FROM QuestionDetail where QuestionMasterID = '" + qusetid.Text + "'"

            drd = cmd.ExecuteReader
            If drd.HasRows = True Then
                drd.Read()

                quest1.Text = drd.Item("Question")
                QuestDETID.Text = drd.Item("QuestDetailID")

            End If
            cmd.Dispose()
            drd.Close()
cmd.CommandText = "select * FROM QuestOptiodetail where QuestDetailID = '" + QuestDETID.Text + "'"

            drd = cmd.ExecuteReader
            If drd.HasRows = True Then
                drd.Read()
                quest1.Text = drd.Item("Questoption")
            End If

In the above code actually there are 10 QuestDetailID in one QuestionMasterID. For one it is working fine. I can retrive the QuestDetailID and question from questiondetail table.
But i am not sure how to show all QuestDetailID in the first select statement.("select * FROM QuestionDetail where QuestionMasterID = '" + qusetid.Text + "'")
When tried QuestDETID2.Text = drd.Item("QuestDetailID") then same first QuestDetailID is showing. I can not show in grid view. Each QuestDetailID must be shown in textboxes.

Appreciate the help

EDIT

Model is as below. Hence I can not use grid view.In between Each question there must be options retrieving from another table.(So 1 question,1 set of options 2nd question as below)

1) what you know about .net -> ("select * FROM QuestionDetail where QuestionMasterID = '" + qusetid.Text + "'")

1 . Excellent 2.Good 3. bad -> (These 3 are option button values coming from select * FROM QuestOptiodetail where QuestDetailID = '" + QuestDETID.Text + "'")

2)what is SQL ->("select * FROM QuestionDetail where QuestionMasterID = '" + qusetid.Text + "'")

1 . Database 2.Programming language 3. Both ->(select * FROM QuestOptiodetail where QuestDetailID = '" + QuestDETID.Text + "'")

3) question number 3

option1 option2 option3

4)question number 4

option1 option2 option3

Friday, August 17, 2018 3:36 AM

Answers

User283571144 posted

Hi shsu,

After each question, i need to show the options which are retrieving from another table.

According to your description, I suggest you could get the option value according to questionid in RowDataBound event.

More details, you could refer to below codes:

ASPX:

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="2145596.aspx.vb" Inherits="WebApplication.VB._2145596" %>
 
<!DOCTYPE html>
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:GridView ID="gvData" runat="server" AutoGenerateColumns="False" DataKeyNames="ID">
                <Columns>
                    <asp:TemplateField>
                        <HeaderTemplate>
                            <table>
                                <thead>
                                    <tr>
                                        <td colspan="2">QA</td>
                                    </tr>
                                </thead>
                        </HeaderTemplate>
                        <ItemTemplate>
                            <tr>
                                <td>Q:</td>
                                <td style="text-align: left;">
                                    <asp:Label ID="lblQuestion" runat="server" Text='<%# Bind("Title") %>'></asp:Label>
                                </td>
                            </tr>
                            <tr>
                                <td>A:</td>
                                <td style="text-align: left;">
                                    <asp:RadioButtonList ID="rblDetail" runat="server" RepeatDirection="Horizontal"></asp:RadioButtonList>
                                </td>
                            </tr>
                        </ItemTemplate>
                        <FooterTemplate>
                            </table>
                        </FooterTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>
        </div>
    </form>
</body>
</html>

Code-behind:

Imports System.Data.SqlClient
 
Public Class _2145596
    Inherits System.Web.UI.Page
 
    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            Using connection As New SqlConnection()
                connection.ConnectionString = ConfigurationManager.ConnectionStrings("dbString").ConnectionString
                connection.Open()
                Using command As New SqlCommand()
                    command.CommandText = "select * from QuestionMaster"
                    command.Connection = connection
                    command.CommandType = CommandType.Text
                    Using reader As SqlDataReader = command.ExecuteReader()
                        Me.gvData.DataSource = reader
                        Me.gvData.DataBind()
                    End Using
                End Using
            End Using
        End If
    End Sub
 
    Protected Sub gvData_RowDataBound(sender As Object, e As GridViewRowEventArgs) Handles gvData.RowDataBound
        Dim gv As GridView = CType(sender, GridView)
 
        If e.Row.RowType = DataControlRowType.DataRow Then
            Dim rbl As RadioButtonList = CType(e.Row.FindControl("rblDetail"), RadioButtonList)
 
            Dim questionId As Integer = CType(gv.DataKeys(e.Row.RowIndex).Value, Integer)
 
            Using connection As New SqlConnection()
                connection.ConnectionString = ConfigurationManager.ConnectionStrings("dbString").ConnectionString
                connection.Open()
                Using command As New SqlCommand()
                    command.CommandText = "select * from QuestionDetail where QuestionId = " + questionId.ToString()
                    command.Connection = connection
                    command.CommandType = CommandType.Text
                    Using reader As SqlDataReader = command.ExecuteReader()
                        rbl.DataSource = reader
                        rbl.DataTextField = "Title"
                        rbl.DataValueField = "ID"
                        rbl.DataBind()
                    End Using
                End Using
            End Using
        End If
    End Sub
End Class

Database:

USE [Demo]
GO
 
/****** Object:  Table [dbo].[QuestionMaster]    Script Date: 8/23/2018 9:45:12 AM ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[QuestionMaster](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [varchar](50) NOT NULL,
CONSTRAINT [PK_QuestionMaster] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 
 
USE [Demo]
GO
 
/****** Object:  Table [dbo].[QuestionDetail]    Script Date: 8/23/2018 9:45:20 AM ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[QuestionDetail](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [varchar](50) NOT NULL,
    [QuestionId] [int] NOT NULL,
CONSTRAINT [PK_QuestionDetail] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Result:

Best Regards,

Bradno

Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM

Wednesday, September 5, 2018 7:33 AM

All replies

User283571144 posted

Hi shsu,

In the above code actually there are 10 QuestDetailID in one QuestionMasterID. For one it is working fine. I can retrive the QuestDetailID and question from questiondetail table.
But i am not sure how to show all QuestDetailID in the first select statement.("select * FROM QuestionDetail where QuestionMasterID = '" + qusetid.Text + "'")
When tried QuestDETID2.Text = drd.Item("QuestDetailID") then same first QuestDetailID is showing. I can not show in grid view. Each QuestDetailID must be shown in textboxes.

According to your description,I suggest you could directly bind the girdview’s datasorce with the datareader.

More detaisl, you could refer to below codes:

Notice: Since the datareader will read the row one by one, normally we will write a loop logic to read the data. But the girdview datasource will auto read the row.

Details about how to use loop logic to read the row, you could refer to this exmaple:https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader?redirectedfrom=MSDN&view=netframework-4.7.2#examples

ASPX:

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="gridview.aspx.vb" Inherits="WebApplication.VB.gridview" %>
 
<!DOCTYPE html>
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:GridView ID="gvData" runat="server" AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333" GridLines="None">
                <AlternatingRowStyle BackColor="White" />
                <Columns>
                    <asp:TemplateField HeaderText="DetailID">
                        <ItemTemplate>
                            <asp:TextBox ID="txbDetailID" runat="server" Text='<%# Bind("DetailID") %>'></asp:TextBox>
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
                <EditRowStyle BackColor="#2461BF" />
                <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
                <RowStyle BackColor="#EFF3FB" />
                <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
                <SortedAscendingCellStyle BackColor="#F5F7FB" />
                <SortedAscendingHeaderStyle BackColor="#6D95E1" />
                <SortedDescendingCellStyle BackColor="#E9EBEF" />
                <SortedDescendingHeaderStyle BackColor="#4870BE" />
            </asp:GridView>
        </div>
    </form>
</body>
</html>

Code-behind:

Imports System.Data.SqlClient
 
Public Class gridview
    Inherits System.Web.UI.Page
 
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Using connection As New SqlConnection()
            connection.ConnectionString = ConfigurationManager.ConnectionStrings("dbString").ConnectionString
            connection.Open()
            Using command As New SqlCommand()
                command.CommandText = "select * from GV where MasterID = 1"
                command.Connection = connection
                command.CommandType = CommandType.Text
                Using reader As SqlDataReader = command.ExecuteReader()
                    Me.gvData.DataSource = reader
                    Me.gvData.DataBind()
                End Using
            End Using
        End Using
    End Sub
End Class

Result:

Best Regards,

Brando

Monday, August 20, 2018 3:36 AM

User-369506445 posted

please try below

Dim strConnString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString

Dim con As New SqlConnection(strConnString)

Dim cmd As New SqlCommand()

cmd.CommandType = CommandType.Text

cmd.CommandText = "select * FROM QuestionDetail where QuestionMasterID = '" + qusetid.Text + "'"

cmd.Connection = con

Try

    con.Open()

    GridView1.EmptyDataText = "No Records Found"

    GridView1.DataSource = cmd.ExecuteReader()

    GridView1.DataBind()

Catch ex As Exception

    Throw ex

Finally

    con.Close()

    con.Dispose()

End Try

here is <g class="gr_ gr_27 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" id="27" data-gr-id="27">full</g> sample with DEMO

https://www.aspsnippets.com/Articles/Bind-data-to-ASPNet-GridView-using-Stored-Procedure.aspx

Monday, August 20, 2018 4:12 AM

0

Sign in to vote

User-1578974752 posted

After each question, i need to show the options which are retrieving from another table.

Thanks

Monday, August 20, 2018 7:28 AM

User283571144 posted

Hi shsu,

After each question, i need to show the options which are retrieving from another table.

According to your description, I suggest you could get the option value according to questionid in RowDataBound event.

More details, you could refer to below codes:

ASPX:

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="2145596.aspx.vb" Inherits="WebApplication.VB._2145596" %>
 
<!DOCTYPE html>
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:GridView ID="gvData" runat="server" AutoGenerateColumns="False" DataKeyNames="ID">
                <Columns>
                    <asp:TemplateField>
                        <HeaderTemplate>
                            <table>
                                <thead>
                                    <tr>
                                        <td colspan="2">QA</td>
                                    </tr>
                                </thead>
                        </HeaderTemplate>
                        <ItemTemplate>
                            <tr>
                                <td>Q:</td>
                                <td style="text-align: left;">
                                    <asp:Label ID="lblQuestion" runat="server" Text='<%# Bind("Title") %>'></asp:Label>
                                </td>
                            </tr>
                            <tr>
                                <td>A:</td>
                                <td style="text-align: left;">
                                    <asp:RadioButtonList ID="rblDetail" runat="server" RepeatDirection="Horizontal"></asp:RadioButtonList>
                                </td>
                            </tr>
                        </ItemTemplate>
                        <FooterTemplate>
                            </table>
                        </FooterTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>
        </div>
    </form>
</body>
</html>

Code-behind:

Imports System.Data.SqlClient
 
Public Class _2145596
    Inherits System.Web.UI.Page
 
    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            Using connection As New SqlConnection()
                connection.ConnectionString = ConfigurationManager.ConnectionStrings("dbString").ConnectionString
                connection.Open()
                Using command As New SqlCommand()
                    command.CommandText = "select * from QuestionMaster"
                    command.Connection = connection
                    command.CommandType = CommandType.Text
                    Using reader As SqlDataReader = command.ExecuteReader()
                        Me.gvData.DataSource = reader
                        Me.gvData.DataBind()
                    End Using
                End Using
            End Using
        End If
    End Sub
 
    Protected Sub gvData_RowDataBound(sender As Object, e As GridViewRowEventArgs) Handles gvData.RowDataBound
        Dim gv As GridView = CType(sender, GridView)
 
        If e.Row.RowType = DataControlRowType.DataRow Then
            Dim rbl As RadioButtonList = CType(e.Row.FindControl("rblDetail"), RadioButtonList)
 
            Dim questionId As Integer = CType(gv.DataKeys(e.Row.RowIndex).Value, Integer)
 
            Using connection As New SqlConnection()
                connection.ConnectionString = ConfigurationManager.ConnectionStrings("dbString").ConnectionString
                connection.Open()
                Using command As New SqlCommand()
                    command.CommandText = "select * from QuestionDetail where QuestionId = " + questionId.ToString()
                    command.Connection = connection
                    command.CommandType = CommandType.Text
                    Using reader As SqlDataReader = command.ExecuteReader()
                        rbl.DataSource = reader
                        rbl.DataTextField = "Title"
                        rbl.DataValueField = "ID"
                        rbl.DataBind()
                    End Using
                End Using
            End Using
        End If
    End Sub
End Class

Database:

USE [Demo]
GO
 
/****** Object:  Table [dbo].[QuestionMaster]    Script Date: 8/23/2018 9:45:12 AM ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[QuestionMaster](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [varchar](50) NOT NULL,
CONSTRAINT [PK_QuestionMaster] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 
 
USE [Demo]
GO
 
/****** Object:  Table [dbo].[QuestionDetail]    Script Date: 8/23/2018 9:45:20 AM ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[QuestionDetail](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [varchar](50) NOT NULL,
    [QuestionId] [int] NOT NULL,
CONSTRAINT [PK_QuestionDetail] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Result:

Best Regards,

Bradno

Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM

Wednesday, September 5, 2018 7:33 AM

Answered by:

Retrieving Multiple values in select command -VB.NET

Question

Answers

All replies