469,934 Members | 2,465 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,934 developers. It's quick & easy.

delete data in ms access using asp

46
this my coding..
Expand|Select|Wrap|Line Numbers
  1. <%
  2. dim cintid
  3. cintid = request.form("txtid")
  4. dim objconn
  5. set objconn = server.createobject("ADODB.connection")
  6. objconn.provider = "Microsoft.Jet.OLEDB.4.0"
  7. objconn.ConnectionString = "Data Source=" & Server.MapPath("product.mdb")
  8. objconn.mode = 3
  9. objconn.open
  10.  
  11. mysql = "delete * from processor where id-no = " & cintid & ";"
  12.  
  13. objconn.execute(mysql)
  14.  
  15.  response.redirect("home.asp") %>
when i run, it give me an error
Expand|Select|Wrap|Line Numbers
  1. Error Type:
  2. Microsoft JET Database Engine (0x80040E10)
  3. No value given for one or more required parameters.
please help me..i dont know where is my error..
Sep 20 '07 #1
23 4515
jhardman
3,406 Expert 2GB
the minus sign (hyphen) is a special character in SQL. "id-no" should be enclosed in square brackets like this:
Expand|Select|Wrap|Line Numbers
  1. mysql = "DELETE * FROM processor WHERE [id-no] = " & cintid & ";"
I have decided this is so tedious I would rather rename all of my db fields without hyphens. Let me know if this solves the problem.

Jared
Sep 20 '07 #2
najmi
46
the minus sign (hyphen) is a special character in SQL. "id-no" should be enclosed in square brackets like this:
Expand|Select|Wrap|Line Numbers
  1. mysql = "DELETE * FROM processor WHERE [id-no] = " & cintid & ";"
I have decided this is so tedious I would rather rename all of my db fields without hyphens. Let me know if this solves the problem.

Jared
it worked..thank again..i have one more problem again about updating data..here my coding..

mysql = "update processor set description = " & cstrdes & "& price =" & ccurprice & ";"

it give me an error:

Microsoft JET Database Engine (0x80040E10)
No value given for one or more required parameters.

this error is same as my last error in add data..
Sep 22 '07 #3
jhardman
3,406 Expert 2GB
it worked..thank again..i have one more problem again about updating data..here my coding..

mysql = "update processor set description = " & cstrdes & "& price =" & ccurprice & ";"

it give me an error:

Microsoft JET Database Engine (0x80040E10)
No value given for one or more required parameters.

this error is same as my last error in add data..
the ampersand (&) is not recognized in SQL. You need to use the word "AND" like this:
Expand|Select|Wrap|Line Numbers
  1. mysql = "update processor set description = " & cstrdes & " AND price =" & ccurprice &  ";"
after this line you can print "mysql" to see what it gives you. Some database managers have a function where you can test you SQL queries. It should say something like this:
Expand|Select|Wrap|Line Numbers
  1. update processor set description = 432 AND price = 12.67
Let me know if this works.

Jared
Sep 22 '07 #4
najmi
46
the ampersand (&) is not recognized in SQL. You need to use the word "AND" like this:
Expand|Select|Wrap|Line Numbers
  1. mysql = "update processor set description = " & cstrdes & " AND price =" & ccurprice &  ";"
after this line you can print "mysql" to see what it gives you. Some database managers have a function where you can test you SQL queries. It should say something like this:
Expand|Select|Wrap|Line Numbers
  1. update processor set description = 432 AND price = 12.67
Let me know if this works.

Jared

