MS Acces Autonumber

teddyk · October 22nd, 2008, 09:42 AM

Hi

Im trying to insert a record into ms access table and im using an autonumber as my primary key. My code goes something like this:

       strSQL = "INSERT INTO stfEntry VALUES(" & Label10.Text & "," &sl.Text & ",'" & FnL.Text & "','" & LnL.Text & "','" & C1.TodaysDate & "','" & C2.TodaysDate & "')"
        oledbcon.Open()
        Dim cmd As New OleDbCommand(strSQL, oledbcon)
        Dim i As Integer = cmd.ExecuteNonQuery()
        cmd.CommandText = "SELECT MAX(entryid) FROM stfEntry"
        Dim id As Integer = cmd.ExecuteScalar()
        Me.Label10.Text = id+1
        cmd.Dispose()
        oledbcon.Close()

But it issues Syntax error in the Insert statement. Please anybody help what can i do really. Thank all that help, wrox has been hekpful throughout my project, thanks a lot!

supertedz

gbianchi · October 22nd, 2008, 10:52 AM

Hello there.. If you are using an autonumeric field, then it will fill by itself, you don't have to fill in the SQL command. You will have to supply every field name and every field value, but not the autonumeric one.

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

Bob Bedell · October 22nd, 2008, 04:40 PM

Its been a long time since I worked with a Jet database, but I think:

1. Date values in dynamic SQL strings need to be enclosed in pound signs for Jet, like:

"INSERT INTO Tables1 ([Date],[Name]) VALUES(#" & Text1.Text & "#,'" & Text2.Text & "')"

2. And you can use @@Identity with Jet to retrive the last inserted autonumer value, like:

Dim idcmd As OleDbCommand = New OleDbCommand("SELECT @@IDENTITY", conn)
newid = CInt(idcmd.ExecuteScalar())

HTH,

Bob

Old Pedant · October 22nd, 2008, 07:23 PM

Bob wrote:
> "And you can use @@Identity with Jet to retrive the last inserted autonumber value..."

In fact, it's a HUGE mistake to not do so. If you don't do that, it's possible for somebody else to insert a record between the time you do your insert and the time you do your SELECT MAX(). Using

Code:

        cmd.CommandText = "SELECT @@IDENTITY"
        Dim id As Integer = cmd.ExecuteScalar()

is a much, much safer way to go.

********************

On another topic: If you really *DO* want today's date as the value of those fields, then do *NOT* pass it in from VB. Instead, just specify it in the SQL. And finally, you really really really should list the fields you are inserting to in your SQL query. So:

Code:

strSQL = "INSERT INTO stfEntry (someNumberField,anotherNumber,someTextFld,anotherText,someDate,anotherDate) " _
       & " VALUES(" & Label10.Text & "," & sl.Text & ",'" & FnL.Text & "','" & LnL.Text & "',DATE(),DATE() )"

****************

One more point: You aren't protecting yourself against SQL injection! Nor are you allowing people to enter apostrophes into the text field values.

I could show you how to fix that in the SQL String, but maybe it's time to start thinking about using command parameters. Solves both problems, and many more.

gbianchi · October 22nd, 2008, 07:34 PM

OP is working with access, are you sure that it has the @@Identity?? That's not only for SQL server?

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

Old Pedant · October 22nd, 2008, 08:19 PM

Access doesn't have @@IDENTITY, but the JET OLEDB driver does.

Remember, any similarity between queries in Access and queries via the Jet driver are almost accidental. The two were created by completely different groups at MS. Access-the-program can actually use VBA to invoke JET-the-driver, that's the funny part! The JET driver is, after all, just a DLL that becomes part of your executable program, same as any other DLL.

There are, for example, a handful of functions available in Access that are not available via the JET driver (presumably, they were overlooked by the JET team when creating it and nobody at MS cares enough to fix it). And, of course, there are some capabilities of JET that Access doesn't have. SELECT @@IDENTITY being one of them.

gbianchi · October 22nd, 2008, 08:51 PM

that's for all version of jet?? 3.5 and 4???

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

Bob Bedell · October 22nd, 2008, 09:07 PM

The Jet 4.0 OleDb Provider (shipped with Access 2000) added a lot of SQL Server compatible syntax (ANSI-92). You can also, for example, use Create Procedure syntax to create Access parameterized queries.

Yes, supertedz should loose the in-line SQL. Impossible to keep track of in code and a debugging hastle. Here's a way to use @@Identity with Access to get BOTH the rows affected AND the identity value that supertedz is after in one method call. Access parameterized queries don't, of course, support output parameters, but you can emulate them using method 'out' parameters.

Code:

static void Main(string[] args)
        {
            int identityValue;
            int rowsAffected = ProductDB.InsertProduct(out identityValue,(new Product(1, "mmmm", 1, 1, "10 per thing", 2, 1, 1, 3, true)));
        }

public static int InsertProduct(out int identityValue, Product product)
        {
            using (OleDbConnection connection = new OleDbConnection
                (String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data source={0}\\NorthWindTables.mdb", Application.StartupPath)))
            {
                connection.Open();
                using (OleDbCommand command = connection.CreateCommand())
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "Products_InsertProduct";

                    command.Parameters.AddWithValue("@productName", product.ProductName);
                    command.Parameters.AddWithValue("@supplierID", product.SupplierID);
                    command.Parameters.AddWithValue("@categoryID", product.CategoryID);
                    if (String.IsNullOrEmpty(product.QuantityPerUnit))
                    {
                        command.Parameters.AddWithValue("@quantityPerUnit", DBNull.Value);
                    }
                    else
                    {
                        command.Parameters.AddWithValue("@quantityPerUnit", product.QuantityPerUnit);
                    }
                    command.Parameters.AddWithValue("@unitPrice", product.UnitPrice);
                    command.Parameters.AddWithValue("@unitsInStock", product.UnitsInStock);
                    command.Parameters.AddWithValue("@unitsOnOrder", product.UnitsOnOrder);
                    command.Parameters.AddWithValue("@reorderLevel", product.ReorderLevel);
                    command.Parameters.AddWithValue("@discontinued", product.Discontinued);

                    // Do insert
                    int rowsAffected = command.ExecuteNonQuery();

                    // return identity value as a method output parameter
                    command.CommandType = CommandType.Text;
                    command.CommandText = "SELECT @@IDENTITY";
                    identityValue = (int)command.ExecuteScalar();

                    return rowsAffected;
                }
            }
        }

Rows afftected is the return value and the identity value is an out parameter.

Here's the parameterized Access query:

Code:

PARAMETERS 
  [@productName] Text ( 255 ), 
  [@supplierID] Long, 
  [@categoryID] Long, 
  [@quantityPerUnit] Text ( 255 ), 
  [@unitPrice] Currency, 
  [@unitsInStock] Long, 
  [@unitsOnOrder] Long, 
  [@reorderLevel] Long, 
  [@discontinued] Bit;
INSERT INTO Products ( 
  ProductName, 
  SupplierID, 
  CategoryID, 
  QuantityPerUnit, 
  UnitPrice, 
  UnitsInStock, 
  UnitsOnOrder, 
  ReorderLevel, 
  Discontinued )
SELECT 
  [@productName], 
  [@supplierID], 
  [@categoryID], 
  [@quantityPerUnit], 
  [@unitPrice], 
  [@unitsInStock], 
  [@unitsOnOrder], 
  [@reorderLevel], 
  [@discontinued];

The brackets around the parameter values are enforced by the Jet engine. Must use them.