使用vb.net在sql数据库中插入数字(如学生ID、员工ID)

Question

我想做一个自动生成事务处理编号。在sql数据库中，每次我在sql数据库中插入数据时，如学生ID或员工ID。我现在所能做的就是插入数据。但我不知道如何创建交易编号。在每次我添加数据的时候。

Dim query As String = String.Empty
        query = "insert into marketingLog(TransCode,Endorser,Enrollee_Name,Course,Rebates,Trans_Date,CreatedBy,Status)" _
            & "values(@transcode,@endorser,@name,@course,@rebates,@transdate,@user,@status)"

        Using conn As New SqlConnection(dbUse.db)
            conn.Open()
            Using comm As New SqlCommand
                With comm
                    .Connection = conn
                    .CommandType = CommandType.Text
                    .CommandText = query

                    .Parameters.Add("@endorser", SqlDbType.VarChar).Value = cmbEndorser.Text 'Endorser
                    .Parameters.Add("@name", SqlDbType.VarChar).Value = txtEnrolleeName.Text 'Enrolle_Name
                    .Parameters.Add("@course", SqlDbType.VarChar).Value = cmbCourse.Text 'Course
                    .Parameters.Add("@rebates", SqlDbType.Int).Value = lblRebate.Text 'Rebates
                    .Parameters.Add("@transdate", SqlDbType.DateTime).Value = DateAndTime.Now
                    .Parameters.Add("@user", SqlDbType.VarChar).Value = lblUsername.Text
                    .Parameters.Add("@status", SqlDbType.VarChar).Value = "Standby"
                End With
                Try
                    comm.ExecuteNonQuery()
                    conn.Close()
                    loadmyGrid()
                    loadDefault()
                Catch ex As Exception
                    MessageBox.Show(ex.Message.ToString(), "Error Message")
                End Try
            End Using
        End Using

TransCode是我的列名。我想在每个交易中添加1,2,3,4,5。我将其用作我的事务编号。

Answer 1

在将数据插入到表中之前，您可以做什么有问题的

Select Max(TranCode) From marketingLog

它将第一次返回某个整型数或0。

只需将其递增1即可。

然后执行插入。

实际上我不知道如何用vb编写代码，但我正在尝试用你的代码编写逻辑，请参考它，并尝试使用vb应用

例如

    Dim query As String = String.Empty

    query = "Select Max(TranCode) From marketingLog";

    Dim dtb As New DataTable;

    Using conn As New SqlConnection(dbUse.db)
    conn.Open()

    Using dad As New SqlDataAdapter(query, conn)
    dad.Fill(dtb)

    Dim intTransNo As Integer  =  Int32.Parse(dtb.Rows(0)(0).ToString()) + 1;

    query = "insert into marketingLog(TransCode,Endorser,Enrollee_Name,Course,Rebates,Trans_Date,CreatedBy,Status)" _
        & "values(@transcode,@endorser,@name,@course,@rebates,@transdate,@user,@status)"


        Using comm As New SqlCommand
            With comm
                .Connection = conn
                .CommandType = CommandType.Text
                .CommandText = query
                .Parameters.Add("@transcode", SqlDbType.VarChar).Value = intTransNo
                .Parameters.Add("@endorser", SqlDbType.VarChar).Value = cmbEndorser.Text 'Endorser
                .Parameters.Add("@name", SqlDbType.VarChar).Value = txtEnrolleeName.Text 'Enrolle_Name
                .Parameters.Add("@course", SqlDbType.VarChar).Value = cmbCourse.Text 'Course
                .Parameters.Add("@rebates", SqlDbType.Int).Value = lblRebate.Text 'Rebates
                .Parameters.Add("@transdate", SqlDbType.DateTime).Value = DateAndTime.Now
                .Parameters.Add("@user", SqlDbType.VarChar).Value = lblUsername.Text
                .Parameters.Add("@status", SqlDbType.VarChar).Value = "Standby"
            End With
            Try
                comm.ExecuteNonQuery()
                conn.Close()
                loadmyGrid()
                loadDefault()
            Catch ex As Exception
                MessageBox.Show(ex.Message.ToString(), "Error Message")
            End Try
        End Using
    End Using

Answer 2

CREATE TABLE Persons
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
)

您可以在表中使用AUTO_INCREMENT列。您不需要为该列指定任何数据。此列的数据将在插入新记录时自动插入。

INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen')

Check the fiddle here.

您的代码:您只需跳过将数据放入Auto_Increment列。

Dim query As String = String.Empty

    //query = "Select Max(TranCode) From marketingLog";

    Dim dtb As New DataTable;

    Using conn As New SqlConnection(dbUse.db)
    conn.Open()


    query = "insert into marketingLog(Endorser,Enrollee_Name,Course,Rebates,Trans_Date,CreatedBy,Status)" _
        & "values(@endorser,@name,@course,@rebates,@transdate,@user,@status)"


        Using comm As New SqlCommand
            With comm
                .Connection = conn
                .CommandType = CommandType.Text
                .CommandText = query
                                .Parameters.Add("@endorser", SqlDbType.VarChar).Value = cmbEndorser.Text 'Endorser
                .Parameters.Add("@name", SqlDbType.VarChar).Value = txtEnrolleeName.Text 'Enrolle_Name
                .Parameters.Add("@course", SqlDbType.VarChar).Value = cmbCourse.Text 'Course
                .Parameters.Add("@rebates", SqlDbType.Int).Value = lblRebate.Text 'Rebates
                .Parameters.Add("@transdate", SqlDbType.DateTime).Value = DateAndTime.Now
                .Parameters.Add("@user", SqlDbType.VarChar).Value = lblUsername.Text
                .Parameters.Add("@status", SqlDbType.VarChar).Value = "Standby"
            End With
            Try
                comm.ExecuteNonQuery()
                conn.Close()
                loadmyGrid()
                loadDefault()
            Catch ex As Exception
                MessageBox.Show(ex.Message.ToString(), "Error Message")
            End Try
        End Using
    End Using