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:
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)
{
if (Page.IsValid)
{
// Define data objects
SqlConnection conn;
SqlCommand comm;
// Read the connection string from Web.config
string connectionString =
ConfigurationManager.ConnectionStrings[
"SPCC HelpDesk"].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.Add("@EmployeeID", System.Data.SqlDbType.Int);
comm.Parameters["@EmployeeID"].Value = 5;
comm.Parameters.Add("@StationNumber", System.Data.SqlDbType.Int);
comm.Parameters["@StationNumber"].Value = stationTextBox.Text;
comm.Parameters.Add("@CategoryID", System.Data.SqlDbType.Int);
comm.Parameters["@CategoryID"].Value = categoryList.SelectedItem.Value;
comm.Parameters.Add("@SubjectID", System.Data.SqlDbType.Int);
comm.Parameters["@SubjectID"].Value = subjectList.SelectedItem.Value;
comm.Parameters.Add("@Description", System.Data.SqlDbType.NVarChar, 50);
comm.Parameters["@Description"].Value = descriptionTextBox.Text;
comm.Parameters.Add("@StatusID", System.Data.SqlDbType.Int);
comm.Parameters["@StatusID"].Value = 1;
// Enclose database code in Try-Catch-Finally
try
{
// Open the connection
conn.Open();
// Execute the command
comm.ExecuteNonQuery();
// Reload page if the query executed successfully
Response.Redirect("HelpDesk.aspx");
}
catch
{
// Display error message
dbErrorMessage.Text =
"Error submitting the help desk request! Please " +
"try again later, and/or change the entered data!";
}
finally
{
// Close the connection
conn.Close();
}
}
}
}
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]
AS
SELECT
RequestID,
EmployeeID,
CategoryID,
StationNumber,
[Description],
[Subject],
[Status]
FROM Requests R
LEFT JOIN Subjects S ON R.SubjectID = S.SubjectID
LEFT JOIN [Status] St ON R.StatusID = St.StatusID
GO
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?
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
CREATE VIEW [v_HelpDesk]
AS
SELECT
HD.RequestID,
HD.EmployeeID,
E.[Name],
D.Department,
E.[Address],
E.HomePhone,
E.Extension,
E.MobilePhone,
HDC.[Category],
HD.StationNumber,
HD.[Description],
HDS.[Subject],
HDSt.[Status]
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
GO
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
