Problem inserting data to database

I have developed a intranet . Using HTML , SQL and ASP . I have created a table with SQL is just fine . And design a form is just looks fine to me . But when I try to insert Data into the SQL I just have the Error . The Error appears when I press submit button . But when i just call it using this url http://10.164.1.86/lala.asp . It produce the massege i want it to produce after inserting the information. Hope you can help . My Code looks this one here .

Code: ( text )

<%
Catergory = Trim(Request.Form("registry"))
If Catergory= "Title" then
Title = Trim(Request.Form("Title"))
Author = Trim(Request.Form("Author"))
ISBN = Trim(Request.Form("ISBN"))
Publisher= Trim (Request.Form("Publisher"))
Place_of_Publication= Trim(Request.Form("Place of Publication"))
Year=Trim(Request.Form("Year"))
Collation=Trim(Request.Form("Collation"))
Shelf_Number=Trim(Request.Form("Shelf Number"))
If Title <>"" and Author <>"" and ISBN <>"" and Publisher <>"" and Place_of_Publication <>"" and Year <>"" and Collation <>"" and Shelf_Number<>"" Then
%>
MyStatement = "Insert into Book_files Set Values
Title='"&Title&"',Author='"&Author&"', ISBN='"&ISBN&"',Publisher='"&Publisher& "',Place_of_Publication='"& PlaceofPublication&"',Year='"&Year&"',Collation='"& Collation&"',Shelf_Number='"&ShelfNumber&"'"
<%
Set Con = Server.CreateObject("ADODB.Conection")
Con.Open "PROVIDER=SQLOLEDB;DATA SOURCE=master;UID=sa;PWD=sa;DATABASE=Book_files"
Con.Execute MyStatement
End IF
End IF
%>
<Html>
<Head><Title>Insert Data</Title></Head>
<Body>
<Form Method="POST" action.asp? /registry.html><Head> <Center> <Font Color="Brown" Size=11 Face="Comic Sans Ms">
Confirmation Page </Font> </Center>
<Body BGColor="Yellow">
<Br><Br>
<%
Response.Write(" Your Value Has Been Successfully Inserted In The Table!")
%>
</Body>
</Html>

bseakgano

Newbie

12 Posts

March 13th, 2007
12:36 PM
#10

Re: Problem inserting data to database

Can you please modify this code if possible . I have months now trying to insert data with this codes . When ever I try to execute this I get an error . I want to insert data into SQL with this codes . If I have skipped a line please let me know. I have tried to search from this site , hoping that someone had the same problems . but theirs was not responded . Now I don’t know what to do. Please help .

Code: ( text )

<A Href="library.Html" Target="ThirdPartition"><Font Face="Garamond" Size="1">HOME</Font></A><br></br>
<html>
<Head> <U> <Center><Font Color="Red" Face="Garamond" Size=11> Registration Book</Font> </Center> </U>
<Title> Registration Page </Title>
</Head>
<Body Bgcolor="Pink">
<Form Method="Post" Action="lala.asp?">
<Br> <Br>
<B>Title:</B>                                <Input Type="Text" Name="Title">
<Br> <Br>
<B>Author:</B>                             <Input Type="Text" Author="Author">
<Br> <Br>
<B>ISBN/ISSN :</B>                   <Input Type="Text" ISBN/ISSN="ISBN/ISSN">
<Br> <Br>
<B>Publisher:</B>                         <Input Type="Text" Name="Publisher"><Br> <Br>
<B>Place Of Publication:</B>     <Input Type="Text" Place Of Publication="Place Of Publication"><Br> <Br>
<B>Year:</B>                                 <Input Type="Text" Year="Year">
<br></br>
<B>Collation:</B>                          <Input Type="Text" Collation="Collation">
<Br> <Br>
<B>Shelf Number :</B>                 <Input Type="Text" Shelf Number="Shelf Number">
<Br> <Br>
<Br> <Br>
<Center><Input Type="Submit" Style="Font-Family:Monotype Corsiva;Color:Yellow;Font-Size:22;Font-Weight:Bold;Background:Brown" Value="Submit"></Center>
</Form>
</Body>
</Html>
<%
registry=Trim(Request.Form("registry"))
Catergory = Trim(Request.Form("registry"))
If Catergory= "Title" then
Title = Trim(Request.Form("Title"))
Author = Trim(Request.Form("Author"))
ISBN = Trim(Request.Form("ISBN"))
Publisher= Trim (Request.Form("Publisher"))
Place_of_Publication= Trim(Request.Form("Place of Publication"))
Year=Trim(Request.Form("Year"))
Collation=Trim(Request.Form("Collation"))
Shelf_Number=Trim(Request.Form("Shelf Number"))
If Title <>"" and Author <>"" and ISBN <>"" and Publisher <>"" and Place_of_Publication <>"" and Year <>"" and Collation <>"" and Shelf_Number<>"" Then
<%
MyStatement = "Insert into Book_files Set Values
Title='"&Title&"',Author='"&Author&"', ISBN='"&ISBN&"',Publisher='"&Publisher&"',Place_of_Publication='"& PlaceofPublication&"',Year='"&Year&"',Collation='"&Collation &"',Shelf_Number='"&ShelfNumber&"'"
Set Con = Server.CreateObject("SQLOLEDB")
Con.Open "PROVIDER=SQLOLEDB;DATA SOURCE=master;UID=<hidden>;PWD=<hidden>;DATABASE=Book_files"
Con.Execute MyStatement
End IF
End IF
%>
<Html>
<Head><Title>Insert Data</Title></Head>
<Body>
<Form Method="POST" action="/registry.html"><Head> <Center> <Font Color="Brown" Size=11 Face="Comic Sans Ms">
Confirmation Page </Font> </Center>
<Body BGColor="Yellow">
<Br><Br>
<%
Response.Write(" Your Value Has Been Successfully Inserted In The Table!")
End IF
%>
</Body>
</Html>

