Solved

Problem updating two fields in Access database table

Posted on 2009-07-09
5
165 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

856 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