Inserting record into another table

[vpa]

Hello everyone,

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)
    {
        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();
            }
        }
    }
} 

[cpradio]

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

Then running

SELECT * FROM v_HelpDesk

will show you a friendlier view of your data

[vpa]

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
(
  @EmployeeID int,
  @StationNumber int,
  @CategoryID int,
  @SubjectID int,
  @Description nvarchar(50),
  @StatusID int
)
AS
INSERT INTO HelpDesk (EmployeeID, StationNumber, CategoryID,
    SubjectID, Description, StatusID)
VALUES (@EmployeeID, @StationNumber, @CategoryID, @SubjectID,
    @Description, @StatusID) 

[cpradio]

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]

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

Then running

SELECT * FROM v_HelpDesk

will show you a friendlier view of your data

I tried running that but I get an error:
Invalid object name 'Requests'.

[cpradio]

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]

Oh I see, ok. Is this what you needed?
The table names are:

- Departments
DepartmentID int
Department nvarchar()

- Employees
EmployeeID int
DepartmentID int
Name nchar(50)
Username nchar(50)
Password nchar(50)
Address nchar(50)
City nchar(50)
County nchar(50)
PostCode nchar(50)
HomePhone nchar(50)
Extension nchar(50)
MobilePhone nchar(50)

- HelpDesk
RequestID int
EmployeeID int
CategoryID int
StationNumber int
Description nvachar(50)
SubjectID int
StatusID int

- HelpDeskCategories
CategoryID int
Category nvachar(50)

- HelpDeskStatus
StatusID int
Status nvachar(50)

- HelpDeskSubjects
SubjectID int
Subject nvachar(50)

[cpradio]

Perfect! Try the following:

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

[vpa]

Thanks but I still get an error in the first LEFT JOIN line:
Invalid column name 'CategoryID'.
Would it be EmployeeID?

[cpradio]

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]

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]

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