Update Query Works In Access But Not From .NET

I have a situation here that is driving me nuts. First the yes/no columns answer to 0 and -1 not 0 and 1 like they do in .NET (checkboxes). I wrote a function to convert them but it shouldn't be like that.I have the UPDATE statement below that runs fine in access but from vb.net it fails with a syntax error. Enclosed is the UPDATE statement and the table schema. Any help is greatly appreciated. Thanks so much.

"UPDATE [XIFDAConfig] SET [Description] = '" & Me.txtXrayDescription.Text & "', CreatedBy = '" & Me.txtCreatedBy.Text & "', CreateDate = """ & Me.txtCreateDate.Text & _
""", BitDepth = " & Me.cmbBitDepth.Text & ", AutoExposure = " & Me.rbAutoExposure.Checked & ",  [ImageType] = 0" & _
", Binning = " & Me.cmbBinning.Text & ", FullChip = " & Me.rbFullChip.Checked & ", [Left] = " & Me.numLeft.Value & _
", [Top] = " & Me.numTop.Value & ", [Width] = " & Me.numWidth.Value & ", [Heigth] = " & Me.numHeigth.Value & _
", Brightness = " & Me.numBrightness.Value & ", AutoGainLimit = " & Me.cmbAutoGain.Text & ", MinExposure = " & YN(Me.chkUseMinExposure) & _
", MinExposureValue = " & Me.numMinExposure.Value & ", ManualExposureValue = " & Me.numManualExposure.Value & ", ManualGainLimit = " & _
Me.numManualExposure.Value & ", BlackLevelSubtract = 0, ChipDefectCorrection = " & YN(Me.chkChipDefectCorrection) & _
", BackGroundSubtract= 0, NoiseFilter = " & YN(Me.chkNoiseFilter) & ", NoiseFilterValue = " & _
Me.numNoiseFilter.Value & ", BackGroundFrames = 0, BrightfieldImage = '" & Me.txtBrightfieldImage.Text & _
"', Voltage = " & Me.numVoltage.Value & ", Current = " & Me.numCurrent.Value & ", Maxma = " & YN(Me.chkMaxma) & _
", DefSysCal = " & YN(Me.chkUseSysCal) & ", SpatialCalFactor = '" & Me.txtCalibrationFactor.Text & "', ODCalFile = '" & Me.txtODCalFile.Text & _
"',  [Default] = 0, AutoNormalize = " & YN(Me.chkAutomaticallyNormalize) & ", DarkfieldImage = '" & Me.txtDarkfieldImage.Text & _
"' WHERE [ID] = " & IDValue & ";"


 XIFDAConfig.zip
jnordengAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GRayLCommented:
"Invalid Menu Handle" error when I download and try to open.
0
HainKurtSr. System AnalystCommented:
please post a sample value for the query above...
0
HainKurtSr. System AnalystCommented:
post code for YN(v) function
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

HainKurtSr. System AnalystCommented:
have a look at this

http://databases.aspfaq.com/database/how-do-i-handle-bit/boolean-columns.html

and make sure your  YN(v) function returns "0": False/Ubchecked, "1":True/checked
0
jnordengAuthor Commented:
Well the funciton YN is trained to return a value of -1 or 0 or the checkboxes instead of 0 and 1. That is what that does.
0
Jacques Bourgeois (James Burger)PresidentCommented:
A lot of things could be wrong. But there is no way to tell in the way it is presented to us. A concatenation is not the result.

A syntax error can be a very simple ' missing somewhere. It is very hard to detect when you see the code. It would be a lot easier to pinpoint if you were looking at the resulting String instead of trying to read in between the numerous " & ". It could be the format of a date. Seeing the code does not tells us what the date looks like when it is passed to the database.

I freak when I see something such as CreateDate = """ & Me.txtCreateDate.Text & """. A user can type anything in a TextBox. For Access, 02-03-04 is not the same as 02/03/04. Is there some routine that checks if the date is valid before simply inserting the Text the user types into the SQL? Treating a Date as Text is never a good idea, since the format can change from one computer to another and be different than the one in the database. Is the date treated as a Date or as Text in the database? If it is as a date, then the date should be passed in the following format so as not to be dependant on the control panel of the caller:

CreateDate = #" & <Date in format MM-dd-yyyy> & "#"

Same thing with the TextBoxes that are supposed to return numerical values. Are they checked for a valid numeric value?

Where do you get the 1 from CheckBoxes in .NET? True is 1 in some languages and -1 in VB, but if you convert the result of a CheckBox that is True in VB.NET (CInt(CheckBox.Checked)), you do get -1. Anyway, VB sees everything that is zero as False and everything that is not zero as True. So 1 and -1 both gives you True. That should not be a problem. In any case, if you get 1 and need -1, simply do it this way, adding the minus sign:

MinExposure = -" & YN(Me.chkUseMinExposure)

Grab the result of the concatenation through a watch, a Debug.WriteLine or a MessageBox. Seeing it as it is, with the real values, enables you to see what the command really is. As a big concatenation, it is very hard to see what could be the problem. As a real SQL statement, a syntax error very often jumps at you.
0
mansooralia_yahooCommented:
check with your single double quotes, normally access to .net understanding of these is different, to write a double quote in a string you can use " " " " in access where as in .net you have to use " " ", without spaces ofcourse.
0
jnordengAuthor Commented:
My problem is that the string it generates I copy and paste it in Access query and it runs fine. It makes the troubleshooting very difficult because I don't know why vb.net is telling me there is a syntax error. But once I get to work monday I will copy the output string to you.
0
jnordengAuthor Commented:
I have no trouble downloading the attached file from the original post by the way.
0
jnordengAuthor Commented:
The YN function is like this. Its an attempt to find out if this was causing the problem.
Function YN(ByVal chx As CheckBox) As Integer
        If chx.Checked = True Then
            Return 0
        Else
            Return -1
        End If
    End Function
0
jnordengAuthor Commented:
This is the data call
Function DataAction(ByVal ds As DataSet, ByVal Table As DataTable, ByVal CommandText As String, ByVal ActionValue As SqlCommandType) As Boolean
        Dim cmd As New OleDb.OleDbCommand(CommandText)
        cmd.Connection = TableAdapter.Connection
        Dim Conn As OleDb.OleDbConnection = cmd.Connection
        Try
            Dim da As New OleDb.OleDbDataAdapter
            If cmd.Connection.State = ConnectionState.Closed Then
                cmd.Connection.Open()
            End If
            Select Case ActionValue
                Case 0
                    da.SelectCommand = cmd
                    ds.Tables(Table.TableName).Clear()
                    da.Fill(ds.Tables(Table.TableName))
                Case 1
                    da.InsertCommand = cmd
                    cmd.ExecuteNonQuery()
                    'da.InsertCommand.ExecuteNonQuery()
                Case 2
                    da.UpdateCommand = cmd
                    da.UpdateCommand.ExecuteNonQuery()
                Case 3
                    da.DeleteCommand = cmd
                    da.DeleteCommand.ExecuteNonQuery()
            End Select
            cmd.Connection.Close()
            Return True

        Catch ex As Exception

            ErrorString = ex.Message & vbCrLf & ex.ToString

            Debug.WriteLine(cmd.CommandText)

            If Conn.State = ConnectionState.Open Then

                Conn.Close()

            End If

            MessageBox.Show("An error has occured." & vbCrLf & vbCrLf & ErrorString)

            Return False

        End Try

    End Function
0
jnordengAuthor Commented:
I took out all the line continuation too

If DataAction(Me.XIFDA, Me.XIFDA.Tables("XIFDAConfig"), _
"UPDATE [XIFDAConfig] SET [Description] = '" & Me.txtXrayDescription.Text & "', CreatedBy = '" & Me.txtCreatedBy.Text & "', CreateDate = """ & Me.txtCreateDate.Text & """, BitDepth = " & Me.cmbBitDepth.Text & ", AutoExposure = " & RB(Me.rbAutoExposure) & ",  [ImageType] = 0" & ", Binning = " & Me.cmbBinning.Text & ", FullChip = " & RB(Me.rbFullChip) & ", [Left] = " & Me.numLeft.Value & ", [Top] = " & Me.numTop.Value & ", [Width] = " & Me.numWidth.Value & ", [Heigth] = " & Me.numHeigth.Value & ", Brightness = " & Me.numBrightness.Value & ", AutoGainLimit = " & Me.cmbAutoGain.Text & ", MinExposure = " & CBool(Me.chkUseMinExposure.Checked) & ", MinExposureValue = " & Me.numMinExposure.Value & ", ManualExposureValue = " & Me.numManualExposure.Value & ", ManualGainLimit = " & Me.numManualExposure.Value & ", BlackLevelSubtract = 0, ChipDefectCorrection = " & CBool(Me.chkChipDefectCorrection.Checked) & ", BackGroundSubtract = 0, NoiseFilter = " & CBool(Me.chkNoiseFilter.Checked) & ", NoiseFilterValue = " & Me.numNoiseFilter.Value & ", BackGroundFrames = 0, BrightfieldImage = '" & Me.txtBrightfieldImage.Text & "', Voltage = " & Me.numVoltage.Value & ", Current = " & Me.numCurrent.Value & ", Maxma = " & CBool(Me.chkMaxma.Checked) & ", DefSysCal = " & CBool(Me.chkUseSysCal.Checked) & ", SpatialCalFactor = '" & Me.txtCalibrationFactor.Text & "', ODCalFile = '" & Me.txtODCalFile.Text & "',  [Default] = 0, AutoNormalize = " & CBool(Me.chkAutomaticallyNormalize.Checked) & ", DarkfieldImage = '" & Me.txtDarkfieldImage.Text & "' WHERE [ID] = " & IDValue & ";", SqlCommandType.UpdateData) = True Then

            RefreshTables()
            GetDGDataRow(IDValue, Me.txtXrayDescription.Text)
        End If
0
jnordengAuthor Commented:
Found the solution. After I bracketed all the column names everything worked.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jnordengAuthor Commented:
It was a stupid and dumb mistake on my part. I really don't care who gets credit.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.