Solved

update query problem with sql server be

Posted on 2013-02-01
14
394 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

744 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

14 Experts available now in Live!

Get 1:1 Help Now