Solved

update query problem with sql server be

Posted on 2013-02-01
14
406 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 119

Expert Comment

by:Rey Obrero
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
 
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

912 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now