updating a table with a space in the field name

  • Thread starter Thread starter Eych
  • Start date Start date
E

Eych

I get a VB error when I try to update a table whose field I change
with the following statement:

dcUpdate.Dataset.Tables(0).Rows(0)("User Name") = Me.textbox1.Text


if I change the field name to "UserName", one word, in the table and
in the statement above, it works...

realistically, I can't do this since the users already have a copy of
this database...

i've tried ("[User Name]"), didn't work...
i've tried using the field number (i.e., Rows(0)(2)), didn't work

even if I don't update that field and update another that is one word,
I still get the error because the statement:
dcUpdate.Adapter.Update(dcUpdate.Dataset, "users")
internally builds an Update SQL that includes the field 'User Name'
and errors...

any guidance?

thanks...

Eych
 
instead of calling dcUpdate.Adapter.Update(dcUpdate.Dataset, "users")
and having VB auto-generate the Update statement, I created it with
the UpdateCommand property, thus being able to use brackets...


dcUpdate.Adapter.UpdateCommand = New OleDbCommand("UPDATE user SET
[User Name] = ? WHERE LinkID = ?", dcUpdate.Connection)

dcUpdate.Adapter.UpdateCommand.Parameters.Add(New
OleDbParameter("Name", OleDbType.Char, 50))
dcUpdate.Adapter.UpdateCommand.Parameters.Add(New OleDbParameter("ID",
OleDbType.Integer, 2))

dcUpdate.Adapter.UpdateCommand.Parameters(0).Value = Me.textbox1.Text
dcUpdate.Adapter.UpdateCommand.Parameters(1).Value = Me.tmpUserID

dcUpdate.Connection.Open()
dcUpdate.Adapter.UpdateCommand.ExecuteNonQuery()
 
Eych,

Did you had any reason that you asked this question to the newsgroup and
than tell that the answer is a total different solution? Your question was
updating a datarowitem and you show a solution for an update of an item
directly in the database bypassing the datarow completly.

I tell this because some people search in newsgroups and find than a
solution like yours which does not fit the problem when it is about a
dataset/table.

Cor
 
Did you had any reason that you asked this question to the newsgroup and
than tell that the answer is a total different solution? Your question was
updating a datarowitem and you show a solution for an update of an item
directly in the database bypassing the datarow completly.
Click to expand...

I think you misunderstood the question, and I did too at first. He wasn't
saying that this code failed:

dcUpdate.Dataset.Tables(0).Rows(0)("User Name") = Me.textbox1.Text

He was saying that when he calls the Update method it throws an error due to
the field name having a space. The problem, as he discovered, was letting
the DataAdapter autogenerate the UPDATE statement, and the solution was to
create the UpdateCommand manually.
 
Jeff,
I think you misunderstood the question, and I did too at first. He wasn't
saying that this code failed:

dcUpdate.Dataset.Tables(0).Rows(0)("User Name") = Me.textbox1.Text

He was saying that when he calls the Update method it throws an error due
to the field name having a space. The problem, as he discovered, was letting
the DataAdapter autogenerate the UPDATE statement, and the solution was >
Click to expand...
to create the UpdateCommand manually.

You cannot do this statement normally at all or he would have created
completly a class with a lot of subclasses and that I do not believe.

To find the solution did take me a lot of time. I first had to make a
testtable situation (I never use datanames with a space) and bring that name
from the dataset to a bytearray before I saw it.

Therefore I am not glad that when I found the solution to an forever comming
problem someone starts to tell that he found the solution and you tell that
I did understand the question wrong.

Cor
 
Cor,

aside from your suggestion of adding 2 spaces, which I haven't yet
tried, there were no other options...

my problem was updating a field in an Access table where the field
name has a space. I can do a Select on it no problem (e.g. Select *
from users where [User Name]=...) using the brackets.

the problem came when I called:
dcUpdate.Adapter.Update(dcUpdate.Dataset, "users")

this was a problem because with the above statement, VB generates its'
own Update SQL. Thus, the SQL went something like ...and User Name =
?...
which threw an error because User Name is not enclosed...

the major issue was, even if I only updated a different one-word only
field in the table, I still got the error because when it
auto-generates the SQL, it still included the User Name field...

therefore, I wouldn't call my response a totally different solution,
but actually a plausible one...

all this said of course without trying your suggestion...but I am
doubtful if the 2 spaces would actually work...if it does, I guess I
could have saved a bunch of time...
 
put the field name in square brackets [Field Name in Brackets]


Eych said:
I get a VB error when I try to update a table whose field I change
with the following statement:

dcUpdate.Dataset.Tables(0).Rows(0)("User Name") = Me.textbox1.Text


if I change the field name to "UserName", one word, in the table and
in the statement above, it works...

realistically, I can't do this since the users already have a copy of
this database...

i've tried ("[User Name]"), didn't work...
i've tried using the field number (i.e., Rows(0)(2)), didn't work

even if I don't update that field and update another that is one word,
I still get the error because the statement:
dcUpdate.Adapter.Update(dcUpdate.Dataset, "users")
internally builds an Update SQL that includes the field 'User Name'
and errors...

any guidance?

thanks...

Eych
Click to expand...
 
exactly...thanks Jeff


Jeff Johnson said:
I think you misunderstood the question, and I did too at first. He wasn't
saying that this code failed:

dcUpdate.Dataset.Tables(0).Rows(0)("User Name") = Me.textbox1.Text

He was saying that when he calls the Update method it throws an error due to
the field name having a space. The problem, as he discovered, was letting
the DataAdapter autogenerate the UPDATE statement, and the solution was to
create the UpdateCommand manually.
Click to expand...
 
all this said of course without trying your suggestion...but I am
doubtful if the 2 spaces would actually work...if it does, I guess I
could have saved a bunch of time...
Click to expand...
Again why did you not try it, this is a public usenet newsgroup. Putting
questions means as well that you try the suggestion, while this was one
which could cost you 1 minute and costed me a lot of time.

Cor
 
Hal,

As I mentioned in my original post, I did try all variations of using
the square brackets to no avail...


Cor,

I didn't see your response until the next day...there's a delay in
posting to google groups, so I came up with my solution...however, I
did try your suggestion and it still gave an error...


Hal Rosser said:
put the field name in square brackets [Field Name in Brackets]


Eych said:
I get a VB error when I try to update a table whose field I change
with the following statement:

dcUpdate.Dataset.Tables(0).Rows(0)("User Name") = Me.textbox1.Text


if I change the field name to "UserName", one word, in the table and
in the statement above, it works...

realistically, I can't do this since the users already have a copy of
this database...

i've tried ("[User Name]"), didn't work...
i've tried using the field number (i.e., Rows(0)(2)), didn't work

even if I don't update that field and update another that is one word,
I still get the error because the statement:
dcUpdate.Adapter.Update(dcUpdate.Dataset, "users")
internally builds an Update SQL that includes the field 'User Name'
and errors...

any guidance?

thanks...

Eych
Click to expand...
Click to expand...
 
I found it easier just to rename the field and write the code to reflect
that.

Eych said:
Hal,

As I mentioned in my original post, I did try all variations of using
the square brackets to no avail...


Cor,

I didn't see your response until the next day...there's a delay in
posting to google groups, so I came up with my solution...however, I
did try your suggestion and it still gave an error...


"Hal Rosser" <[email protected]> wrote in message
put the field name in square brackets [Field Name in Brackets]


Eych said:
I get a VB error when I try to update a table whose field I change
with the following statement:

dcUpdate.Dataset.Tables(0).Rows(0)("User Name") = Me.textbox1.Text


if I change the field name to "UserName", one word, in the table and
in the statement above, it works...

realistically, I can't do this since the users already have a copy of
this database...

i've tried ("[User Name]"), didn't work...
i've tried using the field number (i.e., Rows(0)(2)), didn't work

even if I don't update that field and update another that is one word,
I still get the error because the statement:
dcUpdate.Adapter.Update(dcUpdate.Dataset, "users")
internally builds an Update SQL that includes the field 'User Name'
and errors...

any guidance?

thanks...

Eych
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
How to subtract a number from a Mergefield? 0
VBA Excel - Select a value from one cell in Excel to update a SQL table 1
Moving table field name to variable name in VB.NET 1
Access How to check that the value of a field in a new record is not present in 2 fields of the table? 0
Access Access - Is it possible to repeat fields in a form? 1
Access Daily Entry with multiple entries per field 0
Update a table with code 1

Back
Top