Solved spaces in AddNew fields caising issues...

[Cal Varchar]

Hola Muchachos y Muchachas!

As you can see it's pretty festive here with the slew of errors that are getting thrown from this one statement.

Any ideas why, ONLY WHEN I HAVE a SPACE in the field name, i get this error?

Gracias!

 

[arnelgp]

you replace ' with double ':

Adorec("Company") = Replace$("Boby's Company", "'", "''")

 

[Cal Varchar]

you replace ' with double ':

Adorec("Company") = Replace$("Boby's Company", "'", "''")

I'm probably misunderstanding; the issue is with the "Job Title" field.

If I used "Address", it goes right through. And I would suspect that if I used "JobTitle", it would go right through. "Company" updates no matter what I put in: "Teddy's Company", "Bubby's Company"...all of that kind of stuff gets added to the DB.

Anytime I have spaces in a field/column name, it does not work.

FYI, there are spaces in the majority of field names in the DB.

 

[arnelgp]

ok, then use .Fields()
.Fields(0) refers to the first field in your query
.Fields(1) is your second field:

ADOrec.AddNew
ADOrec.Fields(0) = "Bobby's Company"
ADOrec.Fields(1)="1231x312x31"
ADOrec.Update

 

[isladogs]

Best practice is to avoid spaces in field and object names. If you must use spaces enclose in square brackets [Job Title] or replace spaces with underscores: Job_Title

 

[Cal Varchar]

ok, then use .Fields()
.Fields(0) refers to the first field in your query
.Fields(1) is your second field:

ADOrec.AddNew
ADOrec.Fields(0) = "Bobby's Company"
ADOrec.Fields(1)="1231x312x31"
ADOrec.Update

I looked through the help and found that, but it yielded something about an improper accessor, from what I remember.

 

[Cal Varchar]

Best practice is to avoid spaces in field and object names. If you must use spaces enclose in square brackets [Job Title] or replace spaces with underscores: Job_Title

Yeeh. Thanks. I haven't put a space in a field name in my life, it's not my creation and renaming the fields, and everything they relate to, is beyong the scope of my duties and willingness.

 

[Cal Varchar]

ok, then use .Fields()
.Fields(0) refers to the first field in your query
.Fields(1) is your second field:

ADOrec.AddNew
ADOrec.Fields(0) = "Bobby's Company"
ADOrec.Fields(1)="1231x312x31"
ADOrec.Update

 

[arnelgp]

i think you need to Save it not Update it.

'ADOrec.Update <= this is wrong
Adorec.Save


'==============
ADOrec.AddNew
ADOrec.Fields("Company") = "Bobby's Company"
ADOrec.Fields("Job Title")="1231x312x31"
ADOrec.Save

 

[Cal Varchar]

i think you need to Save it not Update it.

'ADOrec.Update <= this is wrong
Adorec.Save


'==============
ADOrec.AddNew
ADOrec.Fields("Company") = "Bobby's Company"
ADOrec.Fields("Job Title")="1231x312x31"
ADOrec.Save

I've done a few tens of trials. Made sure that my SQL statement was correct and it works for Save and Update.
AND
Works with AND without .Fields without ordinals.
Works with ADOrec("fieldname"), but not ADOrec("field name")

 

[arnelgp]

i just tested it on SQLServer Express with space on field name (Job Title) and it does not have any error.
the error you have is you are Adding and using Update at the end, when you should use Save.

 

[cheekybuddha]

Works with ADOrec("fieldname"), but not ADOrec("field name")

Arnel already gave you the answer:

ADOrec.Fields("field name") = "Whatever"

 

[cheekybuddha]

ADOrec.Save does not do what you think it does:

See documentation here

 

[arnelgp]

..then what does Save do on a New record?
i tested it on my SQLServer and it saves the record.
what else does it do on New Record (AddNew method)?

 

[cheekybuddha]

..then what does Save do on a New record?
i tested it on my SQLServer and it saves the record.

Can you show the code you used?

 

[Cal Varchar]

Arnel already gave you the answer:

ADOrec.Fields("field name") = "Whatever"

I tried it. It didn't work. That's up there in post #10

Almost all of these work EXCEPT those with ordinals AND those that use ("field name") That's been my experience. Maybe it's SQL Server 2016.

But you can see my code down below, perhaps someone can try running my original code and see if it works on their system.

As I say, it works ANY TIME I have a field name without a space. I'm certain that with "JobTitle" it would work.

I just thought of this. I'll try it with "Job Title" first, followed by "Company", when I have time. Might be a couple of hours.

 

[arnelgp]

BECAUSE YOU ARE USING ADOrec.Update
use ADOrec.Save

can't you see, it is highlighting that line?

 

[gemma-the-husky]

You need [field name] when there is a space, I think. Some version of that, with square brackets

 

[Cal Varchar]

BECAUSE YOU ARE USING ADOrec.Update
use ADOrec.Save

can't you see, it is highlighting that line?

I think I said that it doesn't work with save either. Yeah, not particularly clearly, but that's what I meant up in #10.

 

[arnelgp]

i can't believe that is True? like i said, i did re-create your table.
see this video