如何在插入语句期间从C#中的Access数据库获取下一个自动增量值 [英] How to get next auto increment value from access database in c# during insert statement

问题描述

我当前正在从用C#编写的Windows窗体应用程序输入数据.我的数据库是Microsoft Access数据库.一切工作正常，我接受复制主键值时不断出错.我已经在互联网上闲逛了一段时间，却发现与我的问题无关.

I am currently entering data from a Windows Form Application written in C#. My Database is a Microsoft Access Database. Everything is working fine accept I keep getting errors for duplicating my Primary Key value. I have been digging around the internet for a while and haven't found much pertaining to my issue.

我当前的表格设置如下:

My current table setup is below:

Field Name     Data
-------------------
ID             AutoNumber
MonsterName    Text
Drop           Text
AmountDropped  Text

下面是我当前的SQL查询:

And my current SQL Query is below:

"INSERT INTO MonsterDrops (MonsterName, Drop, AmountDropped) VALUES ('" + Monsters.SelectedCells[0].Value.ToString() + "','" + DropName + "'," + Amount + ")"

这给了我错误:"INSERT INTO语句中的语法错误"

This gives me the error: "Syntax Error in INSERT INTO statement"

我正在使用此功能执行查询:

I am executing the query with this function:

private void executeNonQuery(string query)
    {
        //create a new connection and pass in config settings for it
        OleDbConnection db = new OleDbConnection(Properties.Settings.Default.DFUWDBConnectionString);
        try
        {
        //Open the DB
        db.Open();

            try
            {
            //create a new command object
            OleDbCommand command = new OleDbCommand();

            //set the command to be your query
            command.CommandText = query;

            //set the DB for the query to be performed on
            command.Connection = db;

            //execute the insert
            command.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            MessageBox.Show(e.Message);
        }
    }
    catch (Exception e)
    {
        MessageBox.Show(e.Message);
    }
    finally
    {
        db.Close();
    }
}

我了解到，如果Identity设置为true，则不必包括AutoNumber字段，不确定是否以这种方式设置了我的编号.如何在运行SQL查询时使AutoNumber函数起作用??

I read that you don't have to include the AutoNumber field if Identity is set to true, not sure if mine is set that way or not. How can I get the AutoNumber function to work while running a SQL Query???

谢谢！

推荐答案

自动编号使您不必费心获得下一个值. AutoNumber应该不会给您带来任何插入问题，因为您没有使用它的字段集，它会自动获取下一个要插入的值.

Auto-number facilitates you to not bother about getting next value. AutoNumber should not be offering you any problem in insert as you are not using it field set, it gets next value automatically for insertion.

首先尝试

"INSERT INTO MonsterDrops (MonsterName, Drop, AmountDropped) VALUES 
('BOOGYMAN', 'BOOGERS', '1')";

然后尝试这个

"INSERT INTO MonsterDrops (MonsterName, Drop, AmountDropped) VALUES
 ('" + Monsters.SelectedCells[0].Value.ToString() + "','" + DropName + "'
,'" + Amount + ")"'"

如果您在第二个中发现问题，那么您所获得的值就会立即出现问题

If you find problem in the 2nd one then you have straight away problem in the values you are getting

否则，如果即使在第一个查询中也遇到问题，则应尝试这种方式

Otherwise if you get problem even in the first query, then you should try this way

将您的ID字段设置为Number而不是自动编号，然后尝试

Make your ID field Number instead of auto-number and try

"INSERT INTO MonsterDrops (ID,MonsterName, Drop, AmountDropped) VALUES 
(2222, 'BOOGYMAN', 'BOOGERS', '1')";

如果您通过这种方式获得成功，那么您必须阅读有关自动编号的更多信息，并且您将很容易解决问题.如果即使遇到这种问题，我也将为您提供进一步的帮助.

If you get succeeded this way, then you have to read more about auto-number and you will easily fix your problem, If you get problem even with this one, I am ready to help further.

修改 来自注释:将您的Drop命名字段更改为Drop111或Drop以外的名称.因为Drop是SQL中的关键字.

Edit From comments: Change your Drop named field to Drop111 or something other than Drop. Because, the Drop is keyword in SQL.

这篇关于如何在插入语句期间从C#中的Access数据库获取下一个自动增量值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！