Edited Code - Removed username and password for database from his code (also wrapped it in code tags) - iam_clint

jhardman

Moderator

2,171 Posts

March 13th, 2007
06:27 PM
#11

Re: Problem inserting data to database

Alright, bseakgano. I will help you, but you must trust me and do what I say. Understand?

First off, there are several mistakes in your form. you can't name a tag attribute like this:

Code: ( text )

<Input Type="Text" ISBN/ISSN="ISBN/ISSN">

and then get the data like this:

Code: ( text )

ISBN = Trim(Request.Form("ISBN"))

instead it should read:

Code: ( text )

<Input Type="Text" name="ISBN">

in other words, each input tag should have a name attribute which tells the name of the input, then you access that input according to the name attribute:

Code: ( text )

ISBN = Trim(Request.Form("ISBN"))

You were very sloppy not using the same name you had chosen when you tried to access the input data. First, fix all of the form input names.

Second, make sure you are getting all of the input data you expect by listing them severally right before you need to use them:

Code: ( text )

response.write "Author: " & request.form("author") & "<br>" & vbNewLine
'etc

This verifies that the data was passed to the script that will handle it in the way you expect it.

Third, your insert code is all within a complex if...then statement wherein if a single input is blank or mis-named the entire thing will not execute and the entered data will be lost. Unless you fix everything else, the database will never be updated. Besides that, it is very difficult for us as anonymous people on a forum to fix data base access on someone else's server. In order for your code to work on our servers, we would need a database set up identically to yours, so it is going to be difficult for us to even check.

Fourth, please fix your HTML code. Running your page through an HTML validator (see the sticky at the top of the HTML forum) would have caught several of these errors. Formatting your pages with css or even placing your inputs within a table would have aligned them better and made reading through the code easier. It is very difficult to read through someone's code where half of the code is

Code: ( text )

I have connected to different types of databases and updated databases in different ways and using different methods. If you fix those problems and post your corrected code I will help you in the next step. Oh, and post your code within code tags (the word "code" in square brackets to start, and "/code" in square brackets to finish).

Jared

bseakgano

Newbie

12 Posts

March 14th, 2007
08:04 AM
#12

Re: Problem inserting data to database

I still have a problem . Now I do understand that what I am doing is not easy like I thought . Please you may do something like what I did . All I want is light . I will follow . Please believe me I have tried everything from you guys I still have the code. The worst part now is when I try to execute it turn itself into a search engine. Guys please I need you help. This my code after I try to fix something in it. But it become worse . My wish to help others with same problem in the future .

Code: ( text )

