2 sinat 34861539 sinat_34861539 于 2018.05.10 15:27 提问

asp.net 如何把Table里的数据一次性插入数据库中

table有两列 数据是在前端添加的所以行数不确定

希望的效果是:
按保存按钮 将textbox1和table里两列x行的数据分别插入表1和表2中
前台已经做好了 求后台button-Click ()的具体代码 能用立马采纳 谢谢

4个回答

showbo
showbo   Ds   Rxr 2018.05.10 15:38
已采纳

https://ask.csdn.net/questions/688319
下面内容直接保存为x.aspx进行测试,x.aspx,不要改动,如果改了文件名注意修改ajax请求的页面名称


<%@ Page Language="C#" AutoEventWireup="true" %>
<script runat="server">
    protected void Page_Load(object sender, EventArgs args)
    {
        if (Request.Form["op"] == "save")
        {
            string data = Request.Form["data"], sql = "";
            if (!string.IsNullOrEmpty(data))
            {
                string[] arr = data.Split('\n'), item;
                foreach (string s in arr)
                {
                    item = s.Split('|');
                    if (item.Length < 2) continue;
                    sql += "insert into xxxtable(name,num)values('" + item[0] + "'," + item[1] + ")";
                }
                if (sql != "")
                { //执行sql语句,不用我多少了吧。。。


                    Response.Write("1");//成功输出1
                }
                else Response.Write("数据错误...");
            }

            Response.End();
        }
    }
</script>

<script src="https://cdn.bootcss.com/jquery/1.7.1/jquery.min.js"></script>
货品名:<input type="text" id="text1" /> 数量:<input type="text" id="text2" /><input type="button" value="添加" /><br />
<table id="table" border="1"><tr><td>货品名</td><td>数量</td></tr></table>
<br /><a onclick="save()">保存</a>
<script>
    $(function () {
        $(':button').click(function (e) {
            var text1 = $('#text1').val();
            var text2 = $('#text2').val();
            $('#table').append('<tr><td>' + text1 + '</td>><td>' + text2 + '</td></tr>')
        })
    });
    function save() {
        var data = $('#table tr:gt(0)').map(function () {
            //如果输入内容存在英文状态下|则替换为全角状态下的|
            return $('td', this).map(function () { return this.innerHTML.replace(/\|/g, '|') }).get().join('|');//每组用英文状态下|分隔
        }).get().join('\n');//每行数据用换行符隔开

        if (!data) { alert('先添加数据!'); return }

        $.ajax({
            type: 'POST', url: 'x.aspx', data: { data: data, op: 'save' }, complete: function (xhr) {
                var s = xhr.responseText;
                if (s == '1') alert('保存成功')
                else alert(s)
            }
        })
    }
</script>

