如何从SQL获取最大值并将其插入数据行 [英] How do I get the max value from SQL and insert it to a data row

问题描述

使用sql SERVER 2014

我有表地址包含addID PK身份（1,1），city varchar（）

表客户端包含CLNAME varchar（），clID PK，来自上一个表的addID外键。

i有一个表单允许我用新客户端添加新地址

正确添加地址并给它一个新的addID自动（IDENTITY（1,1））但由于客户端中的addID未自动插入而发生错误

i需要从表地址获取addID的最大值并将其添加到我的数据行中一个包含表client的数据集，以便更新数据库



我尝试过：

using sql SERVER 2014
I have table address contains addID PK identity(1,1), city varchar()
table client contains CLNAME varchar(), clID PK, addID foreign key from previous table.
i have a form that allows me to add new address with a new client
the address is added correctly and giving it a new addID automatically (IDENTITY (1,1)) but an error occurs since the addID in client is not inserted automatically
i need to get the max value of addID from table address and add it to my data-row in a data set containing table "client" in order to update the data base

What I have tried:

sqlconn = conxn()
        Dim dset As New DataSet("client")
        Dim dadpt As New SqlDataAdapter("select * From client", sqlconn)
        dadpt.Fill(dset, "client")
        Dim dsADRS As New DataSet("adrs")
        Dim daADRS As New SqlDataAdapter("SELECT MAX(addID) FROM addres ", sqlconn)
        daADRS.Fill(dsADRS, "adrs")
        sen  = 
 

        sqlconn.Close()
 
        Dim cmdBuilder As SqlCommandBuilder = New SqlCommandBuilder(dadpt)
        Dim dr As DataRow
        dr = dset.Tables("client").NewRow()
 
        dr("name") = name
        dr("fname") = fname
      
        dr("addID") = Convert.ToDecimal(sqls)
        dset.Tables("lawyer").Rows.Add(dr)
        dadpt.Update(dset, "lawyer")

推荐答案

您不想选择SELECT MAX。相反，在插入地址表后立即执行

You do not want to SELECT MAX. Instead, right after doing the insert into the address table do

SELECT @newAddID = SCOPE_IDENTITY()  -- this gets the most recently inserted Identity number

...


INSERT INTO client (field1, ship_to_add_id, ...)
VALUES (@field1, @newAddID...)

这篇关于如何从SQL获取最大值并将其插入数据行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！