Go Back  DVD Talk Forum > General Discussions > Tech Talk
Reload this Page >

adding records to access database from asp

Tech Talk Discuss PC Hardware, Software, Internet and Other Technology

adding records to access database from asp

Old 12-21-01, 01:22 PM
  #1  
Banned
Thread Starter
 
Join Date: Aug 2001
Posts: 2,322
Likes: 0
Received 0 Likes on 0 Posts
adding records to access database from asp

I had posted a few weeks back that i needed some help regarding connecting to an access database from an asp. with the help of some forum users, i was able to populate some drop down lists via sql queries after establiashing a connection.


now that that part works, i am making a new asp form that will make changes to the database. basically if they want to add a doctor to the doctor table, i have them enter the firstname, lastname and middle initial. i then save those values in variables called doc_first, doc_last, and doc_middle. basically all i want to do is add the entered values to my "Doclist" table. i was wondering what i need to do. i assume i need to establish a connection and then assign some sql command to add each entry to the table. the fields in the table are Firstname, Lastname, MiddleInit and I have a primary key DocID that automatically assigns a numerical value to each entry added. I was wondering if anyone can help me.

Thanks, Dave
Old 12-21-01, 02:03 PM
  #2  
Banned
Thread Starter
 
Join Date: Aug 2001
Posts: 2,322
Likes: 0
Received 0 Likes on 0 Posts
i set the DSN on the server's odbc settings to point to the database, and i know that works since the form that queries the database to fill the drop down fields works perfectly. so the DSN is doctors_dsn........i tried putting this code into the asp page to add to the database:


PHP Code:
 

i get an error message when i submit the changes on my form :

ADODB.Recordset error '800a0cb3'

The operation requested by the application is not supported by the provider.

/forms/TTadd_doc.asp, line 71
Old 12-21-01, 02:13 PM
  #3  
X
Administrator
 
X's Avatar
 
Join Date: Oct 1987
Location: AA-
Posts: 10,765
Likes: 0
Received 4 Likes on 3 Posts
Why don't you just use a SQL insert statement?
Old 12-21-01, 02:18 PM
  #4  
Banned
Thread Starter
 
Join Date: Aug 2001
Posts: 2,322
Likes: 0
Received 0 Likes on 0 Posts
the link that says
PHP Code:
RS.Open "Doclist",Conn 

i took out something after Conn:

,adopenstatic,adlockoptimistic


because i was getting errors, but i think its because i didn't include some Adovbs.inc file maybe..... (i took the code off a tutorial website and changed some variable names and such to fit my needs).



X, i don't know, i am new to all this and am in search of any suggestions that might make my task easier.

--Dave
Old 12-21-01, 02:22 PM
  #5  
X
Administrator
 
X's Avatar
 
Join Date: Oct 1987
Location: AA-
Posts: 10,765
Likes: 0
Received 4 Likes on 3 Posts
First, don't open static. You can't update or insert. Open dynamic. And use SQL statements and EXEC operations rather than updates.
Old 12-21-01, 02:36 PM
  #6  
Banned
Thread Starter
 
Join Date: Aug 2001
Posts: 2,322
Likes: 0
Received 0 Likes on 0 Posts
i put the openstatic and adlockoptimistic lines back in and included ADOVBS.INC file, and i got two errors regarding the
Call ErrorVBscriptReport("Adding Record")
Call ErrorADOReport("Adding Record",RS.activeconnection)


lines, somethnig about undefined variable or something. i took those out and it worked. but when i checked the database, it added the name 3 times!!!!

i will change the openstatic to opendynamic
Old 12-21-01, 02:42 PM
  #7  
Banned
Thread Starter
 
Join Date: Aug 2001
Posts: 2,322
Likes: 0
Received 0 Likes on 0 Posts
forget it, it seems to work now. now i need to put some if statement in to make sure the user didn't leave any of the fields blank (for doc_first and doc_last variables) and i need to work on deleting records now.

anyone know how to generate an if statement to make sure the doc_first or doc_last isn't blank, and if it is, i want to brnig the user to a new page, an error page. (i am new to vb script and html as well.) --dave

