0

EDIT:

this issue has changed. HansUp solved this syntax issues with in the update statement. What is happening now is completely different. process is

  • user selects a gridview item
  • it redirects them to the update page and using a datareader, fills the text boxes and check boxes based on the id passed in the url
  • the user can then make their changes to the text boxes/ check boxes and then press the update button which runs the update query.

what i have found is happening is that although a user might change the text, when they submit the changes, the update query is still using whatever was loaded into that text box by the data reader on the page load. Here is the code below:

 Protected Sub SubmitBTN_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles UpdateBTN.Click
        Dim tiresdim As Integer = 0
        If TiresCHK.Checked = True Then
            tiresdim = -1
        ElseIf TiresCHK.Checked = False Then
            tiresdim = 0
        End If
        Dim repairs As Integer = 0
        If RepairsCheckBX.Checked = True Then
            repairs = -1
        ElseIf RepairsCheckBX.Checked = False Then
            repairs = 0
        End If
        Dim onlotdim As Integer = 0
        If OnLotCheckBX.Checked = True Then
            onlotdim = -1
        ElseIf OnLotCheckBX.Checked = False Then
            onlotdim = 0
        End If
        Dim offpropdim As Integer = 0
        If OffPropertyCheckBX.Checked = True Then
            offpropdim = -1
        ElseIf OffPropertyCheckBX.Checked = False Then
            offpropdim = 0
        End If
        Dim soldim As Integer = 0
        If SoldCheckBX.Checked = True Then
            soldim = -1
        ElseIf SoldCheckBX.Checked = False Then
            soldim = 0
        End If

        Dim id = CType(Request.QueryString("param1"), Integer)


        Dim connection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Jason\Desktop\UsedCarProductionSched\app_data\UsedCars.accdb;Persist Security Info=False;")
        Dim sql As String = "update Master set stocknum='" & StockNumTxt.Text & "',[year]='" & YearTxt.Text & "',make='" & MakeTxt.Text & "', model='" & ModelTxt.Text & "', color='" & ColorTxt.Text & "',location='" & LocationDropDownList.SelectedValue & "',tiresneeded=" & tiresdim & ",stockin=#" & StockInDateTxt.Text & "#,SvcRONum='" & SrvcROnumTxt.Text & "',ucistartdate=#" & UCIStartDateTxt.Text & "#,UCIEstCompleteDate=#" & UCIEstComDateTXT.Text & "#,repairs=" & repairs & ",CollisionRONum='" & CollisionRONumTXT.Text & "',[detail]=#" & DetailTXTbox.Text & "#, other='this has to work',onlot=" & onlotdim & ",offproperty=" & offpropdim & ",sold=" & soldim & " WHERE recnum=" & id
        connection.Open()

        Dim cmd As New OleDb.OleDbCommand(sql, connection)

        cmd.ExecuteNonQuery()
        connection.Close()
        'Dim updateta As New DataSet1TableAdapters.Master1TableAdapter
        'updateta.UpdateQuery(StockNumTxt.Text, YearTxt.Text, MakeTxt.Text, ModelTxt.Text, ColorTxt.Text, LocationDropDownList.SelectedValue, TiresCHK.Checked, StockInDateTxt.Text, SrvcROnumTxt.Text, UCIStartDateTxt.Text, UCIEstComDateTXT.Text, RepairsCheckBX.Checked, CollisionRONumTXT.Text, DetailTXTbox.Text, OtherTxt.Text, OnLotCheckBX.Checked, OffPropertyCheckBX.Checked, SoldCheckBX.Checked, Request.QueryString("param1"))
        Response.Redirect("success.aspx")
    End Sub

    Function myCStr(ByVal test As Object) As String
        If isdbnull(test) Then
            Return ("")
        Else
            Return CStr(test)
        End If
    End Function

    Public Shared Function IsDBNull( _
 ByVal value As Object _
) As Boolean
        Return DBNull.Value.Equals(value)
    End Function

    Private Sub getData(ByVal user As String)
        'declare variables to fill
        Dim stock As String, make As String, color As String, stockin As Date, ucistart As Date, repairs As Boolean, _
            tires As Boolean, onlot As Boolean, sold As Boolean, year As Boolean, model As String, location As String, srvcRO As String, ucicompldate As Date, _
            collRO As String, other As String, offprop As Boolean, detail As Date

        Dim dt As New DataTable()
        Dim connection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Jason\Desktop\UsedCarProductionSched\app_data\UsedCars.accdb;Persist Security Info=False;")
        connection.Open()
        Dim sqlcmd As String = "SELECT * from Master WHERE RecNum = @recnum"
        Dim FileCommand3 As New OleDb.OleDbCommand(sqlcmd, connection)
        FileCommand3.Parameters.AddWithValue("@recnum", user)
        Dim Reader3 As OleDb.OleDbDataReader = FileCommand3.ExecuteReader()
        If Reader3.Read Then

            stock = myCStr(Reader3("StockNum"))
            make = myCStr(Reader3("Make"))
            color = myCStr(Reader3("Color"))
            stockin = IIf(Reader3("stockin") Is DBNull.Value, Nothing, Reader3("stockin"))
            ucistart = IIf(Reader3("ucistartdate") Is DBNull.Value, Nothing, Reader3("ucistartdate"))
            repairs = Reader3("Repairs")
            tires = Reader3("tiresneeded")
            onlot = Reader3("onlot")
            sold = Reader3("sold")
            year = myCStr(Reader3("year"))
            model = myCStr(Reader3("model"))
            location = myCStr(Reader3("location"))
            srvcRO = myCStr(Reader3("svcROnum"))
            ucicompldate = IIf(Reader3("uciestcompletedate") Is DBNull.Value, Nothing, Reader3("uciestcompletedate"))
            collRO = myCStr(Reader3("collisionROnum"))
            other = myCStr(Reader3("other"))
            offprop = Reader3("offProperty")
            detail = IIf(Reader3("detail") Is DBNull.Value, Nothing, Reader3("detail"))

        End If
        connection.Close()

        If detail <> Nothing Then
            DetailTXTbox.Text = detail.ToString("M/dd/yyyy")
        Else : DetailTXTbox.Text = ""
        End If
        If ucicompldate <> Nothing Then
            UCIEstComDateTXT.Text = ucicompldate.ToString("MM/dd/yyyy")
        Else : UCIEstComDateTXT.Text = ""
        End If

        If stockin <> Nothing Then
            StockInDateTxt.Text = stockin.ToString("MM/dd/yyyy")
        Else : StockInDateTxt.Text = ""
        End If
        If ucistart <> Nothing Then
            UCIStartDateTxt.Text = ucistart.ToString("M/dd/yyyy")
        Else : UCIStartDateTxt.Text = ""
        End If
        StockNumTxt.Text = stock
        MakeTxt.Text = make
        ColorTxt.Text = color
        RepairsCheckBX.Checked = repairs
        TiresCHK.Checked = tires
        OnLotCheckBX.Checked = onlot
        SoldCheckBX.Checked = sold
        YearTxt.Text = year
        ModelTxt.Text = model
        If location <> Nothing Then
            LocationDropDownList.SelectedValue = location
        End If
        SrvcROnumTxt.Text = srvcRO
        CollisionRONumTXT.Text = collRO
        OtherTxt.Text = other
        OffPropertyCheckBX.Checked = offprop
    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        getData(Request.QueryString("param1"))
    End Sub

My asp.net application is supposed to execute a simple update query against an access DB but instead it throws a syntax error. I have copy and pasted the exact query directly into my access DB and it executes properly. Here is the code:

Dim connection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Jason\Desktop\UsedCarProductionSched\app_data\UsedCars.accdb;Persist Security Info=False;")
    Dim sql As String = "update Master " _
                          + "set stocknum='" & StockNumTxt.Text & "',year='" & YearTxt.Text & "',make='" & MakeTxt.Text & "', model='" & ModelTxt.Text & "', color='" & ColorTxt.Text & "',location='" & LocationDropDownList.SelectedValue & "',tiresneeded=" & tiresdim & ",stockin=#" & StockInDateTxt.Text & "#,SvcRONum='" & SrvcROnumTxt.Text & "',ucistartdate=#" & UCIStartDateTxt.Text & "#,UCIEstCompleteDate=#" & UCIEstComDateTXT.Text & "#,repairs=" & repairs & ",CollisionRONum='" & CollisionRONumTXT.Text & "',detail=#" & DetailTXTbox.Text & "#, other='" & OtherTxt.Text & "',onlot=" & onlotdim & ",offproperty=" & offpropdim & ",sold=" & soldim & " " _
                          + "WHERE recnum=" & Request.QueryString("param1")
    connection.Open()

    Dim cmd As New OleDb.OleDbCommand(sql, connection)
    cmd.ExecuteNonQuery()
    connection.Close()
