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
<*> 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/
