Explain different ways of adding data into a database 1

[Guest_imported]

Can you all explain the different ways to add data into a database?

E.g SQL sever, Microsoft Acces etc

 

[Ovatvvon]

Entering data into databases, regardless of the type of database, is controlled by your sql statement. The two main(used most often) ways to write to the database are the update and append statements.

Append will add a new/additional line to the table with new information. Update will take an existing record within a table and change or add new information to it.

Append:
-------
Dim value1, value2, value3
value1 = "John"
value2 = "Doe"
value3 = "New York"

sql = "Insert Into yourTableName (field1Name, field2Name, field3Name) VALUES ('" & value1 & "', '" & value2 & "', '" & value3 & "');"


Update:
-------
Dim value1, value2, value3, someNumber
value1 = "Hank"
value2 = "Hill"
value3 = "Texas"
someNumber = Request.Form("recordID&quot ' Autonumber of the record you want to change

sql = "UPDATE yourTableName SET field1Name='" & value1 & "',field2Name='" & value2 & "',field3Name='" & value3 & "' WHERE ((yourTableName.recordPK)=" & someNumber & ""

' recordPK is the name of the autonumber field in your table.


Let me know if that helps. -Ovatvvon :-Q

 

[Guest_imported]

Thank you, Ovatvvon

 

[Guest_imported]

what must be put in the val?
i am still feel confused about the value insert.
bcoz my field consists of student_name, UserID,Password,Department,Year,E-mail. So what value must be insert so that the data can be added to the database.
Thanks

 

[Ovatvvon]

Suppose your table name was myTable.
Then with your fields:
name (gonna shorten it for typeing here, but is the same thing as student_name)
userID
pwd (short for password)
dept (short for department)
yr
email (reformatted name for E-mail)

Suppose on a form from the last page you had the user input this data, like so:

page1.html
--------------

<form action='page2.asp' method='post'>
Name: <input type='text' name='name'><BR>
ID: <input type='text' name='id'><BR>
Password: <input type='text' name='pwd'><BR>
Department: <input type='text' name='dept'><BR>
Year: <input type='text' name='yr'><BR>
Email Addr: <input type='text' name='email'><BR>
<input type='submit' value='Submit Information'><BR>
</Form>


It doesn't matter what you name each field here because that's just the name of the element on the form of the page taking the inforamtion, you'll pull this info on the next page and assign it to your variables.

Remember on the next page, it doesn't matter what your variables are named, just as long as you know what name is what data. And remember to put your actual database field names in the insert statement rather than my shortened ones for this quick description/lesson.

page2.asp
----------
<% @ Language=VBScript%>
<% Option Explicit
Dim myName, myUserID, myPwd, myDept, myYr, myEmail, sql

myName = Request.Form(&quot;name&quot
myUserID = Request.Form(&quot;id&quot
myPwd = Request.Form(&quot;pwd&quot
myDept = Request.Form(&quot;dept&quot
myYr = Request.Form(&quot;yr&quot
myEmail = Request.Form(&quot;email&quot

sql = &quot;INSERT INTO myTable (name, userID, pwd, dept, yr, email) VALUES ('&quot; & myName & &quot;', '&quot; & myUserID & &quot;', '&quot; & myPwd & &quot;', '&quot; & myDept & &quot;', '&quot; & myYr & &quot;', '&quot; & myEmail & &quot;');&quot;

Dim conn, rs, DSNtemp
Set conn = Server.CreateObject(&quot;ADODB.Connection&quot
Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
DSNtemp = &quot;DRIVER={Microsoft Access Driver (*.mdb)};&quot;
DSNtemp = DSNtemp & &quot;DBQ=C:\Inetpub\

http://wwwroot\db\yourDatabaseName.mdb;&quot;

conn.Open DSNtemp
conn.Execute(sql)
conn.Close
Set conn = nothing

Response.redirect(&quot;page1.html&quot
%>


just change the field names listed in the sql statement (name, userID, pwd, etc etc) to the actual names of the fields in your database, change the DBQ line in the DSNtemp variable to the actual location of your database on the hard drive (c:\Inetpub\

http://wwwroot\...etc

etc) and then copy this code into the two pages and them them accordingly (page1.html, & page2.asp) and you should have a working site, quick and simple. That'll show you how it works. (also, don't forget to change the name &quot;myTable&quot; in the sql statement to the actual name of your database.

So
1) change the name of the database in the sql statement.
2) change the names of the fields in the sql statement to your names.
3) change the location of the database in the DBQ line of the DSNtemp variable to the location of your database on your harddrive.
4) create two pages, page1.html and page2.asp and copy the coresponding code I have displayed here with your alterations into each of those pages, then run them. You'll see them work. Then you'll see how the insert into (append) statement works.


Does this make sense?
-Ovatvvon :-Q

 

[Guest_imported]

Thank you X-)

 

[Ovatvvon]

Your Welcome.
Just let me know if you have any more problems. -Ovatvvon :-Q

 

[Guest_imported]

I have done all this but it is still not working. Has the design in database affect the connection as i have 2 tables in the databases

 

[Ovatvvon]

the amount of tables doesn't matter in the database, you're only telling the connection to deal with the one. But it IS important to make sure your db path and all your names (table, fields, etc) are all exactly correct! Very important. Double check...if you still can't get it, then I'll design a small access database for you with 1 or 2 .asp pages and put 'em in a zip file for you to open and run in your web server and try out...but I really don't have a lot of time to do this, so please try to double check everything again first.

let me know... -Ovatvvon :-Q

 

[Guest_imported]

Ok. Thank a lot