adding row to access database with autoincrement column

I have a access procedure that worked with my vb.net code, to add a record to the table.  However I added another column at the end of the table that is a auto increment column, now my procedure is telling parameters and fields do not match.  I thought If I set a autoincrement column and put it last, and because its autoincrement, I did not need a parameter to it.

I could use some light on this.

thanks
mgmhicksAsked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
If you don't include the column names in the INSERT INTO line of the query, the assumption is that you are inserting values into all of the columns, but you are not inserting into the last column.  Assuming that the field names in the table match the parameter names, try modifying the query:

PARAMETERS [@ID] Long, [@WO_Number] Text ( 255 ), [@ItemID] Text ( 255 ), [@ItemGroup] Text ( 255 ), [@ItemDescription] Text ( 255 ), [@ItemQty] IEEEDouble, [@ItemCost] IEEEDouble, [@ItemPrice] IEEEDouble, [@ResidentCharge] Text ( 255 ), [@LastEditDate] DateTime, [@LastEditBy] Text ( 255 ), [@ItemHours] IEEEDouble;
INSERT INTO InspectionsDetails (WO_Number, ItemID, ItemGroup, ItemDescription, ItemQty, ItemCost, ItemPrice, ResidentCharge, LastEditDate, LastEditBy, ItemHours)
VALUES ([@ID], [@WO_Number], [@ItemID], [@ItemGroup], [@ItemDescription], [@ItemQty], [@ItemCost], [@ItemPrice], [@ResidentCharge], [@LastEditDate], [@LastEditBy], [@ItemHours]);
0
 
Dale FyeCommented:
Could you provide an example of the SQL you are using and provide the error message that you are getting when you attempt to append to the table?

The general syntax for an Insert (append) query is:

INSERT INTO yourTable (Field1, Field2, Field3)
Values (Value1, Value2, Value3)

In this instance Value1-3 would need to be delimited with quotes for strings or # for dates.  If Field#n is an Auto-increment field, then you should not include it in either of the rows of the Insert syntax
0
 
mgmhicksAuthor Commented:
Here is the access procedure.

PARAMETERS [@ID] Long, [@WO_Number] Text ( 255 ), [@ItemID] Text ( 255 ), [@ItemGroup] Text ( 255 ), [@ItemDescription] Text ( 255 ), [@ItemQty] IEEEDouble, [@ItemCost] IEEEDouble, [@ItemPrice] IEEEDouble, [@ResidentCharge] Text ( 255 ), [@LastEditDate] DateTime, [@LastEditBy] Text ( 255 ), [@ItemHours] IEEEDouble;
INSERT INTO InspectionsDetails
VALUES ([@ID], [@WO_Number], [@ItemID], [@ItemGroup], [@ItemDescription], [@ItemQty], [@ItemCost], [@ItemPrice], [@ResidentCharge], [@LastEditDate], [@LastEditBy], [@ItemHours]);

This table does have a column call IdExternal that is a autoincrement, but I dont have the column mentioned in access procedure.

Here is the Vb.net code

Select Case ds.Tables(1).Rows(i).RowState
                    Case Is = DataRowState.Added
                        myCMD.Parameters.Clear()
                        With myCMD
                            .Connection = AccessConnection
                            .CommandText = "AddInspectionDetails"
                            .CommandType = CommandType.StoredProcedure
                            .Parameters.Add(New OleDbParameter("@ID", myRow.Item(0)))
                            .Parameters.Add(New OleDbParameter("@WO_Number", myRow.Item(1)))
                            .Parameters.Add(New OleDbParameter("@ItemID", myRow.Item(2)))
                            .Parameters.Add(New OleDbParameter("@ItemGroup", myRow.Item(3)))
                            .Parameters.Add(New OleDbParameter("@ItemDesciption", myRow.Item(4)))
                            .Parameters.Add(New OleDbParameter("@ItemQty", myRow.Item(5)))
                            .Parameters.Add(New OleDbParameter("@ItemCost", myRow.Item(6)))
                            .Parameters.Add(New OleDbParameter("@ItemPrice", myRow.Item(7)))
                            .Parameters.Add(New OleDbParameter("@ResidentCharge", myRow.Item(8)))
                            .Parameters.Add(New OleDbParameter("@LastEditDate", myRow.Item(9)))
                            .Parameters.Add(New OleDbParameter("@LastEditBy", myRow.Item(10)))
                            .Parameters.Add(New OleDbParameter("@ItemHours", myRow.Item(11)))
                        End With
                        Try
                            AccessConnection.Open()
                            myCMD.ExecuteNonQuery()
                            AccessConnection.Close()
                        Catch ex As Exception
                            If AccessConnection.State = ConnectionState.Open Then
                                AccessConnection.Close()
                            End If
                            MsgBox(ex.Message)
                            SaveInspectionDetails = False
                            Exit Function
                        End Try
                    Case Is = DataRowState.Modified
                        MsgBox("row modified")
                    Case Is = DataRowState.Deleted
                        '            MsgBox("row deleted")
                        With myCMD
                            .Connection = AccessConnection
                            .CommandText = "RemoveInspectionDetail"
                            .CommandType = CommandType.StoredProcedure
                            .Parameters.Add(New OleDbParameter("@WONumber", Trim(myRow.Item(1, DataRowVersion.Original))))
                            .Parameters.Add(New OleDbParameter("@ItemID", myRow.Item(2, DataRowVersion.Original)))
                        End With
                        AccessConnection.Open()
                        myCMD.ExecuteNonQuery()
                        AccessConnection.Close()
                        '  myRow.AcceptChanges()
                    Case Is = DataRowState.Unchanged
                        '            MsgBox("row unchanged")
                End Select
            Next
            SaveInspectionDetails = True

        Catch ex As Exception

            If AccessConnection.State = ConnectionState.Open Then
                AccessConnection.Close()
            End If
            MsgBox(ex.Message)
            SaveInspectionDetails = False
        End Try

thanks
0
 
CodeCruiserConnect With a Mentor Commented:
You need to change this

INSERT INTO InspectionsDetails
VALUES ([@ID], [@WO_Number], [@ItemID], [@ItemGroup], [@ItemDescription], [@ItemQty], [@ItemCost], [@ItemPrice], [@ResidentCharge], [@LastEditDate], [@LastEditBy], [@ItemHours]);

and add column names like

INSERT INTO InspectionsDetails(Wo_Number, ItemGroup, ...)
VALUES ([@ID], [@WO_Number], [@ItemID], [@ItemGroup], [@ItemDescription], [@ItemQty], [@ItemCost], [@ItemPrice], [@ResidentCharge], [@LastEditDate], [@LastEditBy], [@ItemHours]);
0
 
mgmhicksAuthor Commented:
that did it, thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.