SQL Insert query

H

Harddrive747

I have created a database that has a couple of tables that I need to insert
records into. FrontPage will not allow me to add records to multiple tables
from a form.

So what I would like to do is to send it to another asp page and do an
insert query, so the query would look something like this:

INSERT INTO Activities (Activity_Date)
VALUES :):Act_Date::)

Now the Act_Date is a "DATE" field on the form. The information is getting
passed to the new page, but it gives me the following error

Database Results Wizard Error
Unable to find operator in query string. Query string currently is INSERT
INTO Activities (Activity_Date) VALUES :):Act_Date::)

So I add the operator of an equal sign, which makes the VALUES look like
(=::Act_Date::) and then I get the following error,

Database Results Wizard Error
Your page contains a query with user input parameters that could not be
resolved.
This could happen if your DatabaseRegionStart webbot has an empty or missing
s-columnnames or s-columntypes attributes.
You may need to read Microsoft Knowledge Base Article 817029.

So I add the s-columnnames and types and it still gives me the same error.

So I'm looking to be able to add the record from information on another
form. Or is there a way, I can update multiple tables when I send the
information to a datbase?

I also have thought about adding a blank record then updating the fields
from there. However, I have a table that I need the Activity ID from one
table and the Contact ID from a second table.

I look forward to the answer
 
J

Jon Spivey

Hi,
You need to delimit the date
For Access
INSERT INTO Activities (Activity_Date)
VALUES (#::Act_Date::#)

For SQL Server
INSERT INTO Activities (Activity_Date)
VALUES ('::Act_Date::')
 
H

Harddrive747

Jon thanks for the information, however it still didn't work.

Here is the error that I get when I made the suggested change you gave me.

Database Results Wizard Error
Unable to find operator in query string. Query string currently is UPDATE
Activities SET Activity_Date=#::Act_Date::# WHERE Activity_ID=Act_ID;

Talk to you later
Terry
 
H

Harddrive747

I got one response on this. Does anyone have an answer for me? This is a
problem because I would like to be able to have other people update activity
information and if I can't get the dates and times inputted into the
database, then a lof ot my work has been for not.

Here is the code of the update page.
<!--webbot bot="DatabaseRegionStart" s-columnnames="Activity_Date"
s-columntypes="135" s-dataconnection="Activities" b-tableformat="FALSE"
b-menuformat="FALSE" s-menuchoice s-menuvalue b-tableborder="TRUE"
b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE"
b-listseparator="TRUE" i-listformat="1" b-makeform="FALSE" s-recordsource
s-displaycolumns s-criteria s-order s-sql="UPDATE Activities<br>SET
Activity_Date='#::Active_Date::#'<br>WHERE Activity_ID=Act_ID;"
b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields="Act_Date="
s-norecordsfound="No records returned." 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="<table border=0 width="100%"><tr><td
bgcolor="#FFFF00"><font color="#000000">This is the start of a Database
Results region. The page must be fetched from a web server with a web browser
to display correctly; the current web is stored on your local disk or
network.</font></td></tr></table>" startspan b-UseDotNET="FALSE" CurrentExt
sa-InputTypes b-DataGridFormat="FALSE" b-DGridAlternate="TRUE" sa-CritTypes
b-WasTableFormat="FALSE" b-ReplaceDatabaseRegion="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="UPDATE Activities SET Activity_Date='#::Active_Date::#' WHERE
Activity_ID=Act_ID;"
fp_sDefault="Act_Date="
fp_sNoRecords="No records returned."
fp_sDataConn="Activities"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&Activity_Date=135&"
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="6420" --><p></p>
<hr>
<!--webbot bot="DatabaseRegionEnd" b-tableformat="FALSE"
b-menuformat="FALSE" u-dbrgn2="../_fpclass/fpdbrgn2.inc" i-groupsize="0"
clientside tag="BODY" preview="&lt;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 i-checksum="56926" -->

I have tried Jon's suggestion and it failed on me. SO IS THIS A BUG OR NOT?

Thanks
Terry
 
T

Thomas A. Rowe

1. If the record already exist, then use "Update"
2. If the record doesn't exist, then use "Insert"

If you were coding this by hand and not using the FP database component and the record already exist
you would use:

ActiveDate = Request.Form("Activity_Date")
or
ActiveDate = Request.QueryString("Activity_Date")

UPDATE Activities SET Activity_Date='" & ActiveDate & "' WHERE Activity_ID= " & Act_ID

==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================
 
H

Harddrive747

Thomas, thanks for the information and it seems like it will work, because I
was able to get the update statement to work when I sent it to a variable.

Now the problem I got, because I haven't coded this way before is to get my
database open and then update it and close it back down.

Can you give me the coding to be able to do this. That is to open the
database run the query and then close the database down.

I think this is the code below and when I run it, I get the following error:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver]General error Unable to open
registry key 'Temporary (volatile) Jet DSN for process 0xb30 Thread 0xfb0 DBC
0x14bde34 Jet'.
/Updates/ActivityUpconf.asp, line 25