Improve this question
4
  • Can you please post the exact SQL query you intend to run so I can compare Commented Jul 14, 2011 at 3:35
  • copied from the immediate window in debug this is the SQL query it is trying to run: update Master set stocknum='1',year='True',make='1', model='1', color='1',location='Select A Location...',tiresneeded=-1,stockin=#06/01/2011#,SvcRONum='1',ucistartdate=#6/10/2011#,UCIEstCompleteDate=#07/09/2011#,repairs=-1,CollisionRONum='1',detail=#7/09/2011#, other='THIS WORKSSSSSSSSS',onlot=-1,offproperty=-1,sold=-1 WHERE recnum=15" Commented Jul 14, 2011 at 3:45
  • That UPDATE statement in your comment includes a stray double quote at the end. Is that actually part of the statement? Commented Jul 14, 2011 at 3:53
  • Yea. I added it so there would be a space before the where clause started. I guess i could just put that space before the "where" rather than have 2 stray quotes. Commented Jul 14, 2011 at 3:57

3 Answers 3

Reset to default
2

Two of your columns use reserved words as their names: year; detail. If you must keep those names, enclose them in square brackets in the UPDATE statement to avoid the possibility of confusing Access' database engine.

What data type is the year field? The finished statement in your comment includes year='True' And that's fine if "year" is text data type. But if it's a Yes/No field, lose the quotes from around the word True.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Ok...the year is string data type. It was an input error on my part for it to say true, but it doesnt make a difference. putting brackets solved one problem, but now i have another. i now get this error: InvalidCastException was unhandled by user code Conversion from string " WHERE recnum=" to type 'Double' is not valid. im assuming it has something to do with the string value i am requesting from the query string not matching with the identity value of the access DB
Please show us the exact text of the current UPDATE statement which is producing that error. Copy and paste it as an edit to your question. Also tell us what happens when you paste the same text into SQL View of a new query in an Access session with your UsedCars.accdb open.
It looks like a different issue to me now. Before it was the Access database engine complaining about the UPDATE syntax. Now it's about something else ... apparently some Dot.Net issue which is Greek to me.
1

What is the syntax error? Also, you should be validating the input for the SQL before throwing that into the database update. If you have apostrophes in the text that will cause a problem. A parameterized query would be more ideal, too.

Improve this answer

3 Comments

I have included part of the entire error details: System.Data.OleDb.OleDbException was unhandled by user code ErrorCode=-2147217900 Message=Syntax error in UPDATE statement. Source=Microsoft Office Access Database Engine
And you are positive the statement that you provided runs fine when executed in Access?
You should give credit to HansUp for your answer and post your new problem as a separate question.
1

Sample for parameterized SQL operations, as suggested by SkinnyWhiteNinja

I have a table with 4 colunms, CollCode and CollSeq are the key, TermType and TermText are the modifiable data The code explains how to insert, update or delete a row with parameters instaed if textvalues in the SQL. The code is valid only for ACCESS, SQL SERVER or MYSQL require different code for the template and have different DbTypes

