SQL Insert query

Harddrive747 · Nov 20, 2004

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

Jon Spivey · Nov 20, 2004

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::')

Harddrive747 · Nov 21, 2004

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

Harddrive747 · Nov 24, 2004

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.

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

<p></p>
<hr>



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

Thanks
Terry

Thomas A. Rowe · Nov 24, 2004

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

Harddrive747 · Nov 25, 2004

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 · Nov 25, 2004

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

Click to expand...

Harddrive747 · Nov 26, 2004

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.

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

<p></p>
<hr>



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

Click to expand...

Click to expand...

Error Adding Record with INSERT INTO	4	May 21, 2008
Adding data to MS Access from csv file	0	Jul 24, 2022
Creating a Chart on form from selected data in query.	0	Oct 7, 2021
Database Update Issues	2	Sep 15, 2006
QBF & Query Malfunction	0	Jul 17, 2017
Database Results Wizard Error	3	Feb 24, 2005
DLookup in Continuous form	0	Aug 1, 2019
Insert Index into a Table	1	Oct 4, 2019

SQL Insert query

Harddrive747

Jon Spivey

Harddrive747

Harddrive747

Thomas A. Rowe

Harddrive747

Thomas A. Rowe

Harddrive747

Ask a Question

Similar Threads