Data entry, deletion and modification problems.

  • Thread starter Thread starter Kong
  • Start date Start date
K

Kong

hi,

Currently, i am doing an asset management with asp and the microsoft
access database. the operating system environment is windows XP
professional edition, service pack 2.


However, after i finish the database design part and testing it, (at my

company's computer), it only can retrieve the data that i key-in
manually into the database and appear on the screen via the system.
However, i can't modify, delete and add new record into the access
database via the designed system. But, when i run the same system at
the personal computer at my home, in same operating system environment,

it runs normally.
---------------------------------------------------------------------------­-----------------

The eror message that i can view via the browser are as followings:


Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) [Microsoft]
[ODBC Microsoft Access Driver] Number of query values and definition
fields are not the same.
/j03/asset.asp, line 301


---------------------------------------------------------------------------­---------

The line that has problem as mentioned in the error type is as
followings:


con.Execute sql, Recordsaffected


---------------------------------------------------------------------------­------------

I try to modify the codes in other way round, it still bring the same
error message. Hope to gain the solution on this poblems here.

-----------------------------------------------------------------------
Codes are as followings:
<% if Request("submit.x") <> "" then
openAddress con
SQL = "Select * from assetdetails where asset_id = '" &
request("asset_id") & "'"
set rs=con.Execute(SQL)
if Not rs.EOF then
response.write("<script>alert('Asset ID does exist');</script>")
else
SQL = "Insert into assetdetails (asset_id,asset_name,staff_id," & _
"staff_name,asset_type,location," & _
"department,dateupdate,updateby) " & _
"values ('" & Request("asset_id") & "','" & _
ucase(Request("asset_name")) & "','" & Request("staff_id") & _
"','" & ucase(Request("asset_type")) & "','" & Request("staff_name")
& _
"','" & Request("location") & "','" & Request("department") & _
"','" & Request("ser_num") & "','" & Request("manufacturer") & _
"','" & Request("staff_tel") & "','" & Request("remark") & _
"','" & date & "','" & session("session_id") & "')"
con.Execute sql, Recordsaffected
con.close
Response.Write("Record was add successful")
end if
elseif Request("update.x") <> "" then
openAddress con
SQL = "Update assetdetails set asset_name='" &
ucase(Request("asset_name")) & _
"',staff_id='" & Request("staff_id") &"',asset_typeo='" &
Request("asset_type") & _
"',staff_name='" & Request("staff_name") & "',location='" &
Request("location") & _
"','" & Request("staff_tel") & "','" & Request("remark") & _
"','" & Request("ser_num") & "','" & Request("manufacturer") & _
"',updateby='" & session("session_id") & "',dateupdate='" & date & _
"',department='" & ucase(Request("department")) & "' where
asset_id='" & Request("asset_id") & "'"
con.Execute sql, Recordsaffected
con.close
Response.Write("Record was update successful")
elseif Request("delete.x") <> "" then
openAddress con

SQL = "Delete from assetdetails where asset_id='" &
Request("asset_id") & "'"
con.Execute sql, Recordsaffected
errMess = "False"

con.close
if errMess = "True" then
Response.Write("You dont have permission to delete this data.")
else
Response.Write("Record was delete successful.")
end if

end if

%>
 
Kong said:
hi,

Currently, i am doing an asset management with asp and the microsoft
access database. the operating system environment is windows XP
professional edition, service pack 2.


However, after i finish the database design part and testing it, (at
my

company's computer), it only can retrieve the data that i key-in
manually into the database and appear on the screen via the system.
However, i can't modify, delete and add new record into the access
database via the designed system. But, when i run the same system at
the personal computer at my home, in same operating system
environment,

it runs normally.
---------------------------------------------------------------------------­-----------------

The eror message that i can view via the browser are as followings:


Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) [Microsoft]
[ODBC Microsoft Access Driver] Number of query values and definition
fields are not the same.
/j03/asset.asp, line 301


---------------------------------------------------------------------------­---------

The line that has problem as mentioned in the error type is as
followings:


con.Execute sql, Recordsaffected


---------------------------------------------------------------------------­------------

I try to modify the codes in other way round, it still bring the same
error message. Hope to gain the solution on this poblems here.

