Solved

adding row to access database with autoincrement column

Posted on 2013-01-14
5
1,766 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

740 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