asked on

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" %>



<html>

<head>
<title><% =App_Name %></title>
<script language="JavaScript" type="text/JavaScript">

</script>
</head>

<BODY <%=BodyTag%>>



<p align="right">  </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">   <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"> 
<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"> 
<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">      
<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
%>



</body>
</html>

rohanbairat3

Whatr are u doing with this form?? IS it submitting to itself ?

where is the insert part >??

johnson00

ASKER

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
%>

ASKER CERTIFIED SOLUTION

rohanbairat3

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

>>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.

SOLUTION

nurbek

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

davidlars99

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")

davidlars99

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

SOLUTION

fritz_the_blank

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

johnson00

ASKER

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

johnson00

ASKER

Correction: It's adding ONLY the blank record now.

nurbek

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

davidlars99

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

SOLUTION

davidlars99

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

davidlars99

sorry try this

CmdEditTest.Open "myTable", ConnPasswords, 2, 3

johnson00

ASKER

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

davidlars99

yeaeeeeeeee!!! :)

fritz_the_blank

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

davidlars99

OR

don't use SELECT statement at all like this

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

Anthony Perkins

Or:

Skip using a recordset in the first place and use Insert/Update/Delete statements instead.

fritz_the_blank

@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

davidlars99

no, it will just make reference to that table and won't keep any data in the recordset

fritz_the_blank

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>

Anthony Perkins

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

Anthony Perkins

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.

Anthony Perkins

My comments were posted in response to davidlars99's comments not fritz_the_blank's example.

Anthony Perkins

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.

davidlars99

@ ftb

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

Anthony Perkins

For the record, you cannot use adExecuteNoRecords with the recordset's Open method only with the Command object's Execute method.

Anthony Perkins

Which brings me back to the point of using an INSERT/UPDATE/DELETE SQL statement with adExecuteNoRecords <g>

fritz_the_blank

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

davidlars99

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! :)

Anthony Perkins

>>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.

davidlars99

yep!