delete data in ms access using asp

[najmi]

this my coding..

<%
dim cintid
cintid = request.form("txtid")
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 = "delete * from processor where id-no = " & cintid & ";"
 
objconn.execute(mysql)
 
 response.redirect("home.asp") %>

when i run, it give me an error

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

please help me..i dont know where is my error..

[jhardman]

the minus sign (hyphen) is a special character in SQL. "id-no" should be enclosed in square brackets like this:

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

[najmi]

the minus sign (hyphen) is a special character in SQL. "id-no" should be enclosed in square brackets like this:

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..

[jhardman]

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:

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:

update processor set description = 432 AND price = 12.67

Let me know if this works.

Jared

[najmi]

the ampersand (&) is not recognized in SQL. You need to use the word "AND" like this:

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:

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:

<%
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") %>

err:

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

[najmi]

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..

[shweta123]

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)

[jhardman]

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:

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

2- between lines 12 & 13 put this line:

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:

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

Notice that the value of description is in quotes.

Jared

[jhardman]

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

[najmi]

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'.

[jhardman]

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

[najmi]

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..

[jhardman]

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:

for each x in request.form
   response.write x & ": " & request.form(x) & "<br>" & vbnewLine
next

This just prints out what was sent through the form.

Anyway, my code for updating the db would look something like this:

<%
dim cstrdes,ccurprice,cintid
cintid = request.form("txtid")
cstrdes = request.form("txtdes")
ccurprice = request.form("txtprice")
dim objconn, objRS, mysql
set objconn = server.createobject("ADODB.connection")
objconn.provider = "Microsoft.Jet.OLEDB.4.0"
objconn.ConnectionString = "Data Source=" & Server.MapPath("product.mdb")
objconn.open
 
set objRS = server.createobject("ADODB.recordset")
 
mysql = "SELECT * FROM processor WHERE [id-no] = " & cintid & ";" 
response.write "mysql: " & mysql & "<br>" & vbNewLine
objRS.open mysql, objconn, 2, 3
 
objRS.description = cstrdes
objRS.price = ccurprice
objRS.update
 
' 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

[najmi]

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.

[jhardman]

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

response.write mysql

What does this print out? Please post the output.

Jared

[najmi]

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'

[jhardman]

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:

objRS("description") = cstrdes
objRS("price") = ccurprice

Sorry about the mistake. Try it and let me know.

Jared

[markrawlingson]

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.

mysql = "UPDATE processor SET description = '" & cstrdes & "', price =" & ccurprice
 

Hope that helps!

Sincerely,
Mark

[jhardman]

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.

mysql = "UPDATE processor SET description = '" & cstrdes & "', price =" & ccurprice
 

Hope that helps!

Sincerely,
Mark

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

Jared

[najmi]

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.

[markrawlingson]

Yep, it's pretty much the same deal.

On Error Resume Next
If Err.Number = 1 Then
'do something
Err.Clear 'to destroy the error
End If
 

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

[najmi]

Yep, it's pretty much the same deal.

On Error Resume Next
If Err.Number = 1 Then
'do something
Err.Clear 'to destroy the error
End If
 

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..

[najmi]

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...

[markrawlingson]

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.