The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Append Comma to field causing "E_Fail Status' error
Posted: 25 Oct 07 7:19 AM
|
I am looping through a recordset in an attempt to append a comma to the end of all records in a field. The code is below... If I remove the "," from the end of the code being updated it runs fine.. How can I accomplish this? I want to update every county field in the table with a comma on the end of it.
Thanks
Set objRS = objSLXDB.GetNewRecordSet
strSQL = "Select County,C_RSM_STATE_CROSSREFID from C_RSM_STATE_CROSSREF order by C_RSM_STATE_CROSSREFID"
objRS.Open strSQL, objSLXDB.Connection
Do while not objRS.eof
Set objRS2 = objSLXDB.GetNewRecordSet
strSQL2 = "Select County from C_RSM_STATE_CROSSREF where C_RSM_STATE_CROSSREFID='" & objRS("C_RSM_STATE_CROSSREFID") & "'"
objRS2.Open strSQL2, objSLXDB.Connection
If not objRS2.eof then
objRS2.Fields("County").Value = objRS("County") & ","
objRS2.Fields("MODIFYUSER").Value = Application.BasicFunctions.CurrentUserID
objRS2.Fields("MODIFYDATE").Value = Now
objRS2.Update
End If
Set objRS2 = Nothing
objRS.movenext
Loop |
|
|
|
Re: Append Comma to field causing "E_Fail Status' error
Posted: 25 Oct 07 10:05 AM
|
I'm not sure if this will help, but it would be very easy to try. Instead of passing the string, try using the ASCII code for a comma
Like:
objRS2.Fields("County").Value = objRS("County") & Chr(44)
|
|
|
|
Re: Append Comma to field causing "E_Fail Status' error
Posted: 25 Oct 07 11:42 AM
|
I wonder if maybe resources aren't getting cleaned up and a leak is causing the problem? Only that that stands out in that area is to explicitly close the objRS2 recordset in each interation of the loop before setting it to Nothing. Also, there's not really a need for the inner objRS2 recordset anyway. You could just update the same one you're looping through.
ie:
Set objRS = objSLXDB.GetNewRecordSet
strSQL = "Select * from C_RSM_STATE_CROSSREF"
objRS.Open strSQL, objSLXDB.Connection
Do while not objRS.eof
objRS.Fields("County").Value = objRS("County") & ","
objRS.Fields("MODIFYUSER").Value = Application.BasicFunctions.CurrentUserID
objRS.Fields("MODIFYDATE").Value = Now
objRS.Update
objRS.movenext
Loop
Also, maybe try doing it as a single SQL update statement?
update c_rsm_state_crossref set county = county+','
Make sense? |
|
|
|
Re: Append Comma to field causing "E_Fail Status' error
Posted: 25 Oct 07 11:48 AM
|
| Actually I fixed the problem by 'trim'ing the objRS("county") field. I did a msgbox len(objRS("County")) and it was returning the maximum length of the field for some records.. Anyway all is well. Thanks for the tips as always. |
|
|
|
Re: Append Comma to field causing "E_Fail Status' error
Posted: 26 Oct 07 7:11 AM
|
I was going to suggest that adding the comma for some records exceeded the field length. Seems you hit upon that.
I am kind of curious, though. Why would you want to append a comma to the end of the county field? |
|
|
|
Re: Append Comma to field causing "E_Fail Status' error
Posted: 26 Oct 07 8:34 AM
|
| Why indeed would anyone want to do that? I have a utility that allows the admin to assign sales reps to states/counties, then when an opportunity is added or when some other criteria changed the sales rep is autopopulated based on the counties being in the sales reps delimited list. This involves a checkboxlist control and a comma delimited string of counites. I ended up looking for the county and a comma to match a county with a sales rep. So I had bug with counties like Smith and Smithson so I had to add the comma to the search criteria. I had originally coded it with no comma at the end of the stored string of counties so I had to retrofit the data. A little short sighted but that's how it goes sometimes. |
|
|
|
Re: Append Comma to field causing "E_Fail Status' error
Posted: 29 Oct 07 9:12 AM
|
Interesting. Something else you might want to think about is similarly names counties between states. For example there is a Montgomery, AL and a Montgomery, PA. We use counties here as well and have to include the states as well in county listing to differentiate.
John G. |
|
|
|