Column 'Number01' does not belong to table Company

SimpsonGranco · September 24, 2019, 2:21pm

Hi all. Going through our uplift from 9.05.702A to 10.2.400. I have a button on Part Entry that when clicked, reads from Company.Number01 to grab the next part number then it increments that number by one and saves it back to Company.Number01. In 10, when I click the button, I get an error “Column ‘Number01’ does not belong to table Company” In a BAQ, I see that I do have all of the UD fields within Company. It is there and it has data. I’ve tried Company_UD and Company1 in my customization but no luck. I’ve Googled this issue like crazy and still no such luck. Any help would be appreciated.
Oh, I did add Number01 to SysCompany, regenerated and stop/start the app pool but still getting the error.
Thanks,
Chris

Jason_Woods · September 24, 2019, 2:58pm

This doesn’t answer you question directly, but there are other, possibly better, options for an increment process.
As for a customization, I would offer this gets moved away from the Company tables and into a lightweight table like UDCodes.

ckrusen · September 24, 2019, 3:23pm

Did you create table Company_UD, add Number01, and regen ?

SimpsonGranco · September 24, 2019, 3:28pm

Thanks for the suggestion. I’ve definitely thought of doing exactly this but I want to get through this uplift first.

SimpsonGranco · September 24, 2019, 3:29pm

Calvin,
I didn’t need to. It already exists from the upgrade.

ckrusen · September 24, 2019, 3:32pm

And yet it doesn’t show up as a field in the BAQ designer?
(Note that the UD fields appear at the very end of the field list).

Is that field on a customized version of Company config?

If so, is the binding okay?

If not, try adding a textbox and binding it to that field.

SimpsonGranco · September 24, 2019, 3:37pm

It does in the BAQ designer. This is what is generating the error. It is from the Next Part button in Part Entry.
Private Sub epiButtonC1_Click(ByVal sender As Object, ByVal args As System.EventArgs)
’ ** Place Event Handling Code Here **
Dim compAdapt As CompanyAdapter = new CompanyAdapter(PartForm)
compAdapt.BOConnect()
Dim newPartID As Decimal = compAdapt.CompanyData.Tables(“Company_UD”).Rows(0)(“Number01”)
Dim newPart As String = newPartID.ToString(“0.00”)
Dim txtKeyField as EpiTextBox = CType(csm.GetNativeControlReference(“38e1671b-0c3f-4ab3-b8ab-95f26285f1d2”), Ice.Lib.Framework.EpiTextBox)
txtKeyField.text = newPart
txtKeyField.Focus()

	  newPartID = newPartID + .01
      compAdapt.CompanyData.Tables("Company_UD").Rows(0).BeginEdit()
      compAdapt.CompanyData.Tables("Company_UD").Rows(0)("Number01") = newPartID
      compAdapt.CompanyData.Tables("Company_UD").Rows(0).EndEdit()
      compAdapt.Update()
      compAdapt.Dispose()

End Sub

ckrusen · September 24, 2019, 3:47pm

Did you look at

And further down that same thread …

SimpsonGranco · September 24, 2019, 3:50pm

Yes. I tried that too. Added it to SysCompany, regenerated and restarted the app pool. No luck.

ckrusen · September 24, 2019, 3:53pm

Does the form show the value that is in Number01, before you try to change it with the button_click function? (just trying to see if the form andn its data view are seeing it)

Can yoiu manually change the value and save the form? Does the new value get saved, and is displayed the next time the form is loaded?

SimpsonGranco · September 24, 2019, 3:58pm

Calvin,

I don’t have Company bound to the part form. But I also do not in E9 and it works just fine.

ckrusen · September 24, 2019, 4:01pm

Customize the Company Config form, adding a text box bound to that Company field.

This is just to see if DB table is accessible and getting updated when changed.

Also, I strongly suggest you start using C# in customizations - even if it means recreating ones that were ported from E9, but were in VB.

edit:

just noticed you have

Dim newPartID As Decimal = compAdapt.CompanyData.Tables(“Company_UD”).Rows(0)(“Number01”)

Number01 is an integer, no?

SimpsonGranco · September 24, 2019, 4:07pm

