Ja men undtaget farven. Cellens tekst er "green", "red" eller "yellow"
Jeg har prøver med backcolor=<%#getstatus(container.dataItem("Status"))%>
men det funker heller ikke. Jeg har prøvet at sætte den linie ind alle de steder hvor det virkede logisk.
her er koden:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Public Class mssql
Inherits System.Web.UI.Page
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.OleDbDataAdapter1 = New System.Data.OleDb.OleDbDataAdapter
Me.OleDbInsertCommand1 = New System.Data.OleDb.OleDbCommand
Me.OleDbConnection1 = New System.Data.OleDb.OleDbConnection
Me.OleDbSelectCommand1 = New System.Data.OleDb.OleDbCommand
Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
'
'OleDbDataAdapter1
'
Me.OleDbDataAdapter1.InsertCommand = Me.OleDbInsertCommand1
Me.OleDbDataAdapter1.SelectCommand = Me.OleDbSelectCommand1
'
'OleDbInsertCommand1
'
Me.OleDbInsertCommand1.CommandText = "INSERT INTO view_steen(cust_name, db_sw_type, db_patch_level) VALUES (?, ?, ?); S" & _
"ELECT cust_name, ms_sql_id, db_sw_type, db_patch_level FROM view_steen"
Me.OleDbInsertCommand1.Connection = Me.OleDbConnection1
Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("cust_name", System.Data.OleDb.OleDbType.VarChar, 10, "cust_name"))
Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("db_sw_type", System.Data.OleDb.OleDbType.VarChar, 50, "db_sw_type"))
Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("db_patch_level", System.Data.OleDb.OleDbType.VarChar, 10, "db_patch_level"))
'
'OleDbConnection1
'
Me.OleDbConnection1.ConnectionString = "User ID=Jan;Data Source=""WINSRV01\patchwatch"";Tag with column collation when poss" & _
"ible=False;Provider=""SQLOLEDB.1"";Use Procedure for Prepare=1;Auto Translate=True" & _
";Persist Security Info=False;Workstation ID=NUMBERONE;Use Encryption for Data=Fa" & _
"lse;Packet Size=4096"
'
'OleDbSelectCommand1
'
Me.OleDbSelectCommand1.CommandText = "SELECT cust_name, ms_sql_id, db_sw_type, db_patch_level FROM view_steen"
Me.OleDbSelectCommand1.Connection = Me.OleDbConnection1
'
'SqlSelectCommand1
'
Me.SqlSelectCommand1.CommandText = "SELECT cust_name, ms_sql_id, db_sw_type, db_patch_level FROM view_steen"
'
'SqlInsertCommand1
'
Me.SqlInsertCommand1.CommandText = "INSERT INTO view_steen(cust_name, db_sw_type, db_patch_level) VALUES (@cust_name," & _
" @db_sw_type, @db_patch_level); SELECT cust_name, ms_sql_id, db_sw_type, db_patc" & _
"h_level FROM view_steen"
Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@cust_name", System.Data.SqlDbType.VarChar, 10, "cust_name"))
Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@db_sw_type", System.Data.SqlDbType.VarChar, 50, "db_sw_type"))
Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@db_patch_level", System.Data.SqlDbType.VarChar, 10, "db_patch_level"))
'
'SqlDataAdapter1
'
Me.SqlDataAdapter1.InsertCommand = Me.SqlInsertCommand1
Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "view_steen", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("cust_name", "cust_name"), New System.Data.Common.DataColumnMapping("ms_sql_id", "ms_sql_id"), New System.Data.Common.DataColumnMapping("db_sw_type", "db_sw_type"), New System.Data.Common.DataColumnMapping("db_patch_level", "db_patch_level")})})
End Sub
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected WithEvents TextBox1 As System.Web.UI.WebControls.TextBox
Protected WithEvents Label4 As System.Web.UI.WebControls.Label
Protected WithEvents Button1 As System.Web.UI.WebControls.Button
Protected WithEvents Button2 As System.Web.UI.WebControls.Button
Protected WithEvents Label5 As System.Web.UI.WebControls.Label
Protected WithEvents Label2 As System.Web.UI.WebControls.Label
Protected WithEvents DropDownList1 As System.Web.UI.WebControls.DropDownList
Protected WithEvents OleDbDataAdapter1 As System.Data.OleDb.OleDbDataAdapter
Protected WithEvents OleDbSelectCommand1 As System.Data.OleDb.OleDbCommand
Protected WithEvents OleDbInsertCommand1 As System.Data.OleDb.OleDbCommand
Protected WithEvents OleDbConnection1 As System.Data.OleDb.OleDbConnection
Protected WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
Protected WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand
Protected WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter
Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
Protected WithEvents Label6 As System.Web.UI.WebControls.Label
Protected WithEvents Button3 As System.Web.UI.WebControls.Button
'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Class myHyperlinkcolumn
Implements ITemplate
Sub Instantiatein(ByVal container As Control) Implements ITemplate.InstantiateIn
Dim hlink As HyperLink = New HyperLink
AddHandler hlink.DataBinding, AddressOf BindHyperLinkColumn
container.Controls.Add(hlink)
End Sub
Public Sub BindHyperLinkColumn(ByVal sender As Object, ByVal e As EventArgs)
Dim hlink As HyperLink = CType(sender, HyperLink)
Dim container As DataGridItem = CType(hlink.NamingContainer, DataGridItem)
Dim strVals As String
strVals = "id=" & Convert.ToString(DataBinder.Eval( _
(CType(container, DataGridItem)).DataItem, "MS_SQL_ID")) & _
"&Cust_Name=" & Convert.ToString(DataBinder.Eval( _
(CType(container, DataGridItem)).DataItem, "Cust_Name"))
hlink.NavigateUrl = "details.aspx?" & strVals
hlink.Text = "Show details"
End Sub
End Class
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
If Session("LoggedIn") = "mssql" Then
Label5.Text = "Logged In as Mssql user"
Dim myConnection As SqlConnection
Dim myCommand As SqlDataAdapter
Dim strConnMssql As String = "SERVER=WINSRV01\patchwatch;DATABASE=patchwatch;uid=MSSQLUSER; pwd=MSSQL123;"
myCommand = New SqlDataAdapter("SELECT Cust_Name, MS_SQL_ID, DB_SW_TYPE, DB_Patch_Level FROM MS_SQL ORDER BY Cust_Name", myConnection)
Dim ds As DataSet = New DataSet
myCommand.Fill(ds)
End If
If Session("LoggedIn") = "oracle" Then
Label5.Text = "Logged In as Oracle user"
Dim strConnOracle As String = "SERVER=WINSRV01\patchwatch;DATABASE=patchwatch;uid=oracleuser; pwd=ora123;"
End If
If Session("LoggedIn") = "sap" Then
Label5.Text = "Logged In as SAP user"
Dim myConnection As SqlConnection
Dim myCommand As SqlDataAdapter
myConnection = New SqlConnection("SERVER=WINSRV01\patchwatch;DATABASE=patchwatch;uid=SAPUSER1; pwd=SAP1234;")
myCommand = New SqlDataAdapter("SELECT Cust_Name, MS_SQL_ID, DB_SW_TYPE, DB_Patch_Level, DATEDIFF(day, Patchtime, getdate()) AS Status FROM MS_SQL ORDER BY Cust_Name", myConnection)
Dim ds As DataSet = New DataSet
myCommand.Fill(ds)
DataGrid1.DataSource = ds
DataGrid1.DataBind()
'forsøg
Dim tempclm As New TemplateColumn
tempclm.ItemTemplate = New myHyperlinkcolumn
tempclm.HeaderText = "Customer"
DataGrid1.Columns.Add(tempclm)
DataGrid1.DataSource = ds.Tables(0)
DataGrid1.DataBind()
Else : Response.Redirect("login.aspx")
End If
End If
End Sub
Protected Function GetStatus(ByVal Status As Integer) As String
Select Case Status
Case 0 To 30
Return "green"
Case 31 To 60
Return "yellow"
Case Else
Return "red"
End Select
End Function
Private Sub DataGrid1_ItemDataBound(ByVal sender As Object, ByVal e As _
System.web.ui.webcontrols.DataGridItemEventArgs) Handles DataGrid1.ItemDataBound
Select Case e.Item.ItemType
Case ListItemType.AlternatingItem, ListItemType.Item
DataGrid1.Items(e.Item.ItemIndex).BackColor = System.Drawing.Color.FromName(GetStatus _
(e.Item.DataItem("Status")))
End Select
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Response.Redirect("dbselect.aspx")
End Sub
Private Sub DropDownList1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
End Sub
Private Sub OleDbDataAdapter1_RowUpdated(ByVal sender As System.Object, ByVal e As System.Data.OleDb.OleDbRowUpdatedEventArgs) Handles OleDbDataAdapter1.RowUpdated
End Sub
Private Sub DataGrid1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
Dim myConnection As SqlConnection
Dim myCommand As SqlDataAdapter
myConnection = New SqlConnection("SERVER=WINSRV01\patchwatch;DATABASE=patchwatch;uid=SAPUSER1; pwd=SAP1234;")
myConnection.Open()
If TextBox1.Text = "Customer" Then
myCommand = New SqlDataAdapter("SELECT * FROM MS_SQL WHERE Cust_Name LIKE " & TextBox1.Text & " ORDER BY Cust_Name", myConnection)
End If
If TextBox1.Text = "Software Type" Then
myCommand = New SqlDataAdapter("SELECT * FROM MS_SQL WHERE DB_SW_TYPE LIKE " & TextBox1.Text & " ORDER BY Cust_Name", myConnection)
End If
If TextBox1.Text = "Operating System" Then
myCommand = New SqlDataAdapter("SELECT * FROM MS_SQL WHERE OS_Type LIKE " & TextBox1.Text & " ORDER BY Cust_Name", myConnection)
End If
If TextBox1.Text = "Responsible" Then
myCommand = New SqlDataAdapter("SELECT * FROM MS_SQL WHERE Responsible LIKE " & TextBox1.Text & " ORDER BY Cust_Name", myConnection)
End If
Dim ds As DataSet = New DataSet
myCommand.Fill(ds)
DataGrid1.DataSource = ds
DataGrid1.DataBind()
myConnection.Close()
Catch
Label6.Text = "No matches for " & TextBox1.Text
Button3.Visible = True
End Try
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Label6.Text = ""
Button3.Visible = False
End Sub
End Class
aspx koden
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="mssql.aspx.vb" Inherits="patchwatch.mssql"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>mssql</title>
<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="Visual Basic .NET 7.1" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="
http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
</HEAD>
<body bgColor="#000000" background="img-homeBG.gif" MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<asp:label id="Label1" style="Z-INDEX: 101; LEFT: 200px; POSITION: absolute; TOP: 56px" runat="server"
Width="168px" ForeColor="White">MS SQL Info Area</asp:label><asp:label id="Label2" style="Z-INDEX: 102; LEFT: 8px; POSITION: absolute; TOP: 80px" runat="server"
Width="184px" ForeColor="White">Search MSSQL database for</asp:label><asp:dropdownlist id="DropDownList1" style="Z-INDEX: 103; LEFT: 8px; POSITION: absolute; TOP: 96px"
runat="server" Width="176px" ForeColor="Black" AutoPostBack="True">
<asp:ListItem Value="Select table">Please select</asp:ListItem>
<asp:ListItem Value="Customer Name">Customer</asp:ListItem>
<asp:ListItem Value="Database Software Type">Software Type</asp:ListItem>
<asp:ListItem Value="Operating System">Operating System</asp:ListItem>
<asp:ListItem Value="Responsible">Responsible</asp:ListItem>
<asp:ListItem Value="Status">Status</asp:ListItem>
</asp:dropdownlist><asp:textbox id="TextBox1" style="Z-INDEX: 106; LEFT: 8px; POSITION: absolute; TOP: 144px" runat="server"
Width="176px"></asp:textbox><asp:label id="Label4" style="Z-INDEX: 107; LEFT: 8px; POSITION: absolute; TOP: 128px" runat="server"
ForeColor="White">Keyword</asp:label><asp:button id="Button1" style="Z-INDEX: 108; LEFT: 8px; POSITION: absolute; TOP: 176px" runat="server"
Width="63" Text="Search"></asp:button>
<asp:button id="Button2" style="Z-INDEX: 109; LEFT: 8px; POSITION: absolute; TOP: 232px" runat="server"
Width="63" Text="Back"></asp:button><asp:label id="Label5" style="Z-INDEX: 110; LEFT: 384px; POSITION: absolute; TOP: 56px" runat="server"
Width="152px">status</asp:label>
<asp:datagrid id="DataGrid1" autogeneratecolumns="False" style="Z-INDEX: 111; LEFT: 200px; POSITION: absolute; TOP: 96px"
runat="server" Width="600px" ToolTip="Click on an item to see further information" CellPadding="1"
CellSpacing="1" BorderColor="Black" BackColor="White" AllowPaging="True" AllowSorting="True">
<Columns>
<asp:BoundColumn DataField ="Cust_Name" HeaderText = "Customer"></asp:BoundColumn>
<asp:BoundColumn DataField ="MS_SQL_ID" HeaderText = "mssql ID"></asp:BoundColumn>
<asp:BoundColumn DataField ="DB_SW_TYPE" Headertext = "Software Type"></asp:BoundColumn>
<asp:BoundColumn DataField ="DB_Patch_Level" HeaderText = "Patch Level"></asp:BoundColumn>
<asp:BoundColumn DataField ="Status" HeaderText = "Status"></asp:BoundColumn>
<asp:TemplateColumn>
<ItemTemplate>
<%#getstatus(container.dataitem("Status"))%>
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
<HeaderStyle Font-Bold="True" BackColor="Gray"></HeaderStyle>
<PagerStyle BackColor="Gray"></PagerStyle>
</asp:datagrid>
<asp:label id="Label6" style="Z-INDEX: 112; LEFT: 88px; POSITION: absolute; TOP: 176px" runat="server"
Width="80px" ForeColor="White"></asp:label><asp:button id="Button3" style="Z-INDEX: 113; LEFT: 88px; POSITION: absolute; TOP: 232px" runat="server"
Width="63px" Text="OK" Visible="False"></asp:button></form>
</body>
</HTML>
