Syntax Error using Command Object

[aikidokid]

I am trying to use, for the first time, the command object to insert dates into my table.
I have copied the code from the example in the FAQ's, replacing with my variables.

I am getting the error
"Syntax error in Insert Into statement"
when the execute line fires.

Now I have looked and looked and it looks the same setup at in the FAQ
In a way I hope it's simple, but then again that makes me look

Below are my code and the debug of the strSQL statement:

    strSQL = "INSERT INTO tbl_Grade (Yellow, Orange, Green, GreenBlack, Blue, BlueBlack, Brown, " & _
                            "First, Second, Third, Fourth, Fifth, Sixth, Seventh, Eighth) " & _
                            "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
    Debug.Print strSQL
    
    With adoCommand
        .ActiveConnection = cn
        .CommandType = adCmdText
        .CommandText = strSQL
        .Prepared = True
        
        .Parameters.Append .CreateParameter(, adDate, adParamInput, , GradeHistoryDates(0))
        .Parameters.Append .CreateParameter(, adDate, adParamInput, , GradeHistoryDates(1))
        .Parameters.Append .CreateParameter(, adDate, adParamInput, , GradeHistoryDates(2))
        .Parameters.Append .CreateParameter(, adDate, adParamInput, , GradeHistoryDates(3))
        .Parameters.Append .CreateParameter(, adDate, adParamInput, , GradeHistoryDates(4))
        .Parameters.Append .CreateParameter(, adDate, adParamInput, , GradeHistoryDates(5))
        .Parameters.Append .CreateParameter(, adDate, adParamInput, , GradeHistoryDates(6))
        .Parameters.Append .CreateParameter(, adDate, adParamInput, , GradeHistoryDates(7))
        .Parameters.Append .CreateParameter(, adDate, adParamInput, , GradeHistoryDates(8))
        .Parameters.Append .CreateParameter(, adDate, adParamInput, , GradeHistoryDates(9))
        .Parameters.Append .CreateParameter(, adDate, adParamInput, , GradeHistoryDates(10))
        .Parameters.Append .CreateParameter(, adDate, adParamInput, , GradeHistoryDates(11))
        .Parameters.Append .CreateParameter(, adDate, adParamInput, , GradeHistoryDates(12))
        .Parameters.Append .CreateParameter(, adDate, adParamInput, , GradeHistoryDates(13))
        .Parameters.Append .CreateParameter(, adDate, adParamInput, , GradeHistoryDates(14))
        
        .Execute , , adCmdText + adExecuteNoRecords
    End With

INSERT INTO tbl_Grade (Yellow, Orange, Green, GreenBlack, Blue, BlueBlack, Brown, First, Second, Third, Fourth, Fifth, Sixth, Seventh, Eighth) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

[si_the_geek]

"FIRST" is a reserved word, and some of the others might be too.

Looking at it now, why have you got a table arranged like that.. what data is going to be stored in it (I know dates, but what do they relate to - there is no field which is a 'parent')?

[brucevde]

Which database, OLEDB provider are you using?

First and Second maybe keywords. Surround them with square brackets.

[aikidokid]

"FIRST" is a reserved word, and some of the others might be too.

To be honest, I never thought of that. The names are easy enough to change.

Looking at it now, why have you got a table arranged like that.. what data is going to be stored in it (I know dates, but what do they relate to - there is no field which is a 'parent')?

The dates relate to the date that specific examination was passed.
The fields shown are the grade levels. There are 3 other fields in the table;

• Memb_ID
• Grade
• SortOrder

Memb_ID is the primary key
Grade is the current grade of the member
SortOrder is used to sort the listview in order of grades.

I have just realised that I forgot to add a WHERE clause when changing from the previous way I was trying to do this.

Would this be a straight forward add the following to the end: (field names to be changed)

    strSQL = "INSERT INTO tbl_Grade (Yellow, Orange, Green, GreenBlack, Blue, BlueBlack, Brown, " & _
                            "First, Second, Third, Fourth, Fifth, Sixth, Seventh, Eighth) " & _
                            "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
    strSQL = strSQL & " WHERE Memb_ID = " & lngMembID

[si_the_geek]

