Solved

update query problem with sql server be

Posted on 2013-02-01
14
420 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
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

832 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