• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 473
  • Last Modified:

rs.AddNew is adding my record to the db, and also adding a blank record each time

Please help.  I have changed and changed the recordset properties and can't get it to stop inserting a blank record for every new record I create.  Here's my code.  All suggestions are appreciated!

<%@ LANGUAGE="VBSCRIPT" %>

<!--#INCLUDE FILE="call_dataconn_config_inc.asp"-->

<html>

<head>
<title><% =App_Name %></title>
<script language="JavaScript" type="text/JavaScript">
<!--
function MM_goToURL() { //v3.0
  var i, args=MM_goToURL.arguments; document.MM_returnValue = false;
  for (i=0; i<(args.length-1); i+=2) eval(args[i]+".location='"+args[i+1]+"'");
}
//-->
</script>
</head>

<BODY <%=BodyTag%>>

<!--#INCLUDE FILE="header_inc.asp"-->

<p align="right">&nbsp; </p>
<form method="POST" action="Add_Test.asp">
  <div align="center"><center><table border="0" cellspacing="1" bgcolor="#ECECE6" width="90%">
    <tr>
      <td colspan="2" bgcolor="#FFFFFF">
        <p align="center"><font face="Arial">
<strong>Maintain Tests <br>
        </strong></font></p>
      </td>
    </tr>
    <tr>
      <td valign="top" align="right"><strong><font face="Arial">Test ID</font></strong> </td>
      <td valign="top"> &nbsp; <input name="Test_ID" type="text" id="Test_ID" size="30"></td>
    </tr>
    <tr>
      <td valign="top" align="right"><strong><font face="Arial">Test Name</font> </strong></td>
      <td valign="top"><font face="Arial">&nbsp;
        <input name="Test_Name" type="text" id="Test_Name" size="30">
      </font></td>
    </tr>
   

    <tr>
      <td valign="top" align="right"><strong><font face="Arial">Test Description </font></strong></td>
      <td valign="top"><font face="Arial">&nbsp;
        <input name="Test_Desc" type="text" id="Test_Desc" size="30">
      </font></td>
    </tr>
      <tr>
      <td colspan="2" bgcolor="#FFFFFF">
        <p align="center"><input type="submit" value="Add Record"> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
          <input name="Submit" type="submit" onClick="MM_goToURL('parent','default.asp');return document.MM_returnValue" value="Home">
        </p>
      </td>
    </tr>
  </table>
  </center></div>
</form>
<%
Set ConnPasswords = Server.CreateObject("ADODB.Connection")      
Set CmdEditTest = Server.CreateObject("ADODB.Recordset")


ConnPasswords.Open ConnectionString
      SQL = "SELECT * FROM TESTS"
      CmdEditTest.Open SQL, ConnPasswords, 0, 2
CmdEditTest.addnew


CmdEditTest("Test_ID")   = request("Test_ID")
CmdEditTest("Test_Name") = request("Test_Name")
CmdEditTest("Test_Desc") = request("Test_Desc")

CmdEditTest.update

CmdEditTest.close
set CmdEditTest = nothing
%>

<!--#INCLUDE FILE="footer_inc.asp"-->

</body>
</html>
0
johnson00
Asked:
johnson00
  • 11
  • 9
  • 5
  • +3
4 Solutions
 
rohanbairat3Commented:
Whatr are u doing with this form?? IS it submitting to itself ?

where is the insert part >??

0
 
johnson00Author Commented:
Yes, this form submits to itself.  I'm not using insert command, as I was told this was a bad programming choice, and to use rs.addnew instead.  Here is the portion of the code that does the addnew and update:

<%
Set ConnPasswords = Server.CreateObject("ADODB.Connection")    
Set CmdEditTest = Server.CreateObject("ADODB.Recordset")


ConnPasswords.Open ConnectionString
     SQL = "SELECT * FROM TESTS"
     CmdEditTest.Open SQL, ConnPasswords, 0, 2
CmdEditTest.addnew


CmdEditTest("Test_ID")   = request("Test_ID")
CmdEditTest("Test_Name") = request("Test_Name")
CmdEditTest("Test_Desc") = request("Test_Desc")

CmdEditTest.update

CmdEditTest.close
set CmdEditTest = nothing
%>
0
 
rohanbairat3Commented:
Hi,

WHen you load this form for the first time ..the ASP code that inserts the record is going to be executed ....so it adds an empty row ...since it gets no username and password...you have to check it its a post back.. you can do that by setting a hidden variable.


-rons
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Anthony PerkinsCommented:
>>I'm not using insert command, as I was told this was a bad programming choice<<
When you find that person again, tell them they don't have a clue.
0
 
nurbekCommented:
as rohanbairat3 mentioned

you need to check for the post back data , otherwise, it every time inserts  empty value


<%
If LCase(Request.ServerVariables("REQUEST_METHOD"))="post" Then
      Set ConnPasswords = Server.CreateObject("ADODB.Connection")    
      Set CmdEditTest = Server.CreateObject("ADODB.Recordset")
      
      
      ConnPasswords.Open ConnectionString
             SQL = "SELECT * FROM TESTS"
             CmdEditTest.Open SQL, ConnPasswords, 0, 2
      CmdEditTest.addnew
      
      
      CmdEditTest("Test_ID")   = request("Test_ID")
      CmdEditTest("Test_Name") = request("Test_Name")
      CmdEditTest("Test_Desc") = request("Test_Desc")
      
      CmdEditTest.update
      
      CmdEditTest.close
      set CmdEditTest = nothing

End If
%>
0
 
davidlars99Commented:
something here doesn't make sence... this is how you should be using AddNew method

Recordset.AddNew(field,value)

or

Recordset.AddNew("field1, field2, field3", "value1, value2, value3")
0
 
davidlars99Commented:
in your example you can try this

   ConnPasswords.Open ConnectionString
   CmdEditTest.Open "myTable", ConnPasswords, adOpenKeyset, adLockOptimistic
   CmdEditTest.addnew
        CmdEditTest.Fields("Test_ID")   = request("Test_ID")
        CmdEditTest.Fields("Test_Name") = request("Test_Name")
        CmdEditTest.Fields("Test_Desc") = request("Test_Desc")
   CmdEditTest.update
     
   CmdEditTest.close
   Set CmdEditTest = nothing
0
 
fritz_the_blankCommented:
I do it like this:

If Request.Form("submit") = "Add Record" then
      Set ConnPasswords = Server.CreateObject("ADODB.Connection")    
      Set CmdEditTest = Server.CreateObject("ADODB.Recordset")

      ConnPasswords.Open ConnectionString
      SQL = "SELECT * FROM TESTS WHERE 1=0"
      CmdEditTest.Open SQL, ConnPasswords, 0, 2
      CmdEditTest.addnew
            CmdEditTest("Test_ID")   = request("Test_ID")
            CmdEditTest("Test_Name") = request("Test_Name")
            CmdEditTest("Test_Desc") = request("Test_Desc")
      CmdEditTest.update
      CmdEditTest.close
      set CmdEditTest = nothing
      ConnPasswords.Close
      Set ConnPasswords = Nothing
end if


However, what I am doing with the if/ then is pretty much the same as nurbek suggested in http:#13950015 by using:

If LCase(Request.ServerVariables("REQUEST_METHOD"))="post" Then


Note however, the change the I made to the SQL Select:

SQL = "SELECT * FROM TESTS WHERE 1=0"

This way, you simply create an empty recordset rather than bringing the whole table over (a very, very bad thing to do).

Finally, a not about using the recordset object to update rather than using a literal string---using a recordset consumes slightly more resources than does executing a literal sql insert. However, it does offer the advantage of taking care of alll delimiters, illegal characters, troublesome apostrophes and quotation marks and so forth--especially troublesome with memo fields. It may in some cases prevent sql injection. As such, there is much to recommend this approach over using a literal insert.

The best of both worlds can be achieved by using the command object with parameters. That way you consume fewer resouces than using a recordset but you still maintain the advantages listed above:

strSQL = "Insert INTO tblTests (Test_ID,Test_Name,Test_Desc) VALUES(@Test_ID, @Test_Name,@Test_Desc)"

and then you would just add the parameters to the command object.

FtB
0
 
johnson00Author Commented:
Ok, I've changed my code to the following, and it's not entering the blank record anymore, but also not entering the input from the field:

If Request.Form("add new") = "Add New Test" then
     Set ConnPasswords = Server.CreateObject("ADODB.Connection")    
     Set CmdEditTest = Server.CreateObject("ADODB.Recordset")

     ConnPasswords.Open ConnectionString
     SQL = "SELECT * FROM TESTS"
       CmdEditTest.Open SQL, ConnPasswords, 0, 2
     CmdEditTest.addnew
          CmdEditTest("Test_ID")   = request("Test_ID")
          CmdEditTest("Test_Name") = request("Test_Name")
          CmdEditTest("Test_Desc") = request("Test_Desc")
     CmdEditTest.update
     CmdEditTest.close
     set CmdEditTest = nothing
     ConnPasswords.Close
     Set ConnPasswords = Nothing
end if

0
 
johnson00Author Commented:
Correction:  It's adding ONLY the blank record now.
0
 
nurbekCommented:
check the form element values

Test_ID =  request("Test_ID")
Response.Write (Test_ID)
'etc ...

If Request.Form("add new") = "Add New Test" then   ' is this form element correct?

maybe like this
If Request.Form("submit") = "Add New Test" then

0
 
davidlars99Commented:
can you try this? maybe you need to change recordset type

 ConnPasswords.Open ConnectionString
 CmdEditTest.Open "myTable", ConnPasswords, 1, 3  '// you had --> 0, 2
 CmdEditTest.addnew
        CmdEditTest.Fields("Test_ID").Value   = request.form("Test_ID")
        CmdEditTest.Fields("Test_Name").Value = request.form("Test_Name")
        CmdEditTest.Fields("Test_Desc").Value = request.fom("Test_Desc")
 CmdEditTest.update
     
 CmdEditTest.close
 Set CmdEditTest = nothing


http://www.w3schools.com/ado/met_rs_open.asp#CursorTypeEnum

[CursorTypeEnum Values]
      Const adOpenUnspecified=-1
      Const adOpenForwardOnly=0
      Const adOpenKeyset=1
      Const adOpenDynamic=2
      Const adOpenStatic=3
      
[LockTypeEnum Values]
      Const adLockUnspecified=-1
      Const adLockReadOnly=1
      Const adLockPessimistic=2
      Const adLockOptimistic=3
      Const adLockBatchOptimistic=4
0
 
davidlars99Commented:
and do it in this fashion

CmdEditTest.Fields("Test_ID").Value   = request.form("Test_ID")
0
 
davidlars99Commented:
sorry try this

CmdEditTest.Open "myTable", ConnPasswords, 2, 3
0
 
johnson00Author Commented:
It works it works it works!  Here's the final code:

<%
If request.form("mybutton") = "Add Record" Then
     Set ConnPasswords = Server.CreateObject("ADODB.Connection")    
     Set CmdEditTest = Server.CreateObject("ADODB.Recordset")

     ConnPasswords.Open ConnectionString
     SQL = "SELECT * FROM TESTS"
       CmdEditTest.Open SQL, ConnPasswords, 2, 3
     CmdEditTest.addnew
          CmdEditTest.Fields("Test_ID").Value   = request.form("Test_ID")
              CmdEditTest.Fields("Test_Name").Value = request.form("Test_Name")
          CmdEditTest.Fields("Test_Desc").Value = request.form("Test_Desc")
     CmdEditTest.update
     CmdEditTest.close
     set CmdEditTest = nothing
     ConnPasswords.Close
     Set ConnPasswords = Nothing
end if
0
 
davidlars99Commented:
yeaeeeeeeee!!!  :)
0
 
fritz_the_blankCommented:
I am glad that you have this sorted out. Please change this line:

 SQL = "SELECT * FROM TESTS"

to this:

 SQL = "SELECT * FROM TESTS WHERE 1=0"

it will be much more efficient especially as your table grows.

FtB
0
 
davidlars99Commented:
OR

don't use SELECT statement at all like this

CmdEditTest.Open "tableName", ConnPasswords, 2, 3
0
 
Anthony PerkinsCommented:
Or:

Skip using a recordset in the first place and use Insert/Update/Delete statements instead.
0
 
