mgmhicks
asked on
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
I could use some light on this.
thanks
ASKER
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).RowSt ate
Case Is = DataRowState.Added
myCMD.Parameters.Clear()
With myCMD
.Connection = AccessConnection
.CommandText = "AddInspectionDetails"
.CommandType = CommandType.StoredProcedur e
.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("@ItemDesci ption", 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("@ResidentC harge", myRow.Item(8)))
.Parameters.Add(New OleDbParameter("@LastEditD ate", myRow.Item(9)))
.Parameters.Add(New OleDbParameter("@LastEditB y", 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.StoredProcedur e
.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
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).RowSt
Case Is = DataRowState.Added
myCMD.Parameters.Clear()
With myCMD
.Connection = AccessConnection
.CommandText = "AddInspectionDetails"
.CommandType = CommandType.StoredProcedur
.Parameters.Add(New OleDbParameter("@ID", myRow.Item(0)))
.Parameters.Add(New OleDbParameter("@WO_Number
.Parameters.Add(New OleDbParameter("@ItemID", myRow.Item(2)))
.Parameters.Add(New OleDbParameter("@ItemGroup
.Parameters.Add(New OleDbParameter("@ItemDesci
.Parameters.Add(New OleDbParameter("@ItemQty",
.Parameters.Add(New OleDbParameter("@ItemCost"
.Parameters.Add(New OleDbParameter("@ItemPrice
.Parameters.Add(New OleDbParameter("@ResidentC
.Parameters.Add(New OleDbParameter("@LastEditD
.Parameters.Add(New OleDbParameter("@LastEditB
.Parameters.Add(New OleDbParameter("@ItemHours
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.StoredProcedur
.Parameters.Add(New OleDbParameter("@WONumber"
.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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
that did it, thank you
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