Grab session from insert - C# and MySQL

Question

I've never done this before so I was hoping some of you would know how to do it.

Essentially I do an insert on the CreateModule page and then I want to grab the new ModuleID, (which is made in the database and I haven't inserted) and the ModuleTitle and carry it to the CreateModule2 page.

I really appreciate all help given.

C#

protected void CreateNewModule_Click(object sender, EventArgs e)
        {
            // open new connection
            SqlConnection connect1 = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
            connect1.Open();

            // initalise variables for update
            String Title = ModuleTitleText.Text;
            String Mtext = ModuleTextText.Text;
            String Com = CompulsoryDropdown.Text;
            String CAT = CATpointsText.Text;
            String Lev = LevelText.Text;
            String Ass = AssessmentText.Text;
            String MCode = ModuleCodeText.Text;
            String Status = ModuleStatusDropdown.Text;

            // convert string to Int
            Int32 Levconverted = Convert.ToInt32(Lev);
            Int32 CATconverted = Convert.ToInt32(CAT);
            

            // Insert Query to Add new student record to student records table in database
            String queryInsert = "INSERT INTO Module_Info (ModuleTitle, ModuleText, Compulsory, CATpoints, Level, Assessment, ModuleCode, ModuleStatus) VALUES ('" + Title + "', '" + Mtext + "', '" + Com + "', '" + CAT + "', '" + Lev + "', '" + Ass + "',  '" + MCode + "', '" + Status + "'); SELECT  LAST_INSERT_ID()";

            // excute insert query
            SqlCommand myCommand = new SqlCommand(queryInsert, connect1);
            myCommand.Parameters.Add("@title", SqlDbType.NVarChar).Value = Title;
            int idmodule = Convert.ToInt32(myCommand.ExecuteScalar());
            
            

            // alerts for successfull upload
            Response.Write("<script type='text/javascript'>");
            Response.Write("alert('New Module has been added. Please select a course to align the module to in the next page.');");
            Response.Write("document.location.href='CreateModule2.aspx';");
            Response.Write("</script>");
        }

And then this is the front end code.

<table style="width: 100%;">
                <tr>
                    <td><asp:Label ID="ModuleTitle" runat="server" Text="Module Title" Font-Bold="true"></asp:Label></td>
                    <td><asp:TextBox ID="ModuleTitleText" runat="server" TextMode="MultiLine" style="overflow:hidden" onkeyup="AutoExpand(this)" Rows="1" width="700px" ></asp:TextBox>
                        <asp:RequiredFieldValidator runat="server" id="reqModuleTitle" controltovalidate="ModuleTitleText" 
                            errormessage="* Please enter the module title" ForeColor="Red" Font-Bold="true" Font-Size="Small" /></td>
                </tr>
                <tr>
                    <td><asp:Label ID="ModuleText" runat="server" Text="Module Text" Font-Bold="true" ></asp:Label></td>
                    <td><asp:TextBox ID="ModuleTextText" runat="server" TextMode="MultiLine" style="overflow:hidden" onkeyup="AutoExpand(this)" Rows="4" width="800px"></asp:TextBox>
                        <asp:RequiredFieldValidator runat="server" id="reqModuleText" controltovalidate="ModuleTextText" 
                            errormessage="*Please enter the Module Information" ForeColor="Red" Font-Bold="true" Font-Size="Small" /></td>
                </tr>
                <tr>
               <!-- dropdown list to select value-->
                  <td><asp:Label ID="Compulsory" runat="server" Text="Compulsory Status" Font-Bold="true" ></asp:Label> </td>
                    <td><asp:DropDownList ID="CompulsoryDropdown" runat="server">
                        <asp:ListItem Value="true">Compulsory</asp:ListItem>
                        <asp:ListItem Value="false">Non-Compulsory</asp:ListItem>
                </asp:DropDownList></td>
                </tr>
                <tr>
                    <td><asp:Label ID="CATpoints" runat="server" Text="CATpoints" Font-Bold="true"></asp:Label></td>
                    <td><asp:TextBox ID="CATpointsText" runat="server" TextMode="MultiLine" style="overflow:hidden" onkeyup="AutoExpand(this)" Rows="1" width="100px"></asp:TextBox>
                        <asp:RequiredFieldValidator runat="server" id="reqCATpoints" controltovalidate="CATpointsText" 
                            errormessage="*Please enter the A-Level Requirements" ForeColor="Red" Font-Bold="true" Font-Size="Small" /></td>
                </tr>
                <tr>
                    <td><asp:Label ID="Level" runat="server" Text="Level" Font-Bold="true"></asp:Label></td>
                    <td><asp:TextBox ID="LevelText" runat="server" TextMode="MultiLine" style="overflow:hidden" onkeyup="AutoExpand(this)" Rows="1" width="100px"></asp:TextBox>
                        <asp:RequiredFieldValidator runat="server" id="reqLevel" controltovalidate="LevelText" 
                            errormessage="*Please enter the Level of the module" ForeColor="Red" Font-Bold="true" Font-Size="Small" /></td>
                </tr>
                <tr>
                    <td><asp:Label ID="Assessment" runat="server" Text="Assessment" Font-Bold="true"></asp:Label></td>
                    <td><asp:TextBox ID="AssessmentText" runat="server" TextMode="MultiLine" style="overflow:hidden" onkeyup="AutoExpand(this)" Rows="1" width="600px"></asp:TextBox>
                        <asp:RequiredFieldValidator runat="server" id="ReqAssessment" controltovalidate="AssessmentText" 
                            errormessage="*Please enter the Assessment details" ForeColor="Red" Font-Bold="true" Font-Size="Small" /></td>
                </tr>
                <tr>
                    <td><asp:Label ID="ModuleCode" runat="server" Text="Module Code" Font-Bold="true" ></asp:Label>  </td>
                    <td><asp:TextBox ID="ModuleCodeText" runat="server"  TextMode="MultiLine" style="overflow:hidden" onkeyup="AutoExpand(this)" Rows="1" width="300px"></asp:TextBox>
                        <asp:RequiredFieldValidator runat="server" id="ReqModuleCode" controltovalidate="ModuleCodeText" 
                            errormessage="*Please enter the module code" ForeColor="Red" Font-Bold="true" Font-Size="Small" />
                    </td>
                </tr>
                <tr>
               <!-- dropdown list to select value-->
                  <td><asp:Label ID="ModuleStatus" runat="server" Text="Module Status" Font-Bold="true" ></asp:Label> </td>
                    <td><asp:DropDownList ID="ModuleStatusDropdown" runat="server">
                        <asp:ListItem Value="Running">Running</asp:ListItem>
                        <asp:ListItem Value="Suspended">Suspended</asp:ListItem>
                        <asp:ListItem Value="Withdrawn">Withdrawn</asp:ListItem>
                </asp:DropDownList></td>
            </tr>
               <tr>
                    <td>&nbsp;</td>
                    <td>&nbsp;</td>
               </tr>
               <tr>
                    <td><asp:Button ID="SubmitModule" runat="server" Text="Submit" OnClick="CreateNewModule_Click" /></td>
               </tr>
            </table>

Are you using MySql as your database or Sql Server ? Code uses classes for Sql Server but your tag says MySql. To retrieve the last identity value the answer will be different — Steve, Mar 04 '16 at 14:03

Oscar · Answer 1 · 2016-03-04T14:04:15.420

0

Your code is ugly, buggy and asking for an SQL injection. Just pray that none enters a text like 'DROP TABLE 'Module_Info'; in ModuleTitleText.. That say, change your query to be:

String queryInsert = "INSERT INTO Module_Info (ModuleTitle, ModuleText, Compulsory, CATpoints, Level, Assessment, ModuleCode, ModuleStatus) VALUES ('" + Title + "', '" + Mtext + "', '" + Com + "', '" + CAT + "', '" + Lev + "', '" + Ass + "',  '" + MCode + "', '" + Status + "'); SELECT  LAST_INSERT_ID()"

Then use:

int result = (int) myCommand.ExecuteScalar();

Supposing that your id col is set as AUTO_INCREMENT

Edit. In SQL Server change it for SCOPE_IDENTITY()

edited Mar 04 '16 at 14:04

answered Mar 04 '16 at 13:58

Oscar

13,594
8
47
75

Thanks Oscar, new to this stuff so was shown this way. Can you explain the int result = (int) myCommand.ExecuteScalar(); bit? And where in my ode do I place this? And how do I get that in the next page? Is it like "" – Marty Mar 04 '16 at 14:04

score 0 · Answer 2 · edited May 23 '17 at 12:15

First you should know that string concatenation is a very bad practice in database code. It leads to Sql Injections and parsing problems. For all of these causes the better practice is to use a parameterized query, then to get back the last inserted identity in your table the fix is really easy

String queryInsert = @"INSERT INTO Module_Info 
        (ModuleTitle, ModuleText, Compulsory, CATpoints, Level, 
         Assessment, ModuleCode, ModuleStatus) VALUES 
        (@title, @mtext, @Com, @cat, @lev, @Ass, @MCode, @Status);
        SELECT SCOPE_IDENTITY()";

SqlCommand myCommand = new SqlCommand(queryInsert, connect1);
myCommand.Parameters.Add("@title", SqlDbType.NVarChar).Value = Title;
.... ...
// and so on with all other parameters required
// paying attention to use the appropriate SqlDbType for the 
// field updated by the parameter value...

// Don't run ExecuteNonQuery, but ExecuteScalar to get the last
// value returned by SCOPE_IDENTITY()
int idmodule = Convert.ToInt32(myCommand.ExecuteScalar());

As you can see the query contains two instructions, the last one returns the ID inserted in your module_info table and you could get it using ExecuteScalar

Waiting for a clarification on MySql vs Sql Server issue. I will show you the same code but for MySql

String queryInsert = @"INSERT INTO Module_Info 
        (ModuleTitle, ModuleText, Compulsory, CATpoints, Level, 
         Assessment, ModuleCode, ModuleStatus) VALUES 
        (@title, @mtext, @Com, @cat, @lev, @Ass, @MCode, @Status);
        SELECT last_insert_id()";

MySqlCommand myCommand = new MySqlCommand(queryInsert, connect1);
myCommand.Parameters.Add("@title", MySqlDbType.VarChar).Value = Title;

Notice the usage of the last_insert_id function and the usage of the classes for MySql instead of Sql Server

About passing it to your called page. The usual way is to put the result in the query string and grab it in the called page looking at the QueryString collection

    Response.Write("<script type='text/javascript'>");
    Response.Write("alert('New Module has been added. Please select a course to align the module to in the next page.');");
    Response.Write("document.location.href='CreateModule2.aspx?ModuleID=" + idmodule + "&Title=" + Title + "';");
    Response.Write("</script>");

SCOPE_IDENTITY() is SQL Server specific, he is asking for MySql database — Oscar, Mar 04 '16 at 14:01
@Oscar the Tag is mysql but he uses the SqlCommand. Need a clarification here — Steve, Mar 04 '16 at 14:02
I shall edit my code in the question and you can tell me if I've done it correctly — Marty, Mar 04 '16 at 14:14
No @Marty do not change the original question otherwise we loose track of the situation. Try on you own code and if you have another problem ask again or post a new question — Steve, Mar 04 '16 at 14:16
@Marty your update doens't add all the parameters required (one for each value) see the example and the comments in the first snippet above. — Steve, Mar 04 '16 at 18:29

score 0 · Answer 3 · answered Mar 04 '16 at 14:04

You'll want to append this to the end of your SQL string:

SELECT SCOPE_IDENTITY()

This will return the ID of the newly created record. You can then get that value by replacing your myCommand.ExecuteQuery() with :

int id = myCommand.ExecuteScalar();

You can then use a Response.Redirect with the id to get to your next page and use that to load anything from that your newly created record.

Grab session from insert - C# and MySQL

3 Answers3