I'm confused about a Custom Query in Databse Results Wizard. Extremely Urgent.

Hello Experts,

I have gone through the White Paper from Microsoft KB288328 and created a website that can upload, send e-mail and write tto a database simutaneously. I have looked through several questions here as well but still have a problem.

While all seems to be working fine, (I receive an e-mail the file uploads and the Upload sucess message shows from process2.asp) when I look in the Database table Results I do not get the data placed into the table but rather the field names. I am using the following for a custom query in process2.asp

INSERT INTO Results ([FullName], [Email], [PhoneNumber], [Advertiser], [IssueDate], [AdSize], [AdShape], [File]) VALUES ('FullName','Email','PhoneNumber','Advertiser','IssueDate','AdSize','AdShape','File')

I am really confused as to what I am doing wrong. Can someone please help me I have been stuggling with this for a couple of weeks and really need to get this done.

Thank you in advance.

msc
mcomensAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

barretttCommented:
The values should be parameters it looks like to me you are specifically adding 'FullName' .... Try changing one of these to 'testparameter1' and see if that ends up in the database. Are you using .html, .asp, .aspx, vb.net, scripts? Also can you post the code that surrounds that database statement, and what type of database SQL, Access, other?
Thanks,
0
mcomensAuthor Commented:
Data from a form get written to a text file .csv then inserted to the database through .asp page with the custom query.

Using .asp to Access database from Frontpage 2003 which creates a webot.

Below is the code for the webot.

<!--webbot bot="DatabaseRegionStart" s-columnnames s-columntypes s-dataconnection="Form" b-tableformat="FALSE" b-menuformat="FALSE" s-menuchoice s-menuvalue b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="FALSE" b-listseparator="FALSE" i-listformat="0" b-makeform="FALSE" s-recordsource s-displaycolumns s-criteria s-order s-sql="INSERT INTO Results (FullName, Email, PhoneNumber, Advertiser, IssueDate, AdSize, AdShape, File) VALUES ('FullName','Email','PhoneNumber','Advertiser','IssueDate','AdSize','AdShape','File')" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields s-norecordsfound="Your file has been successfully Uploaded!" i-maxrecords="256" i-groupsize="0" botid="0" u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="BODY" preview="&lt;table border=0 width=&quot;100%&quot;&gt;&lt;tr&gt;&lt;td bgcolor=&quot;#FFFF00&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the start of a Database Results region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;&lt;/table&gt;" startspan b-UseDotNET="FALSE" CurrentExt sa-InputTypes b-DataGridFormat="FALSE" b-DGridAlternate="TRUE" sa-CritTypes b-WasTableFormat="FALSE" --><!--#include file="_fpclass/fpdblib.inc"-->
<% 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="INSERT INTO Results (FullName, Email, PhoneNumber, Advertiser, IssueDate, AdSize, AdShape, File) VALUES ('FullName','Email','PhoneNumber','Advertiser','IssueDate','AdSize','AdShape','File')"
fp_sDefault=""
fp_sNoRecords="Your file has been successfully Uploaded!"
fp_sDataConn="Form"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&"
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="9153" -->


Regards,

msc
0
mcomensAuthor Commented:
barret,

I do not understand what you mean by 'testparameter1'. Please clarify.


msc
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

hhammashCommented:
Hi,

Why are the results written to a .csv file not directly to the database from the form?

Do you actually fine the words FullName, Email..... etc written in the table,  or the table is empty?



0
mcomensAuthor Commented:
Hello,

The results are written to the .csv file and not directly to the database becuase the MS Whitepaper says that to be able to e-mail results write to database and use the upload component simultaneously you must use this method. If thier is a better way to do this I am very open to suggestions. I must be able to do all three things with very little user interaction, such as fill out the form and choose the file that want uploaded and submit.

I do actually find the words FullName, Email etc... written in the table. I have changes words in the INSERT INTO query and what ever I put in the query is what gets written to the table.

I have a Process1.asp that has a select statement:
Select * From form_results.csv

This should loop to and select the last record in the .csv file (which doers contain the data from the form) and post to the process2.asp in which the INSERT INTO query should place the data into the table. It seems to me that the my Select  is not getting the data to the process2.asp.

Howerver I am new to this type of setup and maybe missing somethiong I just cannot find what it is I am missing.

msc
0
hhammashCommented:
I'll check the white paper and get back to you.
0
mcomensAuthor Commented:
Thank You I will look over all I have done to check for typos and such again.

msc
0
barretttCommented:
This is what I meant, you have this statement:
NSERT INTO Results ([FullName], [Email], [PhoneNumber], [Advertiser], [IssueDate], [AdSize], [AdShape], [File]) VALUES ('FullName','Email','PhoneNumber','Advertiser','IssueDate','AdSize','AdShape','File')

the part that has the Values ('FullName', 'Email',.... and so on) those are the exact words that end up in your database because you are defining them here as literals. You need a parameter here so it pulls the data from the form like textbox1.text.

Hope this helps, I've never worked with webbots but, I've built a ton of asp pages that use databases and email.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mcomensAuthor Commented:
So if my database is called From.mdb and the table is called results and the fieldname is FullName, would my parameter be

values (Results.FullName)

msc
0
mcomensAuthor Commented:
OK I just thought about my last comment and realised I cannot use that as an example since that is where I am trying to insert the data. Sometimes I type before I think I guess.

With the form data being written to a .csv file and the select statement being used mentioned in a previous comment I am not sure what my parameter would be.

msc
0
mcomensAuthor Commented:
Does anyone one have an idea on how to make this work or is this just not doable with FP2003?

msc
0
barretttCommented:
Here is a simple example using asp.net and and access database notice how I am sending the @message as a parameter value which comes from txt1.Text box.


Sub Button_Click( Sender As Object, e As EventArgs)
  Dim conMyData As OledbConnection
  Dim cmdSelect3 As OledbCommand
  dim dbPath As String
   dbPath=System.AppDomain.CurrentDomain.BaseDirectory()
   dbPath+= "\.\marquee.mdb"
   
  If IsValid Then
 
 
      conMyData = New OledbConnection( "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="& dbPath )
    cmdSelect3 = New OledbCommand( "update text_source set message=@message where id=1", conMyData )
    cmdSelect3.Parameters.Add( "@message", txt1.Text )

    cmdSelect3.Connection.Open()
      cmdSelect3.ExecuteNonQuery()
    cmdSelect3.Connection.Close()
  End If
 
   if Page.IsPostBack Then
         Response.Redirect("congrats3.aspx")
   end if
End Sub

Hope this is helpful
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development Software

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.