RE: Updating Access DB from .asp Form (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

Long Island Lune -> RE: Updating Access DB from .asp Form (10/28/2003 12:41:54)

quote:

Page 3 has the following statement...
UPDATE DrvTable
SET DrvPswd=('::iDrvPswd::'),
DrvName=('::iDrvName::'),
DrvBio=('::iDrvBio::'),
DrvAward=(::iDrvAward::),
DrvPic=('::iDrvPic::')
WHERE ID=('::iID::')

Fantastic. But there is only one thing.... Now that you have a numeric field to refer too, you have to change the way numeric fields are inturpretted. VERY EASY.

WHERE ID= ::ID::

Text fields and Memos:
'::xxxx::'
Numerics:
::xxxx::

Like in:

UPDATE DrvTable SET DrvPswd=('::iDrvPswd::'), DrvName= ('::iDrvName::'), DrvBio=('::iDrvBio::'), DrvAward= ('::iDrvAward::'), DrvPic=('::iDrvPic::') WHERE ID=(::iID::)

Do you like brackets better? You can do it with or without. Make no difference.

UPDATE DrvTable Set (DrvNum= ‘::DrvNum::’, DrvPswd= ‘::DrvPswd::’, DrvBio= ‘::DrvBio::’, DrvAward= ‘::DrvAward::’, DrvPic= ‘::DrvPic::’ WHERE (ID = ::iID::)

Plus you were missing ' and ' around iDrvAward.

In the future, you can keep your form field names the same as your database table fields if that is easier.

ie: WHERE (ID = ::ID::)
This applies to any db and tbl fields. AGAIN, this doesn't really matter. You can leave it the way it is.

We're almost there!!! [:)]



D_Sutter -> RE: Updating Access DB from .asp Form (10/28/2003 16:30:30)

I have made the changes above...

Here is the statement now:

UPDATE DrvTable
SET DrvPswd='::DrvPswd::',
DrvName='::DrvName::',
DrvBio='::DrvBio::',
DrvAward='::DrvAward::',
DrvPic='::DrvPic::'
WHERE (ID=::ID::)

As you can see, I changed the names of my form fields to be the same as the database fields.

[X(] I am still getting that stupd message...



Long Island Lune -> RE: Updating Access DB from .asp Form (10/28/2003 17:21:33)

Page two is pulling in the value for ID and transferring it to page 3 right?

We need to see what is coming in to page 3.
Place this (monitoring) ASP code on TOP of page 3 above the <html> tag:

<%
a = request.form ("DrvPswd")
b= request.form ("DrvName")
c= request.form ("DrvBio")
d= request.form ("DrvAward")
e= request.form ("DrvPic")
f= request.form ("ID")
response.write ("<BR>DrvPswd= " & a & "<BR>DrvName= " & b & "<BR>DrvBio= " & c & "<BR>DrvAward= " & d & "<BR>DrvPic= " & e & "<BR>ID =" & f)
%>

Now when you make it to page 3, you'll see the values that are being passed from page 2 to 3 on top of the page. Make sure each field is filled with something and that the info is correct.

Are you testing live on your site, or are you testing locally?
Maybe you better send me the error message again.



D_Sutter -> RE: Updating Access DB from .asp Form (10/28/2003 19:55:58)

I put those lines of code into page 3 and everything looked like it should. All the information I entered was transfered to that page.

The error message is...

Database Results Wizard Error
The operation failed. If this continues, please contact your server administrator.

I am testing it on the live site.



Spooky -> RE: Updating Access DB from .asp Form (10/28/2003 20:40:59)

There are many things that can cause that error - can you try FP > tools > web settings > database > verify connection?



D_Sutter -> RE: Updating Access DB from .asp Form (10/28/2003 21:41:49)

Everything verifies fine.



Long Island Lune -> RE: Updating Access DB from .asp Form (10/28/2003 21:46:45)

Ahh.... You posted before I finished.

Did you import the database in, or create it inside the FP environment?



Long Island Lune -> RE: Updating Access DB from .asp Form (10/28/2003 21:47:52)

Is page 3 saved as an ASP page with an extension of .asp?



Long Island Lune -> RE: Updating Access DB from .asp Form (10/28/2003 22:46:17)

quote:

Database Results Wizard Error
The operation failed. If this continues, please contact your server administrator.


When you put the query in the Qizard Dialog Box, did it verify correctly?



D_Sutter -> RE: Updating Access DB from .asp Form (10/29/2003 6:11:51)

I imported the database in after creating it in Access (office XP).

Page 3 is a ASP page.

The query verifies fine.

The only other thing that I can think of the help you find my problems is that I am in a sub-web. I have added all the database information to the main global.asa file (not in sub-web) and put my database in the main webs fpdb subdirectory. It obviously finds the database and information, but I wanted to let you know just in case there was something about it updating from there.



Long Island Lune -> RE: Updating Access DB from .asp Form (10/29/2003 12:51:28)

D,

I think you might be on to something. I've seen people have sub-web problems on the forum before. I don't touch them myself. So your database is in the the main but your pages are in the sub? Hmmm...

Maybe it might be wise to diet the DRW on page 3. Then you would have more control over the code. In the DRW code there is a line like this:

fp_sDataConn="DriverDBX"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0

I don't know if this would work but you could try changing that to:

fp_sDataConn="../fpdb/DriverDBX"

Don't bank on it. [&:]


To see IF all the work we did in this thread "does" work, you should remove the pages out of the subweb temporarily (or just make a copy) and put them in the main web, test the code to make sure it UPDATES your database correctly, then put it back KNOWING that it works and post another question on subwebs and their connections to main web databases.

That's what I would do. I would want to know that the code works before attempting to figure a solution to a new problem. (subwebbing)



D_Sutter -> RE: Updating Access DB from .asp Form (10/29/2003 13:17:19)

[:)] OK! [:)]

Putting the pages in the main web and then trying it WORKED! Thank you very much for your help. I tried changing the information on page 3 as you said above and it still didn't work.

I did have one question though...
When it updated, it stayed on the asp (3) page and said "no records returned". Is there a way to not display this or display a page to let the user know the information has been updated?

I will continue the questions on the sub-web forum.

Thanks again!



Long Island Lune -> RE: Updating Access DB from .asp Form (10/29/2003 13:51:23)

I'm glad it worked. I thought I was going crazy there for a moment.

Question:
Once completed , the DRW will keep you on the same page. But it is hidden from public view. Many posts back -when I was showing you the method for using the DRW, I gave you these instructions:


1): Double-click your yellow Database Region on your page.
2): Click "Next" once. This will put you on page "Step 2 of 5".
3): Click "Custom Query".
4): Click "Edit" beneath "Custom Query".
5): Copy the query I provided you above.
6): Insert it in place of the query showing in the dialog box.
7): Click "Verify Query".
8): Click "OK".
9): Click "Next" until you get to page "Step 4 of 5".
10): Select "Table - one record per row".
11): Make sure NONE of the checkboxes are checked beneath.
12): Click "Next".
13): On page "Step 5 of 5", click "Display all records together".
14): On page "Step 5 of 5", unclick "Add search form".
15): Click "Finish".

