Strange txtbox errors

Discussion in 'ASP .Net' started by Jim in Arizona, Aug 26, 2005.

  1. I'm going insane! I don't know if it's just that the .net 2.0 framework is
    buggy or if it really is my code.

    This is pretty hard to explain since I can't even begin to nail down why
    this is happening.

    I have two text boxes. One is for the ID number, which when postback occurs,
    inserts into the related table as the foreign key.
    The other text box is for notes that go along side the foreign key of the
    the related table. Basically, the SQL statement looks like this:

    strSQL2 = "INSERT INTO TNotes (FK,notes,unnotes,dtnotes) VALUES ('" & _
    txtID.Text & "','" & txtNotes.Text & "','" & strFixedName & _
    "','" & Now() & "')"

    This statement is called on a few different events: when a button called
    "Post Note" is clicked and when a dropdownlist is changed.

    How can it be possible that this will work when the Post Note button is
    clicked some times, but not work others? It's the same code that's execute
    with the same controls in operation on the webform.

    When I response.write the SQL statement, I see that the text from the txtID
    textbox is not being included within the SQL statement. Neither is the text
    from the txtNotes textbox, so my SQL statement ends up looking like this:

    INSERT INTO TNotes (FK,notes,unnotes,dtnotes) VALUES ('','','Jim','8/25/2005
    4:48:22 PM')
    Instead of
    INSERT INTO TNotes (FK,notes,unnotes,dtnotes) VALUES ('35','This is a test
    note','Jim','8/25/2005 4:48:22 PM')

    Something similar to this was happening with another button where the text
    from the txtID textbox was not being included wtihin the SQL statement when
    logically it should have. I got frustrated and just deleted the entire
    button1_click subprocedure and was going to start over with it later on
    after got some more coding done. That's when I came across the other problem
    mentioned above.

    If anyone would like to see the full set of code, not a problem.

    If it helps any, here was the error I was getting (before i did a try/catch
    and placed a response.write to see what the sql statement looked like). Of
    coruse, this would, I think, happen if there was data missing like I showed
    in the above SQL statement. TIA for any helpful clues as to this unusual
    problem.


    Server Error in '/' Application.
    --------------------------------------------------------------------------------

    INSERT statement conflicted with COLUMN FOREIGN KEY constraint
    'FK_TNotes_TTickets'. The conflict occurred in database 'tickets', table
    'TTickets', column 'PK'.
    The statement has been terminated.
    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.SqlClient.SqlException: INSERT statement
    conflicted with COLUMN FOREIGN KEY constraint 'FK_TNotes_TTickets'. The
    conflict occurred in database 'tickets', table 'TTickets', column 'PK'.
    The statement has been terminated.

    Source Error:

    Line 239:
    Line 240: objCommand = New SqlCommand(strSQL, objConnection)
    Line 241: objCommand.ExecuteNonQuery()
    Line 242:
    Line 243: txtID.Text = ""

    Source File: E:\hhsinternal\tickets\reports.aspx.vb Line: 241

    Stack Trace:

    [SqlException (0x80131904): INSERT statement conflicted with COLUMN FOREIGN
    KEY constraint 'FK_TNotes_TTickets'. The conflict occurred in database
    'tickets', table 'TTickets', column 'PK'.
    The statement has been terminated.]
    System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
    Boolean breakConnection) +786274
    System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
    exception, Boolean breakConnection) +684886
    System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
    stateObj) +207
    System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand
    cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
    bulkCopyHandler, TdsParserStateObject stateObj) +1751
    System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName,
    Boolean async) +170
    System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult
    result, String methodName, Boolean sendToPipe) +380
    System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
    reports.btnPostNote_Click(Object sender, EventArgs e) in
    E:\hhsinternal\tickets\reports.aspx.vb:241
    System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
    System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
    +78
    System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
    eventArgument) +7
    System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
    sourceControl, String eventArgument) +11
    System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
    System.Web.UI.Page.ProcessRequestMain(Boolean
    includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5670
    --------------------------------------------------------------------------------
    Version Information: Microsoft .NET Framework Version:2.0.50215.44; ASP.NET
    Version:2.0.50215.44
    Jim in Arizona, Aug 26, 2005
    #1
    1. Advertising

  2. Jim in Arizona

    stand__sure Guest

    well, the trapped error appears to be due to adding an item to a table
    where the referenced key value doesn't exist in the related table...
    but if you are getting a fouled up insert query that would explain that
    error.

    as for full code, yes, that would be helpful to get a handle on what is
    happening -- can you please give us the aspx and aspx.vb source?

    a comment on the technique... it would be safer and more efficient to
    write a stored procedure to do this insert and to pass the inserted
    values as parameters to the procedure (this gives the advantage of
    pre-compilation of the script and reduces the risk of malicious script
    being appended (e.g. theValueYouExpect; DELETE FROM tableName))
    stand__sure, Aug 26, 2005
    #2
    1. Advertising

  3. "stand__sure" <> wrote in message
    news:...
    > well, the trapped error appears to be due to adding an item to a table
    > where the referenced key value doesn't exist in the related table...
    > but if you are getting a fouled up insert query that would explain that
    > error.
    >
    > as for full code, yes, that would be helpful to get a handle on what is
    > happening -- can you please give us the aspx and aspx.vb source?
    >
    > a comment on the technique... it would be safer and more efficient to
    > write a stored procedure to do this insert and to pass the inserted
    > values as parameters to the procedure (this gives the advantage of
    > pre-compilation of the script and reduces the risk of malicious script
    > being appended (e.g. theValueYouExpect; DELETE FROM tableName))
    >


    I'm still pretty much a newbie. I did some classic ASP before moving on to
    ASPNET and I've found ASPNET to be much easier to work with. As far as
    stored procs, I haven't yet learned much about them. I know you can call
    them (I called a query from access) but I don't know how to write them and
    my TSQL skills are novice. I'm learning though.

    I'm currently trying to figure out my error by going line by line through my
    code. I still don't know how a button even can work normally at one point
    and not work normally at another point when the code does the same thing.

    My SQL database has five tables: two tables that are related and three
    tables used to populate three dropdownlists. This application is a system
    which employess use our internal website to report computer problems. They
    post their problem via a web form. Then, the information systems staff
    (myself and a few others) use another webform to view these 'tickets', post
    messages to them, set a priorty, mark them resolved then mark them approved.

    Since the three lookuptables are not relevant, I won't bother mentioning
    them further. Here's the two related tables, in sql create table terms.:

    CREATE TABLE TTickets (
    PK INT IDENTITY PRIMARY KEY,
    subject varchar(40), --Is from a DDL that puts in type of
    problem
    descript varchar(5000), -- the employee's long winded narrative
    of the problem
    name varchar(50), -- the employee's name that they give
    unemp vachar(50), --the empoyee's username from active
    directory
    dtemp smalldatetime, --the Now() function used to insert
    datetime
    ip varchar(14), --IP Address of employee's computer
    assign varchar(50), --Technicians name that the ticket is
    assigned to
    unass varchar(50), --The assigning person (could be self or
    another tech)
    dtass smalldatetime, --The Now() function used to set
    datetime of assignment
    resolve bit, --Marked 1 if ticket has been resolved
    unresolve varchar(50), -- Name of the tech who resolved the ticket
    dtresolve smalldatetime, --Now() used to insert datetime for when
    ticket was resolved
    approve bit, -- Marked 1 if ticket has been approved after being
    resolved
    unapp varchar(50), --Person's name who approved the ticket
    dtapp smalldatetime, --Now() used to set datetime when ticket
    was approved
    prioritycolor varchar(8), --Hex color code for priority. Makes
    everything look like a xmas tree!
    prioritynumber smallint, --Priority number. Value between 0 and 6 (1 to 5
    used)
    disregard bit, --Marked 1 if ticket was so trivial as to not be
    concerned with
    undisregard varchar(50), --Tech who marked ticket as disregarded
    dtdisregard smalldatetime, --When the ticked was marked disregarded
    )

    CREATE TABLE TNotes (
    PK INT IDENTITY PRIMARY KEY,
    FK INT, -- This is the FOREIGN KEY to PK in TTickets
    notes varchar(5000), --This is a note field for technical
    staff
    unnotes varchar(50), --The tech who posted the note
    dtnotes smalldatetime --Now() function used to insert date of
    when the note was posted
    )

    Here's the code for reports.aspx
    ========================================

    <%@ Page Language="VB" AutoEventWireup="false" CodeFile="reports.aspx.vb"
    Inherits="reports" %>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
    "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

    <html xmlns="http://www.w3.org/1999/xhtml" >
    <head runat="server">
    <title>Untitled Page</title>
    </head>
    <body bgcolor="#006600">
    <form id="form1" runat="server">
    <table cellpadding="0" cellspacing="0" style="width: 872px">
    <tr>
    <td style="width: 135px">
    <asp:Label ID="lblID" runat="server" Font-Bold="True"
    Font-Names="Arial" Font-Size="Small"
    ForeColor="Honeydew" Text="ID #"></asp:Label>
    <asp:TextBox ID="txtID" runat="server" Font-Bold="False"
    Font-Names="Arial"
    Font-Size="Small" ForeColor="White" Width="50px"
    BackColor="Black"></asp:TextBox></td>
    <td style="width: 175px">
    <asp:Button ID="btnResolveApprove" runat="server"
    BackColor="Black" BorderColor="WhiteSmoke" Font-Bold="True"
    Font-Names="Arial" Font-Size="Small"
    ForeColor="Lime" Text="Resolve &amp; Approve"
    Width="176px" BorderStyle="Solid" BorderWidth="1px"
    Height="24px" /></td>
    <td>
    <asp:Button ID="btnPostNote" runat="server"
    BackColor="Black" BorderColor="WhiteSmoke" Font-Bold="True"
    Font-Names="Arial" Font-Size="Small"
    ForeColor="WhiteSmoke" Text="Post Note"
    Width="145px" BorderStyle="Solid" BorderWidth="1px"
    Height="24px" /></td>
    <td style="width: 469px">
    <asp:Button ID="btnDisregard" runat="server"
    BackColor="Black" BorderColor="WhiteSmoke" Font-Bold="True"
    Font-Names="Arial" Font-Size="Small"
    ForeColor="Yellow" Text="Disregard"
    Width="145px" BorderStyle="Solid" BorderWidth="1px"
    Height="24px" /></td>
    </tr>
    <tr>
    <td style="width: 135px; height: 24px">
    <asp:DropDownList ID="ddlPriority" runat="server"
    BackColor="Honeydew" Width="216px" Font-Names="Arial" Font-Size="Small"
    ForeColor="Navy">
    <asp:ListItem
    Selected="True">Priority</asp:ListItem>
    <asp:ListItem Value="5">5 - Someday</asp:ListItem>
    <asp:ListItem Value="4">4 - Soon</asp:ListItem>
    <asp:ListItem Value="3">3 - Staff Unable To
    Work</asp:ListItem>
    <asp:ListItem Value="2">2 - High
    Priority</asp:ListItem>
    <asp:ListItem Value="1">1 - Now</asp:ListItem>
    </asp:DropDownList></td>
    <td colspan="3" rowspan="3">
    <asp:TextBox ID="txtNotes" runat="server" Height="80px"
    TextMode="MultiLine" Width="750px" BackColor="Honeydew" BorderColor="Navy"
    BorderStyle="Solid" ForeColor="Navy" Font-Names="Arial"
    Font-Size="Small"></asp:TextBox></td>
    </tr>
    <tr>
    <td style="width: 135px">
    <asp:DropDownList ID="ddlAssign" runat="server"
    Font-Bold="False" Font-Names="Arial"
    Font-Size="Small" ForeColor="Navy" Width="216px"
    AutoPostBack="True" BackColor="Honeydew">
    <asp:ListItem Selected="True">Assign
    To</asp:ListItem>
    <asp:ListItem>Adrienne</asp:ListItem>
    <asp:ListItem>Ed</asp:ListItem>
    <asp:ListItem>Jaime</asp:ListItem>
    <asp:ListItem>Jim</asp:ListItem>
    <asp:ListItem>Tim</asp:ListItem>
    </asp:DropDownList></td>
    </tr>
    <tr>
    <td style="width: 135px">
    <asp:DropDownList ID="ddlViewsReports" runat="server"
    Font-Bold="False" Font-Names="Arial"
    Font-Size="Small" ForeColor="Navy" Width="216px"
    AutoPostBack="True" BackColor="Honeydew">
    <asp:ListItem Selected="True">Choose
    View</asp:ListItem>
    <asp:ListItem Value="Unassigned">View
    Unassigned</asp:ListItem>
    <asp:ListItem Value="Adrienne">View
    Adrienne</asp:ListItem>
    <asp:ListItem Value="Ed">View Ed</asp:ListItem>
    <asp:ListItem Value="Jaime">View
    Jaime</asp:ListItem>
    <asp:ListItem Value="Jim">View Jim</asp:ListItem>
    <asp:ListItem Value="Tim">View Tim</asp:ListItem>
    <asp:ListItem Value="Reports">View
    Reports</asp:ListItem>
    </asp:DropDownList></td>
    </tr>
    </table>
    <hr width="100%" color="AliceBlue" align="center" size="3" noshade
    />
    <table style="width: 304px" id="query1" runat=server>
    <tr>
    <td align="center" colspan="2">
    <asp:DropDownList ID="ddlReportType" runat="server"
    Font-Bold="False" Font-Names="Arial"
    Font-Size="Small" ForeColor="Navy" Width="145px"
    AutoPostBack="True" BackColor="Honeydew">
    <asp:ListItem Selected="True">Choose Report
    Type</asp:ListItem>
    <asp:ListItem>Unresolved</asp:ListItem>
    <asp:ListItem>Unresolved High
    Priority</asp:ListItem>
    <asp:ListItem>Resolved &amp; Not
    Approved</asp:ListItem>
    <asp:ListItem>Approved</asp:ListItem>
    <asp:ListItem>Everything</asp:ListItem>
    </asp:DropDownList>
    <asp:DropDownList ID="ddlQueryTechnician" runat="server"
    Font-Bold="False" Font-Names="Arial"
    Font-Size="Small" ForeColor="Navy" Width="145px"
    AutoPostBack="True" BackColor="Honeydew">
    <asp:ListItem Selected="True">Choose
    Technician</asp:ListItem>
    <asp:ListItem>Adrienne</asp:ListItem>
    <asp:ListItem>Ed</asp:ListItem>
    <asp:ListItem>Jaime</asp:ListItem>
    <asp:ListItem>Jim</asp:ListItem>
    <asp:ListItem>Tim</asp:ListItem>
    <asp:ListItem Value="Select All">Select
    All</asp:ListItem>
    </asp:DropDownList></td>
    </tr>
    <tr>
    <td style="height: 45px" align="center" bgcolor="#009900">
    <asp:Label ID="lblBeginDate" runat="server"
    Font-Bold="True" Font-Names="Arial" Font-Size="Small"
    ForeColor="Honeydew" Text="Begin
    Date"></asp:Label><br />
    <asp:TextBox ID="txtBeginDate"
    runat="server" Width="96px" BackColor="Honeydew"
    Font-Names="Arial" Font-Size="Small" ForeColor="Navy"></asp:TextBox></td>
    <td style="height: 45px" align="center" bgcolor="#009900">
    <asp:Label ID="lblEndDate" runat="server"
    Font-Bold="True" Font-Names="Arial" Font-Size="Small"
    ForeColor="Honeydew" Text="End Date"></asp:Label><br
    />
    <asp:TextBox ID="txtEndDate" runat="server" Width="96px"
    BackColor="Honeydew" Font-Names="Arial" Font-Size="Small"
    ForeColor="Navy"></asp:TextBox></td>
    </tr>
    </table>
    <div id="testgrid" runat=server></div>
    </form>
    </body>
    </html>
    ========================================

    Here's the code for reports.aspx.vb

    ========================================

    Imports System.Data
    Imports System.Data.SqlClient

    Partial Class reports
    Inherits System.Web.UI.Page

    Sub ViewsReports(ByVal strSQLTickets As String, ByVal strSQLNotes As
    String)
    Dim strConnection As String =
    ConfigurationManager.AppSettings("tickets")
    Dim objConnection As New SqlConnection(strConnection)

    Dim parentCol As DataColumn
    Dim childCol As DataColumn
    Dim relation1 As DataRelation
    Dim objAdapter As New SqlDataAdapter(strSQLTickets, objConnection)
    Dim objAdapter1 As New SqlDataAdapter(strSQLNotes, objConnection)
    Dim objDataSet As New DataSet()

    objAdapter.Fill(objDataSet, "ticketstable")
    objAdapter1.Fill(objDataSet, "notestable")

    parentCol = objDataSet.Tables("ticketstable").Columns("PK")
    childCol = objDataSet.Tables("notestable").Columns("FK")

    relation1 = New DataRelation("RelationA", parentCol, childCol)
    objDataSet.Relations.Add(relation1)

    Dim strResults As String = ""
    Dim r As DataRow

    For Each r In objDataSet.Tables("ticketstable").Rows
    strResults += "<table border=""3"" cellspacing=""0""
    cellpadding=""2"" width=""100%"" bordercolor=""black"" bgcolor=""#e7e7e7"">"
    strResults += "<tr bgcolor=" & r.Item("prioritycolor").ToString
    & "><td><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>Priority:</b><br
    />" & r.Item("prioritynumber").ToString & "</span></td>" & _
    "<td bgcolor=""black""><span
    style=""font-family:arial;font-size:10pt;color:white;""><b>ID #:</b><br />"
    & r.Item("PK").ToString & "</span></td><td><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>Subject:</b><br
    />" & r.Item("subject").ToString & "</span></td>" & _
    "<td><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>Date &
    Time:</b><br />" & r.Item("dtemp").ToString & "</span></td><td><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>Emp Name:</b><br
    />" & r.Item("name").ToString & "</span></td>" & _
    "<td><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>AD
    Username:</b><br />" & r.Item("unemp").ToString & "</span></td><td><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>IP
    Address:</b><br />" & r.Item("ip").ToString & "</span></td></tr><tr
    bgcolor=" & r.Item("prioritycolor").ToString & ">" & _
    "<td colspan=""7""><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>Description of
    Problem:</b><br />" & r.Item("descript").ToString & "</td></tr><tr><td><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>Assign To:</b><br
    />" & r.Item("assign").ToString & "</span></td>" & _
    "<td><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>Assign By:</b><br
    />" & r.Item("unass").ToString & "</span></td><td><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>Assign
    Date/Time:</b><br />" & r.Item("dtass").ToString & "</span></td>" & _
    "<td><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>Resolved
    By:</b><br />" & r.Item("unresolve").ToString & "</span></td><td><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>Resolve
    Date/Time:</b><br />" & r.Item("dtresolve").ToString & "</span></td>" & _
    "<td><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>Approved
    By:</b><br />" & r.Item("unapp").ToString & "</span></td><td><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>Approve
    Date/Time:</b><br />" & r.Item("dtapp").ToString & "</span></td></tr>" & _
    "<tr><td colspan=""7"" bgcolor=""silver""><span
    style=""font-family:arial;font-size:10pt;color:#004400;""><center><b>- - -
    Information System Notes - - -</b></center></span></td></tr>"

    Dim childr() As DataRow
    childr = r.GetChildRows("RelationA")

    Dim theChildRow As DataRow

    For Each theChildRow In childr
    strResults += "<tr height=""6""><td colspan=""7""
    bgcolor=""black""></td></tr>"
    strResults += "<tr><td colspan=""4""><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>Posted By:</b><br
    />" & theChildRow.Item("unnotes").ToString & "</span></td>"
    strResults += "<td colspan=""3""><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>Posted
    Date/Time:</b><br />" & theChildRow.Item("dtnotes").ToString &
    "</span></td></tr>"
    strResults += "<tr><td colspan=""7""><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>Comments/Notes:</b><br
    />" & theChildRow.Item("notes").ToString & "</span></td></tr>"

    Next
    strResults += "</table><br /><br />"
    Next
    testgrid.InnerHtml = strResults
    txtID.Text = ""
    txtNotes.Text = ""
    ddlPriority.SelectedIndex = 0
    ddlAssign.SelectedIndex = 0

    objConnection.Close()
    End Sub
    Sub AssignChange()
    Dim strConnection As String =
    ConfigurationManager.AppSettings("tickets")
    Dim objConnection As New SqlConnection(strConnection)
    Dim strSQL, strSQL2, strPriorityColor, strADName, strFixedName As
    String
    Dim objCommand As SqlCommand

    strADName = LCase(HttpContext.Current.User.Identity.Name)
    strFixedName = ""
    Select Case strADName
    Case "hhs\tmurray"
    strFixedName = "Tim"
    Case "hhs\jmorrison"
    strFixedName = "Jim"
    Case "hhs\jdiaz"
    strFixedName = "Jaime"
    Case "hhs\aoldfield"
    strFixedName = "Adrienne"
    Case "hhs\emorrow"
    strFixedName = "Ed"
    End Select

    strPriorityColor = "#ffffff"
    Select Case ddlPriority.SelectedValue
    Case "1"
    strPriorityColor = "#ff0000"
    Case "2"
    strPriorityColor = "#ffcc00"
    Case "3"
    strPriorityColor = "#ffff66"
    Case "4"
    strPriorityColor = "#33ccff"
    Case "5"
    strPriorityColor = "#cc66ff"
    End Select

    strSQL = "UPDATE TTickets SET assign='" &
    ddlAssign.SelectedItem.Value.ToString & _
    "', unass='" & strFixedName & "', dtass='" & Now() & _
    "', prioritycolor='" & strPriorityColor & "',
    prioritynumber='" & _
    ddlPriority.SelectedValue & "' WHERE PK=" & txtID.Text

    strSQL2 = "INSERT INTO TNotes (FK,notes,unnotes,dtnotes) VALUES ('"
    & _
    txtID.Text & "','" & txtNotes.Text & "','" & strFixedName
    & _
    "','" & Now() & "')"

    objConnection.Open()

    objCommand = New SqlCommand(strSQL, objConnection)
    objCommand.ExecuteNonQuery()

    If Not txtNotes.Text = "" Then
    objCommand = New SqlCommand(strSQL2, objConnection)
    objCommand.ExecuteNonQuery()
    End If
    txtID.Text = ""
    txtNotes.Text = ""
    ddlPriority.SelectedIndex = 0
    ddlAssign.SelectedIndex = 0

    DisplayDefault()
    objConnection.Close()
    End Sub
    Sub DisplayDefault()
    Dim strConnection As String =
    ConfigurationManager.AppSettings("tickets")
    Dim objConnection As New SqlConnection(strConnection)

    Dim parentCol As DataColumn
    Dim childCol As DataColumn
    Dim relation1 As DataRelation

    Dim strSQL As String = "SELECT * FROM TTickets WHERE assign IS NULL
    AND disregard IS NULL"
    Dim strSQL1 As String = "SELECT * FROM TNotes where FK in(SELECT PK
    from TTickets WHERE assign IS NULL AND disregard IS NULL)"

    Dim objAdapter As New SqlDataAdapter(strSQL, objConnection)
    Dim objAdapter1 As New SqlDataAdapter(strSQL1, objConnection)
    Dim objDataSet As New DataSet()

    objAdapter.Fill(objDataSet, "ticketstable")
    objAdapter1.Fill(objDataSet, "notestable")

    parentCol = objDataSet.Tables("ticketstable").Columns("PK")
    childCol = objDataSet.Tables("notestable").Columns("FK")

    relation1 = New DataRelation("RelationA", parentCol, childCol)
    objDataSet.Relations.Add(relation1)

    Dim strResults As String = ""
    Dim r As DataRow

    For Each r In objDataSet.Tables("ticketstable").Rows
    strResults += "<table border=""3"" cellspacing=""0""
    cellpadding=""2"" width=""100%"" bordercolor=""black"" bgcolor=""#e7e7e7"">"
    strResults += "<tr bgcolor=" & r.Item("prioritycolor").ToString
    & "><td><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>Priority:</b><br
    />" & r.Item("prioritynumber").ToString & "</span></td>" & _
    "<td bgcolor=""black""><span
    style=""font-family:arial;font-size:10pt;color:white;""><b>ID #:</b><br />"
    & r.Item("PK").ToString & "</span></td><td><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>Subject:</b><br
    />" & r.Item("subject").ToString & "</span></td>" & _
    "<td><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>Date &
    Time:</b><br />" & r.Item("dtemp").ToString & "</span></td><td><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>Emp Name:</b><br
    />" & r.Item("name").ToString & "</span></td>" & _
    "<td><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>AD
    Username:</b><br />" & r.Item("unemp").ToString & "</span></td><td><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>IP
    Address:</b><br />" & r.Item("ip").ToString & "</span></td></tr><tr
    bgcolor=" & r.Item("prioritycolor").ToString & ">" & _
    "<td colspan=""7""><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>Description of
    Problem:</b><br />" & r.Item("descript").ToString & "</td></tr><tr><td><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>Assign To:</b><br
    />" & r.Item("assign").ToString & "</span></td>" & _
    "<td><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>Assign By:</b><br
    />" & r.Item("unass").ToString & "</span></td><td><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>Assign
    Date/Time:</b><br />" & r.Item("dtass").ToString & "</span></td>" & _
    "<td><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>Resolved
    By:</b><br />" & r.Item("unresolve").ToString & "</span></td><td><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>Resolve
    Date/Time:</b><br />" & r.Item("dtresolve").ToString & "</span></td>" & _
    "<td><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>Approved
    By:</b><br />" & r.Item("unapp").ToString & "</span></td><td><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>Approve
    Date/Time:</b><br />" & r.Item("dtapp").ToString & "</span></td></tr>" & _
    "<tr><td colspan=""7"" bgcolor=""silver""><span
    style=""font-family:arial;font-size:10pt;color:#004400;""><center><b>- - -
    Information System Notes - - -</b></center></span></td></tr>"

    Dim childr() As DataRow
    childr = r.GetChildRows("RelationA")

    Dim theChildRow As DataRow

    For Each theChildRow In childr
    strResults += "<tr height=""6""><td colspan=""7""
    bgcolor=""black""></td></tr>"
    strResults += "<tr><td colspan=""4""><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>Posted By:</b><br
    />" & theChildRow.Item("unnotes").ToString & "</span></td>"
    strResults += "<td colspan=""3""><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>Posted
    Date/Time:</b><br />" & theChildRow.Item("dtnotes").ToString &
    "</span></td></tr>"
    strResults += "<tr><td colspan=""7""><span
    style=""font-family:arial;font-size:10pt;color:black;""><b>Comments/Notes:</b><br
    />" & theChildRow.Item("notes").ToString & "</span></td></tr>"

    Next
    strResults += "</table><br /><br />"
    Next
    testgrid.InnerHtml = strResults
    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As
    System.EventArgs) Handles Me.Load
    If Not Page.IsPostBack Then
    query1.Visible = False
    DisplayDefault()
    End If
    End Sub

    Protected Sub ddlAssign_TextChanged(ByVal sender As Object, ByVal e As
    System.EventArgs) Handles ddlAssign.TextChanged
    Try
    Select Case ddlAssign.SelectedValue
    Case "Adrienne"
    AssignChange()
    Case "Ed"
    AssignChange()
    Case "Jaime"
    AssignChange()
    Case "Jim"
    AssignChange()
    Case "Tim"
    AssignChange()
    End Select

    Catch ex As Exception
    Response.Write(ex.Message)
    End Try

    End Sub

    Protected Sub btnPostNote_Click(ByVal sender As Object, ByVal e As
    System.EventArgs) Handles btnPostNote.Click

    Dim strConnection As String =
    ConfigurationManager.AppSettings("tickets")
    Dim objConnection As New SqlConnection(strConnection)
    Dim strSQL, strADName, strFixedName As String
    Dim objCommand As SqlCommand

    strADName = LCase(HttpContext.Current.User.Identity.Name)
    strFixedName = ""
    Select Case strADName
    Case "hhs\tmurray"
    strFixedName = "Tim"
    Case "hhs\jmorrison"
    strFixedName = "Jim"
    Case "hhs\jdiaz"
    strFixedName = "Jaime"
    Case "hhs\aoldfield"
    strFixedName = "Adrienne"
    Case "hhs\emorrow"
    strFixedName = "Ed"
    End Select

    strSQL = "INSERT INTO TNotes (FK,notes,unnotes,dtnotes) VALUES ('" &
    _
    txtID.Text & "','" & txtNotes.Text & "','" & strFixedName
    & _
    "','" & Now() & "')"

    Response.Write(strSQL & "<br>" & txtID.Text & "<br>" &
    txtNotes.Text)

    objConnection.Open()

    objCommand = New SqlCommand(strSQL, objConnection)
    objCommand.ExecuteNonQuery()

    txtID.Text = ""
    txtNotes.Text = ""
    ddlPriority.SelectedIndex = 0
    ddlAssign.SelectedIndex = 0

    DisplayDefault()
    objConnection.Close()


    End Sub

    Protected Sub btnDisregard_Click(ByVal sender As Object, ByVal e As
    System.EventArgs) Handles btnDisregard.Click
    Try
    Dim strConnection As String =
    ConfigurationManager.AppSettings("tickets")
    Dim objConnection As New SqlConnection(strConnection)
    Dim strSQL, strSQL2, strADName, strFixedName As String
    Dim objCommand As SqlCommand

    strADName = LCase(HttpContext.Current.User.Identity.Name)
    strFixedName = ""
    Select Case strADName
    Case "hhs\tmurray"
    strFixedName = "Tim"
    Case "hhs\jmorrison"
    strFixedName = "Jim"
    Case "hhs\jdiaz"
    strFixedName = "Jaime"
    Case "hhs\aoldfield"
    strFixedName = "Adrienne"
    Case "hhs\emorrow"
    strFixedName = "Ed"
    End Select

    strSQL = "UPDATE TTickets SET disregard=1, undisregard='" & _
    strFixedName & "', dtdisregard='" & Now() & _
    "', prioritycolor='#ff66ff', prioritynumber=6 WHERE
    pk=" & _
    txtID.Text

    strSQL2 = "INSERT INTO TNotes (FK,notes,unnotes,dtnotes) VALUES
    ('" & _
    txtID.Text & "','" & txtNotes.Text & "','" &
    strFixedName & _
    "','" & Now() & "')"

    objConnection.Open()

    objCommand = New SqlCommand(strSQL, objConnection)
    objCommand.ExecuteNonQuery()

    If Not txtNotes.Text = "" Then
    objCommand = New SqlCommand(strSQL2, objConnection)
    objCommand.ExecuteNonQuery()
    End If

    txtID.Text = ""
    txtNotes.Text = ""
    ddlPriority.SelectedIndex = 0
    ddlAssign.SelectedIndex = 0

    DisplayDefault()
    objConnection.Close()
    Catch ex As Exception
    Response.Write("<b>" & ex.Message)
    End Try

    End Sub

    Protected Sub ddlViewsReports_TextChanged(ByVal sender As Object, ByVal
    e As System.EventArgs) Handles ddlViewsReports.TextChanged
    Select Case ddlViewsReports.SelectedValue
    Case "Unassigned"
    Dim strSQLTickets As String
    Dim strSQLNotes As String
    strSQLTickets = "SELECT * FROM TTickets WHERE assign IS NULL
    AND disregard IS NULL"
    strSQLNotes = "SELECT * FROM TNotes where FK in(SELECT PK
    from TTickets WHERE assign IS NULL AND disregard IS NULL)"
    ViewsReports(strSQLTickets, strSQLNotes)
    query1.Visible = False

    Case "Adrienne"
    Dim strSQLTickets As String
    Dim strSQLNotes As String
    strSQLTickets = "SELECT * FROM TTickets WHERE
    assign='Adrienne' ORDER BY prioritynumber, dtass"
    strSQLNotes = "SELECT * FROM TNotes where FK in(SELECT PK
    from TTickets WHERE assign='Adrienne')"
    ViewsReports(strSQLTickets, strSQLNotes)
    query1.Visible = False

    Case "Ed"
    Dim strSQLTickets As String
    Dim strSQLNotes As String
    strSQLTickets = "SELECT * FROM TTickets WHERE assign='Ed'
    ORDER BY prioritynumber, dtass"
    strSQLNotes = "SELECT * FROM TNotes where FK in(SELECT PK
    from TTickets WHERE assign='Ed')"
    ViewsReports(strSQLTickets, strSQLNotes)
    query1.Visible = False

    Case "Jaime"
    Dim strSQLTickets As String
    Dim strSQLNotes As String
    strSQLTickets = "SELECT * FROM TTickets WHERE assign='Jaime'
    ORDER BY prioritynumber, dtass"
    strSQLNotes = "SELECT * FROM TNotes where FK in(SELECT PK
    from TTickets WHERE assign='Jaime')"
    ViewsReports(strSQLTickets, strSQLNotes)
    query1.Visible = False

    Case "Jim"
    Dim strSQLTickets As String
    Dim strSQLNotes As String
    strSQLTickets = "SELECT * FROM TTickets WHERE assign='Jim'
    ORDER BY prioritynumber, dtass"
    strSQLNotes = "SELECT * FROM TNotes where FK in(SELECT PK
    from TTickets WHERE assign='Jim')"
    ViewsReports(strSQLTickets, strSQLNotes)
    query1.Visible = False

    Case "Tim"
    Dim strSQLTickets As String
    Dim strSQLNotes As String
    strSQLTickets = "SELECT * FROM TTickets WHERE assign='Jaime'
    ORDER BY prioritynumber, dtass"
    strSQLNotes = "SELECT * FROM TNotes where FK in(SELECT PK
    from TTickets WHERE assign='Jaime')"
    ViewsReports(strSQLTickets, strSQLNotes)
    query1.Visible = False

    Case "Reports"
    query1.Visible = True

    End Select
    End Sub
    End Class

    ========================================

    I'm still trying to go through the code to see if I can find the problem.
    The Resolve & Approve button has not yet been codes and neither of the
    queries for the reports. I deleted the code for the Resolve/Approve button
    since it was my first error that I couldn't figure out so I was going to
    just redo it. Then I saw the other errors, which also made no sense. How can
    the text from a textbox not be there when a postback occurs??

    Thanks for the help. I don't think I could move any further without it!

    Jim
    Jim in Arizona, Aug 26, 2005
    #3
  4. I think I found my problem although I couldn't narrow it down exactly.

    Somehow, I was clearing the txtID.text textbox of its value just prior to
    the SQL statement being executed, which was causing an error since I was
    trying to insert a related record that didn't have a matching foreign key
    for an exisiting primary key.

    With some experimentation, I have resolved the problem (and created all new
    ones too!).

    Sorry for all the reading.

    Jim
    Jim in Arizona, Aug 26, 2005
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Kevin Spencer
    Replies:
    0
    Views:
    471
    Kevin Spencer
    Aug 19, 2004
  2. =?Utf-8?B?eXV2?=

    aspx txtBox value to the .cs file: sending email

    =?Utf-8?B?eXV2?=, Apr 20, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    330
    =?Utf-8?B?eXV2?=
    Apr 20, 2006
  3. Phillip Vong
    Replies:
    3
    Views:
    546
    Phillip Vong
    Oct 20, 2006
  4. Ken

    txtBox.SelectAll() equivalent

    Ken, Nov 1, 2004, in forum: ASP .Net Web Controls
    Replies:
    3
    Views:
    128
    Scott M.
    Nov 2, 2004
  5. Boki
    Replies:
    1
    Views:
    117
    Randy Webb
    May 3, 2006
Loading...

Share This Page