-----------------------------------------------------------------------
Codes are as followings:
<% if Request("submit.x") <> "" then
openAddress con
SQL = "Select * from assetdetails where asset_id = '" &
request("asset_id") & "'"
set rs=con.Execute(SQL)
if Not rs.EOF then
response.write("<script>alert('Asset ID does exist');</script>")
else
SQL = "Insert into assetdetails (asset_id,asset_name,staff_id," & _
"staff_name,asset_type,location," & _
"department,dateupdate,updateby) " & _
"values ('" & Request("asset_id") & "','" & _
ucase(Request("asset_name")) & "','" & Request("staff_id") & _
"','" & ucase(Request("asset_type")) & "','" &
Request("staff_name") & _
"','" & Request("location") & "','" & Request("department") & _
"','" & Request("ser_num") & "','" & Request("manufacturer") & _
"','" & Request("staff_tel") & "','" & Request("remark") & _
"','" & date & "','" & session("session_id") & "')"
con.Execute sql, Recordsaffected
con.close
Response.Write("Record was add successful")
end if
elseif Request("update.x") <> "" then
openAddress con
SQL = "Update assetdetails set asset_name='" &
ucase(Request("asset_name")) & _
"',staff_id='" & Request("staff_id") &"',asset_typeo='" &
Request("asset_type") & _
"',staff_name='" & Request("staff_name") & "',location='" &
Request("location") & _
"','" & Request("staff_tel") & "','" & Request("remark") & _
"','" & Request("ser_num") & "','" & Request("manufacturer") & _
"',updateby='" & session("session_id") & "',dateupdate='" & date &
_ "',department='" & ucase(Request("department")) & "' where
asset_id='" & Request("asset_id") & "'"
con.Execute sql, Recordsaffected
con.close
Response.Write("Record was update successful")
elseif Request("delete.x") <> "" then
openAddress con

SQL = "Delete from assetdetails where asset_id='" &
Request("asset_id") & "'"
con.Execute sql, Recordsaffected
errMess = "False"

con.close
if errMess = "True" then
Response.Write("You dont have permission to delete this data.")
else
Response.Write("Record was delete successful.")
end if

end if

%>
Click to expand...

Well, if I'm reading correct, your column list specifies 9 fields, and
you try to stuff 13 values into it.

Another thing, are every fields text? It seems you are working with
both
numeric and date information too, are they stored as text? If not,
numerics use no delimiters (no single quote), and dates need to be
delimited by octothorpe (#). Access needs dates in an unambiguous way,
ISO 8601 (yyyy-mm-dd), should do.

Perhaps a NG dedicated to the language you're using could be more
helpful?
 
Thanks for the help from Roy-Vidar. Anyway, i have modify the code, in
the mis-match of provided column and the inserted field. The code had
been modified and i paste it here.

However, regrading the information delimiters, i am not so clear on
what you have informed. Hope you may help to modify it for me, although
just a small part, but I think it may open my eye wide.

The modified code are as below.
----------------------------------------------------------------------------------------
<%
if Request("submit.x") <> "" then
openAddress con
SQL = "Select * from assetdetails where asset_id = '" &
request("asset_id") & "'"
set rs=con.Execute(SQL)
if Not rs.EOF then
response.write("<script>alert('Asset ID does exist');</script>")
else
SQL = "Insert into assetdetails (asset_id,asset_name,staff_id," & _
"staff_name,asset_type,location," & _
"department,ser_num, staff_tel,manufacturer, remark,
dateupdate,updateby) " & _

"values ('" & Request("asset_id") & "','" & _
ucase(Request("asset_name")) & "','" & Request("staff_id") &
_
ucase(Request("staff_name")) & "','" & Request("asset_type")
& _
"','" & ucase(Request("location")) & "','" &
Request("department") & _
"','" & Request("ser_num") & "','" & Request("manufacturer")
& _
"','" & Request("staff_tel") & "','" & Request("remark") & _
"','" & date & "','" & session("session_id") & "')"

con.Execute sql, Recordsaffected
con.close
Response.Write("Record was add successful")
end if