here is the code
strSql="UPDATE Activities SET Activity_Date=#"&ActiveDate&"#,
Activity_Type="&Type_ID&" WHERE Activity_ID="&Act_ID

set objCN = Server.CreateObject("ADODB.Connection")
objCN.Open "driver={Microsoft Access Driver (*.mdb)};
dbq="&Server.MapPath("data/AreaActivities.mdb") & ";"
Set objRS = objCN.Execute(strSql)
Set objRS = Nothing
Set objCN = Nothing

Thanks for you help
Terry
 
T

Thomas A. Rowe

Terry,

I use a System DSN for all of my database work, so the following is how I would write the code:

Dim DSN_Name
DSN_Name = Application("database1_ConnectionString")
set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open DSN_Name

Conn.Execute = "UPDATE Activities SET Activity_Date = '" & ActiveDate & "' where Activity_ID = " &
Act_ID
Conn.Execute = "UPDATE Activities SET Activity_Type = '" & Type_ID & "' where Activity_ID = " &
Act_ID

However before trying my code, move the database back to the fpdb folder, which FP will set the
correct permissions, which may not be correct on your current "data" folder when you delete and
recreate the connection.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================


Harddrive747 said:
Thomas, thanks for the information and it seems like it will work, because I
was able to get the update statement to work when I sent it to a variable.

Now the problem I got, because I haven't coded this way before is to get my
database open and then update it and close it back down.

Can you give me the coding to be able to do this. That is to open the
database run the query and then close the database down.

I think this is the code below and when I run it, I get the following error:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver]General error Unable to open
registry key 'Temporary (volatile) Jet DSN for process 0xb30 Thread 0xfb0 DBC
0x14bde34 Jet'.
/Updates/ActivityUpconf.asp, line 25

here is the code
strSql="UPDATE Activities SET Activity_Date=#"&ActiveDate&"#,
Activity_Type="&Type_ID&" WHERE Activity_ID="&Act_ID

set objCN = Server.CreateObject("ADODB.Connection")
objCN.Open "driver={Microsoft Access Driver (*.mdb)};
dbq="&Server.MapPath("data/AreaActivities.mdb") & ";"
Set objRS = objCN.Execute(strSql)
Set objRS = Nothing
Set objCN = Nothing

Thanks for you help
Terry

Thomas A. Rowe said:
1. If the record already exist, then use "Update"
2. If the record doesn't exist, then use "Insert"

If you were coding this by hand and not using the FP database component and the record already
exist
you would use:

ActiveDate = Request.Form("Activity_Date")
or
ActiveDate = Request.QueryString("Activity_Date")

UPDATE Activities SET Activity_Date='" & ActiveDate & "' WHERE Activity_ID= " & Act_ID

==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================
 
H

Harddrive747

Tom thanks for the information. I used a different code, but I got it to work.

Now, I have an Insert Query problem. Here is my statement:
ACstrSq1="INSERT INTO Activity_Contact (Activity_ID, Contact_ID,
Remote_computer_name, User_name, Browser_type, Timestamp1) VALUES
("&Act_ID&", "&Contact_Id1&", '"&Host&"', '"&User&"', '"&HTTP_User&"',
#"&UTime&"#);

When I try to view the page, I get the following error:
# Error Type:
Microsoft VBScript compilation (0x800A0409)
Unterminated string constant
/Updates/ActivityUpconf.asp, line 83, column 215
ACstrSq1="INSERT INTO Activity_Contact (Activity_ID, Contact_ID,
Remote_computer_name, User_name, Browser_type, Timestamp1) VALUES
("&Act_ID&", "&Contact_Id1&", '"&Host&"', '"&User&"', '"&HTTP_User&"',
#"&UTime&"#);
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------^

I'm not sure what is the problem, so any help will be greatly appreichated.

thanks.

Thomas A. Rowe said:
Terry,

I use a System DSN for all of my database work, so the following is how I would write the code:

Dim DSN_Name
DSN_Name = Application("database1_ConnectionString")
set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open DSN_Name

Conn.Execute = "UPDATE Activities SET Activity_Date = '" & ActiveDate & "' where Activity_ID = " &
Act_ID
Conn.Execute = "UPDATE Activities SET Activity_Type = '" & Type_ID & "' where Activity_ID = " &
Act_ID

However before trying my code, move the database back to the fpdb folder, which FP will set the
correct permissions, which may not be correct on your current "data" folder when you delete and
recreate the connection.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================


Harddrive747 said:
Thomas, thanks for the information and it seems like it will work, because I
was able to get the update statement to work when I sent it to a variable.

Now the problem I got, because I haven't coded this way before is to get my
database open and then update it and close it back down.

Can you give me the coding to be able to do this. That is to open the
database run the query and then close the database down.

I think this is the code below and when I run it, I get the following error:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver]General error Unable to open
registry key 'Temporary (volatile) Jet DSN for process 0xb30 Thread 0xfb0 DBC
0x14bde34 Jet'.
/Updates/ActivityUpconf.asp, line 25

