Solved

adding row to access database with autoincrement column

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

Accepted Solution

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

630 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