SQL data not populating in asp webform

Question

Currently can not get any SQL data to populate the web form. When I run the report in Visual Studio preview mode, the data populates, however when running through my web app it displays nothing.

Imports System.Data.SqlClient
Public Class topvendors
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            getEntity()
            ' startdate.Text = DateTime.Today.ToShortDateString
        End If
    End Sub
    Protected Sub getEntity()
        Dim strConnString As String = Session("strconnection") 'ConfigurationManager.ConnectionStrings("TrialConnectionString").ConnectionString
        Dim con As New SqlConnection(strConnString)
        Dim cmd As New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pGetProjectsByUser"
        cmd.Parameters.Add("@employeeid", SqlDbType.Int).Value = Session("userid")
        cmd.Parameters.Add("@reportid", SqlDbType.Int).Value = Request.QueryString("id")
        cmd.Connection = con

        Try
            con.Open()
            Dim dt As New DataTable()
            Dim ds As New DataSet()
            'Dim db As New SqlDataAdapter(cmd)

            Dim da = New SqlDataAdapter(cmd)
            da.Fill(ds)
            'db.Fill(ds)
            division.DataSource = ds.Tables(0)
            division.DataTextField = "entityname"
            division.DataValueField = "entitycode"
            division.DataBind()
            '  If ds.Tables(0).Rows.Count > 1 Then
            'divisions.Visible = False
            ' communities.DataSource = ds.Tables(1)
            'communities.DataTextField = "Projectname"
            ' communities.DataValueField = "projID"
            ' communities.DataBind()
            ' Else
            ' divisions.Text = ds.Tables(0).Rows(0)("entityname").ToString
            'division.Visible = False

            ' communities.DataSource = ds.Tables(1)
            'communities.DataTextField = "Projectname"
            'communities.DataValueField = "projID"
            'communities.DataBind()
            'End If

        Catch ex As Exception
            Throw ex
        Finally

            con.Close()
            con.Dispose()
        End Try
    End Sub
    Protected Sub submit_Click(ByVal sender As Object, ByVal e As EventArgs) Handles submit.Click
        ' Dim communities As String = Server.UrlEncode(Request.Form("communities"))
        ' Response.Write(communities)
        ' Dim removedays As Integer = Request.Form("removedays")
        'getEntity()
        ' MsgBox(sort.SelectedValue)
        Dim builder As New System.Data.SqlClient.SqlConnectionStringBuilder
        builder = New SqlConnectionStringBuilder(Session("strconnection"))

        Dim databasename As String = builder.InitialCatalog
        Dim dataname As String
        If databasename = "G3Live" Then
            dataname = "2fLiveReports"

        Else
            dataname = "2fTestReports"
        End If
        Response.Redirect("http://g3reports.danryanbuilders.com/ReportServer/Pages/ReportViewer.aspx?%" + dataname + "%2fPurchasing%2flance_tutorial&rs:Format=Excel&rs%3aCommand=Render&entityid=" & division.SelectedValue & "&begindate=" + begindate.Text + "&enddate=" + enddate.Text + "&sort=" + DropdownList1.SelectedValue)

    End Sub

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
        Dim builder As New System.Data.SqlClient.SqlConnectionStringBuilder
        builder = New SqlConnectionStringBuilder(Session("strconnection"))



        Dim databasename As String = builder.InitialCatalog
        Dim dataname As String
        If databasename = "G3Live" Then
            dataname = "2fLiveReports"

        Else
            dataname = "2fTestReports"
        End If
        Response.Redirect("http://g3reports.danryanbuilders.com/ReportServer/Pages/ReportViewer.aspx?%" + dataname + "%2fPurchasing%2flance_tutorial&rs:Format=PDF&rs%3aCommand=Render&entityid=" & division.SelectedValue & "&begindate=" + begindate.Text + "&enddate=" + enddate.Text + "&sort=" + DropdownList1.SelectedValue)

    End Sub
End Class

So? Debug everything. Step through the code line by line. See that it takes the correct path through the code. Check your connection strings and your queries etc. — mason
– mason, Commented Mar 26, 2017 at 3:09
Catch ex As Exception Throw ex --> you can safely remove that — T.S.
– T.S., Commented Mar 26, 2017 at 3:40

Community · Accepted Answer · 2017-05-23 12:01:59Z

2

Note: You are storing connection string in Session, it is not recommendable. You can do but it is better you should use web.config file to store connection string.

You can be refer this: Avoid storing connection string in session for different sql schema

For Solution I recommended

You must check the session variable prior to getting the value for the connection string.

' Check if session is null

If Not (Session("strconnection ") Is Nothing) Then
{
     //
}

When it is Null or Nothing or Empty, try to retrieve the connection string again.

You need to cast session variables into their correct types, before use like

Session("strconnection")

Replace it by

Session("strconnection").toString()

And

Cast Session("userid") and Request.QueryString("id") by the help of Integer.Parse/ Integer.TryParse

you can read this for concept: Return Value From Session

edited May 23, 2017 at 12:01

CommunityBot

11 silver badge

answered Mar 26, 2017 at 6:30

Kumar Akhil

1768 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

FrankCastle616 Over a year ago

I figured it out. After a little digging, the division.datavaluefield was set set incorrectly. Should have been entityid not entitycode. Thanks for everyones responses

Collectives™ on Stack Overflow

SQL data not populating in asp webform

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related