Zero-length string problem!

[muppetgrrl]

I can't have zero-length strings in a field (it's a checkbox that should read yes or no) and it worked before, but now it's not, and I can't figure out why. When I response.write the SQL statement, it shows that the string is 'No' but when I execute, it says it's a zero-length string. Gah!

Here's the SQL statement. It's saying that the field "ARCandidate" is zero-length, when it clearly isn't. (See boldface):

----------------------
INSERT INTO commdata(DateEdited,ProjectName,Location,State,StateOther,LeadOffice,SecondLeadOffice,ClientContact,PrimaryContact,SecondaryContacts,FWSigRating,
PracticeArea,DateOpening,Description,StoryStatus,StoryNotes,StoryNextSteps,StoryFWCContact,DatePhotography,PermissionsStatus,ImageStatus,
ImageType,Photographer,ARDate,BookDate,DialogueDate,WebDate,PressBookingDate,ARCandidate,BookCandidate,DialogueCandidate,WebCandidate,PressCandidate,
AwardsCandidate,AwardsWon) SELECT '02/18/2005' AS DateEdited ,
'New Project' AS ProjectName , '' AS Location , ' ' AS State ,
'' AS StateOther , ' ' AS LeadOffice , ' ' AS SecondLeadOffice ,
'' AS ClientContact , '' AS PrimaryContact , '' AS SecondaryContacts ,
' ' AS FWSigRating , ' ' AS PracticeArea , '' AS DateOpening ,
'' AS Description , 'Complete' AS StoryStatus , '' AS StoryNotes ,
'' AS StoryNextSteps , '' AS StoryFWCContact , '' AS DatePhotography ,
'Unknown' AS PermissionsStatus , 'Unknown' AS ImageStatus , 'Unknown' AS ImageType , '' AS Photographer ,
'' AS ARDate , '' AS BookDate , '' AS DialogueDate , '' AS WebDate ,
'New Award' AS AwardsWon, '' AS PressBookingDate,
'No' AS ARCandidate , 'No' AS BookCandidate , 'No' AS DialogueCandidate ,
'No' AS WebCandidate , 'No' AS PressCandidate , 'No' AS AwardsCandidate;
-------------

Here's the asp programming:

-----------------------------------
strSQL = "INSERT INTO commdata" _
& "(DateEdited,ProjectName,Location,State,StateOther,LeadOffice,SecondLeadOffice,ClientContact,PrimaryContact,SecondaryContacts,FWSigRating,PracticeArea,DateOpening,
Description,StoryStatus,StoryNotes,StoryNextSteps,StoryFWCContact,DatePhotography,PermissionsStatus,ImageStatus,ImageType,Photographer,ARDate,BookDate,DialogueDate,WebDate,
PressBookingDate,ARCandidate,BookCandidate,DialogueCandidate,WebCandidate,PressCandidate,AwardsCandidate,AwardsWon) " _
& "SELECT '" & strDateEdited & "' AS DateEdited , '" _
& strProjectName & "' AS ProjectName , '" _
& strLocation & "' AS Location , '" _
& strState & "' AS State , '" _
& strStateOther & "' AS StateOther , '" _
& strLeadOffice & "' AS LeadOffice , '" _
& strSecondLeadOffice & "' AS SecondLeadOffice , '" _
& strClientContact & "' AS ClientContact , '" _
& strPrimaryContact & "' AS PrimaryContact , '" _
& strSecondaryContacts & "' AS SecondaryContacts , '" _
& strFWSigRating & "' AS FWSigRating , '" _
& strPracticeArea & "' AS PracticeArea , '" _
& strDateOpening & "' AS DateOpening , '" _
& strDescription & "' AS Description , '" _
& strStoryStatus & "' AS StoryStatus , '" _
& strStoryNotes & "' AS StoryNotes , '" _
& strStoryNextSteps & "' AS StoryNextSteps , '" _
& strStoryFWCContact & "' AS StoryFWCContact , '" _
& strDatePhotography & "' AS DatePhotography , '" _
& strPermissionsStatus & "' AS PermissionsStatus , '" _
& strImageStatus & "' AS ImageStatus , '" _
& strImageType & "' AS ImageType , '" _
& strPhotographer & "' AS Photographer , '" _
& strARDate & "' AS ARDate , '" _
& strBookDate & "' AS BookDate , '" _
& strDialogueDate & "' AS DialogueDate , '" _
& strWebDate & "' AS WebDate , '" _
& strAwardsWon & "' AS AwardsWon, '" _
& strPressBookingDate & "' AS PressBookingDate, '" _
& strARCandidate & "' AS ARCandidate , '" _
& strBookCandidate & "' AS BookCandidate , '" _
& strDialogueCandidate & "' AS DialogueCandidate , '" _
& strWebCandidate & "' AS WebCandidate , '" _
& strPressCandidate & "' AS PressCandidate , '" _
& strAwardsCandidate & "' AS AwardsCandidate;"

