Getting Insert to work!! It must be to early...

[snowboardr]

This code isnt working?! I don't understand why...


'# The error..

Microsoft JET Database Engine error '80004005'
Operation must use an updateable query.
/rate/report_done.asp, line 41

<%

set my_conn= Server.CreateObject(&quot;ADODB.Connection&quot;)
ConnString = &quot;Provider=Microsoft.Jet.OLEDB.4.0; Data Source=&quot; & Server.MapPath(&quot;db.mdb&quot;)
'set rs = server.CreateObject(&quot;ADODB.RecordSet&quot;) '# I tried this commented / uncommented

username = Request.Form(&quot;username&quot;)
email = Request.Form(&quot;email&quot;)
comments = Request.Form(&quot;comments&quot;)



my_conn.Open ConnString

strSql = &quot;INSERT INTO flag_it (flag_username,flaged_by_email,flaged_reason)&quot;
strSql = strSql & &quot; VALUES (&quot; 
strSql = strSql & &quot;'&quot; & username & &quot;',&quot;
strSql = strSql & &quot;'&quot; & email & &quot;',&quot;
strSql = strSql & &quot;'&quot; & comments & &quot;'&quot;
strSql = strSql & &quot;)&quot;

Set rs = my_conn.execute(strSql)'# I tried this commented / uncommented
'rs = my_conn.Execute (strSql)'# I tried this commented / uncommented

'Response.write strSql  '# I looked at my querystring to see if it was right...it looks fine.
'Response.end


'# I check the DB to see if all table names were right...evething is correct. am I missing something?!


'my_conn.close
'set my_conn = nothing


%>

Any suggestions?

-Thanks in advance

 

[cmmrfrds]

You can return a recordset on the execute but the 'Insert" is not going to do this. On an insert you could return the number of records affected, but this makes more sense on an update statement.

my_conn.execute(strSql)
my_conn.execute strSql

Dim ret As Long 'returns the number of records affected.
cn.Execute strSql, ret

Some things to do.
1. check the permissions on the directory where the MDB is located.
2. Are you using the default 'admin' account on the Access database (MDB)?

I recommend building your connection string with the Microsoft User Data Link Wizard (udl file) and then you can use the test button on the wizard to check the connection. Also, look under the "ALL" tab to see all the properties. Especially check the mode property because you can deny update with this property.

 

[snowboardr]

I checked the Dir the db is in I looked for the data link wizard but could not find it. I am still getting the error mentioned above..i am using the default admin account as well.

Jason

 

[cmmrfrds]

Here is how you invoke the udl wizard. Do this on your desktop.

1. create a blank notepad file.
2. save the file.
3. rename the file by adding a new extention of .udl
make sure you save as all files not txt or some other
file type.
4. the icon for the file should change from notepad to a
little computer - it has now become the wizard.
5. double click on the file and it will bring up a dialog
box with multipule tabs.
6. use the microsoft access provider.
7. look under the ALL tab and there will be parameter settings which you can change if necessary. There is a test
button, press to test the connection.
8. close the file.
9. open the file from notepad instead of double clicking the icon. You will see the connection string which you can copy and paste into your program.

Paste your query back here so we can see the latest syntax you are using.

 

[cmmrfrds]

Do you have other fields in your table, other than the ones you have listed. I noticed that you are not providing a primary key on the insert, is the key auto number. Do you have some required fields in the table that you are not providing a value for - this would cause an error.

 

[snowboardr]

Oh man, I think you maybe right.. ill try it!

Thanks.

 

[snowboardr]

set my_conn= Server.CreateObject("ADODB.Connection&quot
'set rs = server.CreateObject("ADODB.RecordSet&quot

'set my_conn= Server.CreateObject("ADODB.Connection")
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("db.mdb")
'set rs = server.CreateObject("ADODB.RecordSet")
my_conn.Open ConnString
'set rs = my_conn.Execute (StrSql)
strSql = "INSERT INTO flag_it (flag_username,flaged_by_email,flaged_reason)"
strSql = strSql & " VALUES (" 
strSql = strSql & "'" & username & "',"
strSql = strSql & "'" & email & "',"
strSql = strSql & "'" & comments & "'"
strSql = strSql & ")"

'# It errors with anything I try here on the following line:

set rs = my_conn.execute strSql
rs = my_conn.execute (strSql)
my_conn.execute strSql

I tried the wizard, the connection was fine, and there was read/write access.

 

[cmmrfrds]

There was a thread around the 22th that dealt with a similar issue. It had to do with the user account permissions on the server. What kind of permissions your account has on the server. To test out give full permissions on the directory.

 

[snowboardr]

I gave full permissions to the folder its in, and check to see if it was not read only.. I still get the not updateable error..

 

[snowboardr]

Do you think it has to be settings? Or is it my code?

 

[cmmrfrds]

As an alternative to the execute method try the open method on the recordset. I am not sure of the parameter settings for the execute method, but I use the Open all the time. If this works you can concentrate on Parameter settings on the execute method. replace my_conn.execute strSql with

set rs = server.CreateObject("ADODB.RecordSet&quot
rs.Open strSql, my_conn, 3, 3

 

[link9]

Please read this post. We covered alot of bases, and got the problem resolved.

thread333-217591

Also, please read this thread concerning using an INSERT statement to "open" a recordset, and why not to do that.

thread333-221912

hope that helps!
paul