[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

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
0
mcomens
Asked:
mcomens
  • 7
  • 3
  • 2
1 Solution
 
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now