database

[pavol]

hi
i have a very simple part in my program where the user types something in a text box and they click save to save it in a database, but an error appears reading something like insufficient key column information for update or refresh. i've never seen this error and no matter what i do i can't seem to fix it. somewhere else in my program i do the exact same thing but no error appears. this is wierd. also, the database i'm using is in MS Access and i have created it during runtime.
any ideas

[Monstrosity_]

pavol wrote:

...but an error appears reading something like insufficient key column information for update or refresh.

An EXACT qoute and/or error number would have been better.

pavol wrote:

i've never seen this error and no matter what i do i can't seem to fix it. somewhere else in my program i do the exact same thing but no error appears.

And how can we be sure, since you havnt posted either? Also Missing is the library / control you are using.

My guess is when you create your table, you are not specifying which field is to be the primary key. The primary key is a field/group of fields in a table which uniquely identifies a record. Try google for a better definition if you like.

[pavol]

This is the exact error i get if i use the code:

code:

adoCal.Recordset.Fields("Entry") = entry adoCal.Recordset.Update

Where 'entry' is a variable that stores the string that i want to put in the database.
Run-Time Error:"-2147467259 (80004005)- Insufficient key column information for updating or refreshing"
I'm using an Adodc data control and the database i'm trying to save info into has been created during runtime (that might be the problem as i'm not sure how to set a primary key in a table using code). This is the code i use to create the database

code:

Dim Calendar As Database         Dim January As TableDef     Dim February As TableDef     Dim March As TableDef     Dim April As TableDef     Dim May As TableDef     Dim June As TableDef     Dim July As TableDef     Dim August As TableDef     Dim September As TableDef     Dim October As TableDef     Dim November As TableDef     Dim December As TableDef         Dim day(0 To 11) As Field     Dim extension(0 To 11) As Field     Dim entry(0 To 11) As Field     '     'create database     Set Calendar = CreateDatabase(App.Path & "\Databases\" & txt(11).Text & " Calendar.mdb", dbLangGeneral)         'Create tables         Set January = Calendar.CreateTableDef("January")             'create fields             Set day(0) = January.CreateField("Day", dbMemo, 2)             Set extension(0) = January.CreateField("Extension", dbMemo, 2)             Set entry(0) = January.CreateField("Entry", dbMemo, 2)                             'add fields to table                 January.Fields.Append day(0)                 January.Fields.Append extension(0)                 January.Fields.Append entry(0)                                     'add table to databse                     Calendar.TableDefs.Append January                         'Add to the progress bar.     pbrCreate.Value = pbrCreate.Value + 1                                     Set February = Calendar.CreateTableDef("February")             'create fields             Set day(1) = February.CreateField("Day", dbMemo, 2)             Set extension(1) = February.CreateField("Extension", dbMemo, 2)             Set entry(1) = February.CreateField("Entry", dbMemo, 2)                     'add fields to table                 February.Fields.Append day(1)                 February.Fields.Append extension(1)                 February.Fields.Append entry(1)                                     'add table to databse                     Calendar.TableDefs.Append February     'SAME CODE FOR THE REST OF THE MONTHS MARCH..DECEMBER                                         Calendar.Close     'Add to the progress bar.     pbrCreate.Value = pbrCreate.Value + 1     'after creation, put the necessary info into calendar database     adoCal.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Databases\" & txt(11).Text & " Calendar.mdb;Mode=ReadWrite|Share Deny None;Persist Security Info=False"     adoCal.CommandType = 2 - adCmdTable     adoCal.RecordSource = "January"     adoCal.Refresh         Dim ending As String     Dim i As Byte             ending = ""             For i = 1 To 31         adoCal.Recordset.AddNew         adoCal.Recordset.Fields("Day") = i                 If i = 1 Then ending = "st"         If i = 2 Then ending = "nd"         If i = 3 Then ending = "rd"         If i > 3 And i < 21 Then ending = "th"         If i = 21 Then ending = "st"         If i = 22 Then ending = "nd"         If i = 23 Then ending = "rd"         If i > 23 Then ending = "th"         If i = 31 Then ending = "st"                 adoCal.Recordset.Fields("Extension") = ending     Next i         adoCal.Recordset.Fields("Entry") = "january"         adoCal.Recordset.Update     'Add to the progress bar.     pbrCreate.Value = pbrCreate.Value + 1     '--------------------------------------     'AGAIN THE SAME CODE FOR THE REST OF THE MONTHS (TABLES)

i've tried updating using UpdateBatch, Update, and also tried going around somehow but that didn't work either. Once i addNew to the database, i can't seem to manipulate it in any way. i can move through the recordset at first but once i addNew i get the error if i try. Hope this isn't too confusing.

[Monstrosity_]

Some loops and arrays would clean that code up nice ;p
Theres just something im curious about, given that CreateDatabase() is not a part of any libraries you mentioned(where did it come from?), open up vb and goto "Add-ins" and select "Visual Data Manager". Open up your database and in the tree view the first item should say "Properties", expand it and tell me whats inside (screener will do if you like). Then, since your in there, right click on any of the tables and select design... tell me whats in there too.

[pavol]

these are the screen shots of the two things you requested.

[Monstrosity_]

One of two things comes to mind, you need an index that will act as your primary key (notice the check boxes beside the index listbox), or the "Entry" field is a required field with no default value and your not setting them all, since your only setting it once outside the loop. For information on the index, go [url=http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/office97/html/output/F1/D2/S5A25F.asp]
here[/url] and find "Index" in the left menu. Hopefully that does it.

[pavol]

i know it's been a long time since i posted here but i am still having problems . Monstrosity_ i thank u for your help , the thing is, i need a solution that involves code, because i can't have the user open up visual basic and start editing the database. is there any way to create a primary key using code?