Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

update query problem with sql server be

Posted on 2013-02-01
14
Medium Priority
?
445 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 31

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 31

Expert Comment

by:hnasr
ID: 38846097
Try:
& Abs(Me.OptionSelected)
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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 31

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 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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 …

722 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