?
Solved

Problem updating two fields in Access database table

Posted on 2009-07-09
5
Medium Priority
?
169 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 1500 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

777 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