Need Advice on coding INSERT statement

[pungy]

VB6- Enterprise Edition Database=MS Access 2003

I have coded the following, it works however I have a problem in this senerio. : txtHomeName and txtVisitorName are Control arrays. Anywhere from 1 to 16 can be "loaded". If I have 16, the Insert works but less than 16, the Insert errors with a Syntax Error.
Question: How can I code the insert statement if there is a variable number iniput fields?

SQL = "Insert into AllTeams (WeekNumber, HTeam1, VTeam1, HTeam2, VTeam2, HTeam3, VTeam3, "
SQL = SQL & "HTeam4, VTeam4,HTeam5, VTeam5,HTeam6, VTeam6,HTeam7, VTeam7,HTeam8, VTeam8, "
SQL = SQL & "HTeam9, VTeam9,HTeam10, VTeam10,HTeam11, VTeam11,HTeam12, VTeam12,HTeam13, VTeam13, "
SQL = SQL & "HTeam14, VTeam14,HTeam15, VTeam15, HTeam16, VTeam16) values("
SQL = SQL & Val(txtWeekNumber.Text) & ", "
SQL = SQL & "'" & txtHomeName(0).Text & "', "
SQL = SQL & "'" & txtVisitorName(0).Text & "', "
SQL = SQL & "'" & txtHomeName(1).Text & "', "
SQL = SQL & "'" & txtVisitorName(1).Text & "', "
SQL = SQL & "'" & txtHomeName(2).Text & "', "
SQL = SQL & "'" & txtVisitorName(2).Text & "', "
SQL = SQL & "'" & txtHomeName(3).Text & "', "
SQL = SQL & "'" & txtVisitorName(3).Text & "', "
SQL = SQL & "'" & txtHomeName(4).Text & "', "
SQL = SQL & "'" & txtVisitorName(4).Text & "', "
SQL = SQL & "'" & txtHomeName(5).Text & "', "
SQL = SQL & "'" & txtVisitorName(5).Text & "', "
SQL = SQL & "'" & txtHomeName(6).Text & "', "
SQL = SQL & "'" & txtVisitorName(6).Text & "', "
SQL = SQL & "'" & txtHomeName(7).Text & "', "
SQL = SQL & "'" & txtVisitorName(7).Text & "', "
SQL = SQL & "'" & txtHomeName(8).Text & "', "
SQL = SQL & "'" & txtVisitorName(8).Text & "', "
SQL = SQL & "'" & txtHomeName(9).Text & "', "
SQL = SQL & "'" & txtVisitorName(9).Text & "', "
SQL = SQL & "'" & txtHomeName(10).Text & "', "
SQL = SQL & "'" & txtVisitorName(10).Text & "', "
SQL = SQL & "'" & txtHomeName(11).Text & "', "
SQL = SQL & "'" & txtVisitorName(11).Text & "', "
SQL = SQL & "'" & txtHomeName(12).Text & "', "
SQL = SQL & "'" & txtVisitorName(12).Text & "', "
SQL = SQL & "'" & txtHomeName(13).Text & "', "
SQL = SQL & "'" & txtVisitorName(13).Text & "', "
SQL = SQL & "'" & txtHomeName(14).Text & "', "
SQL = SQL & "'" & txtVisitorName(14).Text & "', "
SQL = SQL & "'" & txtHomeName(15).Text & "', "
SQL = SQL & "'" & txtVisitorName(15).Text & "'); "
EndInsert:
If Err.Number = 340 Or Err.Number = 0 Then
    pconSports.Execute SQL, intRecordsAffected
Else
    GoTo ErrHandler
End If

 

[gmmastros]

It's been a while since I used an Access database, so I may be a little fuzzy on some of the particulars.

There is a property on text columns called something like, 'Allow Zero Length Strings'. Like I said, the wording may be a little off, but the concept is sound. Anyway, if you allow zero length strings in your columns, the insert statement should work.

Another option would be to replace zero length strings (in your code) with NULL.

For example....

SQL = SQL & IIF(txtVisitorName(15) = "", "NULL", "'" & txtVisitorName(15).Text & "');")

Basically, if the data is an empty string, replace it with NULL, so that your SQL query becomes....

Insert into AllTeams (WeekNumber, HTeam1, VTeam1, HTeam2, VTeam2, HTeam3, VTeam3, etc....)
Values(1,'blah','fooey',NULL,NULL,NULL,NULL,etc...)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pungy]

George:

I appreciate your feedback but
1st - I do have "AllowZeroLength" checked off in the design.
2nd - using your example above, I get the error txtVisitorName(15) does not exist.

Maybe I need to change the logic when I Load the textboxes in the Control array. I only LOAD the number I need. If I only need 4, I will only LOAD 4, not 16.

This brings up another question: In my example above, the maximum I can have is 16. Is there any way to code the Insert if I have more than 16?

 

[gmmastros]

Personally, I think you need to change a lot of things. Most importantly, the table structure.

I strongly encourage you to read up on [google]Database Normalization[/google].

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pungy]

George:

I've decided I am going to make "multiple" records vs. 1 big record. The Table will be described:
AllTeams
fields:
WeekNumber
HTeam
VTeam

I can use WeekNumber as a field to "Select" all the teams for a particular week. This way, I can have "Unlimited" number of teams.

 

[gmmastros]

That's a better idea.

While you are at it, you may want to consider have a 'Team' table to store the name and a unique identifier. Then, your AllTeams table would have....

WeekNumber, HomeTeamId, VisitorTeamId

Ex:

[tt]Teams
ID Name
-- -------
1 Blue Team
2 Red Team
3 Green Team[/tt]

Then, in your all teams table...
[tt]
AllTeams
WeekNumber HomeTeamId VisitorTeamId
---------- ---------- -------------
1 1 2
2 3 2
3 3 1[/tt]

There are some advantages to a table structure like this.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pungy]

George:

Great Idea.

Question: Are you familiar with Crystal Reports-Cross-Tab Report?

 

[gmmastros]

Are you familiar with Crystal Reports-Cross-Tab Report?

I haven't used crystal reports in about 10 years. Sorry.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom