Solved

Update Query Works In Access But Not From .NET

Posted on 2011-09-16
14
247 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:jnordeng
14 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 36552242
"Invalid Menu Handle" error when I download and try to open.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36552385
please post a sample value for the query above...
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36552388
post code for YN(v) function
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36552394
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
 

Author Comment

by:jnordeng
ID: 36553134
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
 
LVL 40
ID: 36553140
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
 
LVL 2

Expert Comment

by:mansooralia_yahoo
ID: 36553791
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:jnordeng
ID: 36554080
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
 

Author Comment

by:jnordeng
ID: 36554084
I have no trouble downloading the attached file from the original post by the way.
0
 

Author Comment

by:jnordeng
ID: 36560196
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
 

Author Comment

by:jnordeng
ID: 36560363
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
 

Author Comment

by:jnordeng
ID: 36560376
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
 

Accepted Solution

by:
jnordeng earned 0 total points
ID: 36560509
Found the solution. After I bracketed all the column names everything worked.
0
 

Author Closing Comment

by:jnordeng
ID: 36591646
It was a stupid and dumb mistake on my part. I really don't care who gets credit.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

743 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

15 Experts available now in Live!

Get 1:1 Help Now