Problem with Insert Query

[Loony064]

Hey All

I'm busy with a program in VB.Net 2008 that performs sales. When a user clicks on the Invoice button, a record with the details of the Transaction is addedd to the Transactions table in the back-end MS Access 2007 database.

The Transactions table has the following fields:

LogID - Autonumber, tables primary key
Code - Text (field size 6)
ItemCode (field size 13)
ItemDesc (field size 100)
Cost (Decimal)
Quantity (integer)
Date1 (Date/Time Field set to General Date)

The following query is used to insert a record into the transactions table:
INSERT INTO `Transactions`
(`Code`, `ItemCode`, `ItemDesc`, `Quantity`, `Cost`, `Date1`) VALUES (?, ?, ?, ?, ?, ?)

It works perfectly when i run it from the database but when i use it in the program it gives me the following error:
"The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data."

If i create an insert query to insert all but the Date1 field, it works perfectly and if I create a query to insert just the date it works perfectly, but when I create a combination of any of the other fields with the date field, I get a Type Mismathc error (and if I use Date1 and Code together, i get the "The field is too small..." error).

I've tried changing the sizes of the textfields in the database to 255 with no luck. I also tried another approach to adding the record. I could insert all the fields except for Date1 and then update the Date1 field of the table where the other fields (in the table) match the values that I used to Insert the record. The problem with this is that on two seperate days, the same person might buy the same quantity of the same item for the same price and resulting in there being more than one matching record and causing more problems. Thus, in order for this method to work, I would have to update the record where the primary key (LogID) matches, but since this is an autonumber field, I'm not exaclty sure how to reference the record that I've just inserted.

Obviously I'd rather fix the "The field is to small..." error, but I'm willing to use this seconde method as an alternative if I can't come right with the first method.

If anyone has any ideas or suggestions as to how I can either fix the errors or go about the second method, I'd really appreciate it.

Regards
Laura

[kvprajapati]

ERROR: "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data."

-- Your program passing long/large value then actual size of column.

[Loony064]

My apologies for being an idiot. I managed to fix the problem. For some reason when I coded this originally, I got it into my head that I need an SQL query to insert the record into the table, even though I had already used VB to create a variable to store a new row in the table. (All the values I needed to add were stored in row.field e.g. row.Cost) All I had to do was add the row to the dataset.

Here is the code I used:

' create new row
Dim row As TuckAssistDataSet.TransactionsRow
row = TuckAssistDataSet.Transactions.NewRow
        
' add values to fields            
row.ItemCode = DataGrid.Item(0, i).Value()
row.ItemDesc = DataGrid.Item(1, i).Value()
row.Quantity = CInt(DataGrid.Item(3, i).Value())
row.Cost = CDec(DataGrid.Item(4, i).Value())
row.Code = TxtAccountNum.Text
row.Date1 = DateTime.Now()

' add row to database
TuckAssistDataSet.Transactions.AddTransactionsRow(row)

Thanks for your help anyway adatapost. It was really appreciated!