it didn`t work i give my full coding:
Expand|Select|Wrap|Line Numbers
  1. <%
  2. dim cstrdes,ccurprice
  3. cstrdes = request.form("txtdes")
  4. ccurprice = request.form("txtprice")
  5. dim objconn
  6. set objconn = server.createobject("ADODB.connection")
  7. objconn.provider = "Microsoft.Jet.OLEDB.4.0"
  8. objconn.ConnectionString = "Data Source=" & Server.MapPath("product.mdb")
  9. objconn.mode = 3
  10. objconn.open
  11.  
  12. mysql = "update processor set description = " & cstrdes & " AND price =" & ccurprice &  ";"
  13. objconn.execute(mysql)
  14.  
  15.  response.redirect("home.asp") %>
err:
Expand|Select|Wrap|Line Numbers
  1. Microsoft JET Database Engine (0x80040E14)
  2. Syntax error (missing operator) in query expression 'Intel Dual Core AND price =300'.
Sep 22 '07 #5
najmi
46
this is my coding:

<%
dim cstrdes,ccurprice
cstrdes = request.form("txtdes")
ccurprice = request.form("txtprice")
dim objconn
set objconn = server.createobject("ADODB.connection")
objconn.provider = "Microsoft.Jet.OLEDB.4.0"
objconn.ConnectionString = "Data Source=" & Server.MapPath("product.mdb")
objconn.mode = 3
objconn.open

mysql = "update processor set description =" & cstrdes & " AND price =" & ccurprice & ";"
objconn.execute(mysql)

response.redirect("home.asp") %>

when i run it give me an err:

Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression 'Intel Dual Core AND price =300'.

please help me..
Sep 24 '07 #6
shweta123
692 Expert 512MB
Hi,

I think , your sql statement is missing quotes. So try putting it this way,

mysql = "update processor set description ='" & cstrdes & "' AND price =" & ccurprice & ";"
objconn.execute(mysql)
Sep 24 '07 #7
jhardman
3,406 Expert 2GB
1- numeric values should not be in quotes, but text values (strings) should be in single quotes. Since the price is a numerical value it should not be in quotes (you got that part right) but the description is a string so it should be in quotes. Your line 12 should look like this:
Expand|Select|Wrap|Line Numbers
  1. mysql = "update processor set description = '" & cstrdes & "' AND price =" & ccurprice &  ";"
2- between lines 12 & 13 put this line:
Expand|Select|Wrap|Line Numbers
  1. response.write mysql & "<br>" & vbNewLine
This will show how your statement ends up before it is sent to the db and causes an error. It should look like this:
Expand|Select|Wrap|Line Numbers
  1. update processor set description = 'Intel Dual Core' AND price =300;
Notice that the value of description is in quotes.

Jared
Sep 24 '07 #8
jhardman
3,406 Expert 2GB
Please don't double-post questions. If you want, it is appropriate to send private messages to site experts asking them to read and reply to a particular post.

Jared
Sep 24 '07 #9
najmi
46
it still not work..i have modified a little bit my coding..it will be like this:

<%
dim cstrdes,ccurprice,cintid
cintid = request.form("txtid")
cstrdes = request.form("txtdes")
ccurprice = request.form("txtprice")
dim objconn
set objconn = server.createobject("ADODB.connection")
objconn.provider = "Microsoft.Jet.OLEDB.4.0"
objconn.ConnectionString = "Data Source=" & Server.MapPath("product.mdb")
objconn.mode = 3
objconn.open

mysql = "update processor set description ='" & cstrdes & "' AND price =" & ccurprice & "where [id-no] = " & cintid & ";"
objconn.execute(mysql)

response.redirect("home.asp") %>


err:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression ''cat' AND price =32where [id-no] = 34'.
Sep 25 '07 #10
jhardman
3,406 Expert 2GB
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression ''cat' AND price =32where [id-no] = 34'.
DO WHAT I SAY! DO WHAT I SAY! ARGGGGHHHH!
I WILL BAN YOU UNLESS YOU DO WHAT I SAY!

PUT A RESPONSE.WRITE LINE AFTER THE MYSQL LINE!

I SAID TO DO THIS IN MY LAST POST AND I AM VERY DISINCLINED TO HELP SOMEONE WHO DOESN"T LISTEN WHEN I TRY TO HELP.

there needs to be a space between the price and the keyword "where". If you had printed out the mysql line like I already told you you would have seen that you told the db to set price equal to "32where" which doesn't make sense. Of course it gives an error! the line makes no sense. The SQL statement should be almost readable English. You may look up SQL syntax for all the details, but it is BLATANTLY OBVIOUS THAT THERE NEED TO BE SPACES BETWEEN DIFFERENT WORDS. This is the reason I suggest that you print out the mysql line before you send it to the database. This will save you a lot of stress and it will keep me from banning you.

Jared
Sep 25 '07 #11
najmi
46
thank a lot for helping me..i already apply what you ask me to do..yes it work and didn`t give any err,but when i update my database the item in description become an integer.for eg: i have intel pentium 4 with price 300...when i want to update for eg its price to 280..when i run, in my database the description become 0 or -1 and the price didn`t change..
Sep 26 '07 #12
jhardman
3,406 Expert 2GB
I'm not sure where the error is, I usually do this in a different method. I'm not sure this will make a difference, but is usually easier for me to understand this way. In the end it could be that some of your form inputs are not coming through as you expect. you can try this to check:
Expand|Select|Wrap|Line Numbers
  1. for each x in request.form
  2.    response.write x & ": " & request.form(x) & "<br>" & vbnewLine
  3. next
This just prints out what was sent through the form.

Anyway, my code for updating the db would look something like this:
Expand|Select|Wrap|Line Numbers
  1. <%
  2. dim cstrdes,ccurprice,cintid
  3. cintid = request.form("txtid")
  4. cstrdes = request.form("txtdes")
  5. ccurprice = request.form("txtprice")
  6. dim objconn, objRS, mysql
  7. set objconn = server.createobject("ADODB.connection")
  8. objconn.provider = "Microsoft.Jet.OLEDB.4.0"
  9. objconn.ConnectionString = "Data Source=" & Server.MapPath("product.mdb")
  10. objconn.open
  11.  
  12. set objRS = server.createobject("ADODB.recordset")
  13.  
  14. mysql = "SELECT * FROM processor WHERE [id-no] = " & cintid & ";" 
  15. response.write "mysql: " & mysql & "<br>" & vbNewLine
  16. objRS.open mysql, objconn, 2, 3
  17.  
  18. objRS.description = cstrdes
  19. objRS.price = ccurprice
  20. objRS.update
  21.  
  22. ' response.redirect("home.asp") %>
Instead of just sending an update statement through the db connection like you did, I prefer to open a recordset with the particular record I want. I modify the recordset then call the update method which updates the db. Let's see what this gives you (notice I commented out the redirect line because I want to stay on this page until I get this problem solved).

Jared
Sep 26 '07 #13
najmi
46
hai..thank again.i really appreciate what you have done to me but i can`t use the objrs method because i have a problem with read & write permission.ok i will share with you what i want to do may be you have a brilliant idea to help me..what i`m try to do is to develop content management system for my website..you can visit my website at www.alnajmtech.com.what i want to do which CMS is to control the data in my website..i already finish with add and delete button.now as we know my problem is in updating data.firstly, what i do is view all my data.then when i think that i need to change the data, i need to click button edit.so to find that particular data i use one form to search the data by enter data id-no.when it found,it will display in a new form that i can change the data.after i finish alter that data,of course i need to save that data.so to save that data,i use the update statement...i hope you can understand what i try to tell you above..erm..what you think.
Sep 27 '07 #14
jhardman
3,406 Expert 2GB
hai..thank again.i really appreciate what you have done to me but i can`t use the objrs method because i have a problem with read & write permission.
Hmm. I believe that if you have permission to do an update, you have permission to use my method. I know of no conflict.
ok i will share with you what i want to do may be you have a brilliant idea to help me..what i`m try to do is to develop content management system for my website..you can visit my website at www.alnajmtech.com.what i want to do which CMS is to control the data in my website..i already finish with add and delete button.now as we know my problem is in updating data.firstly, what i do is view all my data.then when i think that i need to change the data, i need to click button edit.so to find that particular data i use one form to search the data by enter data id-no.when it found,it will display in a new form that i can change the data.after i finish alter that data,of course i need to save that data.so to save that data,i use the update statement...i hope you can understand what i try to tell you above..erm..what you think.
I do something very similar in one of my websites. Are you sure you can't use the recordset? I said earlier to do
Expand|Select|Wrap|Line Numbers
  1. response.write mysql
What does this print out? Please post the output.

Jared
Sep 27 '07 #15
najmi
46
the output for response.write mysql:

update processor set description = 'Intel Dual Core' AND price =350;

and the err for using objrs:

Microsoft VBScript runtime (0x800A01B6)
Object doesn't support this property or method: 'description'
Sep 29 '07 #16
jhardman
3,406 Expert 2GB
the output for response.write mysql:

update processor set description = 'Intel Dual Core' AND price =350;

and the err for using objrs:

Microsoft VBScript runtime (0x800A01B6)
Object doesn't support this property or method: 'description'
your mysql line looks correct to me, but I don't usually use "update" like this, so I could be wrong.

The error line says that it is interpreting it wrong though, it is apparently looking for "description" as a property. I just looked over the code I posted earlier and I see the problem. I had indeed written the "description as if it was a property of the recordset. The syntax should be:
Expand|Select|Wrap|Line Numbers
  1. objRS("description") = cstrdes
  2. objRS("price") = ccurprice
Sorry about the mistake. Try it and let me know.

Jared
Oct 1 '07 #17
markrawlingson
346 Expert 100+
You're on the right track but a little off.. A proper SQL Update Query uses commas to separate the columns, not the AND keyword.

Expand|Select|Wrap|Line Numbers
  1. mysql = "UPDATE processor SET description = '" & cstrdes & "', price =" & ccurprice
  2.  
Hope that helps!

Sincerely,
Mark
Oct 1 '07 #18
jhardman
3,406 Expert 2GB
You're on the right track but a little off.. A proper SQL Update Query uses commas to separate the columns, not the AND keyword.

Expand|Select|Wrap|Line Numbers
  1. mysql = "UPDATE processor SET description = '" & cstrdes & "', price =" & ccurprice
  2.  
Hope that helps!

Sincerely,
Mark
and that's why I stick to what I know... Thanks, Mark.

Jared
Oct 1 '07 #19
najmi
46
thank..it works..i`m very happy..erm..i want to ask you something about err handling..i familiar with vb 6.0..in vb6.0 for err handling is simple just to tell err no..for eg:
if err no 7 then
msgbox"..."
end if.