---------------------

Anyone have any ideas? I'm tearing out my hair here.

 

[Sheco]

Is that SQL statement at the top of your post taken from a Response.Write?

If not, what do you see if you add Response.Write strSQL immediately after you build strSQL ??

Is it possible that you have too many line continuations and that strSQL is being truncated?

 

[muppetgrrl]

Thanks for responding.

The top SQL is a response.write. It's not being truncated, because AwardsCandidate is showing up.

The thing is, if I go into the DB and make ARCandidate allow zero-length, all the other "Candidate" fields work fine. It's the only one going wonky, for some reason. And I have a similar SQL statement in a page designed to edit, not add, and it's working fine.

 

[Chopstik]

Looking at the first section of code you showed, it appears that you missed the last field when you do your insert (unless it's just a typo - in which case, ignore this post). Shouldn't you have the piece in red below (also, please use spaces in your code other the one long string creates a difficult post to view without moving from one side to the other):

INSERT INTO commdata(DateEdited, ProjectName, Location, State, StateOther, LeadOffice, SecondLeadOffice, ClientContact, PrimaryContact, SecondaryContacts, FWSigRating, PracticeArea, DateOpening, Description, StoryStatus, StoryNotes, StoryNextSteps, StoryFWCContact, DatePhotography, PermissionsStatus, ImageStatus,
ImageType, Photographer, ARDate, BookDate, DialogueDate, WebDate, PressBookingDate, ARCandidate, BookCandidate, DialogueCandidate, WebCandidate, PressCandidate,
AwardsCandidate, AwardsWon) SELECT '02/18/2005' AS DateEdited , 
'New Project' AS ProjectName , '' AS Location , ' ' AS State , 
'' AS StateOther , ' ' AS LeadOffice , ' ' AS SecondLeadOffice , 
'' AS ClientContact , '' AS PrimaryContact , '' AS SecondaryContacts , 
' ' AS FWSigRating , ' ' AS PracticeArea , '' AS DateOpening , 
'' AS Description , 'Complete' AS StoryStatus , '' AS StoryNotes , 
'' AS StoryNextSteps , '' AS StoryFWCContact , '' AS DatePhotography , 
'Unknown' AS PermissionsStatus , 'Unknown' AS ImageStatus , 'Unknown' AS ImageType , '' AS Photographer , 
'' AS ARDate , '' AS BookDate , '' AS DialogueDate , '' AS WebDate , 
'New Award' AS AwardsWon, '' AS PressBookingDate, 
'No' AS ARCandidate , 'No' AS BookCandidate , 'No' AS DialogueCandidate , 
'No' AS WebCandidate , 'No' AS PressCandidate , 'No' AS AwardsCandidate[COLOR=red], 'SomeValue' AS AwardsWon[/color];

------------------------------------------------------------------------------------------------------------------------
If you don't have a sense of humor, you probably don't have any sense at all.
- Anonymous

 

[muppetgrrl]

It's in there a bit earlier:

'' AS WebDate , 
[b]'New Award' AS AwardsWon,[/b] '' AS PressBookingDate, 
'No' AS ARCandidate , 'No' AS BookCandidate , 'No' AS DialogueCandidate , 
'No' AS WebCandidate , 'No' AS PressCandidate , 'No' AS AwardsCandidate;

Again, thanks for responding. I'm going nuts here!

 

[muppetgrrl]

Actually, you were right--I moved it to the end so it's all in order (I had logic for putting it in a different place, but it was bad logic!) and after moving AwardsWon to the end, it works now.

Thanks a mil!

 

[Chopstik]

The reason for this, in case you are interested, is that you must insert the values in the order which you have them listed. The system has no way of determining a random order. Glad that it worked for you!

------------------------------------------------------------------------------------------------------------------------
If you don't have a sense of humor, you probably don't have any sense at all.
- Anonymous