oracle参数化保存数据,并提示是否继续增加

下面是一个简单的联合查询,如果查询条件为空,则全部显示

/// <summary> /// 返回查询信息,只可查出已激活的产品分值 /// </summary> private void QueryBind() { string sql = "select b.psname,b.psid,b.pid,b.price,p.integralstartdate,p.integralenddate," + "p.integral from bsc_productspec b inner join Pan_IntegralSet p on rtrim(p.psid)= rtrim(b.psid) where b.activeflag=2 "; if (DDLpro.SelectedItem.Text!= "请选择") { sql += "and b.psname='" + DDLpro.SelectedItem.Text + "'"; } if (txtname.Text.Trim() != "") { sql += "and b.psname like '%" + txtname.Text.Trim() + "%'"; } if (txtStartDate.Value.Trim() != "") { DateTime timestar = Convert.ToDateTime(txtStartDate.Value); sql += "and p.integralstartdate > to_date(' " + timestar + "','YYYY-MM-DD HH24:Mi:SS')"; } if (txtEndDate.Value.Trim() != "") { DateTime timeend = Convert.ToDateTime(txtEndDate.Value); sql += "and p.integralenddate < to_date('" + timeend + "','YYYY-MM-DD HH24:Mi:SS')"; } if (TxtScore.Value.Trim() != "") { int a = int.Parse(TxtScore.Value); sql += "and p.integral =" + a; } OleDbCommand cmd = new OleDbCommand(sql, Dbhelp.oleconection); OleDbDataAdapter dr = new OleDbDataAdapter(cmd); DataTable table = new DataTable(); dr.Fill(table); //没有数据的时候显示表头和提示信息 if (table.Rows.Count == 0) { table.Rows.Add(table.NewRow()); GvInfo.DataSource = table; GvInfo.DataBind(); int columnCount = GvInfo.Rows[0].Cells.Count; GvInfo.Rows[0].Cells.Clear(); GvInfo.Rows[0].Cells.Add(new TableCell()); GvInfo.Rows[0].Cells[0].ColumnSpan = columnCount; GvInfo.Rows[0].Cells[0].Text = "对不起，没有相应的产品积分"; GvInfo.RowStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center; } else { GvInfo.DataSource = table; GvInfo.DataBind(); } }  

 

将值插入数据库

/// <summary> /// 将页面的值插入到数据库 /// </summary> private void InsertPro() { ClientScriptManager cs = this.ClientScript; string sql = "insert into Pan_Integralset(integral,integralstartdate,integralenddate,psid,cid) values(:integral,to_date(:star,'YYYY-MM-DD HH24:Mi:SS'),to_date(:end,'YYYY-MM-DD HH24:Mi:SS'),:psid,:cid)"; OleDbCommand cmd = new OleDbCommand(sql, Dbhelp.oleconection); cmd.Parameters.Add(":integral", OleDbType.Integer).Value = txtscore.Value.Trim(); cmd.Parameters.Add(":star", OleDbType.DBDate).Value = txtStartDate.Value; cmd.Parameters.Add(":end", OleDbType.DBDate).Value = txtenddate.Value; cmd.Parameters.Add("psid", OleDbType.VarChar).Value = Txtpsid.Text.Trim(); cmd.Parameters.Add("psid", OleDbType.VarChar).Value = txtcid.Text.Trim(); Dbhelp.oleconection.Open(); int i = cmd.ExecuteNonQuery(); if (i > 0) { cs.RegisterStartupScript(this.GetType(), "Information", "if(confirm('增加成功，是否继续增加？')){ }else{self.location='Default.aspx';}", true); } Dbhelp.oleconection.Close(); cmd.Dispose(); }