The provider could not determine the Object value Error - MS Access, databind, and I am lost..?
I have to troubleshoot a problem - I keep recieving the error message when one of the records is selected for a search.
The provider could not determine the Object value. For example, the row was just created, the default for the Object column was not available, and the consumer had not yet set a new Object value.
I traced the error back to this line of code from the original compiler output
Projects.databind()
The complete sub is
public sub LoadProjects()
rptProjects.DataSource = GetProjects(hiddenProjectI
hiddenProjectType.Text, _
hiddenDesigner.Text, _
hiddenGroupMemberID.Text, _
hiddenDeliveryYear.Text, _
hiddenMarketSegment.Text, _
hiddenClient.Text, _
hiddenProjectStatus.Text, _
hiddenEducationConsultant.
hiddenInitiator.Text, _
hiddenIsRush.Text, _
hiddenIsLinkedToVPEX.Text,
hiddenSortList)
rptProjects.databind()
end sub
And it is populating a asp.net repeatrer control, but it is also being called on the other control. It only happens for one record....any ideas???
The provider could not determine the Object value. For example, the row was just created, the default for the Object column was not available, and the consumer had not yet set a new Object value.
I traced the error back to this line of code from the original compiler output
Projects.databind()
The complete sub is
public sub LoadProjects()
rptProjects.DataSource = GetProjects(hiddenProjectI
hiddenProjectType.Text, _
hiddenDesigner.Text, _
hiddenGroupMemberID.Text, _
hiddenDeliveryYear.Text, _
hiddenMarketSegment.Text, _
hiddenClient.Text, _
hiddenProjectStatus.Text, _
hiddenEducationConsultant.
hiddenInitiator.Text, _
hiddenIsRush.Text, _
hiddenIsLinkedToVPEX.Text,
hiddenSortList)
rptProjects.databind()
end sub
And it is populating a asp.net repeatrer control, but it is also being called on the other control. It only happens for one record....any ideas???
ASKER
You ready for this one....God bless you for helping - I thought no one would even be able to figure this out!
Public Function GetProjects(ByVal strProjectID As String, _
ByVal strProjectType As String, _
ByVal strDesignerName As String, _
ByVal strGroupMemberID As String, _
ByVal strDeliveryYear As String, _
ByVal strMarketSegment As String, _
ByVal strClient As String, _
ByVal strStatus As String, _
ByVal strEducationConsultant As String, _
ByVal strInitiator As String, _
ByVal strIsRush As String, _
ByVal strIsLinkedToVPEX As String, _
ByVal ddlSortList As DropDownList) As DataSet
Dim strSQL As String = "SELECT P.*, C.ClientName AS ClientName, M.Name as MarketSegment FROM Projects AS P, Clients AS C, MarketSegments AS M" & IIf(strGroupMemberID <> "", ", ProjectResources AS PR, GroupMembers AS GM", "") & " WHERE C.ClientID = P.ClientID AND C.MarketSegmentID = M.MarketSegmentID AND " & IIf(strGroupMemberID <> "", "P.ProjectID = PR.ProjectID AND PR.GroupMemberID = GM.GroupMemberID AND ", "")
If strProjectID <> "" Then
If IsNumeric(strProjectID) Then
strSQL += "P.ProjectID = " & strProjectID.Replace("""",
End If
End If
If strProjectType <> "" Then
strSQL += "ProjectType='" & strProjectType.Replace("""
End If
If strDesignerName <> "" Then
strSQL += "Designer LIKE '%" & strDesignerName.Replace(""
End If
If strGroupMemberID <> "" Then
strSQL += "GM.GroupMemberID = " & strGroupMemberID & " AND "
End If
If strDeliveryYear <> "" Then
If IsNumeric(strDeliveryYear)
strSQL += "YEAR(DeliveryDate) = " & strDeliveryYear.Replace(""
End If
End If
If strMarketSegment <> "" Then
strSQL += "MarketSegment='" & strMarketSegment.Replace("
End If
If strClient <> "" Then
strSQL += "ClientName='" & strClient.Replace("""", "'").Replace("'", "''") & "' AND "
End If
If strEducationConsultant <> "" Then
strSQL += "EducationConsultant='" & strEducationConsultant.Rep
End If
If strInitiator <> "" Then
strSQL += "Initiator='" & strInitiator.Replace("""",
End If
If strStatus <> "" Then
If strStatus <> "Not Completed" Then
strSQL += "Status='" & strStatus.Replace("""", "'").Replace("'", "''") & "' AND "
Else
strSQL += "Status <> 'Completed' AND "
End If
End If
If strIsRush <> "" Then
strSQL += "IsRush=" & strIsRush & " AND "
End If
If strIsLinkedToVPEX <> "" Then
strSQL += "IsLinkedToVPEX=" & strIsLinkedToVPEX & " AND "
End If
If strSQL.EndsWith(" AND ") Then
Dim intLastIndexOfAND As Integer = strSQL.LastIndexOf(" AND ")
strSQL = strSQL.Substring(0, intLastIndexOfAND + 1)
ElseIf strSQL.EndsWith(" WHERE ") Then
Dim intLastIndexOfWHERE As Integer = strSQL.LastIndexOf(" WHERE ")
strSQL = strSQL.Substring(0, intLastIndexOfWHERE + 1)
End If
If ddlSortList.Items.Count > 0 Then
strSQL += " ORDER BY "
Dim itmColumnName As ListItem
'for each itmColumnName in ddlSortList.items
' strSQL += itmColumnName.text & " " & itmColumnName.value & ", "
'next
strSQL += ddlSortList.Items(0).Text & " " & ddlSortList.Items(0).Value
If strSQL.EndsWith(", ") Then
Dim intLastIndexOfComma As Integer = strSQL.LastIndexOf(", ")
strSQL = strSQL.Substring(0, intLastIndexOfComma)
End If
End If
Dim strDataSource As String = Server.MapPath("~/projects
Return GetDataSetFromDB(strSQL, strDataSource, "Projects")
End Function
Public Function GetProjects(ByVal strProjectID As String, _
ByVal strProjectType As String, _
ByVal strDesignerName As String, _
ByVal strGroupMemberID As String, _
ByVal strDeliveryYear As String, _
ByVal strMarketSegment As String, _
ByVal strClient As String, _
ByVal strStatus As String, _
ByVal strEducationConsultant As String, _
ByVal strInitiator As String, _
ByVal strIsRush As String, _
ByVal strIsLinkedToVPEX As String, _
ByVal ddlSortList As DropDownList) As DataSet
Dim strSQL As String = "SELECT P.*, C.ClientName AS ClientName, M.Name as MarketSegment FROM Projects AS P, Clients AS C, MarketSegments AS M" & IIf(strGroupMemberID <> "", ", ProjectResources AS PR, GroupMembers AS GM", "") & " WHERE C.ClientID = P.ClientID AND C.MarketSegmentID = M.MarketSegmentID AND " & IIf(strGroupMemberID <> "", "P.ProjectID = PR.ProjectID AND PR.GroupMemberID = GM.GroupMemberID AND ", "")
If strProjectID <> "" Then
If IsNumeric(strProjectID) Then
strSQL += "P.ProjectID = " & strProjectID.Replace("""",
End If
End If
If strProjectType <> "" Then
strSQL += "ProjectType='" & strProjectType.Replace("""
End If
If strDesignerName <> "" Then
strSQL += "Designer LIKE '%" & strDesignerName.Replace(""
End If
If strGroupMemberID <> "" Then
strSQL += "GM.GroupMemberID = " & strGroupMemberID & " AND "
End If
If strDeliveryYear <> "" Then
If IsNumeric(strDeliveryYear)
strSQL += "YEAR(DeliveryDate) = " & strDeliveryYear.Replace(""
End If
End If
If strMarketSegment <> "" Then
strSQL += "MarketSegment='" & strMarketSegment.Replace("
End If
If strClient <> "" Then
strSQL += "ClientName='" & strClient.Replace("""", "'").Replace("'", "''") & "' AND "
End If
If strEducationConsultant <> "" Then
strSQL += "EducationConsultant='" & strEducationConsultant.Rep
End If
If strInitiator <> "" Then
strSQL += "Initiator='" & strInitiator.Replace("""",
End If
If strStatus <> "" Then
If strStatus <> "Not Completed" Then
strSQL += "Status='" & strStatus.Replace("""", "'").Replace("'", "''") & "' AND "
Else
strSQL += "Status <> 'Completed' AND "
End If
End If
If strIsRush <> "" Then
strSQL += "IsRush=" & strIsRush & " AND "
End If
If strIsLinkedToVPEX <> "" Then
strSQL += "IsLinkedToVPEX=" & strIsLinkedToVPEX & " AND "
End If
If strSQL.EndsWith(" AND ") Then
Dim intLastIndexOfAND As Integer = strSQL.LastIndexOf(" AND ")
strSQL = strSQL.Substring(0, intLastIndexOfAND + 1)
ElseIf strSQL.EndsWith(" WHERE ") Then
Dim intLastIndexOfWHERE As Integer = strSQL.LastIndexOf(" WHERE ")
strSQL = strSQL.Substring(0, intLastIndexOfWHERE + 1)
End If
If ddlSortList.Items.Count > 0 Then
strSQL += " ORDER BY "
Dim itmColumnName As ListItem
'for each itmColumnName in ddlSortList.items
' strSQL += itmColumnName.text & " " & itmColumnName.value & ", "
'next
strSQL += ddlSortList.Items(0).Text & " " & ddlSortList.Items(0).Value
If strSQL.EndsWith(", ") Then
Dim intLastIndexOfComma As Integer = strSQL.LastIndexOf(", ")
strSQL = strSQL.Substring(0, intLastIndexOfComma)
End If
End If
Dim strDataSource As String = Server.MapPath("~/projects
Return GetDataSetFromDB(strSQL, strDataSource, "Projects")
End Function
ASKER
How can I determine if there are dbnulls?
1. To test if a column Value is DBNull
If you use the ODBCDataReader or a SQLDataReader then you can use the Function from the DataReader.
myReader.IsDBNull(NumberOf
2. Can you post the Value of strSQL when your software throws the exception?
If you use the ODBCDataReader or a SQLDataReader then you can use the Function from the DataReader.
myReader.IsDBNull(NumberOf
2. Can you post the Value of strSQL when your software throws the exception?
ASKER
It is a project database - I did not write it - the exception occurs only when one of the ddl values is included in the search, the name of one particular designer and it never acted up before.
I just tried the search and now it is saying that the record is deleted, a totally new error message - and I opend the access DB and the record of the designer is still there,.,Here is what the error message states
Server Error in '/MMS' Application. Record is deleted. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbExc
Source Error:
Line 152: ProjectsDetailedView.IsRus
Source File: D:\Inetpub\Vanguard-Educat
Stack Trace:
[OleDbException (0x80040e23): Record is deleted.] Microsoft.VisualBasic.Comp
I just tried the search and now it is saying that the record is deleted, a totally new error message - and I opend the access DB and the record of the designer is still there,.,Here is what the error message states
Server Error in '/MMS' Application. Record is deleted. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbExc
Source Error:
Line 152: ProjectsDetailedView.IsRus
Source File: D:\Inetpub\Vanguard-Educat
Stack Trace:
[OleDbException (0x80040e23): Record is deleted.] Microsoft.VisualBasic.Comp
ASKER
Also - I am not sure how to display the strSQL without executing the control its on. I am really confused
ASKER
Any luck?
can you change the Line from
strSQL += ddlSortList.Items(0).Text & " " & ddlSortList.Items(0).Value
to
strSQL += ddlSortList.SelectedItem.T
strSQL += ddlSortList.Items(0).Text & " " & ddlSortList.Items(0).Value
to
strSQL += ddlSortList.SelectedItem.T
ASKER
Lemme try that....
ASKER
I still get this
Record is deleted. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbExc
Source Error:
Line 152: ProjectsDetailedView.IsRus
Source File: D:\Inetpub\Vanguard-Educat
Record is deleted. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbExc
Source Error:
Line 152: ProjectsDetailedView.IsRus
Source File: D:\Inetpub\Vanguard-Educat
Did you use any SQL Commands which updates the database or creates new records ?
Can you Debug the application or run's it on a server where you have no access ?
Please post the strSQL Query you can use Response.Write() to write it on your website
or you save it to a textfile.
Can you Debug the application or run's it on a server where you have no access ?
Please post the strSQL Query you can use Response.Write() to write it on your website
or you save it to a textfile.
ASKER
Please provide the syntax on how this would be done - I have provided the two .ascx that are used when the error occurs - all of the controls are called by default.aspx
"ProjectSearch.ascx" and "projectsDetailedView.ascx
The error
Record is deleted. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbExc
Source Error:
Line 152: ProjectsDetailedView.IsRus
Line 153: ProjectsDetailedView.IsLin
Line 155:
Line 156: Dim lblProjectCount As Label
Source File: \MMS\projects\ascx\Project
"ProjectSearch.ascx" and "projectsDetailedView.ascx
The error
Record is deleted. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbExc
Source Error:
Line 152: ProjectsDetailedView.IsRus
Line 153: ProjectsDetailedView.IsLin
Line 155:
Line 156: Dim lblProjectCount As Label
Source File: \MMS\projects\ascx\Project
//ProjectSearch
<%@ Control debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Oledb" %>
<%@ Register TagPrefix="ascx" tagname="ProjectYearsDropDownList" src="ProjectYearsDropDownList.ascx" %>
<%@ Register TagPrefix="ascx" tagname="DesignerDropDownList" src="DesignerDropDownList.ascx" %>
<%@ Register TagPrefix="ascx" tagname="ProjectStatusDropDownList" src="ProjectStatusDropDownList.ascx" %>
<%@ Register TagPrefix="ascx" tagname="MarketSegmentDropDownList" src="MarketSegmentDropDownList.ascx" %>
<%@ Register TagPrefix="ascx" tagname="ClientDropDownList" src="ClientDropDownList.ascx" %>
<%@ Register TagPrefix="ascx" tagname="ProjectTypeDropDownList" src="ProjectTypeDropDownList.ascx" %>
<%@ Register TagPrefix="ascx" tagname="EducationConsultantDropDownList" src="EducationConsultantDropDownList.ascx" %>
<%@ Register TagPrefix="ascx" tagname="InitiatorDropDownList" src="InitiatorDropDownList.ascx" %>
<%@ Register TagPrefix="ascx" tagname="IsRushDropDownList" src="IsRushDropDownList.ascx" %>
<%@ Register TagPrefix="ascx" tagname="IsLinkedToVPEXDropDownList" src="IsLinkedToVPEXDropDownList.ascx" %>
<%@ Register TagPrefix="ascx" tagname="GroupMemberDropDownList" src="GroupMemberDropDownList.ascx" %>
<script language="vb" runat="server">
public property style as string
set
hiddenStyle.text = value
end set
get
return hiddenStyle.text
end get
end property
public property ProjectID as string
set
txtProjectID.text = value
end set
get
return txtProjectID.text
end get
end property
public property ProjectType as string
set
ddlProjectType.selectedProjectType = value
end set
get
return ddlProjectType.selectedProjectType
end get
end property
public property Designer as string
set
ddlDesigner.selectedDesigner = value
end set
get
return ddlDesigner.selectedDesigner
end get
end property
public property GroupMemberID as string
set
ddlGroupMember.selectedValue = value
end set
get
return ddlGroupMember.selectedValue
end get
end property
public property DeliveryYear as string
set
ddlProjectYears.selectedDeliveryYear = value
end set
get
return ddlProjectYears.selectedDeliveryYear
end get
end property
public property MarketSegment as string
set
ddlMarketSegment.selectedMarketSegment = value
end set
get
return ddlMarketSegment.selectedMarketSegment
end get
end property
public property Client as string
set
ddlClient.selectedClient = value
end set
get
return ddlClient.selectedClient
end get
end property
public property ProjectStatus as string
set
ddlProjectStatus.SelectedStatus = value
end set
get
return ddlProjectStatus.SelectedStatus
end get
End Property
Public Property EducationConsultant() As String
Set(ByVal value As String)
ddlEducationConsultant.SelectedEducationConsultant = value
End Set
Get
Return ddlEducationConsultant.SelectedEducationConsultant
End Get
End Property
public property Initiator as string
set
ddlInitiator.SelectedInitiator = value
end set
get
return ddlInitiator.SelectedInitiator
end get
end property
public property IsRush as string
set
ddlIsRush.SelectedValue = value
end set
get
return ddlIsRush.SelectedValue
end get
End Property
Public Property IsLinkedToVPEX() As String
Set(ByVal value As String)
ddlIsLinkedToVPEX.SelectedValue = value
End Set
Get
Return ddlIsLinkedToVPEX.SelectedValue
End Get
End Property
Sub LoadProjects()
Dim ProjectsDetailedView As Object
ProjectsDetailedView = CType(Me.Parent.FindControl("ProjectsDetailedView"), Object)
ProjectsDetailedView.ProjectID = txtProjectID.Text
ProjectsDetailedView.ProjectType = ddlProjectType.SelectedProjectType
ProjectsDetailedView.Designer = ddlDesigner.SelectedDesigner
ProjectsDetailedView.GroupMemberID = ddlGroupMember.SelectedValue
ProjectsDetailedView.DeliveryYear = ddlProjectYears.SelectedDeliveryYear
ProjectsDetailedView.MarketSegment = ddlMarketSegment.SelectedMarketSegment
ProjectsDetailedView.Client = ddlClient.SelectedClient
ProjectsDetailedView.ProjectStatus = ddlProjectStatus.SelectedStatus
ProjectsDetailedView.EducationConsultant = ddlEducationConsultant.SelectedEducationConsultant
ProjectsDetailedView.Initiator = ddlInitiator.SelectedInitiator
ProjectsDetailedView.IsRush = ddlIsRush.SelectedValue
ProjectsDetailedView.IsLinkedToVPEX = ddlIsLinkedToVPEX.SelectedValue
Dim lblProjectCount As Label
lblProjectCount = CType(Me.Parent.FindControl("lblProjectCount"), Label)
lblProjectCount.Text = ProjectsDetailedView.ProjectCount
If ddlGroupMember.SelectedValue <> "" Then
Dim dsGroupMember As New DataSet
dsGroupMember = GetGroupMembersByID(ddlGroupMember.SelectedValue)
Dim lblGroupMemberFullName As Label
lblGroupMemberFullName = CType(Me.Parent.FindControl("lblGroupMemberFullName"), Label)
lblGroupMemberFullName.Text = FormatGroupMemberFullName(dsGroupMember.Tables(0).Rows(0)("FirstName"), dsGroupMember.Tables(0).Rows(0)("MiddleInitial"), dsGroupMember.Tables(0).Rows(0)("LastName")) & " > "
Else
Dim lblGroupMemberFullName As Label
lblGroupMemberFullName = CType(Me.Parent.FindControl("lblGroupMemberFullName"), Label)
lblGroupMemberFullName.Text = ""
End If
End Sub
Public Sub ReloadProjectTypes()
ddlProjectType.LoadProjectTypeDropDownList()
End Sub
sub onFilterClicked(sender as object, e as eventargs)
LoadProjects()
me.visible = false
end sub
sub onCancelClicked(sender as object, e as eventargs)
me.visible = false
end sub
public function GetGroupMembersByID(intGroupMemberID as integer) as dataset
dim strSQL as string = "SELECT * FROM GroupMembers WHERE GroupMemberID = " & intGroupMemberID
dim strDataSource as string = server.MapPath("~/projects/db/db.mdb")
return GetDataSetFromDB(strSQL, strDataSource, "GroupMembers")
end function
public function FormatGroupMemberFullName(strFirstName as string, strMiddleInitial as string, strLastName as string) as string
dim strFullName as string
if strFirstName <> "" then
strFullName = strFirstName
end if
if strFullName <> "" and strMiddleInitial <> "" then
strFullName += " "
end if
if strMiddleInitial <> "" then
strFullName += strMiddleInitial
end if
if strFullName <> "" and strLastName <> "" then
strFullName += " "
end if
if strLastName <> "" then
strFullName += strLastName
end if
return strFullName
end function
'======================================================================================
' DB CORE FUNCTIONS
'======================================================================================
private function GetDataSetFromDB(strSQL as string, strDataSource as string, strTableName as string) as dataset
dim strConnection as String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strDataSource
dim objDataSet as new DataSet()
dim objConnection as new oledbconnection(strConnection)
dim objAdapter as new oledbdataadapter(strSQL, objConnection)
objConnection.close()
objAdapter.fill(objDataSet, strTableName)
objAdapter = nothing
return objDataSet
end function
private function GetSingleValueFromDB(strSQL as string, strDataSource as string, strTableName as string, strFieldName as string) as string
dim strConnection as String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strDataSource
dim objDataSet as new DataSet()
dim objConnection as new oledbconnection(strConnection)
dim objAdapter as new oledbdataadapter(strSQL, objConnection)
objConnection.close()
objAdapter.fill(objDataSet, strTableName)
objAdapter = nothing
try
return objDataSet.tables(0).rows(0)(strFieldName)
catch
end try
end function
private sub ExecuteDB(strSQL as string, strDataSource as string)
dim strConnection as String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strDataSource
dim objConnection = new oledbconnection(strConnection)
objConnection.open()
dim objCommand as oledbcommand
objCommand = new oledbcommand(strSQL, objConnection)
objCommand.executenonquery()
objConnection.close()
end sub
</script>
<asp:label id="hiddenStyle" visible="false" runat="server"/>
<div style="position:absolute; top:0; left:0; width:100%; height:100%; z-index:100;">
<table
width="100%"
height="100%">
<tr>
<td
valign="middle"
align="center">
<table
cellpadding="5"
cellspacing="0"
bgcolor="#aaaaaa"
style="border-width:1pt; border-color:#000000; border-style:solid;">
<tr>
<td bgcolor="#888888">
<table
cellpadding="5"
cellspacing="0"
border="0">
<tr>
<td style="color:#ffffff; font-weight:bold; font-size:10pt; font-weight:bold;">Filter Projects</td>
</tr>
</table>
</td>
</tr>
<tr>
<td valign="top" height="100%">
<table cellpadding="5" cellspacing="1" border="0" width="100%"height="100%" bgcolor="#cccccc">
<tr>
<td width="100%" valign="top" height="100%">
<table
width="100%"
cellpadding="5"
cellspacing="0">
<tr>
<td style="font-size:8pt; color:#000000; font-weight:bold;">
Project ID:
<br>
<asp:textbox id="txtProjectID"
style="font-size:8pt; font-family:arial;"
runat="server"/>
</td>
<td style="font-size:8pt; color:#000000; font-weight:bold;">
Delivery Year:
<br>
<ascx:ProjectYearsDropDownList
id="ddlProjectYears"
runat="server"/>
</td>
</tr>
<tr>
<td style="font-size:8pt; color:#000000; font-weight:bold;">
Designer:
<br>
<ascx:DesignerDropDownList
id="ddlDesigner"
runat="server"/>
</td>
<td style="font-size:8pt; color:#000000; font-weight:bold;" valign="top">
Status:
<br>
<ascx:ProjectStatusDropDownList
id="ddlProjectStatus"
runat="server"/>
</td>
</tr>
<tr>
<td style="font-size:8pt; color:#000000; font-weight:bold;">
Market Segment:
<br>
<ascx:MarketSegmentDropDownList
id="ddlMarketSegment"
runat="server"/>
</td>
<td style="font-size:8pt; color:#000000; font-weight:bold;">
Client:
<br>
<ascx:ClientDropDownList
id="ddlClient"
runat="server"/>
</td>
</tr>
<tr>
<td style="font-size:8pt; color:#000000; font-weight:bold;">
Project Type:
<br>
<ascx:ProjectTypeDropDownList
id="ddlProjectType"
AllowOther="false"
runat="server"/>
</td>
<td style="font-size:8pt; color:#000000; font-weight:bold;">
Initiator:
<br>
<ascx:InitiatorDropDownList
id="ddlInitiator"
runat="server"/>
</td>
</tr>
<tr>
<td style="font-size:8pt; color:#000000; font-weight:bold;">
Group Member:
<br>
<ascx:GroupMemberDropDownList
id="ddlGroupMember"
runat="server"/>
</td>
<td style="font-size:8pt; color:#000000; font-weight:bold;">
Is Rush Job?
<br>
<ascx:IsRushDropDownList
id="ddlIsRush"
runat="server"/>
</td>
</tr>
<tr>
<td style="font-size:8pt; color:#000000; font-weight:bold;">
Is Linked to VPEX?
<br>
<ascx:IsLinkedToVPEXDropDownList
id="ddlIsLinkedToVPEX"
runat="server"/>
</td>
<td style="font-size:8pt; color:#000000; font-weight:bold;">
Education Consultant:
<br>
<ascx:EducationConsultantDropDownList
id="ddlEducationConsultant"
runat="server"/>
</td>
</tr>
</table>
</td>
</tr>
<tr><td height="100%" colspan="2"> </td></tr>
<tr>
<td align="right" colspan="2">
<asp:button
text=" Filter "
onclick="onFilterClicked"
style="border-width:1pt; border-color:#000000; background-color:#f1f1f1; border-style:solid; color:#000000;"
runat="server"/>
<asp:button
text=" Cancel "
onclick="onCancelClicked"
style="border-width:1pt; border-color:#000000; background-color:#f1f1f1; border-style:solid; color:#000000;"
runat="server"/>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
</tr>
</table>
</div>
//ProjectsDetailedView
<%@ Control debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Oledb" %>
<%@ Register TagPrefix="ascx" tagname="ProjectGroupMembers" src="ProjectGroupMembers.ascx"%>
<script language="vb" runat="server">
public property ProjectID as string
set
hiddenProjectID.text = value
end set
get
return hiddenProjectID.text
end get
end property
public property ProjectType as string
set
hiddenProjectType.text = value
end set
get
return hiddenProjectType.text
end get
end property
public property Designer as string
set
hiddenDesigner.text = value
end set
get
return hiddenDesigner.text
end get
end property
public property GroupMemberID as string
set
hiddenGroupMemberID.text = value
end set
get
return hiddenGroupMemberID.text
end get
end property
public property DeliveryYear as string
set
hiddenDeliveryYear.text = value
end set
get
return hiddenDeliveryYear.text
end get
end property
public property MarketSegment as string
set
hiddenMarketSegment.text = value
end set
get
return hiddenMarketSegment.text
end get
end property
public property Client as string
set
hiddenClient.text = value
end set
get
return hiddenClient.text
end get
end property
public property ProjectStatus as string
set
hiddenProjectStatus.text = value
end set
get
return hiddenProjectStatus.text
end get
end property
public property EducationConsultant as string
set
hiddenEducationConsultant.text = value
end set
get
return hiddenEducationConsultant.text
end get
end property
public property Initiator as string
set
hiddenInitiator.text = value
end set
get
return hiddenInitiator.text
end get
end property
public property IsRush as string
set
hiddenIsRush.text = value
end set
get
return hiddenIsRush.text
end get
End Property
Public Property IsLinkedToVPEX() As String
Set(ByVal value As String)
hiddenIsLinkedToVPEX.Text = value
End Set
Get
Return hiddenIsLinkedToVPEX.Text
End Get
End Property
public readonly property ProjectCount as integer
get
return rptProjects.items.count
end get
end property
sub page_load()
if not page.isPostBack() then
LoadSortArrayList()
end if
end sub
sub LoadSortArrayList()
hiddenSortList.items.clear()
dim itmProjectID as new listitem
itmProjectID.text = "ProjectID"
itmProjectID.value = "ASC"
dim itmPoseidonNumber as new listitem
itmPoseidonNumber.text = "PoseidonNumber"
itmPoseidonNumber.value = "ASC"
dim itmClientName as new listitem
itmClientName.text = "C.ClientName"
itmClientName.value = "ASC"
dim itmProjectTitle as new listitem
itmProjectTitle.text = "ProjectTitle"
itmProjectTitle.value = "ASC"
dim itmProjectType as new listitem
itmProjectType.text = "ProjectType"
itmProjectType.value = "ASC"
dim itmMarketSegment as new listitem
itmMarketSegment.text = "M.Name"
itmMarketSegment.value = "ASC"
dim itmStatus as new listitem
itmStatus.text = "Status"
itmStatus.value = "ASC"
dim itmInitiator as new listitem
itmInitiator.text = "Initiator"
itmInitiator.value = "ASC"
dim itmProjectManager as new listitem
itmProjectManager.text = "ProjectManager"
itmProjectManager.value = "ASC"
dim itmProjectCoordinator as new listitem
itmProjectCoordinator.text = "ProjectCoordinator"
itmProjectCoordinator.value = "ASC"
dim itmDesigner as new listitem
itmDesigner.text = "Designer"
itmDesigner.value = "ASC"
dim itmDateReceived as new listitem
itmDateReceived.text = "DateReceived"
itmDateReceived.value = "ASC"
dim itmDeliveryDate as new listitem
itmDeliveryDate.text = "DeliveryDate"
itmDeliveryDate.value = "ASC"
dim itmLastUpdated as new listitem
itmLastUpdated.text = "LastUpdated"
itmLastUpdated.value = "ASC"
dim itmPlanNumber as new listitem
itmPlanNumber.text = "PlanNumber"
itmPlanNumber.value = "ASC"
dim itmStartDate as new listitem
itmStartDate.text = "StartDate"
itmStartDate.value = "ASC"
dim itmIsRush as new listitem
itmIsRush.text = "IsRush"
itmIsRush.value = "DESC"
dim itmURL as new listitem
itmURL.text = "URL"
itmURL.value = "ASC"
dim itmFolderLocation as new listitem
itmFolderLocation.text = "FolderLocation"
itmFolderLocation.value = "ASC"
dim itmAdministrator as new listitem
itmAdministrator.text = "Administrator"
itmAdministrator.value = "ASC"
dim itmEducationConsultant as new listitem
itmEducationConsultant.text = "EducationConsultant"
itmEducationConsultant.value = "ASC"
Dim itmIsLinkedToVPEX As New ListItem
itmIsLinkedToVPEX.Text = "IsLinkedToVPEX"
itmIsLinkedToVPEX.Value = "ASC"
hiddenSortList.items.add(itmDeliveryDate)
hiddenSortList.items.add(itmClientName)
hiddenSortList.items.add(itmDateReceived)
hiddenSortList.items.add(itmProjectTitle)
hiddenSortList.items.add(itmProjectID)
hiddenSortList.items.add(itmPoseidonNumber)
hiddenSortList.items.add(itmProjectType)
hiddenSortList.items.add(itmMarketSegment)
hiddenSortList.items.add(itmStatus)
hiddenSortList.items.add(itmDesigner)
hiddenSortList.items.add(itmIsRush)
hiddenSortList.items.add(itmInitiator)
hiddenSortList.items.add(itmURL)
hiddenSortList.items.add(itmFolderLocation)
hiddenSortList.items.add(itmLastUpdated)
hiddenSortList.items.add(itmAdministrator)
hiddenSortList.items.add(itmEducationConsultant)
hiddenSortList.items.add(itmProjectCoordinator)
hiddenSortList.Items.Add(itmLastUpdated)
hiddenSortList.Items.Add(itmIsLinkedToVPEX)
end sub
public sub LoadProjects()
rptProjects.DataSource = GetProjects(hiddenProjectID.Text, _
hiddenProjectType.Text, _
hiddenDesigner.Text, _
hiddenGroupMemberID.Text, _
hiddenDeliveryYear.Text, _
hiddenMarketSegment.Text, _
hiddenClient.Text, _
hiddenProjectStatus.Text, _
hiddenEducationConsultant.Text, _
hiddenInitiator.Text, _
hiddenIsRush.Text, _
hiddenIsLinkedToVPEX.Text, _
hiddenSortList)
rptProjects.databind()
end sub
sub onProjectTitleClicked(sender as object, e as eventargs)
dim Project as object
Project = CType(me.parent.findControl("Project"), object)
Project.projectID = Cint(CType(sender.findControl("hiddenProjectID"), label).text)
Project.visible = true
end sub
sub onEditClicked(sender as object, e as eventargs)
dim EditProject as object
EditProject = CType(me.parent.findControl("EditProject"), object)
EditProject.projectID = Cint(CType(sender.findControl("hiddenProjectID"), label).text)
EditProject.visible = true
end sub
sub onDeleteClicked(sender as object, e as eventargs)
dim deleteProject as object = LoadControl("deleteProject.ascx")
deleteProject = page.findControl("deleteProject")
deleteProject.projectID = CType(sender.findControl("hiddenProjectID"), label).text
'page.findControl("projects").visible = false
page.findControl("deleteProject").visible = true
end sub
sub onSortProjectIDAscendingClicked(sender as object, e as ImageClickEventArgs)
btnSortProjectIDAscending.attributes("style") = "filter:alpha(opacity=25);"
btnSortProjectIDDescending.attributes("style") = "filter:alpha(opacity=100);"
dim itmColumn as listItem = hiddenSortList.items.findByText("ProjectID")
itmColumn.value = "ASC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortProjectIDDescendingClicked(sender as object, e as ImageClickEventArgs)
btnSortProjectIDAscending.attributes("style") = "filter:alpha(opacity=100);"
btnSortProjectIDDescending.attributes("style") = "filter:alpha(opacity=25);"
dim itmColumn as listItem = hiddenSortList.items.findByText("ProjectID")
itmColumn.value = "DESC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortMarketSegmentAscendingClicked(sender as object, e as ImageClickEventArgs)
btnSortMarketSegmentAscending.attributes("style") = "filter:alpha(opacity=25);"
btnSortMarketSegmentDescending.attributes("style") = "filter:alpha(opacity=100);"
dim itmColumn as listItem = hiddenSortList.items.findByText("M.Name")
itmColumn.value = "ASC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortMarketSegmentDescendingClicked(sender as object, e as ImageClickEventArgs)
btnSortMarketSegmentAscending.attributes("style") = "filter:alpha(opacity=100);"
btnSortMarketSegmentDescending.attributes("style") = "filter:alpha(opacity=25);"
dim itmColumn as listItem = hiddenSortList.items.findByText("M.Name")
itmColumn.value = "DESC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortClientNameAscendingClicked(sender as object, e as ImageClickEventArgs)
btnSortClientNameAscending.attributes("style") = "filter:alpha(opacity=25);"
btnSortClientNameDescending.attributes("style") = "filter:alpha(opacity=100);"
dim itmColumn as listItem = hiddenSortList.items.findByText("C.ClientName")
itmColumn.value = "ASC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortClientNameDescendingClicked(sender as object, e as ImageClickEventArgs)
btnSortClientNameAscending.attributes("style") = "filter:alpha(opacity=100);"
btnSortClientNameDescending.attributes("style") = "filter:alpha(opacity=25);"
dim itmColumn as listItem = hiddenSortList.items.findByText("C.ClientName")
itmColumn.value = "DESC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortProjectTypeAscendingClicked(sender as object, e as ImageClickEventArgs)
btnSortProjectTypeAscending.attributes("style") = "filter:alpha(opacity=25);"
btnSortProjectTypeDescending.attributes("style") = "filter:alpha(opacity=100);"
dim itmColumn as listItem = hiddenSortList.items.findByText("ProjectType")
itmColumn.value = "ASC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortProjectTypeDescendingClicked(sender as object, e as ImageClickEventArgs)
btnSortProjectTypeAscending.attributes("style") = "filter:alpha(opacity=100);"
btnSortProjectTypeDescending.attributes("style") = "filter:alpha(opacity=25);"
dim itmColumn as listItem = hiddenSortList.items.findByText("ProjectType")
itmColumn.value = "DESC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortDesignerAscendingClicked(sender as object, e as ImageClickEventArgs)
btnSortDesignerAscending.attributes("style") = "filter:alpha(opacity=25);"
btnSortDesignerDescending.attributes("style") = "filter:alpha(opacity=100);"
dim itmColumn as listItem = hiddenSortList.items.findByText("Designer")
itmColumn.value = "ASC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortDesignerDescendingClicked(sender as object, e as ImageClickEventArgs)
btnSortDesignerAscending.attributes("style") = "filter:alpha(opacity=100);"
btnSortDesignerDescending.attributes("style") = "filter:alpha(opacity=25);"
dim itmColumn as listItem = hiddenSortList.items.findByText("Designer")
itmColumn.value = "DESC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortProjectTitleAscendingClicked(sender as object, e as ImageClickEventArgs)
btnSortProjectTitleAscending.attributes("style") = "filter:alpha(opacity=25);"
btnSortProjectTitleDescending.attributes("style") = "filter:alpha(opacity=100);"
dim itmColumn as listItem = hiddenSortList.items.findByText("ProjectTitle")
itmColumn.value = "ASC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortProjectTitleDescendingClicked(sender as object, e as ImageClickEventArgs)
btnSortProjectTitleAscending.attributes("style") = "filter:alpha(opacity=100);"
btnSortProjectTitleDescending.attributes("style") = "filter:alpha(opacity=25);"
dim itmColumn as listItem = hiddenSortList.items.findByText("ProjectTitle")
itmColumn.value = "DESC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortDateReceivedAscendingClicked(sender as object, e as ImageClickEventArgs)
btnSortDateReceivedAscending.attributes("style") = "filter:alpha(opacity=25);"
btnSortDateReceivedDescending.attributes("style") = "filter:alpha(opacity=100);"
dim itmColumn as listItem = hiddenSortList.items.findByText("DateReceived")
itmColumn.value = "ASC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortDateReceivedDescendingClicked(sender as object, e as ImageClickEventArgs)
btnSortDateReceivedAscending.attributes("style") = "filter:alpha(opacity=100);"
btnSortDateReceivedDescending.attributes("style") = "filter:alpha(opacity=25);"
dim itmColumn as listItem = hiddenSortList.items.findByText("DateReceived")
itmColumn.value = "DESC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortDeliveryDateAscendingClicked(sender as object, e as ImageClickEventArgs)
btnSortDeliveryDateAscending.attributes("style") = "filter:alpha(opacity=25);"
btnSortDeliveryDateDescending.attributes("style") = "filter:alpha(opacity=100);"
dim itmColumn as listItem = hiddenSortList.items.findByText("DeliveryDate")
itmColumn.value = "ASC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortDeliveryDateDescendingClicked(sender as object, e as ImageClickEventArgs)
btnSortDeliveryDateAscending.attributes("style") = "filter:alpha(opacity=100);"
btnSortDeliveryDateDescending.attributes("style") = "filter:alpha(opacity=25);"
dim itmColumn as listItem = hiddenSortList.items.findByText("DeliveryDate")
itmColumn.value = "DESC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortLastUpdatedAscendingClicked(sender as object, e as ImageClickEventArgs)
btnSortLastUpdatedAscending.attributes("style") = "filter:alpha(opacity=25);"
btnSortLastUpdatedDescending.attributes("style") = "filter:alpha(opacity=100);"
dim itmColumn as listItem = hiddenSortList.items.findByText("LastUpdated")
itmColumn.value = "ASC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortLastUpdatedDescendingClicked(sender as object, e as ImageClickEventArgs)
btnSortLastUpdatedAscending.attributes("style") = "filter:alpha(opacity=100);"
btnSortLastUpdatedDescending.attributes("style") = "filter:alpha(opacity=25);"
dim itmColumn as listItem = hiddenSortList.items.findByText("LastUpdated")
itmColumn.value = "DESC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortStatusAscendingClicked(sender as object, e as ImageClickEventArgs)
btnSortStatusAscending.attributes("style") = "filter:alpha(opacity=25);"
btnSortStatusDescending.attributes("style") = "filter:alpha(opacity=100);"
dim itmColumn as listItem = hiddenSortList.items.findByText("Status")
itmColumn.value = "ASC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortStatusDescendingClicked(sender as object, e as ImageClickEventArgs)
btnSortStatusAscending.attributes("style") = "filter:alpha(opacity=100);"
btnSortStatusDescending.attributes("style") = "filter:alpha(opacity=25);"
dim itmColumn as listItem = hiddenSortList.items.findByText("Status")
itmColumn.value = "DESC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortInitiatorAscendingClicked(sender as object, e as ImageClickEventArgs)
btnSortInitiatorAscending.attributes("style") = "filter:alpha(opacity=25);"
btnSortInitiatorDescending.attributes("style") = "filter:alpha(opacity=100);"
dim itmColumn as listItem = hiddenSortList.items.findByText("Initiator")
itmColumn.value = "ASC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortInitiatorDescendingClicked(sender as object, e as ImageClickEventArgs)
btnSortInitiatorAscending.attributes("style") = "filter:alpha(opacity=100);"
btnSortInitiatorDescending.attributes("style") = "filter:alpha(opacity=25);"
dim itmColumn as listItem = hiddenSortList.items.findByText("Initiator")
itmColumn.value = "DESC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortEducationConsultantAscendingClicked(sender as object, e as ImageClickEventArgs)
btnSortEducationConsultantAscending.attributes("style") = "filter:alpha(opacity=25);"
btnSortEducationConsultantDescending.attributes("style") = "filter:alpha(opacity=100);"
dim itmColumn as listItem = hiddenSortList.items.findByText("EducationConsultant")
itmColumn.value = "ASC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortEducationConsultantDescendingClicked(sender as object, e as ImageClickEventArgs)
btnSortEducationConsultantAscending.attributes("style") = "filter:alpha(opacity=100);"
btnSortEducationConsultantDescending.attributes("style") = "filter:alpha(opacity=25);"
dim itmColumn as listItem = hiddenSortList.items.findByText("EducationConsultant")
itmColumn.value = "DESC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortProjectCoordinatorAscendingClicked(sender as object, e as ImageClickEventArgs)
btnSortProjectCoordinatorAscending.attributes("style") = "filter:alpha(opacity=25);"
btnSortProjectCoordinatorDescending.attributes("style") = "filter:alpha(opacity=100);"
dim itmColumn as listItem = hiddenSortList.items.findByText("ProjectCoordinator")
itmColumn.value = "ASC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortProjectCoordinatorDescendingClicked(sender as object, e as ImageClickEventArgs)
btnSortProjectCoordinatorAscending.attributes("style") = "filter:alpha(opacity=100);"
btnSortProjectCoordinatorDescending.attributes("style") = "filter:alpha(opacity=25);"
dim itmColumn as listItem = hiddenSortList.items.findByText("ProjectCoordinator")
itmColumn.value = "DESC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortPoseidonNumberAscendingClicked(sender as object, e as ImageClickEventArgs)
btnSortPoseidonNumberAscending.attributes("style") = "filter:alpha(opacity=25);"
btnSortPoseidonNumberDescending.attributes("style") = "filter:alpha(opacity=100);"
dim itmColumn as listItem = hiddenSortList.items.findByText("PoseidonNumber")
itmColumn.value = "ASC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortPoseidonNumberDescendingClicked(sender as object, e as ImageClickEventArgs)
btnSortPoseidonNumberAscending.attributes("style") = "filter:alpha(opacity=100);"
btnSortPoseidonNumberDescending.attributes("style") = "filter:alpha(opacity=25);"
dim itmColumn as listItem = hiddenSortList.items.findByText("PoseidonNumber")
itmColumn.value = "DESC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortIsRushAscendingClicked(sender as object, e as ImageClickEventArgs)
btnSortIsRushAscending.attributes("style") = "filter:alpha(opacity=25);"
btnSortIsRushDescending.attributes("style") = "filter:alpha(opacity=100);"
dim itmColumn as listItem = hiddenSortList.items.findByText("IsRush")
itmColumn.value = "ASC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortIsRushDescendingClicked(sender as object, e as ImageClickEventArgs)
btnSortIsRushAscending.attributes("style") = "filter:alpha(opacity=100);"
btnSortIsRushDescending.attributes("style") = "filter:alpha(opacity=25);"
dim itmColumn as listItem = hiddenSortList.items.findByText("IsRush")
itmColumn.value = "DESC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortURLAscendingClicked(sender as object, e as ImageClickEventArgs)
btnSortURLAscending.attributes("style") = "filter:alpha(opacity=25);"
btnSortURLDescending.attributes("style") = "filter:alpha(opacity=100);"
dim itmColumn as listItem = hiddenSortList.items.findByText("URL")
itmColumn.value = "ASC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
end sub
sub onSortURLDescendingClicked(sender as object, e as ImageClickEventArgs)
btnSortURLAscending.attributes("style") = "filter:alpha(opacity=100);"
btnSortURLDescending.attributes("style") = "filter:alpha(opacity=25);"
dim itmColumn as listItem = hiddenSortList.items.findByText("URL")
itmColumn.value = "DESC"
hiddenSortList.items.remove(itmColumn)
hiddenSortList.items.insert(0, itmColumn)
LoadProjects()
End Sub
Sub onSortIsLinkedToVPEXAscendingClicked(ByVal sender As Object, ByVal e As ImageClickEventArgs)
btnSortIsLinkedToVPEXAscending.Attributes("style") = "filter:alpha(opacity=25);"
btnSortIsLinkedToVPEXDescending.Attributes("style") = "filter:alpha(opacity=100);"
Dim itmColumn As ListItem = hiddenSortList.Items.FindByText("IsLinkedToVPEX")
itmColumn.Value = "ASC"
hiddenSortList.Items.Remove(itmColumn)
hiddenSortList.Items.Insert(0, itmColumn)
LoadProjects()
End Sub
Sub onSortIsLinkedToVPEXDescendingClicked(ByVal sender As Object, ByVal e As ImageClickEventArgs)
btnSortIsLinkedToVPEXAscending.Attributes("style") = "filter:alpha(opacity=100);"
btnSortIsLinkedToVPEXDescending.Attributes("style") = "filter:alpha(opacity=25);"
Dim itmColumn As ListItem = hiddenSortList.Items.FindByText("IsLinkedToVPEX")
itmColumn.Value = "DESC"
hiddenSortList.Items.Remove(itmColumn)
hiddenSortList.Items.Insert(0, itmColumn)
LoadProjects()
End Sub
Public Function GetProjects(ByVal strProjectID As String, _
ByVal strProjectType As String, _
ByVal strDesignerName As String, _
ByVal strGroupMemberID As String, _
ByVal strDeliveryYear As String, _
ByVal strMarketSegment As String, _
ByVal strClient As String, _
ByVal strStatus As String, _
ByVal strEducationConsultant As String, _
ByVal strInitiator As String, _
ByVal strIsRush As String, _
ByVal strIsLinkedToVPEX As String, _
ByVal ddlSortList As DropDownList) As DataSet
Dim strSQL As String = "SELECT P.*, C.ClientName AS ClientName, M.Name as MarketSegment FROM Projects AS P, Clients AS C, MarketSegments AS M" & IIf(strGroupMemberID <> "", ", ProjectResources AS PR, GroupMembers AS GM", "") & " WHERE C.ClientID = P.ClientID AND C.MarketSegmentID = M.MarketSegmentID AND " & IIf(strGroupMemberID <> "", "P.ProjectID = PR.ProjectID AND PR.GroupMemberID = GM.GroupMemberID AND ", "")
If strProjectID <> "" Then
If IsNumeric(strProjectID) Then
strSQL += "P.ProjectID = " & strProjectID.Replace("""", "'").Replace("'", "''") & " AND "
End If
End If
If strProjectType <> "" Then
strSQL += "ProjectType='" & strProjectType.Replace("""", "'").Replace("'", "''") & "' AND "
End If
If strDesignerName <> "" Then
strSQL += "Designer LIKE '%" & strDesignerName.Replace("""", "'").Replace("'", "''") & "%' AND "
End If
If strGroupMemberID <> "" Then
strSQL += "GM.GroupMemberID = " & strGroupMemberID & " AND "
End If
If strDeliveryYear <> "" Then
If IsNumeric(strDeliveryYear) Then
strSQL += "YEAR(DeliveryDate) = " & strDeliveryYear.Replace("""", "'").Replace("'", "''") & " AND "
End If
End If
If strMarketSegment <> "" Then
strSQL += "MarketSegment='" & strMarketSegment.Replace("""", "'").Replace("'", "''") & "' AND "
End If
If strClient <> "" Then
strSQL += "ClientName='" & strClient.Replace("""", "'").Replace("'", "''") & "' AND "
End If
If strEducationConsultant <> "" Then
strSQL += "EducationConsultant='" & strEducationConsultant.Replace("""", "'").Replace("'", "''") & "' AND "
End If
If strInitiator <> "" Then
strSQL += "Initiator='" & strInitiator.Replace("""", "'").Replace("'", "''") & "' AND "
End If
If strStatus <> "" Then
If strStatus <> "Not Completed" Then
strSQL += "Status='" & strStatus.Replace("""", "'").Replace("'", "''") & "' AND "
Else
strSQL += "Status <> 'Completed' AND "
End If
End If
If strIsRush <> "" Then
strSQL += "IsRush=" & strIsRush & " AND "
End If
If strIsLinkedToVPEX <> "" Then
strSQL += "IsLinkedToVPEX=" & strIsLinkedToVPEX & " AND "
End If
If strSQL.EndsWith(" AND ") Then
Dim intLastIndexOfAND As Integer = strSQL.LastIndexOf(" AND ")
strSQL = strSQL.Substring(0, intLastIndexOfAND + 1)
ElseIf strSQL.EndsWith(" WHERE ") Then
Dim intLastIndexOfWHERE As Integer = strSQL.LastIndexOf(" WHERE ")
strSQL = strSQL.Substring(0, intLastIndexOfWHERE + 1)
End If
If ddlSortList.Items.Count > 0 Then
strSQL += " ORDER BY "
Dim itmColumnName As ListItem
'for each itmColumnName in ddlSortList.items
' strSQL += itmColumnName.text & " " & itmColumnName.value & ", "
'next
'strSQL += ddlSortList.Items(0).Text & " " & ddlSortList.Items(0).Value & ", "
strSQL += ddlSortList.SelectedItem.Text & ", "
If strSQL.EndsWith(", ") Then
Dim intLastIndexOfComma As Integer = strSQL.LastIndexOf(", ")
strSQL = strSQL.Substring(0, intLastIndexOfComma)
End If
End If
Dim strDataSource As String = Server.MapPath("~/projects/db/db.mdb")
Return GetDataSetFromDB(strSQL, strDataSource, "Projects")
End Function
public function GetClientValueByID(strColumnName as string, intClientID as integer) as string
dim strSQL as string = "SELECT " & strColumnName & " FROM Clients WHERE ClientID=" & intClientID
dim strDataSource as string = server.MapPath("~/projects/db/db.mdb")
return GetSingleValueFromDB(strSQL, strDataSource, "Clients", strColumnName)
end function
function FormatClientName(strClientID as string) as string
if IsNumeric(strClientID) then
return GetClientValueByID("ClientName", strClientID)
end if
end function
function FormatRowColor(strStatus as string) as string
if strStatus.tolower() = "not started" then
return "#fdf8af" 'yellow
else if strStatus.tolower() = "in progress" then
return "#b0fdaf" 'green
else if strStatus.tolower() = "completed" then
return "#affdfc" 'blue
else if strStatus.tolower() = "stopped" then
return "#fdafaf" 'red
else
return "#ffffff"
end if
end function
function FormatDatabaseDate(strDatabaseDate as string) as string
if IsDate(strDatabaseDate) then
return CDate(strDatabaseDate).toshortdatestring()
else
return ""
end if
end function
function FornmatRush(bolIsRush as boolean) as string
if bolIsRush then
'return "<span style='font-weight:bold; font-size:10pt; color:red;'>Yes</span>"
return "<img src='images/icons/rushIcon.gif' title='Rush Job!!!' height='15'>"
else
'return "No"
return ""
end if
end function
function FormatURL(strURL as string)
if strURL <> "" then
if strURL.startsWith("http://") or strURL.startsWith("https://") then
return "<a href='" & strURL & "' target='" & strURL & "' title='launch site'><img src='images/icons/ie.gif' border='0'></a>"
else
return "<a href='http://" & strURL & "' target='http://" & strURL & "' title='launch site'><img src='images/icons/ie.gif' border='0'></a>"
end if
else
return ""
end if
end function
'======================================================================================
' DB CORE FUNCTIONS
'======================================================================================
private function GetDataSetFromDB(strSQL as string, strDataSource as string, strTableName as string) as dataset
dim strConnection as String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strDataSource
dim objDataSet as new DataSet()
dim objConnection as new oledbconnection(strConnection)
dim objAdapter as new oledbdataadapter(strSQL, objConnection)
objConnection.close()
objAdapter.fill(objDataSet, strTableName)
objAdapter = nothing
return objDataSet
end function
private function GetSingleValueFromDB(strSQL as string, strDataSource as string, strTableName as string, strFieldName as string) as string
dim strConnection as String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strDataSource
dim objDataSet as new DataSet()
dim objConnection as new oledbconnection(strConnection)
dim objAdapter as new oledbdataadapter(strSQL, objConnection)
objConnection.close()
objAdapter.fill(objDataSet, strTableName)
objAdapter = nothing
try
return objDataSet.tables(0).rows(0)(strFieldName)
catch
end try
end function
private sub ExecuteDB(strSQL as string, strDataSource as string)
dim strConnection as String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strDataSource
dim objConnection = new oledbconnection(strConnection)
objConnection.open()
dim objCommand as oledbcommand
objCommand = new oledbcommand(strSQL, objConnection)
objCommand.executenonquery()
objConnection.close()
end sub
</script>
<asp:label id="hiddenProjectID" visible="false" runat="server"/>
<asp:label id="hiddenProjectType" visible="false" runat="server"/>
<asp:label id="hiddenDesigner" visible="false" runat="server"/>
<asp:label id="hiddenGroupMemberID" visible="false" runat="server"/>
<asp:label id="hiddenDeliveryYear" visible="false" runat="server"/>
<asp:label id="hiddenMarketSegment" visible="false" runat="server"/>
<asp:label id="hiddenClient" visible="false" runat="server"/>
<asp:label id="hiddenProjectStatus" visible="false" runat="server"/>
<asp:label id="hiddenEducationConsultant" visible="false" runat="server"/>
<asp:label id="hiddenInitiator" visible="false" runat="server"/>
<asp:label id="hiddenIsRush" visible="false" runat="server"/>
<asp:label id="hiddenIsLinkedToVPEX" visible="false" runat="server"/>
<asp:dropdownlist id="hiddenSortList"
visible="false"
runat="server"/>
<table cellpadding="3" cellspacing="1" border="0" width="100%">
<tr bgcolor="#cccccc">
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;"
valign="bottom"
align="right">
<img src="images/icons/folder.gif" border="0" title="Denotes projects with local network folders entered">
</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;"
valign="bottom"
align="right">
<table cellpadding="0" cellspacing="0" width="100%">
<tr>
<td style="font-weight:bold;" width="100%">
<img src="images/icons/ie.gif" border="0" title="Denotes projects with urls entered">
</td>
<td>
<table cellpadding="1" cellspacing="0" width="100%" height="100%">
<tr>
<td valign="top">
<asp:imagebutton id="btnSortURLDescending"
onclick="onSortURLDescendingClicked"
imageURL="../images/icons/arrowUp.gif"
title="Sort Descending"
runat="server"/>
</td>
</tr>
<tr>
<td valign="bottom">
<asp:imagebutton id="btnSortURLAscending"
onclick="onSortURLAscendingClicked"
imageURL="../images/icons/arrowDown.gif"
title="Sort Ascending"
style="filter:alpha(opacity=25);"
runat="server"/>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;"
valign="bottom"
align="right">
<table cellpadding="0" cellspacing="0" width="100%">
<tr>
<td style="font-weight:bold;" width="100%">
Project ID
</td>
<td>
<table cellpadding="1" cellspacing="0" width="100%" height="100%">
<tr>
<td valign="top">
<asp:imagebutton id="btnSortProjectIDDescending"
onclick="onSortProjectIDDescendingClicked"
imageURL="../images/icons/arrowUp.gif"
title="Sort Descending"
runat="server"/>
</td>
</tr>
<tr>
<td valign="bottom">
<asp:imagebutton id="btnSortProjectIDAscending"
onclick="onSortProjectIDAscendingClicked"
imageURL="../images/icons/arrowDown.gif"
title="Sort Ascending"
style="filter:alpha(opacity=25);"
runat="server"/>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;"
valign="bottom">
<table cellpadding="0" cellspacing="0" width="100%">
<tr>
<td style="font-weight:bold;" width="100%">
Market Segment
</td>
<td>
<table cellpadding="1" cellspacing="0" width="100%" height="100%">
<tr>
<td valign="top">
<asp:imagebutton id="btnSortMarketSegmentDescending"
onclick="onSortMarketSegmentDescendingClicked"
imageURL="../images/icons/arrowUp.gif"
title="Sort Descending"
runat="server"/>
</td>
</tr>
<tr>
<td valign="bottom">
<asp:imagebutton id="btnSortMarketSegmentAscending"
onclick="onSortMarketSegmentAscendingClicked"
imageURL="../images/icons/arrowDown.gif"
title="Sort Ascending"
style="filter:alpha(opacity=25);"
runat="server"/>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;"
valign="bottom">
<table cellpadding="0" cellspacing="0" width="100%">
<tr>
<td style="font-weight:bold;" width="100%">
Is Linked to VPEX
</td>
<td>
<table cellpadding="1" cellspacing="0" width="100%" height="100%">
<tr>
<td valign="top">
<asp:imagebutton id="btnSortIsLinkedToVPEXDescending"
onclick="onSortIsLinkedToVPEXDescendingClicked"
imageURL="../images/icons/arrowUp.gif"
title="Sort Descending"
runat="server"/>
</td>
</tr>
<tr>
<td valign="bottom">
<asp:imagebutton id="btnSortIsLinkedToVPEXAscending"
onclick="onSortIsLinkedToVPEXAscendingClicked"
imageURL="../images/icons/arrowDown.gif"
title="Sort Ascending"
style="filter:alpha(opacity=25);"
runat="server"/>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;"
valign="bottom">
<table cellpadding="0" cellspacing="0" width="100%">
<tr>
<td style="font-weight:bold;" width="100%">
Client
</td>
<td>
<table cellpadding="1" cellspacing="0" width="100%" height="100%">
<tr>
<td valign="top">
<asp:imagebutton id="btnSortClientNameDescending"
onclick="onSortClientNameDescendingClicked"
imageURL="../images/icons/arrowUp.gif"
title="Sort Descending"
runat="server"/>
</td>
</tr>
<tr>
<td valign="bottom">
<asp:imagebutton id="btnSortClientNameAscending"
onclick="onSortClientNameAscendingClicked"
imageURL="../images/icons/arrowDown.gif"
title="Sort Ascending"
style="filter:alpha(opacity=25);"
runat="server"/>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;"
valign="bottom">
<table cellpadding="0" cellspacing="0" width="100%">
<tr>
<td style="font-weight:bold;" width="100%">
<img src="images/icons/rushIcon.gif" height="15" title="Indicates Rush Jobs">
</td>
<td>
<table cellpadding="1" cellspacing="0" width="100%" height="100%">
<tr>
<td valign="top">
<asp:imagebutton id="btnSortIsRushDescending"
onclick="onSortIsRushDescendingClicked"
imageURL="../images/icons/arrowUp.gif"
title="Sort Descending"
runat="server"/>
</td>
</tr>
<tr>
<td valign="bottom">
<asp:imagebutton id="btnSortIsRushAscending"
onclick="onSortIsRushAscendingClicked"
imageURL="../images/icons/arrowDown.gif"
title="Sort Ascending"
style="filter:alpha(opacity=25);"
runat="server"/>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;"
valign="bottom">
<table cellpadding="0" cellspacing="0" width="100%">
<tr>
<td style="font-weight:bold;" width="100%">
Project Title
</td>
<td>
<table cellpadding="1" cellspacing="0" width="100%" height="100%">
<tr>
<td valign="top">
<asp:imagebutton id="btnSortProjectTitleDescending"
onclick="onSortProjectTitleDescendingClicked"
imageURL="../images/icons/arrowUp.gif"
title="Sort Descending"
runat="server"/>
</td>
</tr>
<tr>
<td valign="bottom">
<asp:imagebutton id="btnSortProjectTitleAscending"
onclick="onSortProjectTitleAscendingClicked"
imageURL="../images/icons/arrowDown.gif"
title="Sort Ascending"
style="filter:alpha(opacity=25);"
runat="server"/>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;"
valign="bottom">
<table cellpadding="0" cellspacing="0" width="100%">
<tr>
<td style="font-weight:bold;" width="100%">
Project Type
</td>
<td>
<table cellpadding="1" cellspacing="0" width="100%" height="100%">
<tr>
<td valign="top">
<asp:imagebutton id="btnSortProjectTypeDescending"
onclick="onSortProjectTypeDescendingClicked"
imageURL="../images/icons/arrowUp.gif"
title="Sort Descending"
runat="server"/>
</td>
</tr>
<tr>
<td valign="bottom">
<asp:imagebutton id="btnSortProjectTypeAscending"
onclick="onSortProjectTypeAscendingClicked"
imageURL="../images/icons/arrowDown.gif"
title="Sort Ascending"
style="filter:alpha(opacity=25);"
runat="server"/>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;"
valign="bottom">
<table cellpadding="0" cellspacing="0" width="100%">
<tr>
<td style="font-weight:bold;" width="100%">
Designer
</td>
<td>
<table cellpadding="1" cellspacing="0" width="100%" height="100%">
<tr>
<td valign="top">
<asp:imagebutton id="btnSortDesignerDescending"
onclick="onSortDesignerDescendingClicked"
imageURL="../images/icons/arrowUp.gif"
title="Sort Descending"
runat="server"/>
</td>
</tr>
<tr>
<td valign="bottom">
<asp:imagebutton id="btnSortDesignerAscending"
onclick="onSortDesignerAscendingClicked"
imageURL="../images/icons/arrowDown.gif"
title="Sort Ascending"
style="filter:alpha(opacity=25);"
runat="server"/>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;"
valign="bottom">
<table cellpadding="0" cellspacing="0" width="100%">
<tr>
<td style="font-weight:bold;" width="100%">
Date Received
</td>
<td>
<table cellpadding="1" cellspacing="0" width="100%" height="100%">
<tr>
<td valign="top">
<asp:imagebutton id="btnSortDateReceivedDescending"
onclick="onSortDateReceivedDescendingClicked"
imageURL="../images/icons/arrowUp.gif"
title="Sort Descending"
runat="server"/>
</td>
</tr>
<tr>
<td valign="bottom">
<asp:imagebutton id="btnSortDateReceivedAscending"
onclick="onSortDateReceivedAscendingClicked"
imageURL="../images/icons/arrowDown.gif"
title="Sort Ascending"
style="filter:alpha(opacity=25);"
runat="server"/>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;"
valign="bottom">
<table cellpadding="0" cellspacing="0" width="100%">
<tr>
<td style="font-weight:bold;" width="100%">
Delivery Date
</td>
<td>
<table cellpadding="1" cellspacing="0" width="100%" height="100%">
<tr>
<td valign="top">
<asp:imagebutton id="btnSortDeliveryDateDescending"
onclick="onSortDeliveryDateDescendingClicked"
imageURL="../images/icons/arrowUp.gif"
title="Sort Descending"
runat="server"/>
</td>
</tr>
<tr>
<td valign="bottom">
<asp:imagebutton id="btnSortDeliveryDateAscending"
onclick="onSortDeliveryDateAscendingClicked"
imageURL="../images/icons/arrowDown.gif"
title="Sort Ascending"
style="filter:alpha(opacity=25);"
runat="server"/>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;"
valign="bottom">
<table cellpadding="0" cellspacing="0" width="100%">
<tr>
<td style="font-weight:bold;" width="100%">
Last Updated
</td>
<td>
<table cellpadding="1" cellspacing="0" width="100%" height="100%">
<tr>
<td valign="top">
<asp:imagebutton id="btnSortLastUpdatedDescending"
onclick="onSortLastUpdatedDescendingClicked"
imageURL="../images/icons/arrowUp.gif"
title="Sort Descending"
runat="server"/>
</td>
</tr>
<tr>
<td valign="bottom">
<asp:imagebutton id="btnSortLastUpdatedAscending"
onclick="onSortLastUpdatedAscendingClicked"
imageURL="../images/icons/arrowDown.gif"
title="Sort Ascending"
style="filter:alpha(opacity=25);"
runat="server"/>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;"
valign="bottom">
<table cellpadding="0" cellspacing="0" width="100%">
<tr>
<td style="font-weight:bold;" width="100%">
Status
</td>
<td>
<table cellpadding="1" cellspacing="0" width="100%" height="100%">
<tr>
<td valign="top">
<asp:imagebutton id="btnSortStatusDescending"
onclick="onSortStatusDescendingClicked"
imageURL="../images/icons/arrowUp.gif"
title="Sort Descending"
runat="server"/>
</td>
</tr>
<tr>
<td valign="bottom">
<asp:imagebutton id="btnSortStatusAscending"
onclick="onSortStatusAscendingClicked"
imageURL="../images/icons/arrowDown.gif"
title="Sort Ascending"
style="filter:alpha(opacity=25);"
runat="server"/>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;"
valign="bottom">
<table cellpadding="0" cellspacing="0" width="100%">
<tr>
<td style="font-weight:bold;" width="100%">
Initiator
</td>
<td>
<table cellpadding="1" cellspacing="0" width="100%" height="100%">
<tr>
<td valign="top">
<asp:imagebutton id="btnSortInitiatorDescending"
onclick="onSortInitiatorDescendingClicked"
imageURL="../images/icons/arrowUp.gif"
title="Sort Descending"
runat="server"/>
</td>
</tr>
<tr>
<td valign="bottom">
<asp:imagebutton id="btnSortInitiatorAscending"
onclick="onSortInitiatorAscendingClicked"
imageURL="../images/icons/arrowDown.gif"
title="Sort Ascending"
style="filter:alpha(opacity=25);"
runat="server"/>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;"
valign="bottom">
<table cellpadding="0" cellspacing="0" width="100%">
<tr>
<td style="font-weight:bold;" width="100%">
Education Consultant
</td>
<td>
<table cellpadding="1" cellspacing="0" width="100%" height="100%">
<tr>
<td valign="top">
<asp:imagebutton id="btnSortEducationConsultantDescending"
onclick="onSortEducationConsultantDescendingClicked"
imageURL="../images/icons/arrowUp.gif"
title="Sort Descending"
runat="server"/>
</td>
</tr>
<tr>
<td valign="bottom">
<asp:imagebutton id="btnSortEducationConsultantAscending"
onclick="onSortEducationConsultantAscendingClicked"
imageURL="../images/icons/arrowDown.gif"
title="Sort Ascending"
style="filter:alpha(opacity=25);"
runat="server"/>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;"
valign="bottom">
<table cellpadding="0" cellspacing="0" width="100%">
<tr>
<td style="font-weight:bold;" width="100%">
Project Coordinator
</td>
<td>
<table cellpadding="1" cellspacing="0" width="100%" height="100%">
<tr>
<td valign="top">
<asp:imagebutton id="btnSortProjectCoordinatorDescending"
onclick="onSortProjectCoordinatorDescendingClicked"
imageURL="../images/icons/arrowUp.gif"
title="Sort Descending"
runat="server"/>
</td>
</tr>
<tr>
<td valign="bottom">
<asp:imagebutton id="btnSortProjectCoordinatorAscending"
onclick="onSortProjectCoordinatorAscendingClicked"
imageURL="../images/icons/arrowDown.gif"
title="Sort Ascending"
style="filter:alpha(opacity=25);"
runat="server"/>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;"
valign="bottom">
<table cellpadding="0" cellspacing="0" width="100%">
<tr>
<td style="font-weight:bold;" width="100%">
Poseidon Number
</td>
<td>
<table cellpadding="1" cellspacing="0" width="100%" height="100%">
<tr>
<td valign="top">
<asp:imagebutton id="btnSortPoseidonNumberDescending"
onclick="onSortPoseidonNumberDescendingClicked"
imageURL="../images/icons/arrowUp.gif"
title="Sort Descending"
runat="server"/>
</td>
</tr>
<tr>
<td valign="bottom">
<asp:imagebutton id="btnSortPoseidonNumberAscending"
onclick="onSortPoseidonNumberAscendingClicked"
imageURL="../images/icons/arrowDown.gif"
title="Sort Ascending"
style="filter:alpha(opacity=25);"
runat="server"/>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
<!--
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;"
valign="bottom">
Initiator
</td>
-->
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;"
valign="bottom">
</td>
</tr>
<asp:repeater id="rptProjects"
runat="server">
<itemtemplate>
<asp:label id="hiddenProjectID"
text='<%# DataBinder.Eval(Container.DataItem, "ProjectID").toString %>'
visible="false"
runat="server"/>
<tr bgcolor="<%# FormatRowColor(DataBinder.Eval(Container.DataItem, "Status").toString)%>">
<td valign="top" style="font-family:arial;" align="right"><%# IIF(DataBinder.Eval(Container.DataItem, "FolderLocation").toString <> "", "<a href='" & DataBinder.Eval(Container.DataItem, "FolderLocation").toString & "' target='" & DataBinder.Eval(Container.DataItem, "FolderLocation").toString & "' title='open folder'><img src='images/icons/folder.gif' border='0'></a>", "") %></td>
<td valign="top" style="font-family:arial;" align="right"><%# FormatURL(DataBinder.Eval(Container.DataItem, "URL").toString) %></td>
<td valign="top" style="font-family:arial;" align="right"><%# DataBinder.Eval(Container.DataItem, "ProjectID").toString %></td>
<td valign="top" style="font-family:arial;"><%# DataBinder.Eval(Container.DataItem, "MarketSegment").toString %></td>
<td valign="top" style="font-family:arial;"><%# DataBinder.Eval(Container.DataItem, "IsLinkedToVPEX").toString %></td>
<td valign="top" style="font-family:arial; font-size:8pt;"><b><%# DataBinder.Eval(Container.DataItem, "ClientName").toString %></b></td>
<td valign="top" style="font-family:arial;" align="center"><%# FornmatRush(DataBinder.Eval(Container.DataItem, "IsRush").toString) %></td>
<td valign="top"
title='<%# DataBinder.Eval(Container.DataItem, "Description").toString.replace("'", "''") %>'>
<asp:linkbutton
text='<%# IIF(DataBinder.Eval(Container.DataItem, "ProjectTitle").toString = "", "[Blank]", DataBinder.Eval(Container.DataItem, "ProjectTitle").toString) %>'
onclick="onProjectTitleClicked"
style="font-weight:bold; font-size:8pt;"
runat="server"/>
</td>
<td valign="top" style="font-family:arial;"><%# DataBinder.Eval(Container.DataItem, "ProjectType").toString %></td>
<td valign="top" style="font-family:arial;"><%# DataBinder.Eval(Container.DataItem, "Designer").toString %></td>
<td valign="top" style="font-family:arial;"><%# FormatDatabaseDate(DataBinder.Eval(Container.DataItem, "DateReceived").toString) %></td>
<td valign="top" style="font-family:arial;"><%# FormatDatabaseDate(DataBinder.Eval(Container.DataItem, "DeliveryDate").toString) %></td>
<td valign="top" style="font-family:arial;"><%# FormatDatabaseDate(DataBinder.Eval(Container.DataItem, "LastUpdated").toString) %></td>
<td valign="top" style="font-family:arial;"><%# DataBinder.Eval(Container.DataItem, "Status").toString %></td>
<td valign="top" style="font-family:arial;"><%# DataBinder.Eval(Container.DataItem, "Initiator").toString %></td>
<td valign="top" style="font-family:arial;"><%# DataBinder.Eval(Container.DataItem, "EducationConsultant").toString %></td>
<td valign="top" style="font-family:arial;"><%# DataBinder.Eval(Container.DataItem, "ProjectCoordinator").toString %></td>
<td valign="top" style="font-family:arial;"><%# DataBinder.Eval(Container.DataItem, "PoseidonNumber").toString %></td>
<!--
<td valign="top"><%# DataBinder.Eval(Container.DataItem, "Initiator").toString %></td>
-->
<td valign="top" nowrap>
<asp:button text="edit"
onclick="onEditClicked"
style="border-width:1pt; border-color:#000000; background-color:#cccccc; border-style:solid; color:#000000;"
runat="server"/>
<asp:button text="delete"
onclick="onDeleteClicked"
style="border-width:1pt; border-color:#000000; background-color:#cccccc; border-style:solid; color:#000000;"
runat="server"/>
</td>
</tr>
</itemtemplate>
</asp:repeater>
<tr bgcolor="#cccccc">
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;" valign="top" align="center"><img src="images/icons/folder.gif" border="0" title="Denotes projects with local network folders entered"></td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;" valign="top" align="center"><img src="images/icons/ie.gif" border="0" title="Denotes projects with urls entered"></td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;" valign="top" align="right">Project ID</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;" valign="top">Market Segment</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;" valign="top">Is Linked to VPEX</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;" valign="top">Client</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;" valign="top" align="center"><img src="images/icons/rushIcon.gif" height="15" title="Indicates Rush Jobs"></td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;" valign="top">Project Title</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;" valign="top">Project Type</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;" valign="top">Designer</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;" valign="top">Date Received</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;" valign="top">Delivery Date</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;" valign="top">Last Updated</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;" valign="top">Status</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;" valign="top">Initiator</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;" valign="top">Education Consultant</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;" valign="top">Project Coordinator</td>
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;" valign="top">Poseidon Number</td>
<!--
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;" valign="top">Initiator</td>
-->
<td style="border-width:1pt; border-color:#aaaaaa; border-style:solid; font-weight:bold;" valign="top"> </td>
</tr>
</table>
Can you switch the lines
in GetDataSetFromDB and GetSingleValueFromDB
objConnection.close()
objAdapter.fill(objDataSet
to
objAdapter.fill(objDataSet
objConnection.close()
To show the sqlString create into the ascx file a textbox and then
assign on the executesDB function the strSQL value to the textbox.
in GetDataSetFromDB and GetSingleValueFromDB
objConnection.close()
objAdapter.fill(objDataSet
to
objAdapter.fill(objDataSet
objConnection.close()
To show the sqlString create into the ascx file a textbox and then
assign on the executesDB function the strSQL value to the textbox.
ASKER
Hold on I will try..
ASKER
When I make the changes it does not return anything ? Any other ideas?
ASKER
Can you show me how I would do what you are asking - it is confusing as to which sql string you would like to see...
When it does not return anything, is the database filled with data which match your filter critea or does it return everytime nothing ?
i would like to see the sqlStrings from your 3 core DB functions.
Step 1: add a textbox to your control give the control the name "debugSQL"
Step 2: add in the first line of your 3 core DB functions debugSQL.Text = strSQL
i would like to see the sqlStrings from your 3 core DB functions.
Step 1: add a textbox to your control give the control the name "debugSQL"
Step 2: add in the first line of your 3 core DB functions debugSQL.Text = strSQL
ASKER
Ok - I am just not sure of how to prevent the error from happening - I wil attempt to do this
ASKER
I went to debug it in Visual Studio, and there was an error
The provider could not determine the Object value. For example, the row was just created, the default for the Object column was not available, and the consumer had not yet set a new Object value.
private function GetDataSetFromDB(strSQL as string, strDataSource as string, strTableName as string) as dataset
dim strConnection as String = "Provider=Microsoft.Jet.OL
dim objDataSet as new DataSet()
dim objConnection as new oledbconnection(strConnect
dim objAdapter as new oledbdataadapter(strSQL, objConnection)
objAdapter.fill(objDataSet
objConnection.close()
objAdapter = nothing
return objDataSet
end function
The provider could not determine the Object value. For example, the row was just created, the default for the Object column was not available, and the consumer had not yet set a new Object value.
private function GetDataSetFromDB(strSQL as string, strDataSource as string, strTableName as string) as dataset
dim strConnection as String = "Provider=Microsoft.Jet.OL
dim objDataSet as new DataSet()
dim objConnection as new oledbconnection(strConnect
dim objAdapter as new oledbdataadapter(strSQL, objConnection)
objAdapter.fill(objDataSet
objConnection.close()
objAdapter = nothing
return objDataSet
end function
ASKER
On "ProjectsDetailedView", where the "ProjectSearch" goes to on the button click,
I added the text box to the function
Public Function GetProjects(ByVal strProjectID As String, _
ByVal strProjectType As String, _
ByVal strDesignerName As String, _
ByVal strGroupMemberID As String, _
ByVal strDeliveryYear As String, _
ByVal strMarketSegment As String, _
ByVal strClient As String, _
ByVal strStatus As String, _
ByVal strEducationConsultant As String, _
ByVal strInitiator As String, _
ByVal strIsRush As String, _
ByVal strIsLinkedToVPEX As String, _
ByVal ddlSortList As DropDownList) As DataSet
Dim strSQL As String = "SELECT P.*, C.ClientName AS ClientName, M.Name as MarketSegment FROM Projects AS P, Clients AS C, MarketSegments AS M" & IIf(strGroupMemberID <> "", ", ProjectResources AS PR, GroupMembers AS GM", "") & " WHERE C.ClientID = P.ClientID AND C.MarketSegmentID = M.MarketSegmentID AND " & IIf(strGroupMemberID <> "", "P.ProjectID = PR.ProjectID AND PR.GroupMemberID = GM.GroupMemberID AND ", "")
If strProjectID <> "" Then
If IsNumeric(strProjectID) Then
strSQL += "P.ProjectID = " & strProjectID.Replace("""",
End If
End If
If strProjectType <> "" Then
strSQL += "ProjectType='" & strProjectType.Replace("""
End If
If strDesignerName <> "" Then
strSQL += "Designer LIKE '%" & strDesignerName.Replace(""
End If
If strGroupMemberID <> "" Then
strSQL += "GM.GroupMemberID = " & strGroupMemberID & " AND "
End If
If strDeliveryYear <> "" Then
If IsNumeric(strDeliveryYear)
strSQL += "YEAR(DeliveryDate) = " & strDeliveryYear.Replace(""
End If
End If
If strMarketSegment <> "" Then
strSQL += "MarketSegment='" & strMarketSegment.Replace("
End If
If strClient <> "" Then
strSQL += "ClientName='" & strClient.Replace("""", "'").Replace("'", "''") & "' AND "
End If
If strEducationConsultant <> "" Then
strSQL += "EducationConsultant='" & strEducationConsultant.Rep
End If
If strInitiator <> "" Then
strSQL += "Initiator='" & strInitiator.Replace("""",
End If
If strStatus <> "" Then
If strStatus <> "Not Completed" Then
strSQL += "Status='" & strStatus.Replace("""", "'").Replace("'", "''") & "' AND "
Else
strSQL += "Status <> 'Completed' AND "
End If
End If
If strIsRush <> "" Then
strSQL += "IsRush=" & strIsRush & " AND "
End If
If strIsLinkedToVPEX <> "" Then
strSQL += "IsLinkedToVPEX=" & strIsLinkedToVPEX & " AND "
End If
If strSQL.EndsWith(" AND ") Then
Dim intLastIndexOfAND As Integer = strSQL.LastIndexOf(" AND ")
strSQL = strSQL.Substring(0, intLastIndexOfAND + 1)
ElseIf strSQL.EndsWith(" WHERE ") Then
Dim intLastIndexOfWHERE As Integer = strSQL.LastIndexOf(" WHERE ")
strSQL = strSQL.Substring(0, intLastIndexOfWHERE + 1)
End If
If ddlSortList.Items.Count > 0 Then
strSQL += " ORDER BY "
Dim itmColumnName As ListItem
'for each itmColumnName in ddlSortList.items
' strSQL += itmColumnName.text & " " & itmColumnName.value & ", "
'next
'strSQL += ddlSortList.Items(0).Text & " " & ddlSortList.Items(0).Value
strSQL += ddlSortList.SelectedItem.T
If strSQL.EndsWith(", ") Then
Dim intLastIndexOfComma As Integer = strSQL.LastIndexOf(", ")
strSQL = strSQL.Substring(0, intLastIndexOfComma)
End If
End If
Dim strDataSource As String = Server.MapPath("~/projects
Return GetDataSetFromDB(strSQL, strDataSource, "Projects")
debugSQL.Text = strsql
End Function
And this to the form
<asp:textbox id="debugSQL" visible=true runat="server"></asp:textb
No value was returned, and when I try to reolad the page, it gives me the same error message...Thanks for sticking w me
I added the text box to the function
Public Function GetProjects(ByVal strProjectID As String, _
ByVal strProjectType As String, _
ByVal strDesignerName As String, _
ByVal strGroupMemberID As String, _
ByVal strDeliveryYear As String, _
ByVal strMarketSegment As String, _
ByVal strClient As String, _
ByVal strStatus As String, _
ByVal strEducationConsultant As String, _
ByVal strInitiator As String, _
ByVal strIsRush As String, _
ByVal strIsLinkedToVPEX As String, _
ByVal ddlSortList As DropDownList) As DataSet
Dim strSQL As String = "SELECT P.*, C.ClientName AS ClientName, M.Name as MarketSegment FROM Projects AS P, Clients AS C, MarketSegments AS M" & IIf(strGroupMemberID <> "", ", ProjectResources AS PR, GroupMembers AS GM", "") & " WHERE C.ClientID = P.ClientID AND C.MarketSegmentID = M.MarketSegmentID AND " & IIf(strGroupMemberID <> "", "P.ProjectID = PR.ProjectID AND PR.GroupMemberID = GM.GroupMemberID AND ", "")
If strProjectID <> "" Then
If IsNumeric(strProjectID) Then
strSQL += "P.ProjectID = " & strProjectID.Replace("""",
End If
End If
If strProjectType <> "" Then
strSQL += "ProjectType='" & strProjectType.Replace("""
End If
If strDesignerName <> "" Then
strSQL += "Designer LIKE '%" & strDesignerName.Replace(""
End If
If strGroupMemberID <> "" Then
strSQL += "GM.GroupMemberID = " & strGroupMemberID & " AND "
End If
If strDeliveryYear <> "" Then
If IsNumeric(strDeliveryYear)
strSQL += "YEAR(DeliveryDate) = " & strDeliveryYear.Replace(""
End If
End If
If strMarketSegment <> "" Then
strSQL += "MarketSegment='" & strMarketSegment.Replace("
End If
If strClient <> "" Then
strSQL += "ClientName='" & strClient.Replace("""", "'").Replace("'", "''") & "' AND "
End If
If strEducationConsultant <> "" Then
strSQL += "EducationConsultant='" & strEducationConsultant.Rep
End If
If strInitiator <> "" Then
strSQL += "Initiator='" & strInitiator.Replace("""",
End If
If strStatus <> "" Then
If strStatus <> "Not Completed" Then
strSQL += "Status='" & strStatus.Replace("""", "'").Replace("'", "''") & "' AND "
Else
strSQL += "Status <> 'Completed' AND "
End If
End If
If strIsRush <> "" Then
strSQL += "IsRush=" & strIsRush & " AND "
End If
If strIsLinkedToVPEX <> "" Then
strSQL += "IsLinkedToVPEX=" & strIsLinkedToVPEX & " AND "
End If
If strSQL.EndsWith(" AND ") Then
Dim intLastIndexOfAND As Integer = strSQL.LastIndexOf(" AND ")
strSQL = strSQL.Substring(0, intLastIndexOfAND + 1)
ElseIf strSQL.EndsWith(" WHERE ") Then
Dim intLastIndexOfWHERE As Integer = strSQL.LastIndexOf(" WHERE ")
strSQL = strSQL.Substring(0, intLastIndexOfWHERE + 1)
End If
If ddlSortList.Items.Count > 0 Then
strSQL += " ORDER BY "
Dim itmColumnName As ListItem
'for each itmColumnName in ddlSortList.items
' strSQL += itmColumnName.text & " " & itmColumnName.value & ", "
'next
'strSQL += ddlSortList.Items(0).Text & " " & ddlSortList.Items(0).Value
strSQL += ddlSortList.SelectedItem.T
If strSQL.EndsWith(", ") Then
Dim intLastIndexOfComma As Integer = strSQL.LastIndexOf(", ")
strSQL = strSQL.Substring(0, intLastIndexOfComma)
End If
End If
Dim strDataSource As String = Server.MapPath("~/projects
Return GetDataSetFromDB(strSQL, strDataSource, "Projects")
debugSQL.Text = strsql
End Function
And this to the form
<asp:textbox id="debugSQL" visible=true runat="server"></asp:textb
No value was returned, and when I try to reolad the page, it gives me the same error message...Thanks for sticking w me
ASKER
Keeps tracing back to that one line
objAdapter.fill(objDataSet
objAdapter.fill(objDataSet
please change this code to
Dim strDataSource As String = Server.MapPath("~/projects
Return GetDataSetFromDB(strSQL, strDataSource, "Projects")
debugSQL.Text = strsql
try
Dim strDataSource As String = Server.MapPath("~/projects
Return GetDataSetFromDB(strSQL, strDataSource, "Projects")
catch ex as exception
finally
debugSQL.Text = strsql
end try
Dim strDataSource As String = Server.MapPath("~/projects
Return GetDataSetFromDB(strSQL, strDataSource, "Projects")
debugSQL.Text = strsql
try
Dim strDataSource As String = Server.MapPath("~/projects
Return GetDataSetFromDB(strSQL, strDataSource, "Projects")
catch ex as exception
finally
debugSQL.Text = strsql
end try
ASKER
When i click on the filter button - it doesnt return a value, so then I have to reload it and it places the SQL in the text box - why wouldnt it do it on the first try, some type of postback problem? but here is the sqlstr
SELECT P.*, C.ClientName AS ClientName, M.Name as MarketSegment FROM Projects AS P, Clients AS C, MarketSegments AS M WHERE C.ClientID = P.ClientID AND C.MarketSegmentID = M.MarketSegmentID AND Designer LIKE '%Michael Hill%' AND YEAR(DeliveryDate) = 2008 ORDER BY DeliveryDate ASC, C.ClientName ASC, DateReceived ASC, ProjectTitle ASC, ProjectID ASC, PoseidonNumber ASC, ProjectType ASC, M.Name ASC, Status ASC, Designer ASC, IsRush DESC, Initiator ASC, URL ASC, FolderLocation ASC, LastUpdated ASC, Administrator ASC, EducationConsultant ASC, ProjectCoordinator ASC, LastUpdated ASC, IsLinkedToVPEX ASC, DeliveryDate ASC
SELECT P.*, C.ClientName AS ClientName, M.Name as MarketSegment FROM Projects AS P, Clients AS C, MarketSegments AS M WHERE C.ClientID = P.ClientID AND C.MarketSegmentID = M.MarketSegmentID AND Designer LIKE '%Michael Hill%' AND YEAR(DeliveryDate) = 2008 ORDER BY DeliveryDate ASC, C.ClientName ASC, DateReceived ASC, ProjectTitle ASC, ProjectID ASC, PoseidonNumber ASC, ProjectType ASC, M.Name ASC, Status ASC, Designer ASC, IsRush DESC, Initiator ASC, URL ASC, FolderLocation ASC, LastUpdated ASC, Administrator ASC, EducationConsultant ASC, ProjectCoordinator ASC, LastUpdated ASC, IsLinkedToVPEX ASC, DeliveryDate ASC
Your SQL String themes to be ok.
could it be that in your database is no value which could be find by your sql string. When no exception comes up
check herefore the value ex of the catch block. Set a breakpoint with F9 on the line with the catch statement or create a new textbox on your site and assing the value in the catch block.
Create a new Textbox
<asp:textbox id="debugEX" visible=true runat="server"></asp:textb
code for the catchblock
catch ex as exception
debugEX.Text = ex.Message
Finally
debugSQL.Text = strSQL
End Try
could it be that in your database is no value which could be find by your sql string. When no exception comes up
check herefore the value ex of the catch block. Set a breakpoint with F9 on the line with the catch statement or create a new textbox on your site and assing the value in the catch block.
Create a new Textbox
<asp:textbox id="debugEX" visible=true runat="server"></asp:textb
code for the catchblock
catch ex as exception
debugEX.Text = ex.Message
Finally
debugSQL.Text = strSQL
End Try
ASKER
Heres what the new text box has - The provider could not determine the Object value. For example, the row was just created, the default for the Object column was not available, and the consumer had not yet set a new Object value.
So I guess we are back where I started - maybe I should post in another zone?
So I guess we are back where I started - maybe I should post in another zone?
ASKER
I think it has to do with the way the SQL is executed....
ASKER
Melmers - have you given up on this one?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I will try this - thanks. I feel like this may be the right problem, because the error originated when we would query all particular projects by a particular designer. I am hopefull and will re-post tommorrow morning to let you know. Thanks!!!
ASKER
So the only difference I see is that you added "P." in front of the designer - I tried this and it didnt work - can you explain a little further...once again, here is the main function, and this is an access database also....
Public Function GetProjects(ByVal strProjectID As String, _
ByVal strProjectType As String, _
ByVal strDesignerName As String, _
ByVal strGroupMemberID As String, _
ByVal strDeliveryYear As String, _
ByVal strMarketSegment As String, _
ByVal strClient As String, _
ByVal strStatus As String, _
ByVal strEducationConsultant As String, _
ByVal strInitiator As String, _
ByVal strIsRush As String, _
ByVal strIsLinkedToVPEX As String, _
ByVal ddlSortList As DropDownList) As DataSet
Dim strSQL As String = "SELECT P.*, C.ClientName AS ClientName, M.Name as MarketSegment FROM Projects AS P, Clients AS C, MarketSegments AS M" & IIf(strGroupMemberID <> "", ", ProjectResources AS PR, GroupMembers AS GM", "") & " WHERE C.ClientID = P.ClientID AND C.MarketSegmentID = M.MarketSegmentID AND " & IIf(strGroupMemberID <> "", "P.ProjectID = PR.ProjectID AND PR.GroupMemberID = GM.GroupMemberID AND ", "")
If strProjectID <> "" Then
If IsNumeric(strProjectID) Then
strSQL += "P.ProjectID = " & strProjectID.Replace("""",
End If
End If
If strProjectType <> "" Then
strSQL += "ProjectType='" & strProjectType.Replace("""
End If
If strDesignerName <> "" Then
strSQL += "Designer LIKE '%" & strDesignerName.Replace(""
End If
If strGroupMemberID <> "" Then
strSQL += "GM.GroupMemberID = " & strGroupMemberID & " AND "
End If
If strDeliveryYear <> "" Then
If IsNumeric(strDeliveryYear)
strSQL += "YEAR(DeliveryDate) = " & strDeliveryYear.Replace(""
End If
End If
If strMarketSegment <> "" Then
strSQL += "MarketSegment='" & strMarketSegment.Replace("
End If
If strClient <> "" Then
strSQL += "ClientName='" & strClient.Replace("""", "'").Replace("'", "''") & "' AND "
End If
If strEducationConsultant <> "" Then
strSQL += "EducationConsultant='" & strEducationConsultant.Rep
End If
If strInitiator <> "" Then
strSQL += "Initiator='" & strInitiator.Replace("""",
End If
If strStatus <> "" Then
If strStatus <> "Not Completed" Then
strSQL += "Status='" & strStatus.Replace("""", "'").Replace("'", "''") & "' AND "
Else
strSQL += "Status <> 'Completed' AND "
End If
End If
If strIsRush <> "" Then
strSQL += "IsRush=" & strIsRush & " AND "
End If
If strIsLinkedToVPEX <> "" Then
strSQL += "IsLinkedToVPEX=" & strIsLinkedToVPEX & " AND "
End If
If strSQL.EndsWith(" AND ") Then
Dim intLastIndexOfAND As Integer = strSQL.LastIndexOf(" AND ")
strSQL = strSQL.Substring(0, intLastIndexOfAND + 1)
ElseIf strSQL.EndsWith(" WHERE ") Then
Dim intLastIndexOfWHERE As Integer = strSQL.LastIndexOf(" WHERE ")
strSQL = strSQL.Substring(0, intLastIndexOfWHERE + 1)
End If
If ddlSortList.Items.Count > 0 Then
strSQL += " ORDER BY "
Dim itmColumnName As ListItem
'for each itmColumnName in ddlSortList.items
' strSQL += itmColumnName.text & " " & itmColumnName.value & ", "
'next
strSQL += ddlSortList.Items(0).Text & " " & ddlSortList.Items(0).Value
If strSQL.EndsWith(", ") Then
Dim intLastIndexOfComma As Integer = strSQL.LastIndexOf(", ")
strSQL = strSQL.Substring(0, intLastIndexOfComma)
End If
End If
Dim strDataSource As String = Server.MapPath("~/projects
Return GetDataSetFromDB(strSQL, strDataSource, "Projects")
End Function
public function GetClientValueByID(strColu
dim strSQL as string = "SELECT " & strColumnName & " FROM Clients WHERE ClientID=" & intClientID
dim strDataSource as string = server.MapPath("~/projects
return GetSingleValueFromDB(strSQ
end function
function FormatClientName(strClient
if IsNumeric(strClientID) then
return GetClientValueByID("Client
end if
end function
function FormatRowColor(strStatus as string) as string
if strStatus.tolower() = "not started" then
return "#fdf8af" 'yellow
else if strStatus.tolower() = "in progress" then
return "#b0fdaf" 'green
else if strStatus.tolower() = "completed" then
return "#affdfc" 'blue
else if strStatus.tolower() = "stopped" then
return "#fdafaf" 'red
else
return "#ffffff"
end if
end function
function FormatDatabaseDate(strData
if IsDate(strDatabaseDate) then
return CDate(strDatabaseDate).tos
else
return ""
end if
end function
function FornmatRush(bolIsRush as boolean) as string
if bolIsRush then
'return "<span style='font-weight:bold; font-size:10pt; color:red;'>Yes</span>"
return "<img src='images/icons/rushIcon
else
'return "No"
return ""
end if
end function
Public Function GetProjects(ByVal strProjectID As String, _
ByVal strProjectType As String, _
ByVal strDesignerName As String, _
ByVal strGroupMemberID As String, _
ByVal strDeliveryYear As String, _
ByVal strMarketSegment As String, _
ByVal strClient As String, _
ByVal strStatus As String, _
ByVal strEducationConsultant As String, _
ByVal strInitiator As String, _
ByVal strIsRush As String, _
ByVal strIsLinkedToVPEX As String, _
ByVal ddlSortList As DropDownList) As DataSet
Dim strSQL As String = "SELECT P.*, C.ClientName AS ClientName, M.Name as MarketSegment FROM Projects AS P, Clients AS C, MarketSegments AS M" & IIf(strGroupMemberID <> "", ", ProjectResources AS PR, GroupMembers AS GM", "") & " WHERE C.ClientID = P.ClientID AND C.MarketSegmentID = M.MarketSegmentID AND " & IIf(strGroupMemberID <> "", "P.ProjectID = PR.ProjectID AND PR.GroupMemberID = GM.GroupMemberID AND ", "")
If strProjectID <> "" Then
If IsNumeric(strProjectID) Then
strSQL += "P.ProjectID = " & strProjectID.Replace("""",
End If
End If
If strProjectType <> "" Then
strSQL += "ProjectType='" & strProjectType.Replace("""
End If
If strDesignerName <> "" Then
strSQL += "Designer LIKE '%" & strDesignerName.Replace(""
End If
If strGroupMemberID <> "" Then
strSQL += "GM.GroupMemberID = " & strGroupMemberID & " AND "
End If
If strDeliveryYear <> "" Then
If IsNumeric(strDeliveryYear)
strSQL += "YEAR(DeliveryDate) = " & strDeliveryYear.Replace(""
End If
End If
If strMarketSegment <> "" Then
strSQL += "MarketSegment='" & strMarketSegment.Replace("
End If
If strClient <> "" Then
strSQL += "ClientName='" & strClient.Replace("""", "'").Replace("'", "''") & "' AND "
End If
If strEducationConsultant <> "" Then
strSQL += "EducationConsultant='" & strEducationConsultant.Rep
End If
If strInitiator <> "" Then
strSQL += "Initiator='" & strInitiator.Replace("""",
End If
If strStatus <> "" Then
If strStatus <> "Not Completed" Then
strSQL += "Status='" & strStatus.Replace("""", "'").Replace("'", "''") & "' AND "
Else
strSQL += "Status <> 'Completed' AND "
End If
End If
If strIsRush <> "" Then
strSQL += "IsRush=" & strIsRush & " AND "
End If
If strIsLinkedToVPEX <> "" Then
strSQL += "IsLinkedToVPEX=" & strIsLinkedToVPEX & " AND "
End If
If strSQL.EndsWith(" AND ") Then
Dim intLastIndexOfAND As Integer = strSQL.LastIndexOf(" AND ")
strSQL = strSQL.Substring(0, intLastIndexOfAND + 1)
ElseIf strSQL.EndsWith(" WHERE ") Then
Dim intLastIndexOfWHERE As Integer = strSQL.LastIndexOf(" WHERE ")
strSQL = strSQL.Substring(0, intLastIndexOfWHERE + 1)
End If
If ddlSortList.Items.Count > 0 Then
strSQL += " ORDER BY "
Dim itmColumnName As ListItem
'for each itmColumnName in ddlSortList.items
' strSQL += itmColumnName.text & " " & itmColumnName.value & ", "
'next
strSQL += ddlSortList.Items(0).Text & " " & ddlSortList.Items(0).Value
If strSQL.EndsWith(", ") Then
Dim intLastIndexOfComma As Integer = strSQL.LastIndexOf(", ")
strSQL = strSQL.Substring(0, intLastIndexOfComma)
End If
End If
Dim strDataSource As String = Server.MapPath("~/projects
Return GetDataSetFromDB(strSQL, strDataSource, "Projects")
End Function
public function GetClientValueByID(strColu
dim strSQL as string = "SELECT " & strColumnName & " FROM Clients WHERE ClientID=" & intClientID
dim strDataSource as string = server.MapPath("~/projects
return GetSingleValueFromDB(strSQ
end function
function FormatClientName(strClient
if IsNumeric(strClientID) then
return GetClientValueByID("Client
end if
end function
function FormatRowColor(strStatus as string) as string
if strStatus.tolower() = "not started" then
return "#fdf8af" 'yellow
else if strStatus.tolower() = "in progress" then
return "#b0fdaf" 'green
else if strStatus.tolower() = "completed" then
return "#affdfc" 'blue
else if strStatus.tolower() = "stopped" then
return "#fdafaf" 'red
else
return "#ffffff"
end if
end function
function FormatDatabaseDate(strData
if IsDate(strDatabaseDate) then
return CDate(strDatabaseDate).tos
else
return ""
end if
end function
function FornmatRush(bolIsRush as boolean) as string
if bolIsRush then
'return "<span style='font-weight:bold; font-size:10pt; color:red;'>Yes</span>"
return "<img src='images/icons/rushIcon
else
'return "No"
return ""
end if
end function
ASKER
I had a huge breakthrough I believe - the error I have been recieving only seems to happen with a particular designer - and when I query against the year "2008" - all of the previous years I use to filter the search work - what gives...?
ASKER
Also - for some reason, it looks like the later dates in the access database have times appended to them - do you think this has something to do with it, as the older db entries do not...
yes it could be that the older with the time are interpreted as timestamps so dates with times which is a regular fieldtype. the other without the times can be the problem. it can be that the newer records without time will be interpreted as string and not as datetimes.
can you test it when you add to some specifc records from 2008 the time and to some which have it not would then the record from 2008 be showed?
ASKER
That did not work, but I will award points for the code clean-up - Thanks
Can you post your GetProects function ?