?
Solved

adding row to access database with autoincrement column

Posted on 2013-01-14
5
Medium Priority
?
1,835 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 48

Expert Comment

by:Dale Fye
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 1000 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 48

Accepted Solution

by:
Dale Fye earned 1000 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

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.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

719 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