Solved

update query problem with sql server be

Posted on 2013-02-01
14
437 Views
Last Modified: 2013-02-05
I'm trying to run a query with a liked table in sql server 2005 and the -1 doesn't seem to be working. OptionSelected is a bit data type. A similar query with a table within access works.

Any ideas?

Dim strSQL As String
strSQL = "Update tbltrad_commandes_FPdetails set OptionSelected = 0 " & _
"where tbltrad_commandes_FPdetails.OptionSelected <>" & Me.OptionSelected

DoCmd.RunSQL strSQL
0
Comment
Question by:Shawn
[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
  • 5
  • 5
  • 3
  • +1
14 Comments
 
LVL 30

Expert Comment

by:hnasr
ID: 38846086
In SQl Server bit accepts 1 for true and 0 for false values.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38846092
in sql table, bit data type have values 1 for true and 0 for false

try

strSQL = "Update tbltrad_commandes_FPdetails set OptionSelected = 0 " & _
"where tbltrad_commandes_FPdetails.OptionSelected <>" & (Me.OptionSelected) * -1
0
 
LVL 30

Expert Comment

by:hnasr
ID: 38846097
Try:
& Abs(Me.OptionSelected)
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 1

Author Comment

by:Shawn
ID: 38847117
sorry, neither of those worked.

when I hard code it to 0 it works as long as I am cheching the box rather than unchecking.

strSQL = "Update tbltrad_commandes_FPdetails set OptionSelected = 0 " & _
"where tbltrad_commandes_FPdetails.OptionSelected <> 0"

  When I uncheck it I first get a write confilct asking if I want to copy to clipboard or drop changes. Then the error says it can't find the field '      '. Very strange.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 38847262
Try to upload a sample access database with just objects to show the issue.
Type the script to create the sql table.
0
 
LVL 1

Author Comment

by:Shawn
ID: 38852419
Here is a sample DB and a script to create 2 tables in sql server. I am getting the same error with the sample db.
testBITmdb.zip
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38853490
Check your SQL Server table's design and ensure that your OptionSelected  field does not allow nulls, and that it has a default value set to False  (0) .

If you have any existing NULLs in this field, update them to false  (0).

Once you do that, try your original code again:

strSQL = "Update tbltrad_commandes_FPdetails set OptionSelected = 0 " & _
"where tbltrad_commandes_FPdetails.OptionSelected <>" & Me.OptionSelected

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 38853518
Also, what exactly do you want to see when the user chooses "OptionSelected"?

The way you currently have your code written, it will do nothing at all... the syntax you have will look for all cases where OptionSelected is false and update it to false (so there will be no change to your data at all if the user sets Me.OptionSelected to true).  

Do you want to toggle this field according to the user selection?  Make a backup and try this:

Dim strSQL As String
strSQL = "Update tbltrad_commandes_FPdetails set OptionSelected = " & Me.OptionSelected & _
" where tbltrad_commandes_FPdetails.OptionSelected <>" & Me.OptionSelected

DoCmd.RunSQL strSQL 

Open in new window

0
 
LVL 1

Author Comment

by:Shawn
ID: 38853548
>>Check your SQL Server table's design and ensure that your OptionSelected  field does not allow nulls, and that it has a default value set to False  (0) .

this is the case. no nulls allowed and default value set to false


>>Also, what exactly do you want to see when the user chooses "OptionSelected"?
I want only that selection to be checked all others should be unchecked.

tried your code and same error
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38853573
Your write conflict error is occurring because your update code is trying to change the record at the same time that the user is updating it through the form.  You need to *exclude* the current record from your update.

Also from your notes in the form, my understanding is that your goal is simply to allow one selection at a time (unchecking previous selections).

Try this:

Private Sub OptionSelected_AfterUpdate()


DoCmd.SetWarnings False
Dim strSQL As String

strSQL = "Update dbo_TMPtbltrad_commandes_FPdetails set OptionSelected = 0 " & _
"where dbo_TMPtbltrad_commandes_FPdetails.OptionSelected = true AND trad_commandes_FPdetailsID <> " & Me.trad_commandes_FPdetailsID



DoCmd.RunSQL strSQL

DoCmd.SetWarnings True

Requery

End Sub

Open in new window

0
 
LVL 1

Author Comment

by:Shawn
ID: 38853677
there are no longer write conflicts but it doesn't uncheck the other records
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 38854154
That code works, with your tables converted to local Access tables.

Try both of these for SQL Server:

strSQL = "Update dbo_TMPtbltrad_commandes_FPdetails set OptionSelected = 0 " & _
"where dbo_TMPtbltrad_commandes_FPdetails.OptionSelected <> 0 AND trad_commandes_FPdetailsID <> " & Me.trad_commandes_FPdetailsID

Open in new window


Or this, using 1 for true as suggested by the others:

strSQL = "Update dbo_TMPtbltrad_commandes_FPdetails set OptionSelected = 0 " & _
"where dbo_TMPtbltrad_commandes_FPdetails.OptionSelected = 1 AND trad_commandes_FPdetailsID <> " & Me.trad_commandes_FPdetailsID

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 38854161
Or if you are only allowing the current item to be checked:

strSQL = "Update dbo_TMPtbltrad_commandes_FPdetails set OptionSelected = 0 " & _
"where trad_commandes_FPdetailsID <> " & Me.trad_commandes_FPdetailsID

Open in new window

0
 
LVL 1

Author Closing Comment

by:Shawn
ID: 38857486
the first one here worked. thanks for persevering!
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

751 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