Solved

adding row to access database with autoincrement column

Posted on 2013-01-14
5
1,755 Views
Last Modified: 2013-01-15
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
0
Comment
Question by:mgmhicks
  • 2
  • 2
5 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38775675
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
 

Author Comment

by:mgmhicks
ID: 38776080
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
 
LVL 83

Assisted Solution

by:CodeCruiser
CodeCruiser earned 250 total points
ID: 38776116
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
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 250 total points
ID: 38776127
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
 

Author Closing Comment

by:mgmhicks
ID: 38778393
that did it, thank you
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question