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!
|
|
Not sure whats going wrong
Posted: 26 Jun 07 11:49 AM
|
Trying to bring in some data and update a recordset, this one field keeps failing but not for all records... it only fails for the email item... but not for every row. The data from the import file is correct
the error I am getting is: Multiple-step operation generated errors. Check each status value.
The items is not null : Code works for other rows, this is the fifth row in the import..
Here is the part of code:
==============================
With SLXrs
For Each oProduct As WebQQ In alProducts
.AddNew()
x = x + 1
If x = 5 Then
Stop
End If
.Fields("C_WebQQ_ActiveId").Value = CreateSlxID("C_WEBQQ_Active", 1)
.Fields("CreateUser").Value = "Admin"
.Fields("CreateDate").Value = Now()
.Fields("ModifyUser").Value = "Admin"
.Fields("ModifyDate").Value = Now()
.Fields("Request_Number").Value = oProduct.RequestID
.Fields("BranchId").Value = oProduct.BranchId
.Fields("FA_EMail").Value = NullToBlank(oProduct.FA_Email)
ContactId = GetContactId(oProduct.FA_Email)
.Fields("ContactId").Value = ContactId
.Fields("FCID").Value = GetFCID(ContactId)
.Fields("Last_Name").Value = NullToBlank(oProduct.FA_LastName)
.Fields("First_Name").Value = NullToBlank(oProduct.FA_FirstName)
.Fields("Investor").Value = NullToBlank(oProduct.Investor)
FundId = GetFundId(oProduct.FundId)
.Fields("FundId").Value = FundId
.Fields("DateOut").Value = NullToBlank(oProduct.DateOut)
.Fields("Book_Number").Value = NullToBlank(oProduct.Book_Id)
.Fields("Fund_Name").Value = GetFundName(FundId)
.Fields("Est_Amt").Value = NullToBlank(oProduct.InvestAmt)
.Fields("Est_Date").Value = NullToBlank(oProduct.InvestDate)
.Fields("Region").Value = NullToBlank(oProduct.Region)
.Fields("Division").Value = NullToBlank(oProduct.Division)
.Fields("Tax_Exempt").Value = NullToBlank(oProduct.TaxEx)
.Fields("Activity_Flag").Value = ""
.Fields("Investor_Status").Value = NullToBlank(oProduct.Status)
Next
.UpdateBatch(ADODB.AffectEnum.adAffectAll)
End With
==========================================
here is actual Error from catch:
6/26/2007 12:46:33 PMSystem.Runtime.InteropServices.COMException (0x80040E21): Multiple-step operation generated errors. Check each status value.
at ADODB.Field.set_Value(Object pvar)
at ADODB.InternalField.set_Value(Object value)
at SalesLogix_WebbQQ_Load.SlxWebQQLoad.Main() in C:\Documents and Settings\re22341\My Documents\Visual Studio 2005\Projects\SalesLogix_WebbQQ_Load\SalesLogix_WebbQQ_Load\SLX_WebQQ_Load.vb:line 108
thanks all
|
|
|
|
Re: Not sure whats going wrong
Posted: 26 Jun 07 12:42 PM
|
1. I never use the rs.update, .addnew etc. They arent cool for me  Use SQL instead with a parameterized query...
2. This is common when a value is mismatched or missing. Strings into numbers or just blank when not allowed.
I suggest you write it as a good old SQL statement and take a peak. But really you should be able to look at each value and see the problem if you are careful enough - this is tedious.
Are you interactively debugging? |
|
|
|
Re: Not sure whats going wrong
Posted: 26 Jun 07 12:50 PM
|
Hey Jason.. Yeah I am debugging and the value appears as expected, here is the entire process of which you saw a small part...
Import from an excel file into an array,
values from that array are being put into the recordset and added to a table, the reason I chose the rs was this way I only had to hit the database one time not for each row. With the SQl statement I'd have to hit the DB each time corredct..?
Yeah usally when I get this error it is associated to a NULL value however this time it isn't.. value appearing to be a valid email string.. |
|
|
|
Re: Not sure whats going wrong
Posted: 26 Jun 07 12:55 PM
|
You are right about the hitting the db once and all of that, but with a parameterized query the execution plan would be written and only the values would change. Your pipe to the db would remain open for the entire loop, which isnt desireable.
Unless you do this 100 times a day with 1,000,000 records I dont think it will matter, but you have control over what is sent to the db.
This should work, but you are going through the SLXOLEDB provider - that could be changing something (if you arent ok then).
I would get it into SQL and copy and paste it into query analyser.
I bet you are right about everything you have checked, this is just the fastest way that I have found for me to debug DB/Data issues.
It could be something completely unrelated, but I will be interested to find out the end result! |
|
|
|
Re: Not sure whats going wrong
Posted: 26 Jun 07 12:57 PM
|
Other emails are working OK? We are talking SQL server and not Oracle?
Is there something special about this email address?
Are all the others normal or blank? I have had oracle mess up on the @ in email strings or the ? in litterals, but I dont think SQL server.. |
|
|
|
Re: Not sure whats going wrong
Posted: 26 Jun 07 12:59 PM
|
It is the email of the 5th row.. the first 4 go through w/o error.
It is a SQL DB. Wondering if there is any size restriction.. only difference is this email is a bit long.. but not really just 36 chars in total
this is what I mean about strange, I guess I can try to use the SQL profiler.. but it seems to blowup before the Update statement.. blows up on the assignment to the field value
|
|
|
|
Re: Not sure whats going wrong
Posted: 26 Jun 07 1:04 PM
|
ok then desc the table for me (and past it here)
and then paste the email address (change the letters in the email but nothing else) |
|
|
|
Re: Not sure whats going wrong
Posted: 26 Jun 07 1:10 PM
|
Jason,
Great call.. I checked the field size after you asked me about the table.. and it was set @ 32.. changed it to 128 and everything went smoothly..
Thanks |
|
|
|