Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

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
0
jnordeng
Asked:
jnordeng
1 Solution
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
jnordengAuthor Commented:
It was a stupid and dumb mistake on my part. I really don't care who gets credit.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now