Problem to get data from database by gridview using VB.NET

Question

0.00/5 (No votes)

See more:

Hi dear

My problem is how to get the user data that you have specified by his personal Email .. where you log on to the user on another page !

When he enters his page in case the email is correct, his data appears.

But the error here is that it does not bring me anything from the database !

Where is the problem?

This is Code :

ASP.NET

<asp:GridView ID="GridView" runat="server" DataKeyNames="Email" GridLines="None"  Style="width: 100%;text-align:center; direction: ltr;">
    
 <Columns>

         <asp:TemplateField HeaderText="Member Name" ItemStyle-Width="260"  >
            <ItemTemplate>
                <div style="float:left;    padding-left: 10px;">
            <asp:Label runat="server" ID="lblMemberImage"  Text='<%# Eval("MemberImage")%>' ></asp:Label>
            <asp:Label ID="lblEnglishName" runat="server" Text='<%# Eval("EnglishName")%>' style="margin-top: 15px; float:left;"></asp:Label>
            <asp:Label ID="lblArabicName" runat="server" Text='<%# Eval("ArabicName")%>' style="margin-top: 15px; float:left;"></asp:Label>
                </div>    
            </ItemTemplate>
             <ItemStyle Width="260px" />
        </asp:TemplateField>

         <asp:TemplateField HeaderText="Email" ItemStyle-Width="50">
            <ItemTemplate>
                <asp:Label ID="lblEmail" runat="server" Text='<%# Eval("Email")%>'></asp:Label>
            </ItemTemplate>
            <ItemStyle Width="50px" />
        </asp:TemplateField>

        <asp:TemplateField HeaderText="City-Country" ItemStyle-Width="150">
            <ItemTemplate>
                <asp:Label ID="lblCountry"  runat="server" Text='<%# Eval("CityCountry")%>'></asp:Label>
            </ItemTemplate>
            <ItemStyle Width="150px" />
        </asp:TemplateField>

        <asp:TemplateField HeaderText="Date of membership" ItemStyle-Width="180">
            <ItemTemplate>
                <asp:Label ID="lblDateofmembership"  runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "Date", "{0:MMM dd, yyyy}")%>'></asp:Label>
            </ItemTemplate>
            <ItemStyle Width="180px" />
        </asp:TemplateField>

        <asp:TemplateField HeaderText="Phone" ItemStyle-Width="150">
            <ItemTemplate>
                <asp:Label ID="lblPhone"  runat="server" Text='<%# Eval("Phone")%>'></asp:Label>
            </ItemTemplate>
            <ItemStyle Width="150px" />
        </asp:TemplateField>

        <asp:TemplateField HeaderText="Password" ItemStyle-Width="50">
            <ItemTemplate>
                <asp:Label ID="lblPassword" runat="server"  Text='<%# Eval("Password")%>'></asp:Label>
            </ItemTemplate>
            <ItemStyle Width="50px" />
        </asp:TemplateField>

         <asp:TemplateField HeaderText="A Level" ItemStyle-Width="50">
            <ItemTemplate>
                <asp:Label ID="lblA" runat="server"  Text='<%# Eval("AName")%>'></asp:Label>
            </ItemTemplate>
            <ItemStyle Width="50px" />
        </asp:TemplateField>

         <asp:TemplateField HeaderText="B Level" ItemStyle-Width="50">
            <ItemTemplate>
                <asp:Label ID="lblB" runat="server"  Text='<%# Eval("BName")%>'></asp:Label>
            </ItemTemplate>
            <ItemStyle Width="50px" />
        </asp:TemplateField>

         <asp:TemplateField HeaderText="SB Level" ItemStyle-Width="50">
            <ItemTemplate>
                <asp:Label ID="lblC" runat="server"  Text='<%# Eval("CName")%>'></asp:Label>
            </ItemTemplate>
            <ItemStyle Width="50px" />
        </asp:TemplateField>

    </Columns>