in the first part of the program:

    ' Insert 
    Dim DbConn As New OleDbConnection(SqlProv)
    Dim SQLTwInsert As String = "INSERT INTO SearchTerms (CollCode, CollSeq, TermType, TermText) VALUES (?, ?, ?, ?)"
    Dim DRTwInsert As OleDbDataReader = Nothing
    Dim DCCTwInsert As OleDbCommand
    Dim TwInsP1 As New OleDbParameter("@CollCode", OleDbType.VarChar, 4)
    Dim TwInsP2 As New OleDbParameter("@CollSeq", OleDbType.Integer, 4)
    Dim TwInsP3 As New OleDbParameter("@TermType", OleDbType.VarChar, 4)
    Dim TwInsP4 As New OleDbParameter("@TermText", OleDbType.VarChar, 255)
    DCCTwInsert = New OleDbCommand(SQLTwInsert, DbConn)
    DCCTwInsert.Parameters.Add(TwInsP1)
    DCCTwInsert.Parameters.Add(TwInsP2)
    DCCTwInsert.Parameters.Add(TwInsP3)
    DCCTwInsert.Parameters.Add(TwInsP4)

    ' Delete 
    Dim SQLTwDelete As String = "DELETE FROM SearchTerms WHERE CollCode = ? AND CollSeq = ? AND TermType = ? AND TermText = ?"
    Dim DRTwDelete As OleDbDataReader = Nothing
    Dim DCCTwDelete As OleDbCommand
    Dim TwDelP1 As New OleDbParameter("@CollCode", OleDbType.VarChar, 4)
    Dim TwDelP2 As New OleDbParameter("@CollSeq", OleDbType.Integer, 4)
    Dim TwDelP3 As New OleDbParameter("@TermType", OleDbType.VarChar, 4)
    Dim TwDelP4 As New OleDbParameter("@TermText", OleDbType.VarChar, 255)
    DCCTwDelete = New OleDbCommand(SQLTwDelete, DbConn)
    DCCTwDelete.Parameters.Add(TwDelP1)
    DCCTwDelete.Parameters.Add(TwDelP2)
    DCCTwDelete.Parameters.Add(TwDelP3)
    DCCTwDelete.Parameters.Add(TwDelP4)

    ' Update 
    Dim SQLTwUpdate As String = "UPDATE SearchTerms SET TermType = ?, TermText = ? WHERE CollCode = ? AND CollSeq = ? AND TermType = ? AND TermText = ?"
    Dim DRTwUpdate As OleDbDataReader = Nothing
    Dim DCCTwUpdate As OleDbCommand
    Dim TwUpdP1 As New OleDbParameter("@TermType", OleDbType.VarChar, 4)
    Dim TwUpdP2 As New OleDbParameter("@TermText", OleDbType.VarChar, 255)
    Dim TwUpdP3 As New OleDbParameter("@CollCode", OleDbType.VarChar, 4)
    Dim TwUpdP4 As New OleDbParameter("@CollSeq", OleDbType.Integer, 4)
    Dim TwUpdP5 As New OleDbParameter("@oldTermType", OleDbType.VarChar, 4)
    Dim TwUpdP6 As New OleDbParameter("@oldTermText", OleDbType.VarChar, 255)
    DCCTwUpdate = New OleDbCommand(SQLTwUpdate, DbConn)
    DCCTwUpdate.Parameters.Add(TwUpdP1)
    DCCTwUpdate.Parameters.Add(TwUpdP2)
    DCCTwUpdate.Parameters.Add(TwUpdP3)
    DCCTwUpdate.Parameters.Add(TwUpdP4)
    DCCTwUpdate.Parameters.Add(TwUpdP5)
    DCCTwUpdate.Parameters.Add(TwUpdP6)

in the processing part of the program:

    ' Update  
                    TwUpdP1.Value = new value TermType
                    TwUpdP2.Value = new value TermText
                    TwUpdP3.Value = key value CollCode
                    TwUpdP4.Value = key value CollSeq
                    TwUpdP5.Value = old value TermType to avoid updating a row that 1 millisecond earlier was modified by someone else
                    TwUpdP6.Value = old value TermText
                    Try
                        DRTwUpdate = DCCTwUpdate.ExecuteReader()
                    Catch ex As Exception
                        your type of report exception 
                    Finally
                        If Not (DRTwUpdate Is Nothing) Then
                            DRTwUpdate.Dispose()
                            DRTwUpdate.Close()
                        End If
                    End Try

    ' Insert  
                    TwInsP1.Value = new key value CollCode
                    TwInsP2.Value = new key value CollSeq
                    TwInsP3.Value = value TermType  
                    TwInsP4.Value = value TermText
                    Try
                        DRTwInsert = DCCTwInsert.ExecuteReader()
                    Catch ex As Exception
                        your type of report exception 
                    Finally
                        If Not (DRTwInsert Is Nothing) Then
                            DRTwInsert.Dispose()
                            DRTwInsert.Close()
                        End If
                    End Try
    ' Delete  
                    TwDelP1.Value = key value CollCode
                    TwDelP2.Value = key value CollSeq
                    TwDelP3.Value = old value TermType to avoid deleting a row that 1 millisecond earlier was modified by someone else
                    TwDelP4.Value = old value TermText
                    Try
                        DRTwDelete = DCCTwDelete.ExecuteReader()
                    Catch ex As Exception
                        your type of report exception 
                    Finally
                        If Not (DRTwDelete Is Nothing) Then
                            DRTwDelete.Dispose()
                            DRTwDelete.Close()
                        End If
                    End Try

Try it, it really avoids many problems, though a bit clumbsy to write it all.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.