INSERT sql statements 1

[trezlub]

I wish to add a record to a DB table using ASP. I know you can create a recordset and then use the AddNew method, .i.e.

rs.AddNew
rs("field&quot = "some value"
rs.update

but I wish to insert records by using INSERT sql statements. I can't seem to get this to work as ASP complains saying that the query should be updatable. Is it possible to use INSERT sql statements?

 

[mwolf00]

set cn = server.createobject("adodb.connection&quot
cn.open(myConnectionString)

cn.execute("INSERT INTO myTable (fieldList) VALUES ('" & variableValue & "')&quot

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rich Cook

 

[trezlub]

Cheers for replying mwolf00. The logic you have given is what I have been using but it is refusing to work. I am using:

Dim conn, sConnString, rs, sql

sql = 'Insert into tblRegisteredUsers (UserName, [Password], LastLogon, LoggedIn, Visit) Values( 'new','d','22/05/2003 11:47:33','Yes','1');'

set conn=Server.CreateObject("ADODB.Connection&quot
sConnString = &quot;<ACCESS CONNECTION STRING>&quot;

conn.open(sConnString)
conn.Execute(sql)

The exact error I get is:

&quot;Operation must use an updateable query.&quot;

 

[mwolf00]

Try to make the query simple (read &quot;one field&quot to start with. Everything looks fine here, but there may be a data type mismatch or something...

sql = &quot;Insert into tblRegisteredUsers (UserName) Values('new');&quot;




Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rich Cook

 

[trezlub]

I have tried it exactly as you said and I still get the same error. I don't understand!

 

[mwolf00]

Are you sure your connection is working?
Have you tried to SELECT something from the table?
Does the table allow for NULL values in any place that you did not supply a value?

set conn=Server.CreateObject(&quot;ADODB.Connection&quot
sConnString = &quot;<ACCESS CONNECTION STRING>&quot;
conn.open(sConnString)

SET rs = conn.execute(&quot;select count(*) from myTable&quot

if rs.eof then
response.write &quot;failure&quot;
else
response.write rs(0)
end if


Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rich Cook

 

[trezlub]

Yeah SELECT statements work fine. I seem able to retrieve data but not insert it.

The error is :

Operation must use an updateable query.

and it is associated with the line:

conn.Execute(sql)

 

[mwolf00]

http://support.microsoft.com/defaul...port/kb/articles/Q175/1/68.ASP&NoWebContent=1

http://www.xlinesoft.com/asprunner/docs/error_operation_must_use_an_updateable_query.htm

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rich Cook

 

[trezlub]

Have been to these sites and have done what is suggested but it still does not work. I am stumped.

Cheers for your help anyway

 

[trezlub]

Actually I have got it working now!!!

 

[mwolf00]

So, what was the problem & fix?

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rich Cook