</asp:GridView>

VB

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load

        If Not IsPostBack Then

            If Session("Email") = "" Then  
                Response.Redirect("Login-Member.aspx")

            Else

                Dim conn As New SqlConnection("Data Source=...")
                conn.Open()


                Dim da As New SqlDataAdapter("SELECT Member.Email FROM Member Where Email=@Email", conn)
                da.SelectCommand.Parameters.AddWithValue("@Email", Session("Email").ToString)

                Label1.Text = Session("Email")

                Dim obj As New DB_Member

                Dim dt As DataTable = obj.FindMembPage()

                GridView.DataSource = dt
                GridView.DataBind()

            End If

            conn.Close()
        End If

    End Sub

VB

Public Function FindMembPage() As DataTable

        Getconnection()
        Dim sql As String = "SELECT  Member.MemberID,Member.EnglishName,Member.ArabicName,Member.MemberImage,Member.DateOfMembership,Member.Email,Member.Phone,Member.Password,Member.CityID ... ,(City.CityName +', '  +Country.CountryName) AS CityCountry FROM Member,City,Country,ALEVEL,BLEVEL,CLEVEL Where City.CityID=Member.CityID  AND Member.CountryID = Country.CountryID AND .... AND Email=@Email "

        Dim dt As New DataTable
        Dim da As New SqlDataAdapter(sql, Me.conn)

        If CountryID <> 0 Then
            da.SelectCommand.CommandText &= "AND City.CountryID = @CountryID"
            da.SelectCommand.Parameters.Add("@CountryID", SqlDbType.Int).Value = CountryID
        End If

        If CityID <> 0 Then
            da.SelectCommand.CommandText &= " AND Member.CityID =@CityID"
            da.SelectCommand.Parameters.Add("@CityID", SqlDbType.Int).Value = CityID
        End If

        If MemberID <> "" Then
            da.SelectCommand.CommandText &= "AND Member.MemberID Like @MemberID"
            da.SelectCommand.Parameters.Add("@MemberID", SqlDbType.NVarChar).Value = "%" & MemberID & "%"
        End If

        If EnglishName <> "" Then
            da.SelectCommand.CommandText &= " AND Member.EnglishName Like @EnglishName"
            da.SelectCommand.Parameters.Add("@EnglishName", SqlDbType.NVarChar).Value = "%" & EnglishName & "%"
        End If

        If ArabicName <> "" Then
            da.SelectCommand.CommandText &= " AND Member.ArabicName Like @ArabicName"
            da.SelectCommand.Parameters.Add("@ArabicName", SqlDbType.NVarChar).Value = "%" & ArabicName & "%"
        End If

        If Email <> "" Then
            da.SelectCommand.CommandText &= " AND Member.Email Like @Email"
            da.SelectCommand.Parameters.Add("@Email", SqlDbType.NVarChar).Value = Email

        End If

        If AID <> 0 Then
            da.SelectCommand.CommandText &= " AND Member.AID = @AID"
            da.SelectCommand.Parameters.Add("@AID", SqlDbType.Int).Value = AID
        End If

        If BID <> 0 Then
            da.SelectCommand.CommandText &= " AND Member.BID = @BID"
            da.SelectCommand.Parameters.Add("@BID", SqlDbType.Int).Value = BID
        End If

 If CID <> 0 Then
            da.SelectCommand.CommandText &= " AND Member.CID = @CID"
            da.SelectCommand.Parameters.Add("@CID", SqlDbType.Int).Value = CID
        End If

        Try
            Getconnection()
            da.Fill(dt)
        Catch ex As SqlException
            Me.Msg = "Error Message"
        Finally
            Try
                Me.conn.Close()
            Catch
            End Try
        End Try
        Return dt

    End Function

What I have tried:

VB