elseif Request("update.x") <> "" then
openAddress con
SQL = "Update assetdetails set asset_name='" &
ucase(Request("asset_name")) & _
"',staff_id='" & Request("staff_id") &"',asset_typeo='" &
Request("asset_type") & _
"',staff_name='" & Request("staff_name") & "',location='" &
Request("location") & _
"','" & Request("staff_tel") & "','" & Request("remark") & _
"','" & Request("ser_num") & "','" & Request("manufacturer") &
_
"',updateby='" & session("session_id") & "',dateupdate='" &
date & _
"',department='" & ucase(Request("department")) & "' where
asset_id='" & Request("asset_id") & "'"
con.Execute sql, Recordsaffected
con.close
Response.Write("Record was update successful")
elseif Request("delete.x") <> "" then
openAddress con

SQL = "Delete from assetdetails where asset_id='" & Request("asset_id")
& "'"
con.Execute sql, Recordsaffected
errMess = "False"

con.close
if errMess = "True" then
Response.Write("You dont have permission to delete this data.")
else
Response.Write("Record was delete successful.")
end if

end if

%>
---------------------------------------------------------------------------------------------------

for the data type in each field, i have listed out for your references.

asset_id---numeric
asset_name-----numeric+alphabet
staff_id------numeric
staff_name------alphabet
asset_type-------------alphabet
location-----numeric+alphabet
department-------------alphabet
ser_num-------------numeric+alphabet
manufacturer-----numeric+alphabet
staff_tel-----numeric+alphabet
remark-----numeric+alphabet
date------numeric
session_id------numeric
 
Kong Jeck Lee said:
Thanks for the help from Roy-Vidar. Anyway, i have modify the code,
in the mis-match of provided column and the inserted field. The code
had been modified and i paste it here.

However, regrading the information delimiters, i am not so clear on
what you have informed. Hope you may help to modify it for me,
although just a small part, but I think it may open my eye wide.
Click to expand...

Sorry, this is an Access NG, I don't know anything about the syntax
you're using.

Here's a little something on working with Access/VBA/Jet. It is
important to note the datatype of the fields one is working with,
especially when working with dynamic SQL. Say in VBA, when inserting a
record containing in a table with fields of datatype number (single),
text and date/time.

Dim Sql As String
Dim MyNum As Single
Dim MyText As String

MyNum = 10.5
MyText = "O'Neill"

Sql = "INSERT INTO myTest (myNum, myText, myDate) " & _
"VALUES (" & MyNum & ", '" & MyText & "', #" & _
Date & "#)"

This shows using correct delimiters for the datatypes of the fields.

No delimiters for numbers
Text delimited by single quote (')
Date delimited by octothorpe (#) - note, when working with a connection
to SQL server, also dates are delimited with singlw quotes.

But this is still not enough. With US settings, it would choke only on
the name, because it contains a special character, the single quote. In
VB(A), this might be resolved for instance like this

....", '" & Replace(MyText, "'", "''") & "', ...

i e - using VB(A)s replace function to replace any single occurance of
single quotes, with two, which makes the Jet engine accept it as one.

Next, outside US, this date will either make the Jet engine confused,
or
make it choke on the date, because it isn't in the correct format. When
passing a string date for evaluation, it needs to be in an unambiguous
format, for instance like the ISO 8601 format. In VB(A), that might
look
like this

.... "', #" & Format$(Date, "yyyy-mm-dd") & "#)"

Next, in some countries, comma (,) is used as decimalseparator in stead
of period (.). Even after the above amendments, this SQL will choke
where I live, because 10.5 will be concatenated into this string as
10,5, which will provide the same error you had initially. In VB(A),
replacing the comma with a period should work

....VALUES (" & Replace(Cstr(MyNum), ",", ".") & ", ...

The statement might then look like this, after amending

Sql = "INSERT INTO myTest (myNum, myText, myDate) " & _
"VALUES (" & Replace(CStr(MyNum), ",", ".") & ", '" & _
Replace(MyText, "'", "''") & "', #" & _
Format$(Date, "yyyy-mm-dd") & "#)"

So as you see, what we can give of advice here, is about how one might
do stuff like this from within Access.

A small correction about your initial statement
"Currently, i am doing an asset management with asp and the microsoft
access database."
No, technically, you're not using Microsoft Access Database. You are
connecting to a Jet database (mdb file) through ADO. Therefore, it is
perhaps more likeley that you'll get better help in an NG dedicated to
the technology you're using.
 
Back
Top