<html>
<Head> <U> <Center><Font Color="Red" Face="Garamond" Size=11> Registration Book</Font> </Center> </U>
<Title> Registration Page </Title>
</Head>
<Body Bgcolor="Pink">
<code> <Form Method="Post" Action="lala.asp?"> </code>
<Br> <Br>
<B>Title :</B>   <Input Type="Text" Name="Title">
<Br> <Br>
<B>Author :</B>   <Input Type="Text" Author="Author">
<Br> <Br>
<B>ISBN :</B>   <Input Type="Text" ISBN/ISSN="ISBN">
<Br> <Br>
<B>Publisher:</B>     &nbsp<Input Type="Text" Name="Publisher"><Br> <Br>
<B>Place Of Publication:</B>     <Input Type="Text" Place Of Publication="Place Of Publication"><Br> <Br>
<B>Year: </B>    <Input Type="Text" Year="Year">
<br></br>
<B>Collation :</B>        <Input Type="Text" Collation="Collation">
<Br> <Br>
<B>Shelf Number :</B>        <Input Type="Text" Shelf Number="Shelf Number">
<Br> <Br>
<Br> <Br>
<Center><Input Type="Submit" Style="Font-Family:Monotype Corsiva;Color:Yellow;Font-Size:22;Font-Weight:Bold;Background:Brown" Value="Submit"></Center>
</Form>
</Body>
</Html>

ASP CODE

Code: ( text )

<%
category = Trim(Request.Form("registry"))
If category="Book_files" then
Title = Trim(Request.Form("Title"))
Author = Trim(Request.Form("Author"))
ISBN = Trim(Request.Form("ISBN"))
Publisher = Trim(Request.Form("Publisher"))
Place_of_Publication = Trim(Request.Form("PlaceofPublication"))
Year = Trim(Request.Form("Year"))
Collation = Trim(Request.Form("Collation"))
Shelf Number = Trim(Request.Form("ShelfNumber"))
If CBookTitle<>"" AND CAuthor<>"" AND IISBN<>"" AND CPublisher<>""AND CPlaceofPublication<>"" AND IYear<>"" AND CCollation<>"" AND IShelfNu<>"" Then
MySQL = "Insert into Book_files(CBook_Titles,CAuthor,IISBN,CPublisher,C Place_of_Publication,
IYear,CCollation,IShelfNu
Values('"&BookTitle&"','"&Author&"','"&ISBN&"','"Publisher&"','"&PlaceofPublication&"',
'"&Year&"','"&Collation&"','"&ShelfNu&"')"
Set con = Server.CreateObject("ADODB.Connection")
Con.Open "Provider=SQLOLEDB;Data Source=G144379\G144379;UID=sa;Pwd=sa;DataBase=Brun o;
response.write "Title: " & request.form("Title") & "<br>" & vbNewLine
',"Author: " & request.form("author") & "<br>" & vbNewLine
',"ISBN: " & request.form("ISBN") & "<br>" & vbNewLine
',"Publisher: " & request.form("Publisher") & "<br>" & vbNewLine
',"Place of Publication: " & request.form("Place of Publication") & "<br>" & vbNewLine
',"Year: " & request.form("Year") & "<br>" & vbNewLine
',"Collation: " & request.form("Collation") & "<br>" & vbNewLine
',"Shelf Number: " & request.form("Shelf Number") & "<br>" & vbNewLine
Con.Execute MySQL
end if
%>
<Html>
<Head> <Center> <Font Color="Brown" Size=11 Face="Comic Sans Ms">
Confirmation Page </Font> </Center>
<Title> Results Returned By Active Server Pages </Title>
</Head>
<Body BGColor="Yellow">
<Br><Br>
<%
Response.Write ("Your Value Has Been Successfully Inserted In The Table!")
End If
%>
</Body>
</Html>

SQL is Like This

Code: ( text )

Create table Book_file
(
Title char(100)not null,
Author char(100)not null,
ISBN into,
Publisher char(100),
Place_of_Publisher char(100),
Year into,
Collation varchar(100),
Shelf_number int
)

Last edited by jhardman : March 27th, 2008 at 02:09 AM. Reason: put code in code tags. Please note button marked - #

jhardman

Moderator

2,171 Posts

March 14th, 2007
07:54 PM
#13

Re: Problem inserting data to database

try this exact code. Don't change a thing:

Code: ( text )

