p2p.wrox.com: Archives - asp

asp_databases thread: INSERT INTO Problem

Message #1 by "Kelvin Ng" <ngwtat@y...> on Tue, 24 Apr 2001 15:30:43

Hi guys, I know this is simple question, can u help me?





<%

  Option Explicit

  Dim strConnect

%>

<!-- #include file="..\Ch13\DataStore.asp" -->

<!-- METADATA TYPE="typelib" 

              FILE="G:\Program Files\Common Files\System\ado\msado15.dll" -

->

<HTML>

<HEAD>

<TITLE>Using SQL and the Command Object</TITLE>

</HEAD>

<BODY>

<% 

  Dim objComm, intNoOfRecords

  Set objComm = Server.CreateObject("ADODB.Command")



  objComm.ActiveConnection = strConnect

  objComm.CommandText = "INSERT INTO Movies(MovieId, Title) VALUES 

(331, 'Psycho')"

  objComm.CommandType = adCmdText

  objComm.Execute intNoOfRecords	' <-- Line 20, Error in here

  Response.Write "The INSERT command has been executed; " & _

                 "Number of records inserted = " & intNoOfRecords & "<HR>"



  Set objComm = Nothing

%>

</BODY>

</HTML>





HTTP 500.100 - Internal Server Error - ASP error

Internet Information Services



-------------------------------------------------



Technical Information (for support personnel)



Error Type:

Microsoft JET Database Engine (0x80004005)

Operation must use an updateable query.

/Beginning ASP 3.0/Ch14/SQLInsertDelete.asp, line 20



-------------------------------------------------





I was using the Command Object to add a record to a mdb file.

I am sure the attribute of mdb file is not in read-only mode,

but why i got an error message above.



At the same time, if I replace the CommandText Value, 

objComm.CommandText = "SELECT * FROM Movies"

It works fine for me. Any suggestion?

Message #2 by "Charles Feduke" <webmaster@r...> on Tue, 24 Apr 2001 15:59:43 -0400

>   objComm.CommandText = "INSERT INTO Movies(MovieId, Title) VALUES

> (331, 'Psycho')"



    Try a space between "Movies" and the parenthesis.  If that doesn't work,

make sure that Movies is a table and not a nonupdatable view.



- Chuck



----- Original Message -----

From: "Kelvin Ng" <ngwtat@y...>

To: "ASP Databases" <asp_databases@p...>

Sent: Tuesday, April 24, 2001 3:30 PM

Subject: [asp_databases] INSERT INTO Problem





> Hi guys, I know this is simple question, can u help me?

>

>

> <%

>   Option Explicit

>   Dim strConnect

> %>

> <!-- #include file="..\Ch13\DataStore.asp" -->

> <!-- METADATA TYPE="typelib"

>               FILE="G:\Program Files\Common Files\System\ado\msado15.dll" -

> ->

> <HTML>

> <HEAD>

> <TITLE>Using SQL and the Command Object</TITLE>

> </HEAD>

> <BODY>

> <%

>   Dim objComm, intNoOfRecords

>   Set objComm = Server.CreateObject("ADODB.Command")

>

>   objComm.ActiveConnection = strConnect

>   objComm.CommandText = "INSERT INTO Movies(MovieId, Title) VALUES

> (331, 'Psycho')"

>   objComm.CommandType = adCmdText

>   objComm.Execute intNoOfRecords ' <-- Line 20, Error in here

>   Response.Write "The INSERT command has been executed; " & _

>                  "Number of records inserted = " & intNoOfRecords & "<HR>"

>

>   Set objComm = Nothing

> %>

> </BODY>

> </HTML>

>

>

> HTTP 500.100 - Internal Server Error - ASP error

> Internet Information Services

>

> -------------------------------------------------

>

> Technical Information (for support personnel)

>

> Error Type:

> Microsoft JET Database Engine (0x80004005)

> Operation must use an updateable query.

> /Beginning ASP 3.0/Ch14/SQLInsertDelete.asp, line 20

>

> -------------------------------------------------

>

>

> I was using the Command Object to add a record to a mdb file.

