View Full Version : Insert information into database
ooiooipig
03-24-2003, 06:17 AM
Hi guys, anyone can help me with this problem?? I want to insert some informations into the database, below is the way I've done. Can anyone tell me where has it gone wrong?? This is a file whereby I use to insert the data into the database. But the data cant be inserted into the database. I dont know where the problem lies. Please advice!! thank you.
action.asp:
<!--#include file="../../include/dbconn.asp"-->
<html>
<body>
<%
sql="INSERT INTO Requests (AppnId,DesiredDt,"
sql=sql & "reqTitle,funcReq,FreqVol,Reqtr,ReqtrEmail,ContactNo,Dept,RC,Priority,ApprovOfficer,ApprovEmail,Savin gs,Revenue,Benefit,Tester,TesterContact)"
sql=sql & " VALUES "
sql=sql & "('" & Request.Form1("vAppnId") & "',"
sql=sql & "'" & Request.Form1("vDesiredDt") & "',"
sql=sql & "'" & Request.Form1("vreqTitle") & "',"
sql=sql & "'" & Request.Form1("vfuncReq") & "',"
sql=sql & "'" & Request.Form1("vFreqVol") & "',"
sql=sql & "'" & Request.Form1("vReqtr") & "',"
sql=sql & "'" & Request.Form1("vReqtrEmail") & "',"
sql=sql & "'" & Request.Form1("vContactNo") & "',"
sql=sql & "'" & Request.Form1("vDept") & "',"
sql=sql & "'" & Request.Form1("vRC") & "',"
sql=sql & "'" & Request.Form1("vPriority") & "',"
sql=sql & "'" & Request.Form1("vApprovOfficer") & "',"
sql=sql & "'" & Request.Form1("vApprovEmail") & "',"
sql=sql & "'" & Request.Form1("vSavings") & "',"
sql=sql & "'" & Request.Form1("vRevenue") & "',"
sql=sql & "'" & Request.Form1("vBenefit") & "',"
sql=sql & "'" & Request.Form1("vTester") & "',"
sql=sql & "'" & Request.Form1("vTesterContact") & "')"
conn.Execute sql
loop
conn.close
redirecturl = "default.asp"
Response.Redirect redirecturl
%>
</body>
</html>
<!--#include file="../include/dbclose.asp"-->
david7777
03-24-2003, 07:04 AM
First of all - "Request.Form1" is the probable cause. You need to use Request.Form(). It doesnt matter what the name of the form is.
A way i use to make sure i get the correct SQL statement is:
//" Create the statement
sql = "insert into requests values "
sql = sql & "bla bla bla..."
//" then output the statement for testing
Response.Write(sql)
//" Prevent the rest of the code executing
Response.End
//" The rest of you code...
Once you have the correct sql statement, then you can take out the end part. ie: the request.write and the request.end.
As you have it now, you will find that all the places that should contain data from the request.form1 will be blank...
ooiooipig
03-24-2003, 07:39 AM
sorry david, what's the response.write and response.end for?? what is it used to test?? I'm not quite clear about it
david7777
03-24-2003, 07:59 AM
response.write will put the sql statement on screen. - It will show you the exact statement that will be executed. eg:
if you use:
sql = "select * from products where id = " & request.form("id")
response.write(sql)
response.end
con.execute(sql)
"select * from products where id = 334" will display on your screen when run.
The statement will not be executed by the connection because of the response.end.
Response.end stops the processing of the rest of the code of the page.(Only if buffering is on, but this is default, so dont worry abiout this)
Response.write writes out text to the browser. ie: response.write("<p>hello</p>") will output html code to the browser.
A really good site to check these things out is http://www.w3schools.com/asp/default.asp
ooiooipig
03-24-2003, 08:12 AM
yah i got the format of inserting the data from the w3schools website, but it says page cannot be displayed.
can you take a look at the code now?? tell me where I've done wrongly.
<!--#include file="../../include/dbconn.asp"-->
<html>
<body>
<%
sql="INSERT INTO Requests (AppnId,DesiredDt,"
sql=sql & "reqTitle,funcReq,FreqVol,Reqtr,ReqtrEmail,ContactNo,Dept,RC,Priority,ApprovOfficer,ApprovEmail,Savin gs,Revenue,Benefit,Tester,TesterContact)"
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("vAppnId") & "',"
sql=sql & "'" & Request.Form("vDesiredDt") & "',"
sql=sql & "'" & Request.Form("vreqTitle") & "',"
sql=sql & "'" & Request.Form("vfuncReq") & "',"
sql=sql & "'" & Request.Form("vFreqVol") & "',"
sql=sql & "'" & Request.Form("vReqtr") & "',"
sql=sql & "'" & Request.Form("vReqtrEmail") & "',"
sql=sql & "'" & Request.Form("vContactNo") & "',"
sql=sql & "'" & Request.Form("vDept") & "',"
sql=sql & "'" & Request.Form("vRC") & "',"
sql=sql & "'" & Request.Form("vPriority") & "',"
sql=sql & "'" & Request.Form("vApprovOfficer") & "',"
sql=sql & "'" & Request.Form("vApprovEmail") & "',"
sql=sql & "'" & Request.Form("vSavings") & "',"
sql=sql & "'" & Request.Form("vRevenue") & "',"
sql=sql & "'" & Request.Form("vBenefit") & "',"
sql=sql & "'" & Request.Form("vTester") & "',"
sql=sql & "'" & Request.Form("vTesterContact") & "')"
Response.write(sql)
conn.Execute SQL
conn.close
Response.end
redirecturl = "default.asp"
Response.Redirect redirecturl
%>
</body>
</html>
<!--#include file="../include/dbclose.asp"-->
david7777
03-24-2003, 08:20 AM
I need to know the error you are getting.
I also need to see the output of the response.write(sql) you have done. If I can see that, then it will be a lot easier to fix.
So first put the response.end before conn.Execute SQL.
Run the code, and copy and paste the results.
Then take out the response.end and run the code to get the database error. Copy and paste that as well, and send it all to me.
ooiooipig
03-24-2003, 08:51 AM
it says "The page cannot be displayed, HTTP 500 - Internal server error Internet Explorer"
both with or without the response.write and response.end, the results are the same.
david7777
03-24-2003, 08:55 AM
Then it seems that the problem is in the <!--#include file="../../include/dbconn.asp"-->. I need to see that code, and the code in <!--#include file="../include/dbclose.asp"-->.
Just some comments:
- Do as david777 says to debug the sql statement.
- The rest of your code can also use some shaping up. For istance, the close db-include should be placed before the redirect and the redirect should be.
Wy use a variable for the filename?
And the redirect should depend on the result of your db-operation, so you should check for the number of inserted records (= a standard parameter) and only do the redirect if it’s “1”. If it’s “0” , show an errormessage.
- about the erro: are you sure there is a file called defaul.asp in the same folder as this asp file?
Some code that might be inspiring
<%option explicit%>
<%@ LANGUAGE="VBSCRIPT" %><% Response.Buffer=True %>
<html>
<head>
<meta http-equiv="content-type" content="text/html;charset=iso-8859-1">
<title>Add record</title>
</head>
<body>
<center>
<%
dim sql, numberinserted
sql="INSERT INTO table (var1, var2, var3, var4) VALUES ('value1', 'value2', 'value3', 'value4')"
sql=replace(sql,"value1",replace(request.form("variabele1"),"'","''")) 'replace value1 with value from form.
sql=replace(sql,"value2",replace(request.form("variabele2"),"'","''")) 'second replace to prevent problems with single quotes
sql=replace(sql,"value3",replace(request.form("variabele3"),"'","''")) 'all variables are considered as text variables.
sql=replace(sql,"value4",replace(request.form("variabele4"),"'","''"))
dim condb
set condb=server.CreateObject("adodb.connection")
condb.Open("provider=microsoft.jet.oledb.4.0;data source="&server.MapPath("database.mdb"))
condb.Execute sql,numberinserted
'comment: numberinserted is a parameter that return the number of records that are inserted
condb.Close
set condb = nothing
if numberinserted=1 then
response.redirect(“default.asp”)
else
response.write("Problem. Data was not saved in db.")
end if
%>
</center>
</body>
</html>
our posts crossed. check out my previous post and remove your db-name, userid and pasword from yours (just in case some bad guys are lurking around here ...) (in addidtion to myself, that is :D
ooiooipig
03-24-2003, 09:13 AM
thanks raf but I dont understand the code you given me. I've changed e pwd n name already... thanks!! by the way, I'm using sql server to do this project, not access. d:)
Spudhead
03-24-2003, 09:16 AM
Just a quick note on this:
The page cannot be displayed, HTTP 500 - Internal server error Internet Explorer
You need to turn off HTTP-friendly error messages. In IE, go Tools-> Internet Options-> Advanced and, under the "Browsing" section, make sure that "Show HTTP friendly error messages" is unchecked.
You will then get a more detailed description of what error occurred, and where, which should help you track down your problem.
ooiooipig
03-24-2003, 09:25 AM
okie, I've found out the error!! but I dont know what is it about...
here's the error:
Microsoft VBScript runtime error '800a01a8'
Object required: ''
/ISRequest/request_action.asp, line 28
ooiooipig
03-24-2003, 09:44 AM
if I change the
conn.execute(sql) and conn.close
to
MSCS.execute(sql)
then it'll show this error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Disallowed implicit conversion from data type varchar to data type money, table 'isreq.dbo.Requests', column 'Savings'. Use the CONVERT function to run this query.
/ISRequest/request_action.asp, line 28
anyone can help me solve?? Thanks...
No problem. You can use your includes for the dbopening and closing. (Didn’t write this code for you, just somethinng i posted last week ;)) Just replace the related paragraphs with your includes
What do you not understand ?
I build my sql statement a little bit diferent. Might look like more code (lotts of copying and pasting though) but it’s easier to debug and to write.
I just write a sort of dummy sql satement with the right variables and tablename, but with fake values. I then replace these fake values with the values from the formelements. (always use the second replace to change single quotes!!)
It’s very transparant.
Then i use a parameter to get the number of inserted records and evaluate it to know what i need to do (redirect or display errormessage). There nothing more to that then what you see in the code.
I’m quite sure your sql statement can’t be correct.
You treat all variables as text variabels, which will probably not be the case. And you have linebreaks that can’t we right (unless this is due to some copying here …)
(as your error shows).
I think you realy need to look at the sql statent before moving on.
do as david7777 suggested and have the sql string written to the browser and then, on the very next line, include a response.end to stop the further processing . Before the conn.Exectute line
The post the sqlstatement you get in your browser here.
ooiooipig
03-25-2003, 12:47 AM
Hi there, can anyone tell me what is this error about?? I dont know what it means. It's stated below:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Disallowed implicit conversion from data type varchar to data type money, table 'isreq.dbo.Requests', column 'Savings'. Use the CONVERT function to run this query.
/ISRequest/request_action.asp, line 28
david7777
03-25-2003, 07:20 AM
Im not a SQL guru and haven't really used SQL Server. You have used a field type "money" when creating the database, and now are trying to put a "varchar" value in the field. You cant do that without a conversion. You need to use the CONVERT keyword in your statement... I found this site to explain how to use it:
http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/ca-co_1.htm
I think that should give you everything you need - its got quite a lot on both cast and convert... Check it out - its good.
:cool:
dominicall
03-28-2003, 04:00 PM
A couple of thoughts on this...
You can use CONVERT to change the data format to money type, or just as easily use FormatCurrency with the form object, i.e.FormatCurrency(Request.Form("vRevenue"),2)will convert the value from the form to the servers currency with two digits after the decimal.
Go here http://www.devguru.com/Technologies/vbscript/quickref/formatcurrency.html for a good explanation of FormatCurrency fucntion.
And if you're using SQL - try and use stored procedures for all your data work - this will speed up both the site and database interactions as the data work is moved to the data layer. It can make a significant difference.
There's lot of good stuff on 4guys about stored procedures.
dominicall :D
vBulletin® v3.8.2, Copyright ©2000-2012, Jelsoft Enterprises Ltd.