SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2011
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Inserting record into another table

    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!

    PHP Code:
    protected void submitButton_Click(object senderEventArgs 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.NVarChar50);
                
    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();
                }
            }
        }


  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,129
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    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):
    Code:
    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
    Code:
    SELECT * FROM v_HelpDesk
    will show you a friendlier view of your data

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2011
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    PHP Code:
    CREATE PROCEDURE InsertHelpDesk
    (
      @
    EmployeeID int,
      @
    StationNumber int,
      @
    CategoryID int,
      @
    SubjectID int,
      @
    Description nvarchar(50),
      @
    StatusID int
    )
    AS
    INSERT INTO HelpDesk (EmployeeIDStationNumberCategoryID,
        
    SubjectIDDescriptionStatusID)
    VALUES (@EmployeeID, @StationNumber, @CategoryID, @SubjectID,
        @
    Description, @StatusID

  4. #4
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,129
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    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

  5. #5
    SitePoint Enthusiast
    Join Date
    Dec 2011
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    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):
    Code:
    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
    Code:
    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'.

  6. #6
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,129
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    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.

  7. #7
    SitePoint Enthusiast
    Join Date
    Dec 2011
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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)

  8. #8
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,129
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Perfect! Try the following:
    Code:
    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

  9. #9
    SitePoint Enthusiast
    Join Date
    Dec 2011
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks but I still get an error in the first LEFT JOIN line:
    Invalid column name 'CategoryID'.
    Would it be EmployeeID?

  10. #10
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,129
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    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)

  11. #11
    SitePoint Enthusiast
    Join Date
    Dec 2011
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  12. #12
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,129
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •