Connecting Tech Pros Worldwide Help | Site Map
bytes > topic > asp classic > insights

How to insert data into a database with ASP

jhardman's Avatar
Moderator
  
Join Date: Jan 2007
Location: logan, utah
Posts: 2,690
#1   Jul 5 '07
step 1- get data.

for example, make a form with one input field for each piece of data you want to put in the db:[html]<form method="post" action="thisSamePage.asp">
<input type="text" name="userName">User Name<br>
<input type="text" name="phonNumber">Phone Number<br>
<input type="text" name="toothBrushColor">Color of your Toothbrush<br>
<input type="submit" value="submit"></form>[/html]
For convenience, it helps to make the name of the input identical to the name of the fields of the database.

It is a good idea to make sure you got data at this point, so list it like this:
Expand|Select|Wrap|Line Numbers
  1. dim x
  2. for each x in request.form
  3.    response.write x & ": " & request(x) & "<br>" & vbNewLine
  4. next
you got data? good.

step 2- connect to the database.

Unfortunately, ability to connect to a db is not built directly into aspwhen it was originally made, but it was added later in the form of a series of server objects. It is a bit awkward and that is just the way it goes. The connection looks like this:
Expand|Select|Wrap|Line Numbers
  1. dim objConn, objRS
  2. set objConn = server.createObject("adodb.connection")
  3. objConn.Provider="Microsoft.Jet.OLEDB.4.0"
  4. objConn.Open "c:/inetpub/myDBFiles/myDBofUsers.mdb"
step 3- There are several ways to work with the db once you are connected. I prefer to do it by creating a recordset like this:
Expand|Select|Wrap|Line Numbers
  1. dim query
  2. set objRS = server.creatObject("adodb.recordset")
  3. query = "SELECT * FROM userTable"
  4. objRS.open query, objConn
This gives you an object named "objRS" which is a little copy of one table of the db.

step 4- To add a new record, do this:
Expand|Select|Wrap|Line Numbers
  1. objRS.addNew
  2. objRS("userName") = request("userName")
  3. objRS("phonNumber") = request("phonNumber")
  4. objRS("toothBrushColor") = request("toothBrushColor")
  5. objRS.update
Now you have added data to a db. That wasn't so hard, was it?

Jared

PS Remember how I said it was convenient to use the exact db field names as the name of your form inputs? If you did this, then step four can be simplified to this:
Expand|Select|Wrap|Line Numbers
  1. dim x
  2. objRS.addNew
  3. for each x in objRS.fields
  4.    if request.form(x) <> "" then
  5.       objRS(x) = request.form(x)
  6.    end if
  7. next
  8. objRS.update
This opens the db and goes through each field and looks for a form input with the same name. If it finds one, that input is used to populate the corresponding db field.



Newbie
  
Join Date: Nov 2007
Posts: 1
#2   Nov 17 '07

re: How to insert data into a database with ASP

how to connect if you're using c#
jhardman's Avatar
Moderator
  
Join Date: Jan 2007
Location: logan, utah
Posts: 2,690
#3   Nov 17 '07

re: How to insert data into a database with ASP

Quote:

Originally Posted by gp2060

how to connect if you're using c#

You can't write ASP pages in c#, so your question is meaningless (or it would be meaningless if you had written a question). Try the .NET forum.

Jared
omerbutt's Avatar
Needs Regular Fix
  
Join Date: Nov 2006
Location: Earth Obviously :P
Posts: 344
#4   Nov 25 '07

re: How to insert data into a database with ASP

as i was looking through the questions and articles i came by ur article sir iam also using the same thing or say the same way to insert in to a data base but if i use the objrs.AddNew after i open query and conn it adds the record twice means each input in the form that i submit is entered twice .....and if i dont use the objrs.AddNew then it edits the very first record in the data base means it overwrites the 1st record in the table :( y is that so for ur convienience i am writing the code here, and yes one thing also if i exit the editor(dreamweraver)
and explorer windows OR if i edit the code and then save it without any query that gives error and then again write the same query then the first time it enters only once but as i continue to enter the data through the form it tarts adding the record twice again can u help in the matter

file name:Addstk_done.asp
Expand|Select|Wrap|Line Numbers
  1. <%
  2.                codeno=Request("code_no")
  3.     partno=Request("prt_no")
  4.     product=Request("prd")
  5.     brand=Request("brand")
  6.     auto=Request("auto")
  7.     unitprice=Request("unit_price")
  8.     size=Request("size")
  9.     height=Request("height")
  10.     outd1=Request("out_d1")
  11.     outd2=Request("out_d2")
  12.     ind1=Request("in_d1")
  13.     ind2=Request("in_d2")
  14.     trepno=Request("t_rep")
  15.     rpno1=Request("rp_no1")
  16.     rpno2=Request("rp_no2")
  17.     rpno3=Request("rp_no3")
  18.     rpno4=Request("rp_no4")
  19.     rpno5=Request("rp_no5")
  20.     rpno6=Request("rp_no6")
  21.  
  22.  
  23.     Dim Connstr, ADSTK, strSQL
  24.     set ADSTK=Server.CreateObject("ADODB.Recordset")    
  25.     set
  26.     Connstr="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("db/db1.mdb")
  27.     //strSQL = "Select Stk_items.Code_no, Stk_items.Prt_no, Stk_items.Prd, Stk_items.Brand, Stk_items.Auto, Stk_items.U_price, Stk_items.Size, Stk_items.Height, Stk_items.O_d1, Stk_items.O_d2, Stk_items.I_d1, Stk_items.I_d2, Stk_items.T_rep_no, Stk_items.Rep1, Stk_items.Rep2, Stk_items.Rep3, Stk_items.Rep4, Stk_items.Rep5, Stk_items.Rep6 From Stk_items;"
  28.     strSQL = "SELECT * FROM Stk_items;"
  29.  
  30.     ADSTK.CursorType = 2
  31.     ADSTK.LockType = 3    
  32.     ADSTK.Open strSQL, Connstr, 2
  33.     ADSTK.AddNew    
  34.     ADSTK.Fields("Code_no")=codeno
  35.     ADSTK.Fields("Prt_no")=partno
  36.     ADSTK.Fields("Prd")=product
  37.     ADSTK.Fields("Brand")=brand
  38.     ADSTK.Fields("Auto")=auto
  39.     ADSTK.Fields("U_price")=unitprice
  40.     ADSTK.Fields("Size")=size
  41.     ADSTK.Fields("Height")=height
  42.     ADSTK.Fields("O_d1")=outd1
  43.     ADSTK.Fields("O_d2")=outd2
  44.     ADSTK.Fields("I_d1")=ind1
  45.     ADSTK.Fields("I_d2")=ind2
  46.     ADSTK.Fields("T_rep_no")=trepno
  47.     ADSTK.Fields("Rep1")=rpno1
  48.     ADSTK.Fields("Rep2")=rpno2
  49.     ADSTK.Fields("Rep3")=rpno3
  50.     ADSTK.Fields("Rep4")=rpno4
  51.     ADSTK.Fields("Rep5")=rpno5
  52.     ADSTK.Fields("Rep6")=rpno6    
  53.     ADSTK.Update
  54.     ADSTK.Close
  55.     set ADSTK = Nothing
  56.     set Connstr = Nothing
  57.     Response.Redirect("Ad_stk.asp")
  58. %>
  59.  
jhardman's Avatar
Moderator
  
Join Date: Jan 2007
Location: logan, utah
Posts: 2,690
#5   Nov 27 '07

re: How to insert data into a database with ASP

Quote:

Originally Posted by omerbutt

as i was looking through the questions and articles i came by ur article sir iam also using the same thing or say the same way to insert in to a data base but if i use the objrs.AddNew after i open query and conn it adds the record twice means each input in the form that i submit is entered twice .....and if i dont use the objrs.AddNew then it edits the very first record in the data base means it overwrites the 1st record in the table :( y is that so for ur convienience i am writing the code here, and yes one thing also if i exit the editor(dreamweraver)
and explorer windows OR if i edit the code and then save it without any query that gives error and then again write the same query then the first time it enters only once but as i continue to enter the data through the form it tarts adding the record twice again can u help in the matter

I don't see anything in this code to suggest why that might happen. Are you doing anything to monitor or kill double-posts?

Jared
omerbutt's Avatar
Needs Regular Fix
  
Join Date: Nov 2006
Location: Earth Obviously :P
Posts: 344
#6   Nov 27 '07

re: How to insert data into a database with ASP

no nothing like that is happening ......... :) ...but infact yesterday i found the mistake that fixed the problem the mistake don looks like a mistake to me ......well u r gonna decide .....
the form through which i was submitting my data used a button that called a javascript function in which i validate
Addstk()---->that stands for add stock
in the funtion i am applying constraints and check for empty input lemme show u the code that i was using before then i wil tell u a lil change that i made or was that a mistake...
Expand|Select|Wrap|Line Numbers
  1. function Addstk()
  2. {
  3.                 var flg;        
  4.     var str;
  5.     str += "Please Fill in the following";
  6.     str += "\n**************";
  7.  
  8.     if((document.forms[0].code_no.value)=="")
  9.     {
  10.         str += "\n Enter Code No";
  11.         flg = 0;            
  12.     }
  13.     if((document.forms[0].prt_no.value)=="")
  14.     {
  15.         str += "\n Enter Part No";
  16.         flg = 0;
  17.     }
  18.     if((document.forms[0].prd.value)=="none")
  19.     {
  20.         str += "\n Enter Product";
  21.         flg = 0;
  22.     }
  23.     if((document.forms[0].brand.value)=="none")
  24.     {
  25.         str += "\n Enter Brand";
  26.         flg = 0;
  27.     }
  28.     if((document.forms[0].auto.value)=="")
  29.     {
  30.         str += "\n Enter AutoMobile";
  31.         flg = 0;
  32.     }
  33.     if((document.forms[0].unit_price.value)=="")
  34.     {
  35.         str += "\n Enter Unit Price";
  36.         flg = 0;
  37.     }
  38.     if((document.forms[0].qtty.value)=="")
  39.     {
  40.         str += "\n Enter Quantity";
  41.         flg = 0;
  42.     }
  43.     if((document.forms[0].t_rep.value)=="none")
  44.     {
  45.         str += "\n Enter Total Replace No";
  46.         flg = 0;
  47.     }
  48.     if((document.forms[0].size.value)=="none")
  49.     {
  50.         str += "\n Enter Size";
  51.         flg = 0;
  52.     }
  53.     if((document.forms[0].height.value)=="")
  54.     {
  55.         str += "\n Enter Height";
  56.         flg = 0;
  57.     }
  58.     if((document.forms[0].out_d1.value)=="")
  59.     {
  60.         str += "\n Enter Outer Dia1";
  61.         flg = 0;
  62.     }
  63.     if((document.forms[0].out_d2.value)=="")
  64.     {
  65.         str += "\n Enter Outer Dia2";
  66.         flg = 0;
  67.     }
  68.     if((document.forms[0].in_d1.value)=="")
  69.     {
  70.         str += "\n Enter Inner Dia1";
  71.         flg = 0;
  72.     }
  73.     if((document.forms[0].in_d2.value)=="")
  74.     {
  75.         str += "\n Enter Inner Dia2";
  76.         flg = 0;
  77.     }
  78.     if(flg == 0)
  79.     {
  80.         alert(str);
  81.         return false;
  82.     }
  83.     else
  84.     {
  85.                     ducument.forms[0].action="adstk_done.asp";
  86.                                 document.forms[0].submit();
  87.                      return true;
  88.     }
  89.  
  90. }
  91.  
and the form tag was like this

Expand|Select|Wrap|Line Numbers
  1. <HTML>
  2. <form name="addstkfrm"  method="post">
  3. </HTML>.
  4.  
note the last very last else

And what i did was i removed these 2 lines
Expand|Select|Wrap|Line Numbers
  1. document.forms[0].action()="adstk_done.asp";
  2. document.forms[0].submit();
  3.  
and added the action property in the form tag and the specified the same file that i was using to submit in the js function given above


Expand|Select|Wrap|Line Numbers
  1. <form name="adstkfrm" method="post" action="adstk_done.asp">
  2.  
wasnt that stupid :D i don know weather its a logical mistake by me or abnormal behaviour of the application u temme and with this i have made more than 50 entries and none them were duplicated
tanks,
regards Omer



Quote:

Originally Posted by jhardman

I don't see anything in this code to suggest why that might happen. Are you doing anything to monitor or kill double-posts?

Jared
jhardman's Avatar
Moderator
  
Join Date: Jan 2007
Location: logan, utah
Posts: 2,690
#7   Nov 28 '07

re: How to insert data into a database with ASP

Quote:

Originally Posted by omerbutt

no nothing like that is happening ......... :) ...but infact yesterday i found the mistake that fixed the problem the mistake don looks like a mistake to me ......well u r gonna decide .....
the form through which i was submitting my data used a button that called a javascript function in which i validate
Addstk()---->that stands for add stock
in the funtion i am applying constraints and check for empty input lemme show u the code that i was using before then i wil tell u a lil change that i made or was that a mistake...

and the form tag was like this


note the last very last else

And what i did was i removed these 2 lines
and added the action property in the form tag and the specified the same file that i was using to submit in the js function given above



wasnt that stupid :D i don know weather its a logical mistake by me or abnormal behaviour of the application u temme and with this i have made more than 50 entries and none them were duplicated
tanks,
regards Omer

Yes, I'm not a javascript expert, but it appears the mistake was in the line which says to re-submit the form, it was already submitted, you don't have to submit it again, I think the way you handled it is preferred. The form is supposed to have all those attributes. If you decide to set those attributes with javascript you should have a very good reason for doing so.

Jared

PS, Please stop writing in slang. This is an international site and it is very difficult for many readers to understand when you use "u temme" or "u r gonna" or similar incorrect English. I personally find it very annoying. I had to stop myself from insulting you just now.
omerbutt's Avatar
Needs Regular Fix
  
Join Date: Nov 2006
Location: Earth Obviously :P
Posts: 344
#8   Nov 29 '07

re: How to insert data into a database with ASP

yeah obviously you are right
and yes sori about that slang you know while writing it looks a little quick and easy thats why ....anyways i wud watch that next time
regards ,
Omer
Quote:

Originally Posted by jhardman

Yes, I'm not a javascript expert, but it appears the mistake was in the line which says to re-submit the form, it was already submitted, you don't have to submit it again, I think the way you handled it is preferred. The form is supposed to have all those attributes. If you decide to set those attributes with javascript you should have a very good reason for doing so.

