Strange txtbox errors

Jim in Arizona

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(SqlExc eption exception,
Boolean breakConnection) +786274
System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
exception, Boolean breakConnection) +684886
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject
stateObj) +207
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand
cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj) +1751
System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String methodName,
Boolean async) +170
System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(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.RaisePostBackEven t(String eventArgument)
+78
System.Web.UI.WebControls.Button.System.Web.UI.IPo stBackEventHandler.RaisePostBackEvent(String
eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler
sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection 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

Nov 19 '05 #1

Subscribe Post Reply

1668

stand__sure

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))

Nov 19 '05 #2

Jim in Arizona

"stand__sure" <st*********@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

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 & 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 & 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> 
<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> 
<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>Priority: " & r.Item("prioritynumber").ToString & "</td>" & _
"<td bgcolor=""black"">ID #: "
& r.Item("PK").ToString & "</td><td>Subject: " & r.Item("subject").ToString & "</td>" & _
"<td>Date &
Time: " & r.Item("dtemp").ToString & "</td><td>Emp Name: " & r.Item("name").ToString & "</td>" & _
"<td>AD
Username: " & r.Item("unemp").ToString & "</td><td>IP
Address: " & r.Item("ip").ToString & "</td></tr><tr
bgcolor=" & r.Item("prioritycolor").ToString & ">" & _
"<td colspan=""7"">Description of
Problem: " & r.Item("descript").ToString & "</td></tr><tr><td>Assign To: " & r.Item("assign").ToString & "</td>" & _
"<td>Assign By: " & r.Item("unass").ToString & "</td><td>Assign
Date/Time: " & r.Item("dtass").ToString & "</td>" & _
"<td>Resolved
By: " & r.Item("unresolve").ToString & "</td><td>Resolve
Date/Time: " & r.Item("dtresolve").ToString & "</td>" & _
"<td>Approved
By: " & r.Item("unapp").ToString & "</td><td>Approve
Date/Time: " & r.Item("dtapp").ToString & "</td></tr>" & _
"<tr><td colspan=""7"" bgcolor=""silver""><center>- - -
Information System Notes - - -</center></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"">Posted By: " & theChildRow.Item("unnotes").ToString & "</td>"
strResults += "<td colspan=""3"">Posted
Date/Time: " & theChildRow.Item("dtnotes").ToString &
"</td></tr>"
strResults += "<tr><td colspan=""7"">Comments/Notes: " & theChildRow.Item("notes").ToString & "</td></tr>"

Next
strResults += "</table> "
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>Priority: " & r.Item("prioritynumber").ToString & "</td>" & _
"<td bgcolor=""black"">ID #: "
& r.Item("PK").ToString & "</td><td>Subject: " & r.Item("subject").ToString & "</td>" & _
"<td>Date &
Time: " & r.Item("dtemp").ToString & "</td><td>Emp Name: " & r.Item("name").ToString & "</td>" & _
"<td>AD
Username: " & r.Item("unemp").ToString & "</td><td>IP
Address: " & r.Item("ip").ToString & "</td></tr><tr
bgcolor=" & r.Item("prioritycolor").ToString & ">" & _
"<td colspan=""7"">Description of
Problem: " & r.Item("descript").ToString & "</td></tr><tr><td>Assign To: " & r.Item("assign").ToString & "</td>" & _
"<td>Assign By: " & r.Item("unass").ToString & "</td><td>Assign
Date/Time: " & r.Item("dtass").ToString & "</td>" & _
"<td>Resolved
By: " & r.Item("unresolve").ToString & "</td><td>Resolve
Date/Time: " & r.Item("dtresolve").ToString & "</td>" & _
"<td>Approved
By: " & r.Item("unapp").ToString & "</td><td>Approve
Date/Time: " & r.Item("dtapp").ToString & "</td></tr>" & _
"<tr><td colspan=""7"" bgcolor=""silver""><center>- - -
Information System Notes - - -</center></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"">Posted By: " & theChildRow.Item("unnotes").ToString & "</td>"
strResults += "<td colspan=""3"">Posted
Date/Time: " & theChildRow.Item("dtnotes").ToString &
"</td></tr>"
strResults += "<tr><td colspan=""7"">Comments/Notes: " & theChildRow.Item("notes").ToString & "</td></tr>"

Next
strResults += "</table> "
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 & " " & txtID.Text & " " &
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("" & 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

Nov 19 '05 #3

Jim in Arizona

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

Nov 19 '05 #4

Strange txtbox errors

Similar topics