The INSERT INTO statement

LaPluma · Oct 1, 2002

Hello

I have a simple SQL INSERT INTO statement so that I can add a record to a database.

The full code, including the INSERT INTO statement, reads as follows:

<%
Option Explicit

Dim ConnectionString
Dim connObj
Dim sql
Dim oRS

ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("\stevehigham\db\testDB.mdb&quot

& ";" & _
"Persist Security Info=False"

Set ConnObj = Server.CreateObject("ADODB.Connection&quot

connObj.Open ConnectionString

sql = "select * from Table1"
sql = "SELECT girlSurname, girlFirst FROM Table1 ORDER BY girlSurname"
sql = "INSERT INTO Table1 VALUES (girlFirst, girlSurname)"

Set oRS = connObj.Execute(sql)
Do While Not oRS.EOF
Response.Write(oRS("girlFirst&quot

& " - " & oRS("girlSurname&quot

& "<br>&quot

oRS.MoveNext
Loop

oRS.Close
connObj.Close

Set oRS = Nothing
Set connObj = Nothing
%>

Now I suppose I need some HTML code so that the user can input a girl's first and surnames and send it to the database, but should I include this HTML code in the same .asp file as the above code, or is it best to create a new HTML file. If I create a new HTML file, how can the .asp file 'see' it?

Thank you for any help.

Best wishes

LaPluma

snowboardr · Oct 1, 2002

Put all that above the html.. I would suggest putting it as close to the start of your first Response.Write(oRS.. so then at the end you close it as soon as possible. You don't want your connection open for long periods of time, it wastes resources. www.vzio.com
ASP WEB DEVELOPMENT

snowboardr · Oct 1, 2002

I must ask though, why do you have this?

sql = "select * from Table1"
sql = "SELECT girlSurname, girlFirst FROM Table1 ORDER BY girlSurname"
sql = "INSERT INTO Table1 VALUES (girlFirst, girlSurname)"

They are canceling each other out.. www.vzio.com
ASP WEB DEVELOPMENT

LaPluma · Oct 1, 2002

Hello Snowboard

Many thanks for both of your messages.

I suppose the idea was that I would have a page which showed the records in the database, so:

sql = "select * from Table1"

then I wanted the names in order, so:

sql = "SELECT girlSurname, girlFirst FROM Table1 ORDER BY girlSurname"

then I wanted to include an input box so that users could add their own first names and surnames, so I posted the message about INSERT INTO.

How would you go about doing this? The page that appears after the new records have been added to the database would need to show the new entries.

Many thanks

snowboardr · Oct 1, 2002

Ok, if you are wanting to "add" a girls name to the database then you would use the insert statement.

First of all your form would be like this on page1.asp

Code:

<form name=&quot;form1&quot; method=&quot;post&quot; action=&quot;[b]page2.asp[/b]&quot;>
  <input type=&quot;text&quot; name=&quot;first&quot;>
  <input type=&quot;text&quot; name=&quot;sur&quot;>
  <input type=&quot;submit&quot; name=&quot;Submit&quot; value=&quot;Submit&quot;>
</form>

Insert a new record your select statement would be like this on page2.asp:

Code:

<%
Option Explicit

Dim ConnectionString, connObj
Dim strSQL_INSERT, oRS

Dim frmFirst, frmSur

frmFirst = Request.Form(&quot;first&quot;)
frmSur = Request.Form(&quot;frmSur&quot;)


ConnectionString=&quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & Server.MapPath(&quot;\stevehigham\db\testDB.mdb&quot;) & &quot;;&quot; & _
              &quot;Persist Security Info=False&quot;

Set ConnObj = Server.CreateObject(&quot;ADODB.Connection&quot;)
connObj.Open ConnectionString


strSQL_INSERT = &quot;select * from Table1&quot;
strSql = &quot;INSERT INTO Table1 (girlFirst, girlSurname) VALUES (&quot; & &quot;'&quot; & frmFirst & &quot;','&quot; & frmSur & &quot;')&quot;

 
Set oRS = connObj.Execute(sql)


oRS.Close
connObj.Close


Set oRS = Nothing
Set connObj = Nothing


%>

Then after that if you want to display everything.. do what you did above.

www.vzio.com
ASP WEB DEVELOPMENT

LaPluma · Oct 1, 2002

Hello Snowboard

Again, very many thanks for your message.

I have saved it and will work on it tomorrow (I'll need to read it and digest it first and then try it out).

I'll let you know how I get on.

Best wishes and, once more, I'm most appreciative.

LaPluma

LaPluma · Oct 4, 2002

Hello Snowboard

I have created two asp files as you suggested. Page1.asp, the page with the input boxes in it, is here:

http://www11.brinkster.com/stevehigham/db/page1.asp

....but when I click on submit to insert the record (in this case, girl's first name and girl's surname), I get a server message, HTTP 405 telling me (in the title bar): "Resource Not Allowed".

If I then go back into my server's File Manager and click on page2.asp, I get the following:

HTTP 500: Internal Server Error.

Below is the code for page1.asp:

<form name="form1" method="post" action="page2.asp">
<input type="text" name="first">
<input type="text" name="sur">
<input type="submit" name="Submit" value="Submit">
</form>

...and here is the code for page2.asp:

<%
Option Explicit

Dim ConnectionString, connObj
Dim strSQL_INSERT, oRS

Dim frmFirst, frmSur

frmFirst = Request.Form("first&quot

frmSur = Request.Form("frmSur&quot

ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("\stevehigham\db\testDB.mdb&quot

& ";" & _
"Persist Security Info=False"

Set ConnObj = Server.CreateObject("ADODB.Connection&quot

connObj.Open ConnectionString

strSQL_INSERT = "select * from Table1"
strSql = "INSERT INTO Table1 (girlFirst, girlSurname) VALUES (" & "'" & frmFirst & "','" & frmSur & "')"

Set oRS = connObj.Execute(sql)

oRS.Close
connObj.Close

Set oRS = Nothing
Set connObj = Nothing

%>

Do you know what I'm doing wrong?

Best wishes

LaPluma

snowboardr · Oct 4, 2002

take out the in the post form part of page2.asp www.vzio.com
ASP WEB DEVELOPMENT

roy29 · Oct 4, 2002

Here are a few changes to make in your second page.

This line -> frmSur = Request.Form("frmSur&quot

should look like this instead -> frmSur = Request.Form("sur&quot

The second this is that you're creating a variable named oRS which I think you were going to create a recordset with but you never create it. You don't need a recordset for the insert so you can get rid of the variable.

Lastly change this line
Set oRS = connObj.Execute(sql)
to this
connObj.Execute(strSql)
and close just the connection and destroy it
connObj.Close
Set connObj = nothing

Since you're not returning any records you don't need the recordset.

If you plan on making the second page a display page for all the records in the database then you will need the recordset.

LaPluma · Oct 5, 2002

Hello Roy29 and Snowboard

Many thanks for your replies and amendments!

When I click on page1.asp (which, again, contains the form which the visitor uses to insert data - a girl's Christian name and surname - into the database), I get the following message:

HTTP/1.1 405 Method not allowed

What is this error message? I have looked through different FAQs, including that here at Tek-Tips, and can't find it).

The code used in page1.asp is as follows:

<form name="form1" method="post" action="page2.asp">
<input type="text" name="first">
<input type="text" name="sur">
<input type="submit" name="Submit" value="Submit">
</form>

When I click on page2.asp, I get the following message:

Microsoft JET Database Engine error '80004005'

Field 'Table1.girlFirst' cannot be a zero-length string.

/stevehigham/db/page2.asp, line 25

The code for page2.asp is now as follows:

<%
Option Explicit

Dim ConnectionString, connObj, sql

Dim frmFirst, frmSur

frmFirst = Request.Form("first&quot

frmSur = Request.Form("sur&quot

ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("\stevehigham\db\testDB.mdb&quot

& ";" & _
"Persist Security Info=False"

Set ConnObj = Server.CreateObject("ADODB.Connection&quot

connObj.Open ConnectionString

sql = "select * from Table1"
sql = "INSERT INTO Table1 (girlFirst, girlSurname) VALUES (" & "'" & frmFirst & "','" & frmSur & "')"

connObj.Execute(sql)

connObj.Close

Set connObj = Nothing

%>

Please note that I have got rid of the Sqlstr variable because I couldn't see what it does.

I would be grateful for any suggestions.

LaPluma

snowboardr · Oct 5, 2002

Replace your pages with this code:

<form name="form1" method="post" action="page2.asp">
<input type="text" name="first">
<input type="text" name="sur">
<input type="submit" name="Submit" value="Submit">
</form>

<%
Option Explicit

Dim ConnectionString, connObj, sql

Dim frmFirst, frmSur

frmFirst = Request.Form("first&quot

frmSur = Request.Form("sur&quot

ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("\stevehigham\db\testDB.mdb&quot

& ";" & _
"Persist Security Info=False"

Set ConnObj = Server.CreateObject("ADODB.Connection&quot

connObj.Open ConnectionString
If frmFirst = "" then frmFirst = " "
sql = "select * from Table1"
sql = "INSERT INTO Table1 (girlFirst, girlSurname) VALUES (" & "'" & frmFirst & "','" & frmSur & "')"

connObj.Execute(sql)

connObj.Close

Set connObj = Nothing

%>
www.vzio.com
ASP WEB DEVELOPMENT

LaPluma · Oct 5, 2002

Hello Snowboard

Thanks for all your help.

I have finally managed to get it to work with the following:

<%
Option Explicit

Dim ConnectionString, connObj, sql, frmFirst, frmSur

frmFirst = Request.Form("first&quot

frmSur = Request.Form("sur&quot

ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("\stevehigham\db\testDB.mdb&quot

& ";" & _
"Persist Security Info=False"

Set ConnObj = Server.CreateObject("ADODB.Connection&quot

connObj.Open ConnectionString

sql = "select * from Table1"
sql = "INSERT INTO Table1 (girlFirst, girlSurname) VALUES ('" & frmFirst & "','" & frmSur & "')"

connObj.Execute(sql)

connObj.Close

Set connObj = Nothing

%>

The fields are for names so they require an extra ' and their are only two fields.

I can't see any of the records, though. How can I see the full list of records now - after the new addtions? Is that a lot of work?

Best wishes and thanks again for your help. I'm grateful.

LaPluma

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

The INSERT INTO statement

LaPluma

Programmer

snowboardr

Programmer

snowboardr

Programmer

LaPluma

Programmer

snowboardr

Programmer

LaPluma

Programmer

LaPluma

Programmer

snowboardr

Programmer

roy29

Instructor

LaPluma

Programmer

snowboardr

Programmer

LaPluma

Programmer

Similar threads

Part and Inventory Search

Sponsor