更新多个表 [英] Update multiple tables

问题描述

我有2个表，即个人资料和信息。

I have 2 tables namely Profile and Info.

我的表是这样的：

简介

| p_Id | FirstName | LastName |

| 1    | Jack      | Cole     | 
| 2    | Cynthia   | Cole     | 
| 3    | Robert    | Cole     |  

信息

| I_Id | childsID | fathersID | mothersID | Country |

| 1    | 1        | 3         | 2         | USA     |

我在文本框中显示它们检索从这些表中的值，我选择的查询是：

I'm retrieving values from those tables by displaying them in textboxes, my select query is:

SELECT p.p_Id, p.FirstName, p.LastName, i.*, 
(SELECT pp.FirstName+' '+pp.LastName FROM Profile pp WHERE pp.p_Id=i.childsID) AS child,
(SELECT pp.FirstName+' '+pp.LastName FROM Profile pp WHERE pp.p_Id=i.fathersID) AS father,
(SELECT pp.FirstName+' '+pp.LastName FROM Profile pp WHERE pp.p_Id=i.mothersID) AS mother
    FROM Info i
        INNER JOIN Profile p
            ON p.p_Id=i.childsID

与选择没有问题，我能够对文本框显示的值，但问题是，我不能更新它们，我至今尝试过这样的：

no problem with select, I'm able to display values on textboxes, but the problem is, I can't update them, I have tried so far this:

using (SqlCommand cmd = con.CreateCommand())
    {
        con.Open();
        cmd.CommandText = @"UPDATE Profile SET FirstName=@fname, LastName=@lname WHERE p_Id = @pid;
                            UPDATE Info SET childsID=@child, fathersID=@father, mothersID=@mother, Country=@country WHERE I_Id = @iid;";

        cmd.Parameters.Add(new SqlParameter("@fname", txtfname.Text));
        cmd.Parameters.Add(new SqlParameter("@lname", txtlname.Text));
        cmd.Parameters.Add(new SqlParameter("@child", txtchild.Text));
        cmd.Parameters.Add(new SqlParameter("@father", txtfather.Text));
        cmd.Parameters.Add(new SqlParameter("@mother", txtmother.Text));
        cmd.Parameters.Add(new SqlParameter("@country", txtcountry.Text));
        cmd.Parameters.Add(new SqlParameter("@pid", txtpid.Text));
        cmd.Parameters.Add(new SqlParameter("@iid", txtiid.Text));
        cmd.ExecuteNonQuery();
        Response.Write("alert('DATA UPDATED')");
    }

我使用C＃和Asp.net
在此先感谢:)天佑

I'm using c# and Asp.net Thanks in advance :) God Bless

推荐答案

检查MSDN：的的SqlParameter构造函数（String，Object）已

当您在值参数中指定的对象时，SqlDbType是从微软的.NET Framework类型对象的推断。

When you specify an Object in the value parameter, the SqlDbType is inferred from the Microsoft .NET Framework type of the Object.

所以，当你传递的int类型的参数，你需要转换文本值整数：

So when you passing int type parameters you need to convert text value to integer:

cmd.Parameters.Add(new SqlParameter("@pid", int.Parse(txtpid.Text)));

或更好的：

int tempIntValue;
cmd.Parameters.Add(new SqlParameter("@pid", int.TryParse(txtpid.Text, out tempIntValue)?
  (object)intTempValue: (object)DbNull.Value ));

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