-2

How to export data to excel according to below code

Imports System.Data.SqlClient
Imports System.Data.OleDb.OleDbConnection
Imports System.Data.OleDb.OleDbCommand
Imports System.Data
Partial Class Main 
Inherits System.Web.UI.Page

    Public strSQL As String
    Public strConn As String = ConfigurationSettings.AppSettings("strConn")
    Dim objConn As SqlConnection
    Dim objAdap As SqlDataAdapter
    Dim objCmd As SqlCommand
    Dim objReader As SqlDataReader

    Private Sub cbfGenerateTable()


        Try
            objConn = New SqlConnection(strConn)

            strSQL = "SELECT a.strLocation, a.strPhoto, a.strProblem, a.dtInitialize, b.strdesc as strArea, " & _
            "c.strdesc as strCompany, d.strdesc as strDept, e.strDesc as strProblemKind, " & _
            "a.strPIC, a.dtExpected, a.dtEnded, a.strStatus, a.strVIN, a.intFrequency, a.strGravity " & _
            "FROM pm_Main a, pr_area b, pr_company c, pr_dept d, pr_ProblemKind e " & _
            "WHERE a.intAreaId=b.Id AND a.intCompanyId=c.Id AND a.intDeptId=d.Id AND a.intProblemId=e.Id " & _
            "AND a.strModel='" & ddlModel.SelectedValue & "'"

            objAdap = New SqlDataAdapter(strSQL, objConn)

            Dim dtItem As New DataTable
            Dim dgGenerate As New DataGrid
            Dim dr As DataRow
            Dim i As Integer

            objConn.Open()
            objAdap.Fill(dtItem)
         
            Dim strGenerate As String

            strGenerate = "<table id=""dgItem"" border=""1"" rules=""all"" cellspacing=""0"" cellpadding=""3"">"
            strGenerate = strGenerate + "<tr bgcolor=""#0099CC"">"
            strGenerate = strGenerate + "<td align=""center"">No.</td>"
            strGenerate = strGenerate + "<td>Location</td>"
            strGenerate = strGenerate + "<td>Photo</td>"
            strGenerate = strGenerate + "<td>Problem</td>"
            strGenerate = strGenerate + "<td>Initialize Date</td>"
            strGenerate = strGenerate + "<td>Area</td>"
            strGenerate = strGenerate + "<td>Kind of Problem</td>"
            strGenerate = strGenerate + "<td>Company</td>"
            strGenerate = strGenerate + "<td>Department</td>"
            strGenerate = strGenerate + "<td>PIC Name</td>"
            strGenerate = strGenerate + "<td>Expected Date</td>"
            strGenerate = strGenerate + "<td>Ended Date</td>"
            strGenerate = strGenerate + "<td>Status</td>"
            strGenerate = strGenerate + "<td>VIN</td>"
            strGenerate = strGenerate + "<td>Frequency</td>"
            strGenerate = strGenerate + "<td>Gravity</td>"
            strGenerate = strGenerate + "</tr>"

            If dtItem.Rows.Count > 0 Then
                For Each dr In dtItem.Rows


                    strGenerate = strGenerate + "<tr>"
                    strGenerate = strGenerate + "<td align=""center""> " & i + 1 & "  </td>"
                    strGenerate = strGenerate + "<td>" & dr("strLocation") & "</td>"
                    strGenerate = strGenerate + "<td><img src=""images\upload\" & dr("strPhoto") & """ height=""184"" width=""376""></td>"
                    strGenerate = strGenerate + "<td>" & dr("strProblem") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("dtInitialize") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strArea") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strCompany") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strDept") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strProblemKind") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strPIC") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("dtExpected") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("dtEnded") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strStatus") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strVIN") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("intFrequency") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strGravity") & "</td>"
                    strGenerate = strGenerate + "</tr>"

                Next
                strGenerate = strGenerate + "</table>"



            End If

            ltrGenerate.Text = strGenerate

        Catch ex As Exception

        End Try

    End Sub

    Protected Sub ddlModel_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlModel.SelectedIndexChanged

        cbfGenerateTable()

    End Sub

    Protected Sub btnGenerate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGenerate.Click

            End Sub
End Class

adhani85

Newbie Poster

1 post since Nov 2011

Reputation Points: 8

Solved Threads: 0

3 Months Ago

