Jumping in having not read the whole thread.... The reason this... SQL1 = "UPDATE TBL2 SET TblBody='Update Test' WHERE ID='1'"
...doesn't work I'm guessing is because the ID is a numerical data type not a string data type. If it sees a string, even if it contains a number, it will throw a data type mismatch error. If ID were a string data type field, you could do:- SQL1 = "UPDATE TBL2 SET TblBody='Update Test' WHERE ID='Page_Default'" Or SQL1 = "UPDATE TBL2 SET TblBody='Update Test' WHERE ID='Page Default'" ... The space in "Page Default" doesn't matter because it's surrounded by quotes and so delimited. If you had a quote in the string you'd have to change it to a two quotes so that the SQL engine understands that it's a quote in the string, and not the end of the string delimiter. As for running the update query in access, if it succeeds it'll tell you how many records were updated, thus telling you you have a working query. If it isn't a working query it'll tell you what's wrong with it. Dan ________________________________ From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Albert A. Hocking III Sent: 29 June 2005 20:33 To: [email protected] Subject: Re: [AspClassicAnyQuestionIsOk] Scrapen Rust Mark, Well, I don't think that its not a matter that I will never be converted, I think it has been an increasing negative attitude toward Microsoft (a couple of time I almost permanently went to the other side). I will admit that I bought the student version of .NET and I tried it and since that time it has set on a shelf. I don't know if anyone else felt over-hyped when it came out but I was very disappointed. As I looked through samples, I just saw more of a change then an improvement so I was really unimpressed. But I have to say for me they could have had more slack but when I couldn't find an SDI interface option it really spoiled the package for me because I usually feel cramped without it..... So it was both a style and practical issue...... To the code........Yes it works...........but that bothers me because there are some things that don't work and I don't understand why and most of it is the confusion over single, double quote and using nothing at all.......For instance, why would this work: SQL1 = "UPDATE TBL2 SET TblBody='Update Test' WHERE ID=1" And this not SQL1 = "UPDATE TBL2 SET TblBody='Update Test' WHERE ID='1'" Is it because the reference point wasn't a string like: SQL1 = "UPDATE TBL2 SET TblBody='Update Test' WHERE ID='Page_Default'" And to get even more confusing, if there is a space in the name, quotes would have to be used right? Like this: SQL1 = "UPDATE TBL2 SET TblBody='Update Test' WHERE ID='"Page Default"'" Or maybe like this? SQL1 = "UPDATE "Main Page" SET TblBody='Update Test' WHERE ID=1" One final question, When an UPDATE is made, I've tried to open a recordset (to write the newly changed records) and it always failed...........So is it only a SELECT that opens a table or did I do something syntactically wrong? I say that because got an error for trying to do a process while the database was closed.... Now......all that aside, this suggestion confused me....... "Another trick is to run your exact update query directly in access. If it works there, odds are it will work in the page too." What would an update query prove? I'm not seeing your logic at all..... ----- Original Message ----- From: Mark E To: [email protected] Sent: Tuesday, June 28, 2005 7:49 PM Subject: Re: [AspClassicAnyQuestionIsOk] Scrapen Rust Albert, Although a lot of people have moved to .net, someone nees to maintain all those classic asp web apps that won't be converted ;-) As far as your error, try removing the single quotes around your ID. This looks like it might be a field that holds numbers and you would not need any. Another trick is to run your exact update query directly in access. If it works there, odds are it will work in the page too. Let us know how it goes. Mark "Albert A. Hocking III" <[EMAIL PROTECTED]> wrote: Well, I use to be an old subscriber before you guys went on yahoo and the last I knew all everyone was starting to talk about ASP.NET coding. So that kinda dates me right there.... All I have to say is "WOW," pulling the recordset simplifies more then I would have ever thought......I'm a little surprised. So here is my code now: Dim Con Dim SQL1 SQL1 = "UPDATE TBL2 SET TblBody='Update Test' WHERE ID='1'" Set Con = Server.CreateObject("ADODB.Connection") con.Provider = "Microsoft.Jet.OLEDB.4.0" Con.Open "E:\e1\ScratchPad\DB\TestDB.mdb" Con.Execute SQL1 Con.Close Set Con = Nothing I wish that I could say that it works but I still can't get it to. I'm still getting: Microsoft JET Database Engine (0x80040E07) Data type mismatch in criteria expression. Now on this, even though I was a bit baffled by it because we can't get much more simple then this example, I did some digging. There are only two criteria's that I can find for this error and they are: a.. an incorrect date format that is being written to the datafield b.. a null trying to be written to a field that has data But in my case, this is not the case because all I'm trying to write the string "Update Test" to a field that has "This is a Test" for data. Then for the data format that is being used in the field is "TEXT" and it has a limit of 50......So I'm a little baffled....... The last thing that I could think of is that I'm just newly using Microsoft Access XP where I had been using Microsoft Office 97 before. So the last thing that popped into my head was it could be that I'm trying to call on an old driver. So I opened the ODBC dialog and it says that I'm running the "4.00.6200.00" Driver...........I don't know about you but I'm stumped because, to me, it should be working........ By the way, I mentioned the Keyword thing because I'm infamous for writing code that has RS as recordset, DB as database.......that doesn't get me into trouble but I do that on other things like naming a field in Access TEXT for text, MEMO for a memo or Date for a lousy date.........and I tend to get in a bit of a pickle...... ----- Original Message ----- From: Mark E To: [email protected] Sent: Tuesday, June 28, 2005 2:16 PM Subject: Re: [AspClassicAnyQuestionIsOk] Scrapen Rust Albert, You've come to the right place! And there's no shame in asking. You don't need a recordset object to update a record in your access db. Try it like this (untested and typed directly into this email, so take warning!): ' Setting variables Dim con, sql_update sql_update = "UPDATE TBL2 Set TblBody='Update Test' WHERE ID='1'" ' Creating the Connection Object and opening the database Set con = Server.CreateObject("ADODB.Connection") con.Open "E:\e1\ScratchPad\DB\TestDB.mdb" ' Executing the sql update code con.Execute sql_update ' Done. Now Close the connection con.Close Set con = Nothing As far as your rules, they do seem correct. I don't know all of the reserved words for access, but as long as you name your fields something specific, you should be OK. For example, don't use "Update", instead us "Updated" or "Is_Updated". That should cover you. Mark --- "Albert A. Hocking III" <[EMAIL PROTECTED]> wrote: > Howdy Y'all > > I hate to do this but I need to ask a question and > review a painfully > simple subject. I will admit that I'm having > trouble because I > haven't done any programming in a few years. I > could go on and on > about why but that's neither "here nor there." But > nonetheless, the > weaknesses that I had before are once again rising > to the surface so > I'm embarrassed to say that "I need some help." > > What I'm trying to do is simple. I'm once again > reviewing and > implementing SQL functions into my code so that I > can manipulate data > in an Access Database. Of course having some > experience, I know the > best thing to do is to approach this conceptually so > I'm just > trying > to derive the simplest of samples or examples (since > my old ones are > destroyed) so that I can just adapt them and then > develop them into as > large of a statement as I want later on. So, what I > have done is made > a table in my /DB directory in my web server and > then named it > TestDB.mdb. Then I have made three tables because > I'm working > with > three functions (SELECT, UPDATE, INSERT). > > Working with the select statement didn't cause a > problem but I > have > had all types of difficulty with UPDATE and I've > found it > frustrating > because I've written statements like that before. > So, if none of > you > mind, I would like to review some of the rules when > using SQL UPDATE > statements. > > First, here is my code (I'm doing a DNS-Less connect > with an > execute): > > <% > set conn=Server.CreateObject("ADODB.Connection") > conn.Provider="Microsoft.Jet.OLEDB.4.0" > conn.Open "E:\e1\ScratchPad\DB\TestDB.mdb" > set rs=Server.CreateObject("ADODB.Recordset") > SQL = "UPDATE TBL2 Set TblBody='Update Test' WHERE > ID='1'" > Set RS = Conn.Execute(SQL) > > rs.Close > conn.close > %> > > So, here is what I remember in review. The syntax > is: > > UPDATE table SET FIELD_TOBE_CHANGED='Text' WERE > FIELD_REFERENCED='Idendifyer' > > So here is what I remember of the rules: > 1. SQL commands are not case sensitive: > Select vs SELECT > 2. As long as there isn't a new line, SQL isn't > white space > sensitive: > Field='text' vs Field = `Text' > 3. Variables that are given must be enclosed in > single quotes `' > 4. Changing more then one field requires separation > by a comma > 5. Fields cannot be a keyword (boy I would kill for > a list of > these!!!!) > 6. Conditions can be set on the identifier (and, or > and not). > 7. I know about using double quotes and ampersand > signs for variables > and form object....but I don't want to do that much > typing > right now. > > Now all that aside, the simple example that I have > been giving above > has been "puked like a two year old brownie" every > time...is there > something that I'm forgetting? Basically most of > the time > I'm getting > a syntax error or Data type mismatch and frankly I > don't get it. > > > > > ------------------------------------------------------------------------ ------ YAHOO! GROUPS LINKS a.. Visit your group "AspClassicAnyQuestionIsOk" on the web. b.. To unsubscribe from this group, send an email to: [EMAIL PROTECTED] c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service. ------------------------------------------------------------------------ ------ ------------------------------------------------------------------------ ------ No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.5/32 - Release Date: 6/27/2005 [Non-text portions of this message have been removed] --------------------------------- YAHOO! GROUPS LINKS Visit your group "AspClassicAnyQuestionIsOk" on the web. To unsubscribe from this group, send an email to: [EMAIL PROTECTED] Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service. --------------------------------- [Non-text portions of this message have been removed] ------------------------------------------------------------------------ ------ YAHOO! GROUPS LINKS a.. Visit your group "AspClassicAnyQuestionIsOk" on the web. b.. To unsubscribe from this group, send an email to: [EMAIL PROTECTED] c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service. ------------------------------------------------------------------------ ------ ------------------------------------------------------------------------ ------ No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.5/32 - Release Date: 6/27/2005 [Non-text portions of this message have been removed] ________________________________ YAHOO! GROUPS LINKS * Visit your group "AspClassicAnyQuestionIsOk <http://groups.yahoo.com/group/AspClassicAnyQuestionIsOk> " on the web. * To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED] subscribe> * Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service <http://docs.yahoo.com/info/terms/> . ________________________________ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/AspClassicAnyQuestionIsOk/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
