PDA

View Full Version : Import the textarea data into database

BOBKUSPE
08-19-2010, 12:52 PM
Hi,

I am trying import the data from textarea into database, but after dozen of attempts I am declining...


<html>
<head>
<title>Untitled</title>
</head>

<body>

<%
'Declare all local variables
dim conn
dim rs
dim strconn
dim strsql
strsql = ""
'set connection string to local variable-I use a DSN-less connection
%>

<%
strconn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("online.mdb")
%>

<%
Response.Write "<form method=get name=url>"
Response.Write "<textarea name=pageContent id=pageContent rows=10 cols=120 >"
response.write strplaintext
Response.Write "</textarea>"
txtValue = Request.Form("pageContent") %>


<%
DIM arr, arrLen, i, text
text = Request.Form("pageContent")
'Split the content of TextArea text into a array
arr = Split(Request.Form("pageContent"), CHR(13)&CHR(10))

arrLen = UBOUND(arr)
'Loop line by line
FOR i = 0 TO arrLen
Response.Write arr(i) & "<BR>"
NEXT
%>

<%
'build the sql statement based on the input from the form
strSQL = "INSERT INTO table1(url, pageContent)"
strSQL = strSQL & " SELECT "
strSQL = strSQL & "'" & request("url") & "' as text1,"
strSQL = strSQL & "'" & request("pageContent") & "' as text2"



set conn = server.createobject("adodb.connection")
conn.open strconn
'Use the execute method of the connection object the insert the record
conn.execute(strSQL)

conn.close
set conn = nothing

%>

<input type="Submit" value="Add" name="text" >



</form>

<% = "Your record has been added" %>

</body>
</html>


What is wrong in that script?

Bob Kuspe
Old Pedant
08-19-2010, 07:02 PM
Well, your SQL statement is both wrong and inadequate. But even fixing *MAY* not work.

You can try this:

'build the sql statement based on the input from the form
strSQL = "INSERT INTO table1(url, pageContent) " _
& " VALUES('" & Replace(Request("url"),"'","''") & "', " _
& "'" & Replace(Request("pageContent"),"'","''") & "')"

You *must* "ESCAPE" all apostrophes. And, in the process, that will protect you against SQL injection.

But...

But with Access, that still may not work. There might be too much data in the SQL query.

So you may need to completely change how you do the insert, using ADODB.Recordset.AddNew, instead.

This:

<%
' this is a MUCH better driver to use than the ancient Access driver:
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & Server.MapPath("online.mdb")

' this part the same:
set conn = server.createobject("adodb.connection")
conn.open strconn
' but now you create a recordset:
set rs = server.createobject("adodb.Recordset")
' and open the entire table for writing with appropriate cursor
rs.Open "table1", conn, 3, 3

' this creates a new record in the table:
rs.AddNew
' and so you assign the field value to the columns
' you do NOT need or want to do apostrophe replacement
' when using this kind of code:
rs("url") = Request("url")
rs("pageContent") = Request("pageContent")

' and this is the very important step:
rs.Update

' and of course:
rs.Close
conn.Close
%>
Old Pedant
08-19-2010, 07:05 PM
By the way, instead of doing all of this code:

DIM arr, arrLen, i, text
text = Request.Form("pageContent")
'Split the content of TextArea text into a array
arr = Split(Request.Form("pageContent"), CHR(13)&CHR(10))

arrLen = UBOUND(arr)
'Loop line by line
FOR i = 0 TO arrLen
Response.Write arr(i) & "<BR>"
NEXT
%>

You would do this:

<%= Replace( Request("pageContent"), vbNewLine, "<br/>" & vbNewLine ) %>