Jared

PS, Please stop writing in slang. This is an international site and it is very difficult for many readers to understand when you use "u temme" or "u r gonna" or similar incorrect English. I personally find it very annoying. I had to stop myself from insulting you just now.
Newbie
  
Join Date: Jan 2008
Location: Surat,Gujarat
Posts: 28
#9   Jan 20 '08

re: How to insert data into a database with ASP

one should specify the lock type as default lock type doesnt support inserting data into database using record set object
jhardman's Avatar
Moderator
  
Join Date: Jan 2007
Location: logan, utah
Posts: 2,690
#10   Jan 20 '08

re: How to insert data into a database with ASP

Quote:

Originally Posted by chirag1989

one should specify the lock type as default lock type doesnt support inserting data into database using record set object

Thanks, I didn't notice that I had left that out.

Jared
GazMathias's Avatar
Expert
  
Join Date: Oct 2008
Location: Bristol, United Kingdom
Posts: 143
#11   Jun 8 '09

re: How to insert data into a database with ASP

I know that this is an old article and that ASP is not quite so popular these days, but anywho:

One can vastly simplify the amount of database connection and recordset code that clutters up ASP pages by creating a library of Subs and calling them in place:

Consider:

Expand|Select|Wrap|Line Numbers
  1. OpenDB conn, site("DBQ"),  rs, "SELECT stuff FROM table" ' Site("DBQ") contains the connection string.
  2.  
Versus:

Expand|Select|Wrap|Line Numbers
  1. Dim conn
  2. Dim rs
  3. set conn = server.createObject("adodb.connection")
  4. conn.Provider="Microsoft.Jet.OLEDB.4.0"
  5. conn.Open "c:/somedatabase.mdb"
  6.  
  7. dim strSQL
  8. set rs = server.creatObject("adodb.recordset")
  9. strSQL= "SELECT * FROM userTable"
  10. rs.open strSQL, conn
  11.  
The OpenDB Sub itself makes calls to smaller subs with ByRef and ByVal arguments which do all of the work, so if you wanted to open a secondary recordset, for example to fill select box options, you would call the relevant Sub directly:

Expand|Select|Wrap|Line Numbers
  1. OpenRS rs2, conn, "SELECT some more stuff FROM adifferenttable"
  2.  
The closing statements are just as simple:

Expand|Select|Wrap|Line Numbers
  1. CloseRS rs2 ' this closes the rs and sets it to nothing.
  2. CloseDB conn, rs ' as above but for both the conn object and the recordset.
  3.  
Gaz
Reply

« previous insight | next insight »

Similar ASP / Active Server Pages bytes

/bytes/development

/bytes/it

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,223 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved