插入行并在字段中增加值

Question

我在从一个表插入到另一个表时遇到了问题:我希望从另一个表中插入多个行，在插入时，增加目标表中int字段的值。代码是这样的：

 Dim sqlCmd As New SqlClient.SqlCommand()
        Dim conMain As New SqlClient.SqlConnection()

    Dim strSQL As String = 
"INSERT INTO AvizeCorn 
(Seria, DataMAx, NrAviz, Agent, Școala, Data, 
Buc, ZileLivrare, Luna, Produs, Anul) 
SELECT @Seria, @Data+Zile as DataMax, @NrAviz, Agent, Școala, @Data, Zile*BucSt as Buc, 
Zile as ZileLivrare, @Luna, @Produs,@Anul 
FROM AgențiȘcoliCorn"

    Try
        conMain.ConnectionString = "Data Source=cristib\sqlexpress;Initial Catalog=Avize Măr și Corn;Integrated Security=True"
        sqlCmd.Connection = conMain
        conMain.Open()
        sqlCmd.CommandType = CommandType.Text
        sqlCmd.CommandText = strSQL

        With sqlCmd.Parameters

            .AddWithValue("@Seria", "AGP")
            .AddWithValue("@NrAviz", DataSetCorn.AvizeCorn.Compute("Max(NrAviz)", "") + 1)
            .AddWithValue("@Data", Convert.ToDateTime(DataCorn.Text))
            .AddWithValue("@Luna", MonthName(Month(DataCorn.Text)))
            .AddWithValue("@Produs", Produs.Text)
            .AddWithValue("@Anul", Year(DataCorn.Text))

        End With

        sqlCmd.ExecuteNonQuery()
        conMain.Close()
        MsgBox("Avizele au fost create cu succes.")

    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

我的问题是NrAviz

我想检查表中插入的每一行的最大值并增加1。在我的代码中，它为表中插入的每个新行插入相同的值。

请帮助我，因为我在sql和vb.net方面完全是新手

Answer 1

您可以将Max(NrAviz)放到SELECT中，这将获取要插入它们的记录。您可以使用ROW_NUMBER

Dim sqlCmd As New SqlClient.SqlCommand()
Dim conMain As New SqlClient.SqlConnection()

       Try
        conMain.ConnectionString = "Data Source=cristib\sqlexpress;Initial Catalog=Avize Măr și Corn;Integrated Security=True"
        sqlCmd.Connection = conMain
        conMain.Open()
        sqlCmd.CommandType = CommandType.Text
        sqlCmd.CommandText = strSQL


        Dim strSQL As String = "INSERT INTO AvizeCorn (Seria, DataMAx, NrAviz, Agent, Școala, Data, 
Buc, ZileLivrare, Luna, Produs, Anul) 
SELECT @Seria, @Data+Zile as DataMax, (SELECT MAX(NrAviz) from AvizeCorn) + row_number() over (order by ID), Agent, Școala, @Data, Zile*BucSt as Buc, 
Zile as ZileLivrare, @Luna, @Produs,@Anul 
FROM AgențiȘcoliCorn"

        sqlCmd.CommandText = strSQL

        With sqlCmd.Parameters

            .AddWithValue("@Seria", "AGP")
            .AddWithValue("@Data", Convert.ToDateTime(DataCorn.Text))
            .AddWithValue("@Luna", MonthName(Month(DataCorn.Text)))
            .AddWithValue("@Produs", Produs.Text)
            .AddWithValue("@Anul", Year(DataCorn.Text))

        End With

        sqlCmd.ExecuteNonQuery()
        conMain.Close()
        MsgBox("Avizele au fost create cu succes.")

    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

编辑

您也可以尝试使用

INSERT INTO AvizeCorn (Seria, DataMAx, NrAviz, Agent, Școala, Data, Buc, ZileLivrare, Luna, Produs, Anul) 
SELECT @Seria, @Data+Zile as DataMax, MAX(NrAviz)+1 FROM AvizeCorn, Agent, Școala, @Data, Zile*BucSt as Buc, Zile as ZileLivrare, @Luna, @Produs,@Anul 
FROM AgențiȘcoliCorn