An Insert can't have a Where clause... it seems you forgot to change the example SQL to an Update (as with the Insert, simply put the ? where the values should go).


As to the table design, I'm confused!

First off, I have no idea why you have got a separate column for each grade, and would strongly recommend against it - it will almost certainly cause lots of extra work.

I remember the SortOrder field, but that was supposed to be for a lookup table - which contains just the GradeName, SortOrder, preferably GradeID, and if apt any data about the grade itself - not anything to do with any other data (like members).

For storing info about which members have achieved which grade, you should have a table with fields like this:
Memb_ID
GradeName (or preferably GradeID if you have it)
DateAchieved

[aikidokid]

An Insert can't have a Where clause... it seems you forgot to change the example SQL to an Update (as with the Insert, simply put the ? where the values should go).

OK, putting the field names to one side for a minute, would this SQL look right to you?
I got an error just now saying 'Missing semicolon'
I am using Access 2003 and ADO.

    strSQL = "UPDATE tbl_Grade (YellowK, OrangeK, GreenK, GreenBlackK, BlueK, BlueBlackK, BrownK, " & _
                            "FirstD, SecondD, ThirdD, FourthD, FifthD, SixthD, SeventhD, EighthD) " & _
                            "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)" & _
                            " WHERE Memb_ID = " & MembID & ";"

As to the table design, I'm confused!

First off, I have no idea why you have got a separate column for each grade, and would strongly recommend against it - it will almost certainly cause lots of extra work.

I remember the SortOrder field, but that was supposed to be for a lookup table - which contains just the GradeName, SortOrder, preferably GradeID, and if apt any data about the grade itself - not anything to do with any other data (like members).

For storing info about which members have achieved which grade, you should have a table with fields like this:
Memb_ID
GradeName (or preferably GradeID if you have it)
DateAchieved

There's a lot more to this database design that I ever thought there was.
Before, if it worked I was happy with it, but now I am re-writing large parts of my project, so I would like it to be the best way possible.
So, my understanding of the database would be this;

tbl_Membership
This holds personal details about the member, address, DOB ect

tbl_Grade
Memb_ID
Grade_ID
Grade_DateAchieved

tbl_GradeSortOrder
Memb_ID
SortOrder
Grade_ID

Then link all the tables by the Memb_ID field?

Should I code to add the Grades to a Public numeric array, i.e.
White = 0
Yellow = 1
etc

[aikidokid]

This is what I now have in the DB (not in my code yet though )

[si_the_geek]

OK, putting the field names to one side for a minute, would this SQL look right to you?

Unfortunately not.. an Update doesn't have field list and Values list, you need to split it out into a Set clause.

So, my understanding of the database would be this;
...

tbl_Membership is fine, as long as there is only one row of data per member, and Memb_ID is unique (possibly AutoNumber).

I would change the name of tbl_GradeSortOrder to tbl_GradeInfo or just tbl_Grade. This table should not contain Memb_ID, as described in my post above - it should only be the Grade itself, eg:
Grade_ID (possibly AutoNumber)
Grade_Name
SortOrder

The table you called tbl_Grade I would call something like tbl_MemberGrade; the fields you listed are fine. This table provides the link between the other two, and means that you aren't duplicating any data (or leaving blanks).

Should I code to add the Grades to a Public numeric array, i.e.
White = 0
Yellow = 1
etc

No, you should always get the data from the database.

That way if anything ever changes, you only need to change it in one place (a particular row in a particular table), rather than in various places (multiple times in the program, and in the database).


If you want to put it into a combobox/listbox for the user to select, show the text and store the ID to the ItemData (as done by the routine in the combo/list FAQ), and use the ItemData when talking to the database.

If you want to display the text in query results, you add a join to your query.

[aikidokid]

Thanks si_the_geek

I will get to this tomorrow, off out soon.

tbl_Membership is fine, as long as there is only one row of data per member, and Memb_ID is unique (possibly AutoNumber).

Yes and Yes

This is going to be a fair bit of work changing all of the calls to the database, but I think I do understand the reasons behind it all, less wasted space, better performance, better clarity.

[aikidokid]

What are you storing in GradeName in the tbl_GradeInfo (lookup table)
Grade_ID is the grade level
Grade_SortOrder is the order to be sorted in.