> I am sure the attribute of mdb file is not in read-only mode,

> but why i got an error message above.

>

> At the same time, if I replace the CommandText Value,

> objComm.CommandText = "SELECT * FROM Movies"

> It works fine for me. Any suggestion?

>

>

>

>

Message #3 by "Kelvin Ng" <ngwtat@y...> on Wed, 25 Apr 2001 10:27:03

My mdb (Access 2000) Database file Structure is as below:-



CREATE TABLE Movies(

MovieId text,

Title text)



So, I think my view is an updatable view. Space between "Movies" and the 

parenthesis is still continue produce the same error message.



The script is works well on my friend's computer, which runs on PWS 4.0. 

But the same script, it cannot runs on my Windows 2000 system with IIS 

5.0. What is the problem? Do you think install the SERVICE Pack 1 for 

Windows 2000 can solve the problem?

Message #4 by "Charles Feduke" <webmaster@r...> on Wed, 25 Apr 2001 15:45:51 -0400

    Try installing MDAC 2.6 (search www.microsoft.com for mdac_typ.exe).



- Chuck



----- Original Message ----- 

From: "Kelvin Ng" <ngwtat@y...>

To: "ASP Databases" <asp_databases@p...>

Sent: Wednesday, April 25, 2001 10:27 AM

Subject: [asp_databases] Re: INSERT INTO Problem





> My mdb (Access 2000) Database file Structure is as below:-

> 

> CREATE TABLE Movies(

> MovieId text,

> Title text)

> 

> So, I think my view is an updatable view. Space between "Movies" and the 

> parenthesis is still continue produce the same error message.

> 

> The script is works well on my friend's computer, which runs on PWS 4.0. 

> But the same script, it cannot runs on my Windows 2000 system with IIS 

> 5.0. What is the problem? Do you think install the SERVICE Pack 1 for 

> Windows 2000 can solve the problem?

Message #5 by "Ken Schaefer" <ken@a...> on Thu, 26 Apr 2001 10:36:01 +1000

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

: My mdb (Access 2000) Database file Structure is as below:-

:

: CREATE TABLE Movies(

: MovieId text,

: Title text)



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



If MovieID is a *text* field (ie a MEMO field) then you need to put ' '

around the values that you insert.

Your original SQL statement was:



>   objComm.CommandText = "INSERT INTO Movies(MovieId, Title) VALUES

> (331, 'Psycho')"



You don't have ' around 331. You only omit the delimiters when you are

inserting into a numeric field.



Cheers

Ken

Message #6 by "Kelvin Ng" <ngwtat@y...> on Fri, 27 Apr 2001 03:05:46

Thank You to all who helps me.



I already solve the problem!!



I found that the mdb file cannot put in Windows 2000(NTFS) directory! 

After I'm moving the mdb file to other directory (FAT32), the "Operation 

must use an updateable query" error has been disappear. Quiet strange 

solution, but it's works!!!



If you knows why the problem arise, plz let me know.



 - Kelvin (ngwtat@y...)

Message #7 by =?iso-8859-1?Q?H=E5kan_Frennesson?= <hakan@c...> on Fri, 27 Apr 2001 10:02:38 +0200

Hi!



When a file or folder is on a NTFS formatted drive or partition you use NTFS

security ie you can�t write to files or folders without proper permissions.

On a FAT32 partition however, there is no security restrictions. That�t why

you can read and write to the database without error messages. I suggest you

read a little bit about NT security.



Hth,



Hakan



-----Original Message-----

From: Kelvin Ng [mailto:ngwtat@y...]

Sent: den 27 april 2001 03:06

To: ASP Databases

Subject: [asp_databases] Re: INSERT INTO Problem



Thank You to all who helps me.



I already solve the problem!!



I found that the mdb file cannot put in Windows 2000(NTFS) directory!

After I'm moving the mdb file to other directory (FAT32), the "Operation

must use an updateable query" error has been disappear. Quiet strange

solution, but it's works!!!



If you knows why the problem arise, plz let me know.



 - Kelvin (ngwtat@y...)

Return to Index

P2P Archives