Imports System.Data.SqlClient
Imports System.Data.OleDb.OleDbConnection
Imports System.Data.OleDb.OleDbCommand
Imports System.Data
Partial Class Main 
Inherits System.Web.UI.Page

    Public strSQL As String
    Public strConn As String = ConfigurationSettings.AppSettings("strConn")
    Dim objConn As SqlConnection
    Dim objAdap As SqlDataAdapter
    Dim objCmd As SqlCommand
    Dim objReader As SqlDataReader

    Private Sub cbfGenerateTable()


        Try
            objConn = New SqlConnection(strConn)

            strSQL = "SELECT a.strLocation, a.strPhoto, a.strProblem, a.dtInitialize, b.strdesc as strArea, " & _
            "c.strdesc as strCompany, d.strdesc as strDept, e.strDesc as strProblemKind, " & _
            "a.strPIC, a.dtExpected, a.dtEnded, a.strStatus, a.strVIN, a.intFrequency, a.strGravity " & _
            "FROM pm_Main a, pr_area b, pr_company c, pr_dept d, pr_ProblemKind e " & _
            "WHERE a.intAreaId=b.Id AND a.intCompanyId=c.Id AND a.intDeptId=d.Id AND a.intProblemId=e.Id " & _
            "AND a.strModel='" & ddlModel.SelectedValue & "'"

            objAdap = New SqlDataAdapter(strSQL, objConn)

            Dim dtItem As New DataTable
            Dim dgGenerate As New DataGrid
            Dim dr As DataRow
            Dim i As Integer

            objConn.Open()
            objAdap.Fill(dtItem)
         
            Dim strGenerate As String

            strGenerate = "<table id=""dgItem"" border=""1"" rules=""all"" cellspacing=""0"" cellpadding=""3"">"
            strGenerate = strGenerate + "<tr bgcolor=""#0099CC"">"
            strGenerate = strGenerate + "<td align=""center"">No.</td>"
            strGenerate = strGenerate + "<td>Location</td>"
            strGenerate = strGenerate + "<td>Photo</td>"
            strGenerate = strGenerate + "<td>Problem</td>"
            strGenerate = strGenerate + "<td>Initialize Date</td>"
            strGenerate = strGenerate + "<td>Area</td>"
            strGenerate = strGenerate + "<td>Kind of Problem</td>"
            strGenerate = strGenerate + "<td>Company</td>"
            strGenerate = strGenerate + "<td>Department</td>"
            strGenerate = strGenerate + "<td>PIC Name</td>"
            strGenerate = strGenerate + "<td>Expected Date</td>"
            strGenerate = strGenerate + "<td>Ended Date</td>"
            strGenerate = strGenerate + "<td>Status</td>"
            strGenerate = strGenerate + "<td>VIN</td>"
            strGenerate = strGenerate + "<td>Frequency</td>"
            strGenerate = strGenerate + "<td>Gravity</td>"
            strGenerate = strGenerate + "</tr>"

            If dtItem.Rows.Count > 0 Then
                For Each dr In dtItem.Rows


                    strGenerate = strGenerate + "<tr>"
                    strGenerate = strGenerate + "<td align=""center""> " & i + 1 & "  </td>"
                    strGenerate = strGenerate + "<td>" & dr("strLocation") & "</td>"
                    strGenerate = strGenerate + "<td><img src=""images\upload\" & dr("strPhoto") & """ height=""184"" width=""376""></td>"
                    strGenerate = strGenerate + "<td>" & dr("strProblem") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("dtInitialize") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strArea") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strCompany") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strDept") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strProblemKind") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strPIC") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("dtExpected") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("dtEnded") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strStatus") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strVIN") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("intFrequency") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strGravity") & "</td>"
                    strGenerate = strGenerate + "</tr>"

                Next
                strGenerate = strGenerate + "</table>"



            End If

            ltrGenerate.Text = strGenerate

        Catch ex As Exception

        End Try

    End Sub

    Protected Sub ddlModel_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlModel.SelectedIndexChanged

        cbfGenerateTable()

    End Sub

    Protected Sub btnGenerate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGenerate.Click

            End Sub
End Class

Try adding order by in your sql statement, maybe it'll work..

jbutardo

Junior Poster in Training

73 posts since Jan 2012

Reputation Points: 8

Solved Threads: 1

3 Months Ago

As a rule it is considered prudent to provide more than just a block of uncommented code and no actual explanation or question. What is the code supposed to do? How does this differ from what the code actually does? Are you getting any errors? What does your input look like? What does your output look like? Anyone can cut and paste code. At least go to the effort of putting together a coherent request with a few details. And please read the posting guidelines.

Reverend Jim

Posting Shark

Moderator

1,167 posts since Aug 2010

Reputation Points: 253

Solved Threads: 159

3 Months Ago

This appears to be generating HTML, where does Excel come into it.

ChrisPadgham

Posting Pro in Training

413 posts since Sep 2009

Reputation Points: 102