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 -2 Newbie Poster
Reverend Jim commented: No details, no comments, no effort. -2
jbutardo -2 Junior Poster in Training
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..
Reverend Jim 5,224 Hi, I'm Jim, one of DaniWeb's moderators. Moderator Featured Poster
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.
ChrisPadgham 113 Posting Whiz
This appears to be generating HTML, where does Excel come into it.
thines01 401 Postaholic Team Colleague Featured Poster
If treated as XML, Excel will load it.
It will probably generate a warning, however.
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.