so in asp how to make err handling..do you have any eg 4 me...thank you very much.
Oct 2 '07 #20
markrawlingson
346 Expert 100+
Yep, it's pretty much the same deal.

Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next
  2. If Err.Number = 1 Then
  3. 'do something
  4. Err.Clear 'to destroy the error
  5. End If
  6.  
I don't know much about vb6 but in ASP you've got to have the On Error Resume Next before your error handling.. otherwise the page will never execute the error handling code. It will stop execution and display the error instead before it gets to the error handling code.

Sincerely,
Mark
Oct 2 '07 #21
najmi
46
Yep, it's pretty much the same deal.

Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next
  2. If Err.Number = 1 Then
  3. 'do something
  4. Err.Clear 'to destroy the error
  5. End If
  6.  
I don't know much about vb6 but in ASP you've got to have the On Error Resume Next before your error handling.. otherwise the page will never execute the error handling code. It will stop execution and display the error instead before it gets to the error handling code.

Sincerely,
Mark
thanks..one more thing how to print only data from the database not the whole page using asp..
Oct 3 '07 #22
najmi
46
one more thing that i forgot..if we have err using asp it will display err type not the err number..so how could i to code the err handling if did not know the err number..so how can i trace err number according to err type..or if you have any idea...
Oct 3 '07 #23
markrawlingson
346 Expert 100+
Err.Number should return 0 if there is no error, so in this case you check to see if Err.Number > 0 and if it is, you handle the error whatever way you like.. If Err.Number = 0 then the error handling will be ignored because there is no error.
Oct 3 '07 #24

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by Mike & Dyan | last post: by
16 posts views Thread by robert | last post: by
2 posts views Thread by NoSpam | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.