showbo
showbo 回复sinat_34861539: js只是将数据组合成一定格式发送给服务器,服务器做拆解后组合sql语句,然后执行sql入库操作就行。
17 天之前 回复
sinat_34861539
sinat_34861539 请问这是用js将table封装成数组的意思吗?
17 天之前 回复
sinat_34861539
sinat_34861539 回复showbo: 好的 谢谢
17 天之前 回复
showbo
showbo 回复sinat_34861539: 你表名称就是table啊?table是关键字,用[]括起sql += "insert into [table](name,num)values('" + item[0] + "','"+ item[1] + "')"; 而且注意该表字段名称,如果num不是数字类型你还得需要用单引号括起
17 天之前 回复
sinat_34861539
sinat_34861539 错误:关键字 'table' 附近有语法错误。我已改成自己的表名还是提示这个错误 请问有什么语法错误 sql += "insert into table(name,num)values('" + item[0] + "','"+ item[1] + "')";
17 天之前 回复
caozhy
caozhy 为了2块钱赏金给你写这么多代码,可以说showbo很良心吗?
17 天之前 回复
showbo
showbo 如果是access不支持多条sql语句的数据库,上面直接用System.Data.OleDb.OleDbCommand执行sql语句而不是组合后一起执行
17 天之前 回复
caozhy
caozhy 建议采纳这个
17 天之前 回复
dabocaiqq
dabocaiqq   2018.05.10 15:32

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class affair_releaseAffair : System.Web.UI.Page
{
Sqlhelper sp = null;
SqlDataReader dr = null;
SqlCommand comm = null;
protected void Page_Load(object sender, EventArgs e)
{

    if (Session["username"] != null)
    {
        if (ViewState["count"] != null)
        {
            //每次刷新都重新建立表格循环再次添加行
            for (int i = 0; i < Convert.ToInt16(ViewState["count"])/2; i++)
            {
                AddRows(tableTarget);
            }
        }
    }
    else {
        Response.Write("<script>alert('请先登录!')</script>");
        // Server.Transfer("~/login.aspx");
        Response.Write("<script>top.window.location.href = '../login.aspx?r='+Math.random() ;</script>");
        //Response.Write("<script>window.location='../login.aspx';</script>");
       //Response.Redirect("~/login.aspx",true);
    }

}
//  删除按钮事件,总是默认删除表的最后一行
protected void btnDelRow_Click(object sender, EventArgs e)
{
    tableTarget.Rows.RemoveAt(tableTarget.Rows.Count - 1);
    tableTarget.Rows.RemoveAt(tableTarget.Rows.Count - 1);
    ViewState["count"] = Convert.ToInt16(ViewState["count"]) - 2;
}

protected void btnAddRow_Click(object sender, EventArgs e)
{
    AddRows(tableTarget);
    ViewState["count"] = Convert.ToInt16(ViewState["count"]) + 2;  //记录的table行数。
}
/// <summary>
///table中有8列,四列label,三列是TextBox一列是DropDownList,这是一个添加行的函数
///给表table1添加两行
/// </summary>
/// <param name="table"></param>
public void AddRows(Table table) {
    TableRow tr0 = new TableRow();
    TableCell tc = new TableCell();
    Label lb0 = new Label();
    lb0.Text = "指标内容:";
    lb0.Width = Unit.Parse("80px");
    tc.Controls.Add(lb0); tc.Width = Unit.Parse("80px");
    TableCell tc0 = new TableCell();
    TextBox tb0 = new TextBox();
    tb0.ID = "tbc" + table.Rows.Count;  //content指标内容
    tb0.TextMode = TextBoxMode.MultiLine; //多行
    tb0.Width = Unit.Parse("400px");
    tb0.Height = Unit.Parse("50px");
    tc0.Controls.Add(tb0);

    tc0.ColumnSpan = 7;
    tr0.Cells.Add(tc);
    tr0.Cells.Add(tc0);
    table.Rows.Add(tr0);

    TableRow tr = new TableRow();
    for (int i = 1; i <= 3; i++) {
        TableCell tc1 = new TableCell();
        Label lb1 = new Label();
        lb1.Text = "指标"+i+":";
        lb1.Width= Unit.Parse("50px");
        tc1.Controls.Add(lb1); tc1.Width = Unit.Parse("50px");
         TableCell tc2 = new TableCell();
        TextBox tb1 = new TextBox();
        tb1.ID = "tb"+i+""+ table.Rows.Count;
        tb1.TextMode = TextBoxMode.MultiLine;
        tb1.Width = Unit.Parse("300px");
        tc2.Controls.Add(tb1);
        tr.Cells.Add(tc1);
        tr.Cells.Add(tc2);

    }
    TableCell tc7 = new TableCell();
    Label lb4 = new Label(); lb4.Width = Unit.Parse("80px");
    lb4.Text = "您的选择:";
    tc7.Controls.Add(lb4); tc7.Width = Unit.Parse("80px");
    TableCell tc8 = new TableCell();
    DropDownList dpl = new DropDownList();
    dpl.ID = "dpl" + table.Rows.Count;
    for (int i = 1; i < 4; i++) dpl.Items.Add(i.ToString());
    tc8.Controls.Add(dpl);
    tr.Cells.Add(tc7);
    tr.Cells.Add(tc8);

    table.Rows.Add(tr);
    table.Attributes.Add("border", "1");

}
//保存选中的值,保存到数据库
protected void btnSave_Click(object sender, EventArgs e)
{
   int num =  saveAffair();
    if (num == 1)
    {//成功
        Response.Write("<script>alert('添加成功!')</script>");
    }
    else {
        Response.Write("<script>alert('添加失败!')</script>");
    }


}
//保存事务
public int saveAffair() {
    String content,affairName,sqlStr;
    int affairId=0,userId = getUserId(),num2=0;
    SqlDataReader dr = null;
    affairName = text_affairName.Text.Trim();
    content = text_affairContent.Text.Trim();
    if (affairName.Equals("") || content.Equals(""))
    {
        Response.Write("<script>alert('请输入事务!')</script>");
    }
    else {
        num2 = 1;
  sp = Sqlhelper.getSqlhelper();
    comm = sp.getComm();
    sqlStr = "insert into affair(UserId,Content,AffairName,createDate) values("+ userId + ",'"+ content + "','"+ affairName + "',GETDATE());";
    try
    {
        comm.CommandText = sqlStr;
        int num = comm.ExecuteNonQuery();
        sqlStr = "select * from affair order by id desc;";
        comm.CommandText = sqlStr;
        dr = comm.ExecuteReader();
        if (dr.HasRows == true)
        {
                dr.Read();
            affairId = Convert.ToInt32(dr["Id"].ToString());

        }
        dr.Close();
        if (affairId > 0)
            saveTarget(affairId, comm);//保存指标

    }
    catch (Exception ee)
    {
        num2 = 2;
        throw;
    }
    finally {
        sp.Close();
    }
    }//else

    return num2;
}
//保存指标
public int saveTarget(int affairId,SqlCommand comm) {
    String content="", select1="", select2="", select3="", answer,sqlStr="";
    int num=0;
    for (int i = 0; i < tableTarget.Rows.Count; i++)
    {
        if (i % 2 == 0)
        { //content内容
            content = ((TextBox)tableTarget.Rows[i].FindControl("tbc" + i)).Text.Trim();
        }
        else
        {//选项
            select1 = ((TextBox)tableTarget.Rows[i].FindControl("tb1" + i)).Text.Trim();
            select2 = ((TextBox)tableTarget.Rows[i].FindControl("tb2" + i)).Text.Trim();
            select3 = ((TextBox)tableTarget.Rows[i].FindControl("tb3" + i)).Text.Trim();
            answer = ((DropDownList)tableTarget.Rows[i].FindControl("dpl" + i)).SelectedValue;
            if(!content.Equals(""))
            sqlStr = "insert into "+"target"+ creatRandom() + " values("+ affairId + ",'"+ content + "','"+ select1 + "','"+
                    select2 + "','"+ select3 + "','"+ answer + "');";
        }
        if (!sqlStr.Equals("")|| !select1.Equals("")|| !select2 .Equals("")|| !select3.Equals("")) {
            comm.CommandText = sqlStr;
            num = comm.ExecuteNonQuery();
        }
    }
    return num;
}
public int creatRandom() {
    //第二种方法可以指定一个int型参数作为随机种子:
    long tick = DateTime.Now.Ticks;
    Random ran = new Random((int)(tick & 0xffffffffL) | (int)(tick >> 32));
    // 而下面这段代码则指定返回值必须在50 - 100的范围之内:
    int iResult;
    int iUp = 20;
    int iDown = 1;
    iResult = ran.Next(iDown, iUp);
    return iResult;
}
public int getUserId() {
    int userId=0;
    sp = Sqlhelper.getSqlhelper();
    comm = sp.getComm();
    String username = Session["username"].ToString();
    String sqlStr = "select Id from userInfo where Name='"+ username+"'";
    comm.CommandText = sqlStr;
    try
    {
        userId = (int)comm.ExecuteScalar();
    }
    catch (Exception)
    {

        throw;
    }
    return userId;
}

}

caozhy
caozhy 回复sinat_34861539: 因为人家怕你不会,所以写的长,建议采纳。
17 天之前 回复
sinat_34861539
sinat_34861539 为啥这么长。。我只需要插入进数据库那段。。
17 天之前 回复
dabocaiqq
dabocaiqq   2018.05.10 15:33
 <%@ Page Language="C#" AutoEventWireup="true" CodeFile="releaseAffair.aspx.cs" Inherits="affair_releaseAffair" %>

<!DOCTYPE html>
<!--发布事务页面:名称AffairName,内容content,创建日期-->
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
</head>
<body>
    <form id="form1" runat="server">

前台后面的代码,采纳了以后给。

qq_38673212
qq_38673212   2018.05.10 21:57

循环获取table数据一个个插,每循环一次插一次

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!