Last edited by MACD23; 12-21-01 at 02:44 PM.
Old 12-21-01, 02:45 PM
  #8  
X
Administrator
 
X's Avatar
 
Join Date: Oct 1987
Location: AA-
Posts: 10,765
Likes: 0
Received 4 Likes on 3 Posts
I'm surprised you could add anything to a database opened static!
Old 12-21-01, 02:55 PM
  #9  
Banned
Thread Starter
 
Join Date: Aug 2001
Posts: 2,322
Likes: 0
Received 0 Likes on 0 Posts
i changed it to dynamic after you suggested it. (as you can see i have no idea what i am doing )
Old 12-21-01, 03:45 PM
  #10  
DVD Talk Gold Edition
 
Join Date: Dec 1999
Posts: 2,708
Likes: 0
Received 0 Likes on 0 Posts
Originally posted by MACD23
forget it, it seems to work now. now i need to put some if statement in to make sure the user didn't leave any of the fields blank (for doc_first and doc_last variables) and i need to work on deleting records now.

anyone know how to generate an if statement to make sure the doc_first or doc_last isn't blank, and if it is, i want to brnig the user to a new page, an error page. (i am new to vb script and html as well.) --dave
I would use javascript for this.

PHP Code:
<script language="JavaScript">
function 
checkIt() {
if (
document.formName.doc_first.value == '') {
  
alert("You must enter a first name");
  return(
false);
}
if (
document.formName.doc_last.value == '') {
  
alert("You must enter a last name");
  return(
false);
}
return(
true);
}
</
script
then in the form tag

PHP Code:
<form name="formName" method="post" onSubmit="return checkIt()"
Old 12-24-01, 09:23 AM
  #11  
Banned
Thread Starter
 
Join Date: Aug 2001
Posts: 2,322
Likes: 0
Received 0 Likes on 0 Posts
worked great lampei, thanks.
Old 12-24-01, 01:18 PM
  #12  
Banned
Thread Starter
 
Join Date: Aug 2001
Posts: 2,322
Likes: 0
Received 0 Likes on 0 Posts
new problem:

i am trying to pass the variable that holds the selected name to an .asp that asks the user if they are sure they want to remove that person from the database. if they choose "YES" it goes to a third .asp that actually will remove the person from the database. if they choose no, it simply goes back to the original form.

so the user selects a name to remove from my drop down list (ie: joe shmo)and submits it, it then opens a new .asp page (TTremove_doc_check.asp) that says "Are you sure you want to remove "Joe Shmo" from the database? and that works fine, but when they click Yes, and my third asp page opens (TTremove_doc.asp), it displays "Thanks, you have removed from the database" and it isn't printing the name. therefore, the variable in my third page isn't getting the name passed into it. how do i do that?