here is the code
strSql="UPDATE Activities SET Activity_Date=#"&ActiveDate&"#,
Activity_Type="&Type_ID&" WHERE Activity_ID="&Act_ID

set objCN = Server.CreateObject("ADODB.Connection")
objCN.Open "driver={Microsoft Access Driver (*.mdb)};
dbq="&Server.MapPath("data/AreaActivities.mdb") & ";"
Set objRS = objCN.Execute(strSql)
Set objRS = Nothing
Set objCN = Nothing

Thanks for you help
Terry

Thomas A. Rowe said:
1. If the record already exist, then use "Update"
2. If the record doesn't exist, then use "Insert"

If you were coding this by hand and not using the FP database component and the record already
exist
you would use:

ActiveDate = Request.Form("Activity_Date")
or
ActiveDate = Request.QueryString("Activity_Date")

UPDATE Activities SET Activity_Date='" & ActiveDate & "' WHERE Activity_ID= " & Act_ID

==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================
To assist you in getting the best answers for FrontPage support see:
http://www.net-sites.com/sitebuilder/newsgroups.asp

I got one response on this. Does anyone have an answer for me? This is a
problem because I would like to be able to have other people update activity
information and if I can't get the dates and times inputted into the
database, then a lof ot my work has been for not.

Here is the code of the update page.
<!--webbot bot="DatabaseRegionStart" s-columnnames="Activity_Date"
s-columntypes="135" s-dataconnection="Activities" b-tableformat="FALSE"
b-menuformat="FALSE" s-menuchoice s-menuvalue b-tableborder="TRUE"
b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE"
b-listseparator="TRUE" i-listformat="1" b-makeform="FALSE" s-recordsource
s-displaycolumns s-criteria s-order s-sql="UPDATE Activities<br>SET
Activity_Date='#::Active_Date::#'<br>WHERE Activity_ID=Act_ID;"
b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields="Act_Date="
s-norecordsfound="No records returned." 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="<table border=0 width="100%"><tr><td
bgcolor="#FFFF00"><font color="#000000">This is the start of a Database
Results region. The page must be fetched from a web server with a web browser
to display correctly; the current web is stored on your local disk or
network.</font></td></tr></table>" startspan b-UseDotNET="FALSE" CurrentExt
sa-InputTypes b-DataGridFormat="FALSE" b-DGridAlternate="TRUE" sa-CritTypes
b-WasTableFormat="FALSE" b-ReplaceDatabaseRegion="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="UPDATE Activities SET Activity_Date='#::Active_Date::#' WHERE
Activity_ID=Act_ID;"
fp_sDefault="Act_Date="
fp_sNoRecords="No records returned."
fp_sDataConn="Activities"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&Activity_Date=135&"
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="6420" --><p></p>
<hr>
<!--webbot bot="DatabaseRegionEnd" b-tableformat="FALSE"
b-menuformat="FALSE" u-dbrgn2="../_fpclass/fpdbrgn2.inc" i-groupsize="0"
clientside tag="BODY" preview="&lt;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 i-checksum="56926" -->

I have tried Jon's suggestion and it failed on me. SO IS THIS A BUG OR NOT?

Thanks
Terry

:

I have created a database that has a couple of tables that I need to insert
records into. FrontPage will not allow me to add records to multiple tables
from a form.

So what I would like to do is to send it to another asp page and do an
insert query, so the query would look something like this:

INSERT INTO Activities (Activity_Date)
VALUES :):Act_Date::)

Now the Act_Date is a "DATE" field on the form. The information is getting
passed to the new page, but it gives me the following error

Database Results Wizard Error
Unable to find operator in query string. Query string currently is INSERT
INTO Activities (Activity_Date) VALUES :):Act_Date::)

So I add the operator of an equal sign, which makes the VALUES look like
(=::Act_Date::) and then I get the following error,

Database Results Wizard Error
Your page contains a query with user input parameters that could not be
resolved.
This could happen if your DatabaseRegionStart webbot has an empty or missing
s-columnnames or s-columntypes attributes.
You may need to read Microsoft Knowledge Base Article 817029.

So I add the s-columnnames and types and it still gives me the same error.

So I'm looking to be able to add the record from information on another
form. Or is there a way, I can update multiple tables when I send the
information to a datbase?

I also have thought about adding a blank record then updating the fields
from there. However, I have a table that I need the Activity ID from one
table and the Contact ID from a second table.

I look forward to the answer
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top