|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
da.update (syntax error in UPDATE statement)I have three forms and update one table in an Access DB with sucess in one
form. In the other two forms I'm trying to do an update to a different table in the Access DB. The error I'm getting in syntax error on Update on the statement da.update(mydataset, "DATABASENAME") I do have a primary key on both and copied the code from the working update for the other two. Anyone have any ideas? Stephen,
I think that there is something wrong in your code, however we cannot see what, you have that before your eyes and you cannot even find it with that code, how do you think we could do that without that code? Cor Show quoteHide quote "Stephen Plotnick" <splotn***@groupcbf.com> schreef in bericht news:V8Sdnc1ixZ1IfbzYnZ2dnUVZ_rCdnZ2d@giganews.com... >I have three forms and update one table in an Access DB with sucess in one >form. > > In the other two forms I'm trying to do an update to a different table in > the Access DB. > > The error I'm getting in syntax error on Update on the statement > da.update(mydataset, "DATABASENAME") > > I do have a primary key on both and copied the code from the working > update for the other two. > > Anyone have any ideas? > Stephen,
I know nothing more of it, than that I have the idea that this event is what you need. http://msdn2.microsoft.com/en-us/library/system.windows.forms.control.invalidated.aspx I hope this helps, Cor Show quoteHide quote "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> schreef in bericht news:uSVvgTq5GHA.3808@TK2MSFTNGP06.phx.gbl... > Stephen, > > I think that there is something wrong in your code, however we cannot see > what, you have that before your eyes and you cannot even find it with that > code, how do you think we could do that without that code? > > Cor > > "Stephen Plotnick" <splotn***@groupcbf.com> schreef in bericht > news:V8Sdnc1ixZ1IfbzYnZ2dnUVZ_rCdnZ2d@giganews.com... >>I have three forms and update one table in an Access DB with sucess in one >>form. >> >> In the other two forms I'm trying to do an update to a different table in >> the Access DB. >> >> The error I'm getting in syntax error on Update on the statement >> da.update(mydataset, "DATABASENAME") >> >> I do have a primary key on both and copied the code from the working >> update for the other two. >> >> Anyone have any ideas? >> > > Thanks for the help.
It turned out the problem was with the the names of some of my fields in the Access DB; For exmple one was "$ Change"; when I changed it to "DollarChanged" everything worked. Steve Show quoteHide quote "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote in message news:OWas1rw5GHA.3736@TK2MSFTNGP02.phx.gbl... > Stephen, > > I know nothing more of it, than that I have the idea that this event is > what you need. > > http://msdn2.microsoft.com/en-us/library/system.windows.forms.control.invalidated.aspx > > I hope this helps, > > Cor > > "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> schreef in bericht > news:uSVvgTq5GHA.3808@TK2MSFTNGP06.phx.gbl... >> Stephen, >> >> I think that there is something wrong in your code, however we cannot see >> what, you have that before your eyes and you cannot even find it with >> that code, how do you think we could do that without that code? >> >> Cor >> >> "Stephen Plotnick" <splotn***@groupcbf.com> schreef in bericht >> news:V8Sdnc1ixZ1IfbzYnZ2dnUVZ_rCdnZ2d@giganews.com... >>>I have three forms and update one table in an Access DB with sucess in >>>one form. >>> >>> In the other two forms I'm trying to do an update to a different table >>> in the Access DB. >>> >>> The error I'm getting in syntax error on Update on the statement >>> da.update(mydataset, "DATABASENAME") >>> >>> I do have a primary key on both and copied the code from the working >>> update for the other two. >>> >>> Anyone have any ideas? >>> >> >> > > Stephen Plotnick wrote:
> The error I'm getting in syntax error on Update on the statement Are you using a CommandBuilder object? And do your tables, or any of the > da.update(mydataset, "DATABASENAME") fields within them, contain spaces or characters other than A-Z, 0-9 or underscore? If so, it's possible that you need to provide delimiters around the table/field names. For example, this is not a valid Access UPDATE statement: \\\ update my table set my field = 'some value' /// ....but this is: \\\ update [my table] set [my field] = 'some value' /// To fix this up, set properties of the CommandBuilder object as follows when you create it: .QuotePrefix = "[" .QuoteSuffix = "]" I've no idea if that'll help but it was worth a shot. :) -- (O)enone here is the code. There are only two fields changing and both are currency
types. In a field that is not changing there are symbold like "%" and quotes, etc. In the table that is working I do not have that type of data in a text field. I'll experiment. THanks, Steve If myDS.HasChanges Then Dim conn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data source='C:\Program Files\MyArea\Pricing\Pricing.mdb';Persist Security Info=False") Dim uSQL = "select * from Load_Tape WHERE StoreSelected = '" & "X" & "'" da.SelectCommand = New OleDb.OleDbCommand(uSQL, conn) Dim cb As New OleDb.OleDbCommandBuilder(da) Try myDS.Tables("Load_Tape").GetChanges() da.Update(myDS.Tables("Load_Tape")) Catch ex As Exception MessageBox.Show(ex.ToString) End Try End If Show quoteHide quote "Oenone" <oen***@nowhere.com> wrote in message news:uW%23UwXu5GHA.4276@TK2MSFTNGP04.phx.gbl... > Stephen Plotnick wrote: >> The error I'm getting in syntax error on Update on the statement >> da.update(mydataset, "DATABASENAME") > > Are you using a CommandBuilder object? And do your tables, or any of the > fields within them, contain spaces or characters other than A-Z, 0-9 or > underscore? If so, it's possible that you need to provide delimiters > around the table/field names. For example, this is not a valid Access > UPDATE statement: > > \\\ > update my table > set my field = 'some value' > /// > > ...but this is: > > \\\ > update [my table] > set [my field] = 'some value' > /// > > To fix this up, set properties of the CommandBuilder object as follows > when you create it: > > .QuotePrefix = "[" > .QuoteSuffix = "]" > > I've no idea if that'll help but it was worth a shot. :) > > -- > > (O)enone > I ended up changing all the Field Names in my data base to remove any spaces
or special characters. I could not find the delimiter option in VB.NET 2003. THanks for everything, Steve Show quoteHide quote "Oenone" <oen***@nowhere.com> wrote in message news:uW%23UwXu5GHA.4276@TK2MSFTNGP04.phx.gbl... > Stephen Plotnick wrote: >> The error I'm getting in syntax error on Update on the statement >> da.update(mydataset, "DATABASENAME") > > Are you using a CommandBuilder object? And do your tables, or any of the > fields within them, contain spaces or characters other than A-Z, 0-9 or > underscore? If so, it's possible that you need to provide delimiters > around the table/field names. For example, this is not a valid Access > UPDATE statement: > > \\\ > update my table > set my field = 'some value' > /// > > ...but this is: > > \\\ > update [my table] > set [my field] = 'some value' > /// > > To fix this up, set properties of the CommandBuilder object as follows > when you create it: > > .QuotePrefix = "[" > .QuoteSuffix = "]" > > I've no idea if that'll help but it was worth a shot. :) > > -- > > (O)enone > Stephen Plotnick wrote:
> I ended up changing all the Field Names in my data base to remove any From the code you posted, you need to put them here:> spaces or special characters. I could not find the delimiter option > in VB.NET 2003. \\\ [...] da.SelectCommand = New OleDb.OleDbCommand(uSQL, conn) Dim cb As New OleDb.OleDbCommandBuilder(da) cb.QuotePrefix = "[" cb.QuoteSuffix = "]" [...] /// That should get your SQL working regardless of spaces in table or field names. -- (O)enone Stephen...
i have a design question for you ... you mentioned you ...copied the code from the working update for the other two... Does you design approach include 'cut/paste - find and replacement' ? If so, you may want to reconsider and move this code / logic into a class of its own to isolate it or else you will be in for a maintenance nightmare ... what happenes when you have 12 forms that hit the same table, and use same 'update' code ... are you going to cut/paste the code into the other 8 forms? If so, what happens when you need to add 6 more fields to the table and decide to 'rename' 2 fields? Code maintenance nightmare... Jeff Show quoteHide quote "Stephen Plotnick" <splotn***@groupcbf.com> wrote in message news:V8Sdnc1ixZ1IfbzYnZ2dnUVZ_rCdnZ2d@giganews.com... >I have three forms and update one table in an Access DB with sucess in one >form. > > In the other two forms I'm trying to do an update to a different table in > the Access DB. > > The error I'm getting in syntax error on Update on the statement > da.update(mydataset, "DATABASENAME") > > I do have a primary key on both and copied the code from the working > update for the other two. > > Anyone have any ideas? > 
Other interesting topics
VB to C# converter
MDI Min Max - still there Q: closing forms da.update bool--> yes/no RuntimeHelpers.GetObjectValue Discussion Newbie Paint Question Writing Custom DLLs in VB.net 2 Re: Reading a list of users from XP VS 2005: Forms Designer Clears CancelButton and AcceptButton on Compile Recursive Directory / File Listing With Progress? |
|||||||||||||||||||||||