ps: i tried putting this code into the TTremove_doc_check.asp and it works, but it displays an editable input box with the name in it (i don't want the input box to display).
PHP Code:
<form method="post" action="TTremove_doc.asp">
<
input name="to" type="hidden" value="my email here">
<
input name="subject" type="hidden" value="TT remove doc:">
<
input name="from" type="hidden" value="TT_database_Admin">
<
input name="remove_doc" value="" size="1"


and the asp page that recieves that data is TTremove_doc.asp

which has this:

PHP Code:
 

Last edited by MACD23; 12-24-01 at 01:36 PM.
Old 12-25-01, 11:38 PM
  #13  
Banned
Thread Starter
 
Join Date: Aug 2001
Posts: 2,322
Likes: 0
Received 0 Likes on 0 Posts
any ideas???
Old 12-26-01, 09:38 AM
  #14  
DVD Talk Gold Edition
 
Join Date: Dec 1999
Posts: 2,708
Likes: 0
Received 0 Likes on 0 Posts
You might just want to put this in the javascript tag. I usually do it like this...

PHP Code:
<script language="JavaScript">
function 
checkIt() {
if (
document.formName.doc_first.value == '') {
  
alert("You must enter a first name");
  return(
false);
}
if (
document.formName.doc_last.value == '') {
  
alert("You must enter a last name");
  return(
false);
}
if (
confirm("Are you sure you want to remove this person?")) {
  return (
true);
} else {
  return (
false);
}
}
</
script
Alternately, if you really want to do this with new different asp pages, you should probably just pass the reference ID for the name between the pages and then just re-query the database for that person on each of the pages. When you are submitting the forms, you have to make sure that you are including the variable hidden in the form somewhere on each page that you submit...

PHP Code:
<form action="thirdpage.asp" method="post">
<
input type="hidden" name="ID" value="Your form variable here">
<
input type="submit" value="Are you sure?">
</
form

Last edited by Lampei; 12-26-01 at 09:44 AM.
Old 12-27-01, 02:50 PM
  #15  
Banned
Thread Starter
 
Join Date: Aug 2001
Posts: 2,322
Likes: 0
Received 0 Likes on 0 Posts
ok thanks, another quick question.....



i have it so when they choose a name to delete from my drop down list, it gets the DocID of the selected value. for example, suppose my database had this

Firstname Lastname MiddleInit DocID

Dave Jones 01
Mark Malone G. 02
Jane Doe 03


the drop down list displays the lsit of entire names, and if the user highlited Jane Doe and clicked to remove her, i store the DocID 03 in a variable and pass that variable to the .asp that asks if the user is sure they want to delete her.... when I ask:

"Are you sure you want to delete Jane Doe from the database?"


i need to know how to turn my stored DocID into Jane's name when i print out if they want to delete her.....ie, as of now it prints "Are you sure you want to delete 03 from the database"?


i tried this code, but it isn't working (note doc_id is the variable that stores the DocID that was passed from the original asp form).

PHP Code:
 
It prints, "Are you sure you want to remove" and then i get this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

/forms/TTremove_doc_check.asp, line 87

Last edited by MACD23; 12-27-01 at 03:01 PM.
Old 12-27-01, 03:04 PM
  #16  
DVD Talk Gold Edition
 
Join Date: Dec 1999
Posts: 2,708
Likes: 0
Received 0 Likes on 0 Posts
I think your SQL statement is incorrect. Shouldn't it be something like this...

sql="SELECT Lastname,Firstname,MiddleInit,DocID FROM Doclist WHERE DocID = '#form.DocID#'"

(I'm using ColdFusion variables there...I'm not sure how you reference form variables in asp)

This will then select just one doctor from your list that has, say, 03 as their ID.
Old 12-27-01, 03:10 PM
  #17  
Banned
Thread Starter
 
Join Date: Aug 2001
Posts: 2,322
Likes: 0
Received 0 Likes on 0 Posts
ok, you are right, i had the SQL messed up....although this new line gives me a new error.


PHP Code:
sql="SELECT Lastname,Firstname,MiddleInit,DocID FROM Doclist WHERE DocID ='#form.doc_id#'" 

that gives me this error:


Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

/forms/TTremove_doc_check.asp, line 91




note, line 91 has this
PHP Code:
rs.open sql,conn 

Last edited by MACD23; 12-27-01 at 03:15 PM.
Old 12-27-01, 03:17 PM
  #18  
Banned
Thread Starter
 
Join Date: Aug 2001
Posts: 2,322
Likes: 0
Received 0 Likes on 0 Posts
ps, if i replace the part in the SQL statement WHERE DocID = blah blah with WHERE DocID LIKE blah blah, it prints this:


Are you sure you want to remove SELECT Lastname,Firstname,MiddleInit,DocID FROM Doclist WHERE DocID LIKE '#form.doc_id#' from the database TalkTech123db.mdb?
Old 12-27-01, 03:30 PM
  #19  
DVD Talk Gold Edition
 
Join Date: Dec 1999
Posts: 2,708
Likes: 0
Received 0 Likes on 0 Posts
When you get this error

Data type mismatch in criteria expression

it means that you're using something like quotes around a numeric column in your db or no quotes around a text field. Just remove the ' ' from around '#form.docID#' to #form.docID That should clear up the data type mismatch.
Old 12-27-01, 03:51 PM
  #20  
Banned
Thread Starter
 
Join Date: Aug 2001
Posts: 2,322
Likes: 0
Received 0 Likes on 0 Posts
i removed the ' ' around #form.doc_id# and i get this:


Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression 'DocID = #form.doc_id#'.

/forms/TTremove_doc_check.asp, line 72


my line now looks like this:

PHP Code:
sql="SELECT Lastname,Firstname,MiddleInit,DocID FROM Doclist WHERE DocID = #form.doc_id#"  
 
 
rs.open sql,conn 
     
   Response
.Write(sql)
   
Response.Write(" ")
    
rs.close 
Old 12-27-01, 03:55 PM
  #21  
DVD Talk Gold Edition
 
Join Date: Dec 1999
Posts: 2,708
Likes: 0
Received 0 Likes on 0 Posts
OK, now all you have to do is remove the cold fusion code from there I didn't know how you call form variables in asp, so I used the cold fusion equivalent. #form.DocID# refers to the DocID variable that is passed from the previous page. Replace that with whatever the asp version of it is I'm going home now, so I won't be able to answer any questions for a few hours, so if that doesn't work, just bear with me until I can get back to a computer.
Old 12-27-01, 03:59 PM
  #22  
Banned
Thread Starter
 
Join Date: Aug 2001
Posts: 2,322
Likes: 0
Received 0 Likes on 0 Posts
OK, now all you have to do is remove the cold fusion code from there

thats what i figured....what would i type in to do a search on the proper syntax for this?


also, would you or anyone else be able to recommend a good learners book for active server pages and database connectivity, etc? i have never done this stuff before and am still in school learning, so thats why i am asking so many questions. lampei, and others, thanks so much for all the help!

-dave
Old 12-27-01, 07:29 PM
  #23  
DVD Talk Gold Edition
 
Join Date: Dec 1999
Posts: 2,708
Likes: 0
Received 0 Likes on 0 Posts
I just checked in a book I use, Sam's Teach Yourself Active Server Pages in 21 Days (a very simple, easy to understand/use book, that I highly recommend for beginners) and it assigns the SQL statement to a string first. You have to treat it like you would when you are assigning a variable with a string...

PHP Code:
dim strSQL
strSQL 
"SELECT Lastname,Firstname,MiddleInit,DocID FROM Doclist WHERE DocID =  " Request("DocID")

Dim objRS
Set ojbRS 
Server.CreateObject("ADODB.Recordset")
objRS.Open strSQLobjConn 

Last edited by Lampei; 12-27-01 at 07:32 PM.
Old 12-28-01, 10:23 AM
  #24  
Banned
Thread Starter
 
Join Date: Aug 2001
Posts: 2,322
Likes: 0
Received 0 Likes on 0 Posts
ok, if i put this code, here is what happens:

PHP Code:

at the top of my removecheck
.asp i assignt the variable obtained in the first form to doc_id:

Dim doc_id
doc_id 
Request.Form("remove_doc")



then i try to print out "Are you sure you want to remove Joe Shmo?" here is what i did:

<
p align="center"><big>Are you sure you want to remove </big
i get this:

Are you sure you want to remove

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'DocID ='.

/forms/TTremove_doc_check.asp, line 71



if i try this instead:

PHP Code:
<p align="center"><big>Are you sure you want to remove </big
(i selected the 23rd name in the drop downlist to remove), here is what it displays:

Are you sure you want to remove SELECT Lastname,Firstname,MiddleInit,DocID FROM Doclist WHERE DocID = 23 from the database TalkTech123db.mdb?
Old 12-28-01, 10:38 AM
  #25  
Banned
Thread Starter
 
Join Date: Aug 2001
Posts: 2,322
Likes: 0
Received 0 Likes on 0 Posts
i also tried this:

PHP Code:
<p align="center"><big>Are you sure you want to remove </big>

"
   Response.Write(name)
   Response.Write(" ")
   objRS.close     
%>


<big> from the database TalkTech123db.mdb? </big></p> 

and i got this:

Are you sure you want to remove from the database TalkTech123db.mdb?


so its getting no errors withthis, but it isn't filling the name in.

Thread Tools
Search this Thread

Archive - Advertising - Cookie Policy - Privacy Statement - Terms of Service - Do Not Sell My Personal Information

Copyright 2018 MH Sub I, LLC dba Internet Brands. All rights reserved. Use of this site indicates your consent to the Terms of Use.