[si_the_geek]

Grade_ID would be a reference number (eg: 0, 1, ...)

Grade_Name would be the name of the grade (ie: White, Yellow, ...)


If you do it like that, it takes less space (as text takes more space than numbers), and means that if the names ever need to change, you only have to change them in one record in one [short] table (rather than every record of every [potentially long] table that links to it!).

[aikidokid]

In my database I now have the following:

tbl_GradeInfo
Grade_ID
Grade_SortOrder
Grade_Name

tbl_MembersGrade
Memb_ID
Grade_ID
Grade_DateAchieved

But what I cannot now do is display the members history of their gradings and the dates they took the examination. This is why I originally had all of the colours in it's own field, to add the date of passing that grade level.

How would be the best way to add this to the database without adding all the individual grades, or would I have to?

[si_the_geek]

The idea is that you have one record in tbl_Member for each member, one in tbl_GradeInfo for each grade (so a total of 15ish), and multiple records in tbl_MembersGrade for each person or grade (one record for each grade a person has achieved).

So for one member (say Memb_id =1), you might have data like this:

Memb_ID  Grade_ID  Grade_DateAchieved
1        2         1 March 2003
1        3         4 June 2004
1        4         7 April 2007 
...

To return that data along with the member details and grade name, you would use a Join, like this:

SELECT tbl_Member.First_Name, ... , tbl_GradeInfo.Grade_Name, tbl_MembersGrade.Grade_DateAchieved

FROM tbl_Member
LEFT JOIN tbl_MembersGrade ON tbl_MembersGrade.Memb_ID = tbl_Member.Memb_ID
INNER JOIN tbl_GradeInfo ON tbl_GradeInfo.Grade_ID = tbl_MembersGrade.Grade_ID

WHERE ...

ORDER BY tbl_GradeInfo.Grade_SortOrder

The INNER Join means there must be data in both tables, otherwise no data is returned from either of them. This is the usual style of join, and is safe to use for Grade as each MemberGrade record must point to an actual Grade.

The LEFT Join means that if there isn't data in the second table (MembersGrade), the data from the first will still be shown. You will probably want that, so that you still get data even when they haven't got a grade yet.

How would be the best way to add this to the database without adding all the individual grades, or would I have to?

If you mean that you have the data in another table at the moment, and want to transfer it across.. it is probably best if I write an SQL statement for you.

If that is the case, get tbl_GradeInfo and tbl_Member filled (preferably with tbl_Member having the same MembID value for each record as the original), and we can discuss it further.

[aikidokid]

Thanks again si.

This all makes sense to me, and I did think of the way you have set out tbl_MemberGrade.
What did confuse me, and if you could explain a bit further please, is the use of Primary Keys.

I have the Memb_ID field in the tbl_Membership as the Primary Key.

I was of the understanding that every table should have a primary key, so I set Memb_ID as the primary key in the tbl_MemberGrade, so I couldn't do what you suggested above.

Do all tables have to have a primary key?
If so, what would I do about the tbl_MemberGrade?

Thanks again

[si_the_geek]

Do all tables have to have a primary key?

Not exactly "have to", but definitely "should".

This is so that you can uniquely identify each row (and so safely edit/delete them without affecting other rows), and stop accidental duplication of data.

Something that you may not be aware yet of is that a Key doesn't have to map directly to a single field...

If so, what would I do about the tbl_MemberGrade?

I would recommend creating what is known as a Compound key (uses more than one field), using the MembID and GradeID fields.

This will stop you from entering two rows for the same thing - so you can't enter two sets of values for "Joe Bloggs" getting a "Yellow" grade (but can enter multiple grades for "Joe Bloggs", and "Yellow" grades for multiple people).

I'm not exactly sure how to set it up in Access.. I think you go into the table design and select both fields (using Shift or Ctrl) and then press the 'primary key' button.

Thanks again

No problem

[aikidokid]

That was it with Access

This is what I now have.
Does this look right to you now?

[si_the_geek]

Almost - the relationship should be one-to-many (MemberGrade being the many).

[aikidokid]

Almost - the relationship should be one-to-many (MemberGrade being the many).

How do I change that? Is it editing the join type?

[aikidokid]

It's ok found out, some how, but it's done.