Those actions (in RED above) were to HIDE the DRW from view on the page. On the page, (normally) you would put text for your visitor... some wording like: "Your account has been updated. click xxx below to go back to..." near the bottom of the page or something. That's what I do.

To HIDE the message that appears:

1): Double click your yellow DRW area.
2): Advance through to page: "STEP 3 of 5".
3): Click on the "MORE OPTIONS" button.
4): In the WHITE MESSAGE Field, REMOVE that line.
5): Proceed on to the end of the DRW STEPS.

Now the message won't appear each time an UPDATE has occurred.

[;)]



D_Sutter -> RE: Updating Access DB from .asp Form (10/30/2003 9:12:34)

Two final questions...

What does the query look like when you just want to add a record to the database?

Can you retrieve the system date and put it into a form field?

Thanks.



Long Island Lune -> RE: Updating Access DB from .asp Form (10/30/2003 12:53:20)

Question 1: Adding a Record:
INSERT INTO DrvTable (DrvNum , DrvPswd, DrvBio, DrvAward, DrvPic) VALUES (‘::DrvNum::’, ‘::DrvPswd::’, ‘::DrvBio::’, ‘::DrvAward::’, ‘::DrvPic::’)

Question 2: Date in form-field
Sure. You would create another edit field in your form. You know how to do that right? Once you have the new field in your form - do this:

1): Double-click the new form field.
2): In the "Name" field give it a name: (ie: myDate).
3): In the "Initial Value" field place this: <%=Date()%>
4): Click OK.

Now the date will appear in your form field. And it will also be POSTED to page 3 if you should ever need to save it for some reason.



Long Island Lune -> RE: Updating Access DB from .asp Form (10/30/2003 12:55:52)

One thing I forgot to mention:

When naming fields in your forms or database (for date types) - do not name your field Date. It is a reserved word in MS-ACCESS. That's why I gave you the example name"myDate" above.



D_Sutter -> RE: Updating Access DB from .asp Form (10/30/2003 14:08:43)

That does the trick...

Is there anyway to make the date in MM/DD/YYYY format?

Don



Long Island Lune -> RE: Updating Access DB from .asp Form (10/30/2003 14:16:58)

Do you mean something like this:

Full Day and Date:
<%=FormatDateTime(Date(), 1)%>

Or just the 4-digit year?



D_Sutter -> RE: Updating Access DB from .asp Form (10/30/2003 14:18:48)

I just want it to say...

10/30/2003

Don



