Problem updating two fields in Access database table

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

tariqanisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zoidiCommented:
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
zoidiCommented:
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
tariqanisAuthor Commented:
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
zoidiCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tariqanisAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.