Solved

Problem updating two fields in Access database table

Posted on 2009-07-09
5
162 Views
Last Modified: 2012-05-07
Hi,

When using the following strQuery to update one field in the "History" table of an Access 2007databse file every thing works fine:
            strQuery1 = "update History set HourTwo='" & SecondHourNotesLabel.Text & "'  where concierge = '" & ConciergeLabel.Text & "'  And shift = '" & ShiftLabel.Text & " ' And ActivityDate = '" & DateLabel.Text & "'"

But when I try to update two fields with the following query, the Remarks field stays blank:
            strQuery1 = "update History set HourTwo='" & SecondHourNotesLabel.Text & "' Remarks='" & remarksTextBox.Text & "' where concierge = '" & ConciergeLabel.Text & "'  And shift = '" & ShiftLabel.Text & " ' And ActivityDate = '" & DateLabel.Text & "'"

Both fields are text and they get the data from the text of labels on an ASP.NET form
Please point out my mistake.

Thanks

0
Comment
Question by:tariqanis
  • 3
  • 2
5 Comments
 
LVL 1

Expert Comment

by:zoidi
ID: 24811543
Hi,

What I do with my queries is to parametrize them. This means instead of string concatenation like you do, I do the following:




Dim cmdCommand As New OleDbCommand

cmdCommand.CommandText= "update History set HourTwo=@SecondHourNotes Remarks=@Remarks where concierge =@Concierge  And shift =@Shift And ActivityDate =@ActivitiyDate

cmdCommand.Paramters.Add(new OleDbParameter("@SecondHourNotes", SecondHourNotesLabel.Text))

cmdCommand.Paramters.Add(new OleDbParameter("@Remarks", remarksTextBox.Text))

cmdCommand.Paramters.Add(new OleDbParameter("@Concierge", ConciergeLabel.Text))

cmdCommand.Paramters.Add(new OleDbParameter("@Shift",  ShiftLabel.Text))

cmdCommand.Paramters.Add(new OleDbParameter("@ActivitiyDate", DateLabel.Text))

Open in new window

0
 
LVL 1

Expert Comment

by:zoidi
ID: 24811550
Sorry, should look like this
Dim cmdCommand As New OleDbCommand

cmdCommand.CommandText= "update History set HourTwo=@SecondHourNotes Remarks=@Remarks where concierge =@Concierge  And shift =@Shift And ActivityDate =@ActivitiyDate"

cmdCommand.Paramters.Add(new OleDbParameter("@SecondHourNotes", SecondHourNotesLabel.Text))

cmdCommand.Paramters.Add(new OleDbParameter("@Remarks", remarksTextBox.Text))

cmdCommand.Paramters.Add(new OleDbParameter("@Concierge", ConciergeLabel.Text))

cmdCommand.Paramters.Add(new OleDbParameter("@Shift",  ShiftLabel.Text))

cmdCommand.Paramters.Add(new OleDbParameter("@ActivitiyDate", DateLabel.Text))

Open in new window

0
 

Author Comment

by:tariqanis
ID: 24812226
Thank you Zoid for your response
I used your code which works fine, except for the "Remarks" field.
Every thing is saved, but the Remarks field is left empty.
0
 
LVL 1

Accepted Solution

by:
zoidi earned 500 total points
ID: 24812297
Hi,

I see there is an error in the query. You forget a comma before the Remarks=@Remarks. So the query should look like this:
cmdCommand.CommandText= "update History set HourTwo=@SecondHourNotes, Remarks=@Remarks where concierge =@Concierge  And shift =@Shift And ActivityDate =@ActivitiyDate"

Open in new window

0
 

Author Closing Comment

by:tariqanis
ID: 31601470
Thank you so much. It worked at last, and, as you said, it was the missing comma that was the cause of the problem. Thanks again
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now