CODEWITHSUNDEEP
asp.netvb.net
FrankCastle616
FrankCastle616

Reputation: 101

SQL data not populating in asp webform

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

Upvotes: 0

Views: 54

Answers (1)

Kumar Akhil
Kumar Akhil

Reputation: 176

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

  1. 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.

  1. 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

Upvotes: 2

Related Questions