<%
if request.form("title") = "" then %>
<html>
<Head>
<Title>Registration Page</Title>
<style>
<!--
label {
color: black;
position: relative;
left: 40px;
}
input {
position: absolute;
top: inherit;
left: 220px;
}
fieldset {
border: 0px;
padding: 5px;
}
-->
</style>
</head>
<body style="background-color: pink">
<h1 style="text-align: center; text-decoration: underline;font:Garamond
san-serif 16pt;color:'Red'"> Registration Book</h1>
<Form Method="Post" Action="lala.asp">
<fieldset><label for="title">Title:</label><input type="text"
name="title" value="<%=request.form("title")%>"></fieldset>
<fieldset><label for="author">Author:</label><Input type="text"
name="author" value="<%=request.form("author")%>"></fieldset>
<fieldset><label for="isbn">ISBN/ISSN:</label><Input type="text"
name="isbn" value="<%=request.form("isbn")%>"></fieldset>
<fieldset><label for="publisher">Publisher:</label><Input type="text"
name="publisher" value="<%=request.form("publisher")%>"></fieldset>
<fieldset><label for="place">Place of Publication:</label><Input
type="text" name="place" value="<%=request.form("place")%>"></fieldset>
<fieldset><label for="year">Year:</label><Input type="text"
name="year" value="<%=request.form("year")%>"></fieldset>
<fieldset><label for="collation">Collation:</label><Input
type="text" value="<%=request.form("collation")%>" name="collation"></fieldset>
<fieldset><label for="shelf">Shelf Number:</label><Input
type="text" name="shelf" value="<%=request.form("shelf")%>"></fieldset>
<fieldset style="text-align: center"><Input type="submit"
style="Font-Family:Monotype Corsiva;
Color:Yellow;Font-Size:22;Font-Weight:Bold;Background:Brown"
Value="Submit"></fieldset>
</Form>
</Body>
</Html>
<%
else
'category = Trim(Request.Form("registry"))
'If category="Book_files" then
dim fld, inp, objCon, objRS
for each inp in request.form
response.write inp & ": " & request.form(inp) & "<br>" & vbNewLine
next
'Title = Trim(Request.Form("Title"))
'Author = Trim(Request.Form("Author"))
'ISBN = Trim(Request.Form("ISBN"))
'Publisher = Trim(Request.Form("Publisher"))
'Place_of_Publication = Trim(Request.Form("PlaceofPublication"))
'Year = Trim(Request.Form("Year"))
'Collation = Trim(Request.Form("Collation"))
'Shelf Number = Trim(Request.Form("ShelfNumber"))
'If CBookTitle<>"" AND CAuthor<>"" AND IISBN<>"" AND CPublisher<>""AND CPlaceofPublication<>"" AND IYear<>"" AND CCollation<>"" AND IShelfNu<>"" Then
'MySQL = "Insert into Book_files(CBook_Titles,CAuthor,IISBN,CPublisher,C Place_of_Publication,IYear,CCollation,IShelfNu
'Values('"&BookTitle&"','"&Author&"','"&ISBN&"','"Publisher&"','"& PlaceofPublication&"','"&Year&"','"&Collation&"','"&ShelfNu&"')"
Set objCon = Server.CreateObject("ADODB.Connection")
objCon.Open "Provider=SQLOLEDB;Data Source=G144379\G144379;UID=sa;Pwd=sa;DataBase=Brun o;
set objRS = Server.CreateObject("ADODB.Recordset")
objRS.open "SELECT * FROM [Book_files]", objCon
for each fld in objRS.fields
response.write fld.name & "<br>" & vbNewLine
next
'response.write "Title: " & request.form("Title") & "<br>" & vbNewLine
',"Author: " & request.form("author") & "<br>" & vbNewLine
',"ISBN: " & request.form("ISBN") & "<br>" & vbNewLine
',"Publisher: " & request.form("Publisher") & "<br>" & vbNewLine
',"Place of Publication: " & request.form("Place of Publication") & "<br>" & vbNewLine
',"Year: " & request.form("Year") & "<br>" & vbNewLine
',"Collation: " & request.form("Collation") & "<br>" & vbNewLine
',"Shelf Number: " & request.form("Shelf Number") & "<br>" & vbNewLine
'Con.Execute MySQL
'end if %>
<Html>
<Head> <Center> <Font Color="Brown" Size=11 Face="Comic Sans Ms">
Confirmation Page </Font> </Center>
<Title> Results Returned By Active Server Pages </Title>
</Head>
<Body BGColor="Yellow">
<Br><Br>
<%
'Response.Write ("Your Value Has Been Successfully Inserted In The Table!")
End If %>
</Body>
</Html>
<!--
SQL is Like This
Create table Book_file
(
Title char(100)not null,
Author char(100)not null,
ISBN into,
Publisher char(100),
Place_of_Publisher char(100),
Year into,
Collation varchar(100),
Shelf_number int
)
-->

Save the entire thing as one file named lala.asp. Run it and let me know what happened