在sql上保存两个表 [英] Saving in two table on sql

问题描述

try
{
    if (txtDesc1.Text == "")
    {
    }
    else
    {
        con1 = new SqlDbConnect();
        strSQL = "insert into sample (id, description, dosage, qty, selling, amount)";
        strSQL += "select '" + labelTransID.Text.Replace("'", "''") + "'";
        strSQL += ", '" + txtDesc1.Text.Replace("'", "''") + "'";
        strSQL += ", '" + txtDosage1.Text.Replace("'", "''") + "'";
        strSQL += ", '" + txtQty1.Text.Replace("'", "''") + "'";
        strSQL += ", '" + txtSelling1.Text.Replace("'", "''") + "'";
        strSQL += ", '" + txtAmount1.Text.Replace("'", "''") + "'";
        con1.SqlQuery(strSQL);
        con1.NonQueryEx();
        con1.Close();
    }

这是我的btnSave_click代码



i有一张桌子StockBin

描述nvchar50

剂量nvchar50

加上int

减去int

stockleft int



我的问题是当我点击将其保存到数据库时

txtQty1也会更新到我的stockbin表格为减号



提前致谢

this is my btnSave_click code

i had a table StockBin
description nvchar50
dosage nvchar50
plus int
minus int
stockleft int

My question is when i am clicking to save this to database
txtQty1 will also update to my stockbin table as minus

Thanks in advance

推荐答案

首先，请不要使用连接形成声明;使用参数：



通过ADO.NET接口简化数据库访问 [ ^ ]



另外，如果你可以执行一个语句，你可以在一个事务中执行两个优先。



如果这是SQL Server，你可以实际传递多个语句，如果你愿意的话。或者您可以选择编写存储过程来执行此操作。

First of all, please don't use concatenation to form the statement; use parameters:

Simplified Database Access via ADO.NET Interfaces[^]

Plus, if you can execute one statement, you can execute two, in a transaction is prefered.

If this is SQL Server, you can actually pass multiple statements at once if you like. Or you might choose to write a stored procedure to do it.

sqlCon.Open（）;

SqlCommand cmd = new SqlCommand（procDeliveryStock，sqlCon） ;

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add（new SqlParameter（@ id，labelID.Text））;

cmd.Parameters.Add（new SqlParameter（@ date，dateTimePicker1.Text））;

cmd.Parameters.Add（new SqlParameter（@ description，txtItemDescription.Text）） ;

cmd.Parameters.Add（new SqlParameter（@ dosage，txtDosage.Text））;

cmd.Parameters.Add（new SqlParameter（@ qty） ，txtQty.Text））;

cmd.Parameters.Add（new SqlParameter（@ selling，txtSelling.Text））;

cmd.Parameters.Add（new SqlParameter（@ receiving，txtReceiving.Text））;

cmd.Parameters.Add（new SqlParameter（@ plus，txtQty.Text））;

sqlCon 。关闭（）;



和heres我的商店程序



USE [BotikaNiChauncey]

GO

/ ******对象：StoredProcedure [dbo]。[procDeliveryStock]脚本日期：04/26/2014 04:22:34 ****** /

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

更改程序[ dbo]。[procDeliveryStock]

（

@id INT，@ date DATETIME，

@description VARCHAR（50），

@dosage VARCHAR（50），

@selling MONEY，

@receiving MONEY，

@qty INT，

@plus INT，

@minus INT，

@stock INT

）

AS

BEGIN

设置NOCOUNT ON;

BEGIN TRANSACTION

INSERT INTO Delivery

（

id，日期，描述，剂量，数量，销售，收货

）

值

（

@ id，@ date，@ description，@ docage，@ qty，@ selling，@ recece

）

IF @@ ERROR = 0

BEGIN

插入StockCard

（

descripton，剂量，[+]，[ - ]，库存

）

价值

（

@ description，@ dosage，@ plus，@ minus，@ stock

）

IF @@ ERROR = 0

BEGIN

COMMIT TRANSACTION

结束

ELSE

BEGIN

ROLLBACK TRANSACTION
END

END

ELSE

BEGIN

ROLLBACK TRANSACTION

结束



结束



我的问题是它没有保存在数据库中但没有错误在运行时。



在此先感谢

sqlCon.Open();
SqlCommand cmd = new SqlCommand("procDeliveryStock", sqlCon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@id", labelID.Text));
cmd.Parameters.Add(new SqlParameter("@date", dateTimePicker1.Text));
cmd.Parameters.Add(new SqlParameter("@description", txtItemDescription.Text));
cmd.Parameters.Add(new SqlParameter("@dosage", txtDosage.Text));
cmd.Parameters.Add(new SqlParameter("@qty", txtQty.Text));
cmd.Parameters.Add(new SqlParameter("@selling", txtSelling.Text));
cmd.Parameters.Add(new SqlParameter("@receiving", txtReceiving.Text));
cmd.Parameters.Add(new SqlParameter("@plus", txtQty.Text));
sqlCon.Close();

and heres my Store Procedure

USE [BotikaNiChauncey]
GO
/****** Object: StoredProcedure [dbo].[procDeliveryStock] Script Date: 04/26/2014 04:22:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[procDeliveryStock]
(
@id INT, @date DATETIME,
@description VARCHAR(50),
@dosage VARCHAR(50),
@selling MONEY,
@receiving MONEY,
@qty INT,
@plus INT,
@minus INT,
@stock INT
)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION
INSERT INTO Delivery
(
id, date, description, dosage, qty, selling, receiving
)
values
(
@id, @date, @description, @dosage, @qty, @selling, @receiving
)
IF @@ERROR = 0
BEGIN
INSERT INTO StockCard
(
descripton,dosage,[+],[-], stock
)
VALUES
(
@description, @dosage, @plus, @minus, @stock
)
IF @@ERROR = 0
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
END
END
ELSE
BEGIN
ROLLBACK TRANSACTION
END

END

My problem is it is not saving in the database but there is no error on runtime.

Thanks in Advance

这篇关于在sql上保存两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！