ASP automatic Insert

[nkoriginal]

I want an automatic insert.
I've an ASP page with more than 50 fields (form) and I want to an insert all that information in a table in SQL server 2005.

Now, I've statments for insert BUT I need to create en automatic insert, because sometimes I need to add new fields or remove some fields (from HTML form page), and I dont want to modify the insert statment every time I add a new field

I dont know if I explained my idea.

I really appreciate any help

Thank you

[jhardman]

the best way to do this is to make the form input names exactly like the db field names. Then instead of using an "Insert" statement, use "SELECT" and "addnew":

dim query, x
set objRS = server.creatObject("adodb.recordset")
query = "SELECT * FROM userTable"
objRS.open query, objConn
 
objRS.addNew
for each x in objRS.fields
   objRS(x) = request.form(x)
next
objRS.update

This just goes through each field of the db and looks for an input with that name to put in the db. x in this case is a string, so you could do multiple rows in the db at the same time with this method. For example if you had fields named name, address, and phNum, you could put inputs named name1, name2, name3 etc, then put the addnew and loop inside another loop like this:

dim query, x, y
set objRS = server.creatObject("adodb.recordset")
query = "SELECT * FROM userTable"
objRS.open query, objConn
 
for y = 0 to 3
   if name(y) <> "" then
      objRS.addNew
      for each x in objRS.fields
         objRS(x) = request.form(x&y)
      next
   end if
next
objRS.update

Does this work for you? I generally dislike using insert statements, I think "select" is more versatile.

Jared

[jhardman]

Now that I think about it, this might give you an error if there is a field in the db that doesn't come from the form (like an autonumber), so you should probably say:

      for each x in objRS.fields
         if request.form(x) <> "" then
            objRS(x) = request.form(x)
         end if
      next

Jared

[nkoriginal]

THANK YOU SO MUCH!! I really appreciate your help guys!

NIKO

[nkoriginal]

Hi again:

I tried to use this sentence but it's not working, I would like to know if you can help me with this code.

I created an asp page
then I created inside it a html form

and I put this code

<%
'set objRS = server.createObject("adodb.recordset")
'Set loConn = CreateObject("ADODB.Connection")

dim query, x

Set loConn = CreateObject("ADODB.Connection")
loConn.Open(MM_mackoul_STRING)

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.ActiveConnection = MM_mackoul_STRING
objRS.CursorType = 0
objRS.CursorLocation = 2
objRS.LockType = 3

query = "SELECT * FROM t1"
objRS.open query, loConn

objRS.addNew
for each x in objRS.fields
objRS(x) = request.form(x)
next
objRS.update
%>
But I've an error when I try to submit it. this is error:

Microsoft OLE DB Provider for SQL Server error '80004005'

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

Could you help me please?
Thank you

[nkoriginal]

Now that I think about it, this might give you an error if there is a field in the db that doesn't come from the form (like an autonumber), so you should probably say:

      for each x in objRS.fields
         if request.form(x) <> "" then
            objRS(x) = request.form(x)
         end if
      next

Jared

Hi again:

I tried to use this sentence but it's not working, I would like to know if you can help me with this code.

I created an asp page
then I created inside it a html form

and I put this code

<%
'set objRS = server.createObject("adodb.recordset")
'Set loConn = CreateObject("ADODB.Connection")
 
dim query, x
 
Set loConn = CreateObject("ADODB.Connection")
loConn.Open(MM_mackoul_STRING)
 
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.ActiveConnection = MM_mackoul_STRING
objRS.CursorType = 0
objRS.CursorLocation = 2
objRS.LockType = 3
 
query = "SELECT * FROM t1"
objRS.open query, loConn
 
objRS.addNew
for each x in objRS.fields
objRS(x) = request.form(x)
next
objRS.update
%>

But I've an error when I try to submit it. this is error:

Microsoft OLE DB Provider for SQL Server error '80004005'

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

Could you help me please?
Thank you

[jhardman]

what line gives you the error?

Jared

[nkoriginal]

what line gives you the error?

Jared

Thank Jared for answer again my question.

The error is this line

objRS(x) = request.form(x)

Do you know why?
Thank you

[jhardman]

Do you know why?

not really. the error is pretty vague. it is possible that the db is in a read-only mode (this line makes changes to the recordset, although I think if it is in read-only mode you get an error on the update line), so let's try making sure we have an edit mode. locktype should be 3 and sometimes it seems like I get errors unless I make the cursortype = 2. Try that and see if it works.

Jared

[jhardman]

if that doesn't work, you could also try:

for each x in objRS.fields
   objRS(x.name) = request.form(x.name)
next

This is a minor difference, and I think it is unlikely that it will help, but there's always a chance. Have you seen whether you can read from the db at this point? You should be able to read/edit/modify when you open the db in this way. Try, after the "for each" line (before the rest of the loop is executed)

response.write "updating "& x.name & "<br>" & vbNewLine

This should just print out "updating name" or whatever field names you have in your db.

Jared

[nkoriginal]

if that doesn't work, you could also try:

for each x in objRS.fields
   objRS(x.name) = request.form(x.name)
next

This is a minor difference, and I think it is unlikely that it will help, but there's always a chance. Have you seen whether you can read from the db at this point? You should be able to read/edit/modify when you open the db in this way. Try, after the "for each" line (before the rest of the loop is executed)

response.write "updating "& x.name & "<br>" & vbNewLine

This should just print out "updating name" or whatever field names you have in your db.

Jared

Jared, thank you for your help, I tried all you said but it didn't work.
I've the same error in the same line.


objRS(x) = request.form(x) or
objRS(x.name) = request.form(x.name)

I checked my database permission and everything is fine. It's not read-only.
I've many form in differents way, update, delete, insert and never has a similiar problem like this one.
I dont know what I can do..

Do u have another idea for help in this problem?
Maybe create an store procedure, but I dont know how to do it.

thank you
NIKO

[jhardman]

Try, after the "for each" line (before the rest of the loop is executed)

response.write "updating "& x.name & "<br>" & vbNewLine

This should just print out "updating name" or whatever field names you have in your db.

what about the second suggestion. Did you try that? This gives some indication of what the problem is, if this line doesn't give an error, than the db is reading just fine. If it loops through several time before you get an error, you know that only one of the fields is having problems. If it never works, then there is some problem connecting to the db at all.

Jared

[nkoriginal]

what about the second suggestion. Did you try that? This gives some indication of what the problem is, if this line doesn't give an error, than the db is reading just fine. If it loops through several time before you get an error, you know that only one of the fields is having problems. If it never works, then there is some problem connecting to the db at all.

Jared

Jared:

Yes I tried with the 2 alternatives.
When I put the response.write "updating& x.name &" .....

I received this message in the browser

updating id

Microsoft OLE DB Provider for SQL Server error '80004005'

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

/mackoul/beta/forms/borrar.asp, line 30


Now, this my complete code for a simple form with 2 fields

<!--#include virtual="/mackoul/beta/includes/connection.asp"-->
 
<% if request.Form("submit") = "submit" then%>
 
 
<%
 
            Set sshf = Server.CreateObject("ADODB.Recordset")
            sshf.ActiveConnection = MM_mackoul_STRING
            sshf.CursorType = 0
            sshf.CursorLocation = 2
            sshf.LockType = 3
            sshf.source = "select * from t1"
            'response.Write (sshf.source)            
            sshf.open
 
 
 
 
'     objRS.addNew
'     for each x in objRS.fields
'      response.write "updating "& x.name & "<br>" & vbNewLine
'           objRS(x.name) = request.form(x.name)
'      next
     sshf.addNew
     for each x in sshf.fields
      response.write "updating "& x.name & "<br>" & vbNewLine
         sshf(x.name) = request.form(x)
      next
 
      sshf.update
%>
 
 
<% else %>
 
Press submit!
 
<form action="borrar.asp" method="post" name="form">
name1<input name="name1" id="name1" type="text" size="10" maxlength="10"><bR>
name2<input name="name2" id="name2" type="text" size="10" maxlength="10"><bR>
<input name="submit" type="submit" value="submit">
</form>
<% end if %>
 

I've another form with 100 fields and I need this procedure for that form. I working with this one, because I think is more easy to work with 2 field than 100 hehe.

Thank you

[jhardman]

I'm just about out of ideas. This shows that the connection is definitely active. It displays one of the recordset's field names. I don't know. did you try cursortype=2?

Jared

[nkoriginal]

I'm just about out of ideas. This shows that the connection is definitely active. It displays one of the recordset's field names. I don't know. did you try cursortype=2?

Jared

Yes I tried with Cursotype = 2 But nothing.
I will try another way if I found something I will post a new message.

thank you Jared
NIKO

[nkoriginal]

Yes I tried with Cursotype = 2 But nothing.
I will try another way if I found something I will post a new message.

thank you Jared
NIKO

Jared:

I found the solution to my problem.
Thank you for your help.

Here is the complete code for insert X fields from HTML form in SQL table.


Dim rs, data_source
Set rs = Server.CreateObject("ADODB.Recordset")

rs.ActiveConnection = MM_mm_STRING
rs.CursorType = 2
rs.CursorLocation = 2
rs.LockType = 3
rs.source = "select * from t1"
response.Write (rs.source)
rs.open

rs.addNew
for each X in request.Form
if ucase(X) <> "SUBMIT" AND ucase(X) <> "UNIQUEID" and left(ucase(X),4) <> "DROP" and ucase(X) <> "OVERRIDE" then

Response.Write "<BR>"
Response.Write X & " = " & Request.Form( X ) & "<BR>"
rs(x) = request.form(x)
end if
next
rs.update

rs.close
set rs = nothing

If somebody has any problem with this code, please let me know

Thank you again Jared

NIKO

[jhardman]

Doh! Was the entire problem that the script was trying to update an autonumber? I should have seen that. I think I've even answered that question in the past. Anyway, I'm glad you found the sol'n.

Jared