从插入抓取会话 - C#和MySQL
我从来没有这样做过,所以我希望你们中的一些人知道如何去做。从插入抓取会话 - C#和MySQL
本质上,我在CreateModule页面上做了一个插入操作,然后我想抓住新的ModuleID(它是在数据库中创建的,我没有插入)和ModuleTitle并将其携带到CreateModule2页面。
我真的很感谢所有帮助。
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>");
}
然后这是前端的代码。
<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> </td>
<td> </td>
</tr>
<tr>
<td><asp:Button ID="SubmitModule" runat="server" Text="Submit" OnClick="CreateNewModule_Click" /></td>
</tr>
</table>
你的代码是丑陋的,越野车,并要求SQL注入。只要祈祷没有人输入像'DROP TABLE'Module_Info';在ModuleTitleText .. 这就是说,改变你的查询是:
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()"
然后使用:
int result = (int) myCommand.ExecuteScalar();
假设您的ID山口设置为AUTO_INCREMENT
编辑。 在SQL Server中将其更改为SCOPE_IDENTITY()
谢谢奥斯卡,这个新东西,所以这样显示。你能解释一下int result =(int)myCommand.ExecuteScalar();位?我的颂歌在哪里?我怎样才能在下一页获得?是不是像“
首先,您应该知道字符串串联在数据库代码中是非常糟糕的做法。它导致Sql Injections和解析问题。对于所有的这些原因,更好的做法是使用参数化查询,然后取回您的表的最后插入的标识修复是很容易
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());
正如你所看到的查询中包含两个指令,最后返回插入到您的module_info表中的ID,并且您可以使用ExecuteScalar获取它。
等待澄清MySql vs Sql Server问题。我会告诉你同样的代码,但LAST_INSERT_ID功能用于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;
注意使用和类的MySQL的使用而不是SQL Server
的关于它传递给你叫页。通常的方法是把查询字符串的结果,并抓住它在所谓的页面看QueryString集合
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>");
你要附加给你的SQL字符串的结尾:
SELECT SCOPE_IDENTITY()
这将返回新创建的记录的ID。
int id = myCommand.ExecuteScalar();
然后,您可以使用Response.Redirect的用id去你的下一个页面,并用它来从你的新加载任何东西:然后,您可以通过替换您myCommand.ExecuteQuery()得到该值创造了记录。
你使用MySql作为数据库还是Sql Server?代码使用Sql Server的类,但你的标签说MySql。要检索最后的身份值,答案会不同 – Steve
我相信它的MySql – Marty