Post Form is acting odd...

[BPetro]

Is there some odd rule I'm not aware of? I have a form with people-type information. FirstName, LastName, Email, DesiredPassword. If I fill in all the fields, it gets inserted into the database and fields are filled in. If I leave a field blank, for instance the Email field - then a blank row is inserted into the database, but all fields appear blank! Also the modify page doesn't seem to ever work to fix those blank fields. Is there something about initializing the 'blank' fields that needs to be done just right to avoid this sort of problem? I'd appreciate any pointers you can pass along on this...

The forms look pretty standard - on the AddItem, its:
INPUT TYPE="text" NAME="Name" VALUE=" " SIZE=50 MAXLENGTH=50

The updates are pretty standard - addnew, update, set fields, update again.
RSItem("Name&quot = Request.Form("Name&quot

 

[onpnt]

Hi there,
Personally it's a bad idea to let a blank value be inserted into a DB. It takes up resources that don't nee to be taken up and in the long run makes he** for otehr issues you may want to do with the information. Why not simple add some validation to the form to not allow the alocation of empty fields by the user. A language that doesn't affect the way you think about programming is not worth knowing.

http://www.onpntwebdesigns.com

admin@onpntwebdesigns.com

 

[BPetro]

That sounds like a good approach for some things but there are a few valid empty fields such as: NickName, SecondAddressLine, as well as optional fields like HomePhone... In this case someone didn't have an email address. So, am I hearing that if a field is blank avoid writing it to the database? Am I hearing that I can check it with the isEmpty?? If not, how do I check it and avoid it? Are you proposing (and more importantly, do you or others successfully use) something like the following for each and every field in the form?
if not isEmpty( Request.Form("EmpID&quot) then
RSItem("EmpID&quot = Request.Form("EmpID&quot
end if

Thanks again!

 

[RISTMO]

In javascript, you can do this:

if(document.EmpId.RSItem=="&quot{
//if necessary
alert("You Must give a value"
//if not
document.EmpId.RSItem.value="N/A";
}

Rick

 

[onpnt]

Seeing as there are optional fields then yes inserting the blank fields is ok. I've done it for many applications in the past but the point I just wanted to say is it is not efficient. I would not think doing what you said would be a good idea. if you are inserting data from a user the row should be inserted and you will need the empty field to keep your data consistant and uniform for that users entry.

I'm confused on what you mean by when you insert a empty field then all the fields end up empty.
can you explain a little more off this diagram of a table you would be inserting into
name email telephone
joe joe@blah.com 222-2222

now if I insert just name email you should simply recieve this in the DB
name email telephone
joe joe@blah.com

how is your's ending up with the final insert?
A language that doesn't affect the way you think about programming is not worth knowing.

http://www.onpntwebdesigns.com

admin@onpntwebdesigns.com

 

[RISTMO]

In javascript, you can do this (many people do this for validating their forms):

if(document.EmpId.RSItem=="&quot{
//if necessary
alert("You Must give a value"
//if not
document.EmpId.RSItem.value="N/A";
}

Rick

 

[RISTMO]

Hmm. That DID post the first time and "someone" was posting right after me... Hmm.

Rick

 

[BPetro]

Reply to onpnt - thanks for asking clarification ... That's what is SO very odd - I expect what you describe, but if I fill in the name and all fields but leave the email field blank, then a row is inserted, it gets the autoID (PS, this is MSAccess db). But other than the auto ID# that Access generates in the row - nothing else - no other field has any values. In addition, if I then use the modify page (key=Access Auto key) to try again to fill in the missing fields, they refuse to be filled in, they stay blank. But finally if I give up on that row, and try add again, but put a value, any value in every field including the email, then the row is added correctly and can be modified correctly. That's what has me totally stumped. My only guess is that something about a blank form field must return something other than "" (a string) as its value???? But even so, why form-field #3 would affect field #1 and #2 flies against any logic I can imagine on this one. ANY IDEAS?

 

[BPetro]

Wait a minute - I know how a field can affect all fields, but I don't know why its happening. In the add I do
RS.addnew
RS.Update
RS("AField&quot=value
RS("AField2&quot=value2
RS.Update

That's what I was shown how to do. Point is, if the first update works, and the second one fails for any reason, this is what we are seeing. A row with no values except the auto-counter.

Now, why is the second one failing? Something about the values being put in the fields causes the update to fail - sound right?

 

[onpnt]

was the two RS.update a mistake (typo) there shouldn't be two, only after the allocation of the values

I would simply do what rick suggested or go to a insert SQL statement to insert the values. I've never been a fan of the addnew methods etc. due to them doing some interesting things sometimes. A language that doesn't affect the way you think about programming is not worth knowing.

http://www.onpntwebdesigns.com

admin@onpntwebdesigns.com

 

[BPetro]

Ahhh... could be I'm seeing some of the interesting things as we speak. I'll look at doing that.

Thanks!

 

[BPetro]

onpnt

The two RS.Updates were done on purpose because that appeared to be the way its documented. I tried pulling the first one out and indeed the record is not added when that is gone. If it is existing both places the record is added. Hence I think the double update is prob. required.

I went to the RS type updates because I didn't see any quick answers on formatting non-string fields for the SQL. Can you give me a quick lesson on that? My assumptions for formatting SQL for MSAccess are the following:
1. All fields in a FORM come back as strings.
2. To insert / update fields I need the SQL to do something like the following formatting in these clips:
set date = #12/31/2002#
set string = "String Characters"
set integer = 1 (ie: String character no quotes)
set boolean = ?? (No guess, perhaps "Yes" "No&quot

The above is the final SQL String. I'm aware that I need to perform housekeeping to get it such as "#" & myVar & "#".
How does all that sound? Any suggestions or corrections?

 

[onpnt]

Here's a very down to basic way of using the AddNew which you are. What we'll do is add email and date
This is a actual script I use in a simple form of mine so I added a little error handling in there for the email to validate a empty field, I've cut it down tot he email and date for this example though
<%
Response.Buffer = true
dim cnn,rs
set cnn = Server.CreateObject(&quot;ADODB.Connection&quot
set rs = Server.CreateObject(&quot;ADODB.RecordSet&quot
cnn.Open &quot;driver={Microsoft Access Driver (*.mdb)};;DBQ=&quot; & Server.MapPath(&quot;dbase\UserInfo.mdb&quot
sqltext = &quot;SELECT * FROM userInfo&quot;
rs.Open sqltext,cnn,3,3 'open the connection to the database

dim addr, day, error
addr = Request.Form(&quot;EmailAddr&quot
day = Request.Form(&quot;Day&quot

if addr = &quot;&quot; then
error = &quot;You have not entered an email address.&quot;
Response.Write error
Response.End
end if
'If we pass through validation then store the information in the db
rs.AddNew
rs(&quot;EmailAddr&quot = addr
rs(&quot;Date&quot = day
rs.update
Response.Redirect &quot;thank.asp&quot;
%>

now I'll show you the same functionality with a simple sql insert statement, which makes things much easier and more efficient.






I've left out the validation.
<%
Dim cnn, sqltext,day, addr
addr = Request.Form(&quot;EmailAddr&quot
day = Request.Form(&quot;Day&quot
set cnn = Server.CreateObject(&quot;ADODB.Connection&quot
cnn.Open &quot;driver={Microsoft Access Driver (*.mdb)};;DBQ=&quot; & Server.MapPath(&quot;dbase\UserInfo.mdb&quot

sqltext = &quot;INSERT INTO userInfo (EmailAddr,Date)&quot;
sqltext = sqltext & &quot; VALUES ('&quot; & addr & &quot;', # &quot; & day & &quot;# )&quot;
cnn.execute(sqltext)
cnn.close
set cnn = nothing

that's it
the main things you need to remember here is the date delimiter around date values, '&quot; &quot;' surounding strings and &quot; &quot; surounding integers
so if I thru a number in there it would look like this
sqltext = &quot;INSERT INTO userInfo (EmailAddr,Date,Age)&quot;
sqltext = sqltext & &quot; VALUES ('&quot; & addr & &quot;', # &quot; & day & &quot;#, &quot; & age & &quot; )&quot;

here's some a basic tutorial you can try out and see how things work.

http://stardeveloper.com:8080/asp_insform_1.asp

sorry if there's some minor typos in there
hope this helps out
A language that doesn't affect the way you think about programming is not worth knowing.

http://www.onpntwebdesigns.com

admin@onpntwebdesigns.com

 

[onpnt]

just a note
you will get much more help if you refer to the asp forum A language that doesn't affect the way you think about programming is not worth knowing.

http://www.onpntwebdesigns.com

admin@onpntwebdesigns.com

 

[BPetro]

Dang! I missed seeing that ASP forum. Looked for it, just missed it.

Thanks. One more question. If you can answer I'd appreciate it. On the Server site where this group has their site, they are given a DSN for the database which we have to use. One thing I've noticed is that we MUST open with locking in order to do any write operation. I don't notice any locking you did in your example. Am I missing it or did you leave that part out? Here's how I do the open to get locking for the inserts... the adOpenStatic, and adLockOptimistic are just standard defines from adoConsts.inc. I've found w/o the lock spec. nothing is going to happen on this site.

Set conn = Server.CreateObject( &quot;ADODB.Connection&quot; )
conn.Open &quot;OUR_DSN_NAME&quot;
Set RSItem = Server.CreateObject(&quot;ADODB.Recordset&quot
RSItem.Open Session(&quot;TableName&quot, conn, adOpenStatic,
adLockOptimistic
RSItem.AddNew
RSItem.Update
RSItem(&quot;ItemNumber&quot = Request.Form(&quot;ItemNumber&quot
RSItem(&quot;Details&quot = Request.Form(&quot;Details&quot
RSItem(&quot;DisplayDate&quot = Request.Form(&quot;DisplayDate&quot
RSItem.Update

THANKS!

 

[onpnt]

every example I gave above was a DSN-Less connection and that is why you are not seeing it. I personaly don't like to use DSN connections but that is just a preference. What you have is correct above.
(that extra update still jumps out at me though.)

in this line I am simply using the number value instead of the constant name for the adOpenStatic and adLockOptimistic

rs.Open sqltext,cnn,3,3

you can refer to this FAQ for more info on the Cursor types and locations
faq333-618 A language that doesn't affect the way you think about programming is not worth knowing.

http://www.onpntwebdesigns.com

admin@onpntwebdesigns.com

 

[BPetro]

Actually yes, I saw that one, but it wasn't in the other (non-RecordSet) example - my comments weren't clear about that. I understood what the 3,3 was, but it wasn't in the second example which of course does NOT do a RS.open at all - hence my quandry how to do locking. ** However, if what I'm hearing about DSN-less is available for us (IE: we aren't locked out by our provider from doing DSN-less connections) and if what I think you were saying &quot;Locking isn't needed in DSN-Less connections&quot; - then as I migrate toward this, I'll be fine.

I'll try doing a DSN-less connection (if I can figure out the path for the database - since I have no idea where on the NT Box our web directories are located - I'm hoping your above reference to Server.MapPath(&quot;dbase\UserInfo.mdb&quot will solve that little detail). If all that works, I'll move on into that direction. If you want to reply, to clarify anything I'll leave my email notices turned on.

Thanks for All!
Bruce.