<pre>     If Not IsPostBack Then

            If Session("Email") = "" Then 
                Response.Redirect("Login-Member.aspx")

            Else

                Dim conn As New SqlConnection("Data Source=...")
                conn.Open()


                Dim da As New SqlDataAdapter("SELECT Member.Email FROM Member Where Email=@Email", conn)
                da.SelectCommand.Parameters.AddWithValue("@Email", Session("Email").ToString)

                Label1.Text = Session("Email")

                Dim obj As New DB_Member


                Dim EnglishName As String = CType(GridView.SelectedValue.FindControl("lblEnglishName"), Label).Text

                Dim MemberImage As String = CType(GridView.SelectedValue.FindControl("lblMemberImage"), Label).Text
                Dim ArabicName As String = CType(GridView.SelectedValue.FindControl("lblArabicName"), Label).Text
                Dim Email As String = CType(GridView.SelectedValue.FindControl("lblEmail"), Label).Text
                Dim CityCountry As String = CType(GridView.SelectedValue.FindControl("lblCountry"), Label).Text
                Dim Dateofmembership As String = CType(GridView.SelectedValue.FindControl("lblDateofmembership"), Label).Text
                Dim Phone As String = CType(GridView.SelectedValue.FindControl("lblPhone"), Label).Text
                Dim Password As String = CType(GridView.SelectedValue.FindControl("lblPassword"), Label).Text
                Dim PCTName As String = CType(GridView.SelectedValue.FindControl("lblA"), Label).Text
                Dim ISIDName As String = CType(GridView.SelectedValue.FindControl("lblB"), Label).Text
                Dim SBName As String = CType(GridView.SelectedValue.FindControl("lblC"), Label).Text

                'Dim dr As SqlDataReader

                Dim dt As DataTable = obj.FindMembPage()

                GridView.DataSource = dt
                GridView.DataBind()

            End If

            conn.Close()
        End If

Posted 17-Dec-17 22:36pm

NoorAli87

Updated 17-Dec-17 22:39pm

v2

Add a Solution

Comments

Sinisa Hajnal 18-Dec-17 8:10am

You're using datatable object without calling dataaddapters fill method da.Fill(dt). Instead you're creating new DB_Member (who is by default empty, unless you filling it in constructor from your session object).

NoorAli87 18-Dec-17 8:34am

The previous code works correctly with me and calls the data from the table.
The error was with me in this part of the call: AND Email = @ Email
When I corrected my mistake, the code worked well with me, but the problem was that after entering the email I had all the Members data !!
I just want to get the Member data that I just enter the email on login form !

Richard Deeming 18-Dec-17 14:06pm

... FROM Member,City,Country,ALEVEL,BLEVEL,CLEVEL Where City.CityID=Member.CityID AND ...

Don't do joins that way. You're mixing up the join conditions with the filtering conditions, and making life harder for yourself. Use a proper JOIN clause instead:

...
FROM Member
INNER JOIN City ON City.CityID = Member.CityID
INNER JOIN Country ON Country.CountryID = Member.CountryID
INNER JOIN ALEVEL ON ...
INNER JOIN BLEVEL ON ...
INNER JOIN CLEVEL ON ...
WHERE Member.Email = @Email

NoorAli87 19-Dec-17 3:41am

The error is solved , My mistake in this sentence :

Dim da As New SqlDataAdapter("SELECT Member.Email FROM Member Where Email=@Email", conn)

This is the Correct sentence :
Dim da As New SqlDataAdapter ("SELECT Member.MemberID,Member.EnglishName,Member.ArabicName,Member.MemberImage,Member.DateOfMembership,Member.Email,Member.Phone,Member.Password,Member.CityID ... ,(City.CityName +', ' +Country.CountryName) AS CityCountry FROM Member,City,Country,ALEVEL,BLEVEL,CLEVEL Where City.CityID=Member.CityID AND Member.CountryID = Country.CountryID AND .... Where Email=@Email", conn)

Thank you Mr.Sinisa Hajnal , Thank you Mr. Richard Deeming Very Very Much :)
NoorAli,

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)