Solved

Problem updating two fields in Access database table

Posted on 2009-07-09
5
167 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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 how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

740 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