fritz_the_blankCommented:
@davidlars99 --

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
don't use SELECT statement at all like this

CmdEditTest.Open "tableName", ConnPasswords, 2, 3
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

My understanding is that will bring over the whole table--the same as a SELECT without a WHERE clause.

FtB
0
 
davidlars99Commented:
no, it will just make reference to that table and won't keep any data in the recordset
0
 
fritz_the_blankCommented:
Not to be contrary, but I just ran this code below against the Northwind.mdb and it returned all records:

<%@ Language = VBScript %>
<%Option Explicit%>
<%Response.Buffer = True%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
<%
dim  strDataPath, strConnectString, objConnection, objRS

'set connection strings for entire application
strDataPath = server.MapPath("NorthWind.mdb")
strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;"_
                  + " Data Source= " & strDataPath & ";"_
                  + " Mode=Share Deny None;User Id=admin;PASSWORD=;"
                  
if not IsObject("ojbConnection") then
      set objConnection=Server.CreateObject("ADODB.Connection")
      objConnection.ConnectionTimeout = 15
      objConnection.CommandTimeout =  10
      objConnection.Mode = 3 'adModeReadWrite
      if objConnection.state = 0 then
            objConnection.Open strConnectString
      end if
end if

set objRS=Server.CreateObject("ADODB.RecordSet")
objRS.Open "Customers",objConnection,3,3
%>
</HEAD>
<BODY>
<table>
<%
do while not objRS.EOF
      Response.write(objRS("Country") & "<br>")
      objRS.MoveNext()
loop
%>
</table>
</BODY>
<%
objRS.Close
set objRs = Nothing
objConnection.Close
set objConnection = Nothing
%>
</HTML>
0
 
Anthony PerkinsCommented:
If you are going to do that (not that I would recommend it) than at least add the option as in:

CmdEditTest.Open "tableName", ConnPasswords, 2, 3, 512
0
 
Anthony PerkinsCommented:
For the record (and completeness):

adCmdTable 2 Evaluates CommandText as a table name whose columns are all returned by an internally generated SQL query.

adCmdTableDirect 512 Evaluates CommandText as a table name whose columns are all returned. Used with Recordset.Open or Requery only.
 
0
 
Anthony PerkinsCommented:
My comments were posted in response to davidlars99's comments not fritz_the_blank's example.
0
 
Anthony PerkinsCommented:
By defintion both adCmdTable and adCmdTableDirect (when supported by the provider) return all the rows in the table.  The difference is that adCmdTable is just basically "Select * From " prefixing the recordset's Source property.
0
 
davidlars99Commented:
@ ftb

try like this --> CmdEditTest.Open "tableName", ConnPasswords, 2, 3, 128



0
 
Anthony PerkinsCommented:
For the record, you cannot use adExecuteNoRecords with the recordset's Open method only with the Command object's Execute method.
0
 
Anthony PerkinsCommented:
Which brings me back to the point of using an INSERT/UPDATE/DELETE SQL statement with adExecuteNoRecords <g>
0
 
fritz_the_blankCommented:
As ACPerkins indicated, no dice:

ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.


@ACPerkins:

I did suggest what you say above! http:#13954064

Once you showed me how do to do that properly, I have been using it much more often in my coding.

FtB
0
 
davidlars99Commented:
perkins, explaining the specifics of the recordset will require you a lot of writting  :^) would be lot easier if could just provide a link --> http://www.w3schools.com/ado/met_rs_open.asp

I have never used recodset in that fashion anyway, I just blury remembered some basics and thanks to everyone for refreshing my mind. I do agree with everyone though!  :)
0
 
Anthony PerkinsCommented:
>>perkins, explaining the specifics of the recordset will require you a lot of writting <<
No need, I don't use recordsets.

>>would be lot easier if could just provide a link <<
But your link does confirm:
adExecuteNoRecords 128 The command text is a command or stored procedure that does not return rows. If any rows are retrieved, they are discarded and not returned. adExecuteNoRecords can only be passed as an optional parameter to the Command or Connection Execute method.

>>I have never used recodset in that fashion anyway<<
Same here.
0
 
davidlars99Commented:
yep!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 11
  • 9
  • 5
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now