SQL statement for database update

  • Thread starter Thread starter carmen
  • Start date Start date
C

carmen

I am trying to get an SQL statement to work in hard
coding some data into an Access database. When I tried an
INSERT statement I recevied an error message to use and
updatebale query. So I then used the following UPDATE
statement and was told my syntax was in error. Can anyone
help me in properly stating this query? Thanks in advance.

struSQL = "UPDATE Sessions SET
(EventDateTime,URL,SessionID) VALUES ('" & Now() & "','"
& strURL & "','" & strSession & "')"
 
The syntax you have is more like the insert pattern. For the update try

struSQL = "UPDATE Sessions SET EventDateTime='" & Now() & "',URL='"
& strURL & "',SessionID='" & strSession & "')"

The key is:
UPDATE tablename SET field1=value1,field2=value2, etc..
with the optional WHERE condition at the end so that you don't over-write
all your data. If you don't use the WHERE condition it will over-write every
record in the table.

Hope this helps,
Mark Fitzpatrick
Microsoft MVP - FrontPage
 
In addition, the updateable query error doesn't mean you need an update.
What it probably means is that the database does not have write access. If
the DB just has read access than the .asp page can't save to the db.

Hope this helps,
Mark Fitzpatrick
Microsoft MVP - Frontpage
 
In fact, he needs to forget about the UPDATE query, since he wants to INSERT
a new record. What he needs to do is to grant "Modify" permission for the
database folder to the IUSER_<machine name> Anonymous Internet User account
on that machine, and use an INSERT query.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
http://www.takempis.com
Big things are made up of
lots of little things.
 
Sorry about the sex change, Carmen. I can certainly assure you that the
permission problem is exactly what I stated. Let us know how it works out!

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
http://www.takempis.com
Big things are made up of
lots of little things.
 
Kevin:

Sure enough, the permissions needed to be changed and an
INSERT statement was required. Thanks a million. I got
the following SQL atatement to work by using a Command
Object (yet another unexpected twist in my adventure):

Dim objComm, objRS, intNoOfRecords
Set objComm = Server.CreateObject("ADODB.Command")
objComm.ActiveConnection = strConnect
objComm.CommandText = "INSERT INTO Sessions (UserID)
VALUES ('Grey')"
objComm.CommandType = adCmdText
objComm.Execute intNoOfRecords
Response.Write "The INSERT command has been executed; "
& _
"The number of records inserted = " &
intNoOfRecords & "<HR>"



However, I am having a hell of a time editing the SQL
statement above to the latter version below where I try
to insert four values instead of a single value:

"INSERT INTO Sessions
(EventDateTime,URL,UserID,SessionID) VALUES (Mydate,
strURL, strUserName, strSession)"


I get the error: [Microsoft][ODBC Microsoft Access
Driver] Too few parameters. Expected 4. Moreover, the
error message references a line of code which
reads "objComm.Execute intNoOfRecords". How can I alter
this line(s) to accomodate my wish?

Kevin, thanks for any and all advice. Learning ASP in a
month has been both gratifying and hellish. Bumps like
this are excruciatingly frustrating.

Cheers,
Carmen
 
Hi Carmen,

Sounds like you're not using the Database Results Wizard, but are coding
your own ASP. If so, the problem is not with the line you mentioned, but
with your SQL Statement. The VALUES clause is the clause that indicates what
values are to be placed in the fields identified in the fields list. But
your values don't make sense. I'm not sure what those 4 string tokens you
put in there are supposed to represent. Are they the names of form fields?
If so, you need to concatenate the values of the form fields into your WHERE
clause, not the names of them. Also, as a SQL Statement is a string, the
database uses punctuation to indicate the data type of the value being
inserted. Text values are enclosed in single quotes. In Access (are you sing
Access?), DateTime values are enclosed in "#" characters. Numeric values are
not punctuated. So, assuming that (1) these are form field names, and (2)
you're using Access, and (3) the data types for the 4 fields are DateTime,
String, String, and String, your resulting SQL Statement would look like the
following:

strSQL = "INSERT INTO Sessions (EventDateTime,URL,UserID,SessionID) " &_
"VALUES (#" & Mydate & "#, '" & strURL & "', '" & strUserName &_
"', '" & strSession & ")"

--
HTH,

Kevin Spencer
..Net Developer
Microsoft MVP
http://www.takempis.com
Big things are made up
of lots of little things
 
Kevin:

You're a doll! I got the following to work as a variation
of yours:

"INSERT INTO Sessions
(EventDateTime,URL,UserID,SessionID) VALUES (Now(), '" &
strURL & "', 'Carmen', '" & strSession & "')"

When I was trying all types of variations on getting this
statement to allow for my varaible entries, I
attempted '" variableName '", and "' variableName '", but
not your correctly phrased '" & variableName & "'. Whew!
What a relief. Thanks much.

Carmen
 
Back
Top