Long Island Lune -> RE: Updating Access DB from .asp Form (10/30/2003 14:34:45)

Don - try this:

<%=Month(Now)&"/"&Day(Now)&"/"&Year(Now) %>



D_Sutter -> RE: Updating Access DB from .asp Form (10/30/2003 14:37:13)

Works great! Thank you for all your help.

Don



Long Island Lune -> RE: Updating Access DB from .asp Form (10/30/2003 14:39:28)

Your Welcome.
CHEERS [:)]



D_Sutter -> RE: Updating Access DB from .asp Form (10/31/2003 9:53:13)

I did have another question...

Are the codings that you are giving me just html? I was wondering where I could get some information on some of these things...like date and formating output.

I am looking for a way to convert a number stored in my database (date in YYYYMMDD format) to display in a MM/DD/YYYY format.



Long Island Lune -> RE: Updating Access DB from .asp Form (10/31/2003 12:43:15)

I used .ASP and VBScript in this thread. ASP was the code launching pad, but VBScript did most of the work. The date code I gave you was VBscript inside ASP. The DRW was a combination of HTML and ASP.

.ASP:
http://www.devguru.com/Technologies/asp/quickref/asp_list.html

VBScript:
http://www.devguru.com/Technologies/vbscript/quickref/vbscript_list.html

For your latest task, VBScript will be the one to use.
You want to convert UK dates to US?

Here's some poop on this but it will probably confuse you more than help:
http://www.aspfaq.com/show.asp?id=2260

Are ALL the dates people enter in your form in UK format?



D_Sutter -> RE: Updating Access DB from .asp Form (10/31/2003 12:47:59)

I have all the dates going in as YYYYMMDD.

I just wanted to display them as MM/DD/YYYY.

Thank you for the links.

Don



Long Island Lune -> RE: Updating Access DB from .asp Form (10/31/2003 12:48:07)

Maybe something like this:

myDate = year(Now) & left("00",2-len(month(now))) & month(Now) & left("00",2-len(day(Now))) & day(dt)

BUT..... This is assuming that ALL dates incoming are in UK format.



Long Island Lune -> RE: Updating Access DB from .asp Form (10/31/2003 12:50:14)

You want to DISPLAY them? In your DRW display page, or in the form? Where?



D_Sutter -> RE: Updating Access DB from .asp Form (10/31/2003 12:53:11)

It reads the date out of the database in format YYYYMMDD. I just want it to be displayed in the DRW table as MM/DD/YYYY.

Where would I put a statement "myDate = " at? Where in the .asp page?



Long Island Lune -> RE: Updating Access DB from .asp Form (10/31/2003 13:07:35)

Correction:
The following code WILL take a yyyy/mm/dd format and convert it to a mm/dd/yyy format for you:

With / forward slashes embedded:
= mid (mtDate, 6, 2) & "/" & right (mtDate, 2) & "/" & left(mtDate, 4)

Without / forward slashes embedded:
= mid (mtDate, 6, 2) & right (mtDate, 2) & left(mtDate, 4)


I just tested it. It works. Disregard the first code I gave you 2-posts back.

This code will have to be placed in the DRW code by hand. Post your display code. Or you would have to go into your DRW code yourself, find the date display line and replace it with the code I just gave you. Just make sure to change "mtDate" in my code with the field from your database. If you can't figure it out, post the DRW display code.



D_Sutter -> RE: Updating Access DB from .asp Form (10/31/2003 14:20:06)

Here is the DRW section...

I have changed the field like you posted and all I get is "//" for the date.

<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM NewsTable ORDER BY nwDate DESC"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="news"
fp_iMaxRecords=10
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice="nwDate"
fp_sMenuValue="nwDate"
fp_sColTypes="&nwID=3&nwDate=3&nwNews=203&nwNum=202&nwDrv1=202&nwDrv2=202&nwDrv3=202&nwDrv4=202&nwDrv5=202&nwDrv6=202&"
fp_iDisplayCols=2
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan --><table BORDER="0">
<tr>
<td valign="top"><font face="Lucida Sans" size="2">
<%=mid(nwDate, 6, 2) & "/" & right(nwDate, 2) & "/" & left(nwDate, 4)%></font></td>
<td><font face="Lucida Sans" size="2">
<%=FP_FieldVal(fp_rs,"nwNews")%></font></td>
</tr>
</table>
<hr>
<!--webbot bot="DatabaseRegionEnd" b-tableformat="FALSE" b-menuformat="FALSE" u-dbrgn2="_fpclass/fpdbrgn2.inc" i-groupsize="0" clientside tag="BODY" preview="<table border=0 width="100%"><tr><td bgcolor="#FFFF00"><font color="#000000">This is the end of a Database Results region.</font></td></tr></table>" startspan --><!--#include file="_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" endspan --></td>



Page: <<   < prev  1 [2] 3   next >   >>

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.125