vpa — 2013-04-09T13:29:03-04:00 — #1
I've been following the ASP.NET 4 website book, which is a great book. How ever I have come across a problem I am trying to overcome. In chapter 9 of the book a page is created where you can insert data into a form and that data is then inserted into the SQL table. Now everything worked fine as I was following the tutorial. But then I decided to play around a bit and change the data so that instead of it showing the ID's of each table:
it would show the what the actual category, subject and status is.
this is what my .cs code looks like for the submit button when it is stored into the table and I'm sure there is something here I'm supposed to change or add?.
I really don't know and I have spent all day trying to figure it out. Any help will be greatly appreciated!
protected void submitButton_Click(object sender, EventArgs e)
// Define data objects
// Read the connection string from Web.config
string connectionString =
// Initialize connection
conn = new SqlConnection(connectionString);
// Create command
comm = new SqlCommand("InsertHelpDesk", conn);
// Specify we're calling a stored procedure
comm.CommandType = System.Data.CommandType.StoredProcedure;
// Add command parameters
comm.Parameters["@EmployeeID"].Value = 5;
comm.Parameters["@StationNumber"].Value = stationTextBox.Text;
comm.Parameters["@CategoryID"].Value = categoryList.SelectedItem.Value;
comm.Parameters["@SubjectID"].Value = subjectList.SelectedItem.Value;
comm.Parameters.Add("@Description", System.Data.SqlDbType.NVarChar, 50);
comm.Parameters["@Description"].Value = descriptionTextBox.Text;
comm.Parameters["@StatusID"].Value = 1;
// Enclose database code in Try-Catch-Finally
// Open the connection
// Execute the command
// Reload page if the query executed successfully
// Display error message
"Error submitting the help desk request! Please " +
"try again later, and/or change the entered data!";
// Close the connection
cpradio — 2013-04-09T14:18:58-04:00 — #2
First off, you are approaching it from the wrong direction. The book is trying to teach you to normalize your tables (which is a good thing, from a storage standpoint, bad from a viewing standpoint).
To get around that, if you want a way to view it "nicely" in your database, you should create a View, not alter your data stored in the table.
Example (I guessed your table names and I didn't do ones for Category or Employee, as I wasn't sure if those existed):
CREATE VIEW [v_HelpDesk]
FROM Requests R
LEFT JOIN Subjects S ON R.SubjectID = S.SubjectID
LEFT JOIN [Status] St ON R.StatusID = St.StatusID
SELECT * FROM v_HelpDesk
will show you a friendlier view of your data
vpa — 2013-04-09T14:20:38-04:00 — #3
I also had a stored procedure, but in this would I change the values of CategoryID, StatusID and SubjectID to Category, Status and Subject? and would I do the same in the .cs file?
My stored procedure
CREATE PROCEDURE InsertHelpDesk
INSERT INTO HelpDesk (EmployeeID, StationNumber, CategoryID,
SubjectID, Description, StatusID)
VALUES (@EmployeeID, @StationNumber, @CategoryID, @SubjectID,
cpradio — 2013-04-09T14:30:14-04:00 — #4
No, my suggestion is to leave the code and the stored procedure as found in the book, and just create the view I described for viewing your data, you can then query against it and get the "nice" looking output that you want without affecting the code/stored procedure/table
vpa — 2013-04-09T14:31:31-04:00 — #5
I tried running that but I get an error:
Invalid object name 'Requests'.
cpradio — 2013-04-09T14:35:19-04:00 — #6
Yeah, I don't know your table names, so if you can provide the tables and their schemas, I'd be glad to rewrite it accordingly.
vpa — 2013-04-09T14:49:27-04:00 — #7
Oh I see, ok. Is this what you needed?
The table names are:
cpradio — 2013-04-09T14:55:13-04:00 — #8
Perfect! Try the following:
CREATE VIEW [v_HelpDesk]
FROM HelpDesk HD
LEFT JOIN Employees E ON HD.EmployeeID = E.EmployeeID
LEFT JOIN Departments D ON E.DepartmentID = D.DepartmentID
LEFT JOIN HelpDeskCategories HDC ON HD.CategoryID = HDC.CategoryID
LEFT JOIN HelpDeskSubjects HDS ON HD.SubjectID = HDS.SubjectID
LEFT JOIN HelpDeskStatus HDSt ON HD.StatusID = HDSt.StatusID
vpa — 2013-04-09T15:02:32-04:00 — #9
Thanks but I still get an error in the first LEFT JOIN line:
Invalid column name 'CategoryID'.
Would it be EmployeeID?
cpradio — 2013-04-09T15:15:24-04:00 — #10
Dope! Copy and Paste blunder on my part, yes, change it to EmployeeID for table Employees and it should work (I've updated my post accordingly)
vpa — 2013-04-09T15:22:33-04:00 — #11
Amazing! Thank You very much indeed. I hope I can become as skilled as you one day haha
Is there a way to give thanks? like +1 or something
cpradio — 2013-04-09T15:28:39-04:00 — #12
Not a problem. We don't have a +1 system in place at this moment (it has been discussed numerous times though), but your response is more than enough If you have any additional questions, don't hesitate to ask