Calvin,

That is the plan but after I go live. In a time crunch now.
It is being stored as a decimal. See below. Also, I can edit and save the value in an updatable BAQ with no issues.

ckrusen · September 24, 2019, 4:12pm

Have you tried commenting out parts of yopur code to determine if its the initail fetch, vs the update?

Private Sub epiButtonC1_Click(ByVal sender As Object, ByVal args As System.EventArgs)
' ** Place Event Handling Code Here **
Dim compAdapt As CompanyAdapter = new CompanyAdapter(PartForm)
compAdapt.BOConnect()
Dim newPartID As Decimal = compAdapt.CompanyData.Tables("Company_UD").Rows(0)("Number01")
Dim newPart As String = newPartID.ToString("0.00")
Dim txtKeyField as EpiTextBox = CType(csm.GetNativeControlReference("38e1671b-0c3f-4ab3-b8ab-95f26285f1d2"), Ice.Lib.Framework.EpiTextBox)
txtKeyField.text = newPart
txtKeyField.Focus()
newPartID = newPartID + .01
'compAdapt.CompanyData.Tables("Company_UD").Rows(0).BeginEdit()
'compAdapt.CompanyData.Tables("Company_UD").Rows(0)("Number01") = newPartID
'compAdapt.CompanyData.Tables("Company_UD").Rows(0).EndEdit()
'compAdapt.Update()
compAdapt.Dispose()

End Sub

SimpsonGranco · September 24, 2019, 4:47pm

Calvin,
Now I get “There is no row at position 0.”

jgiese.wci · September 24, 2019, 5:15pm

Ditch the ud column use the Ice sequence class on the BPM side Part GetNew post proc

var nextPartNum = new Ice.Lib.NextValue(Db).GetNextSequence("PartNumSeq").ToString("00000")

The to string can be any string format you want. we prefer leading 0’s that’s all

ckrusen · September 24, 2019, 5:48pm

So it must not like

Dim newPartID As Decimal = compAdapt.CompanyData.Tables("Company_UD").Rows(0)("Number01")

I’m just guessing, but do you get the same for the following:

Dim newPartID As Decimal = compAdapt.CompanyData.Tables("Company").Rows(0)("Number01")

(note I removed the _UD)

SimpsonGranco · September 24, 2019, 6:18pm

This gives error There is no row at position 0.

This gives the error Column Number01 does not belong to table Company

ckrusen · September 24, 2019, 7:17pm

@SimpsonGranco - The following appears to work

	Private Sub epiButtonC1_Click(ByVal sender As Object, ByVal args As System.EventArgs)
		' ** Place Event Handling Code Here **
		
		Dim compAdapt As CompanyAdapter = new CompanyAdapter(PartForm)
		
		compAdapt.BOConnect()
		Dim compID as string = "MC"
		Dim compAd as Boolean = compAdapt.GetByID(compID)
		Dim newPartID As Decimal = compAdapt.CompanyData.Tables("Company").Rows(0)("Number01")
		Dim txtKeyField as EpiTextBox = CType(csm.GetNativeControlReference("a307d604-2dda-4c3a-b726-72569d4ba244"), Ice.Lib.Framework.EpiTextBox)
		txtKeyField.text = NewPartID.ToString
		txtKeyField.Focus()
		newPartID = newPartID + 0.01
		compAdapt.CompanyData.Tables("Company").Rows(0).BeginEdit()
		compAdapt.CompanyData.Tables("Company").Rows(0)("Number01") = newPartID
		compAdapt.CompanyData.Tables("Company").Rows(0).EndEdit()
		compAdapt.Update()
		compAdapt.Dispose()
	End Sub

(modify the compID and textbox GUID to match yours)

Not exactly sure why

Dim compAd as Boolean = compAdapt.GetByID(compID)

is now needed, when it wasn’t in E9.

Without that line, I get the “There is no row at postion 0.” error

jgiese.wci · September 24, 2019, 9:25pm

That’s a lot of code vs just getting the next part number from Ice. Use the tools Epicor provides us we don’t have to do it the old E9 ways. I can literally be one line in a set field widget in a BPM.