SQL INSERT Syntax ??

	Home Register Search Help Login

Sponsors

Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions.

Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.

Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax

Search Forums

Recent Posts

Todays Posts
Most Active posts
Posts since last visit
My Recent Posts
Mark posts read

SQL INSERT Syntax ??

View related threads: (in this forum | in all forums)

Logged in as: Guest

Users viewing this topic: none

Printable Version

All Forums >> Web Development >> ASP and Database >> SQL INSERT Syntax ??

Page: [1]


Frank Smith Posts: 9 Joined: 9/14/2002 Status: offline	SQL INSERT Syntax ?? - 9/15/2002 7:08:01 I want to read one Access DB and insert selected records into another Access DB before I " erase/reset" the record in the original DB. In the DB Results Wizard, my reset code works fine and looks like: UPDATE schedule SET xdate=DateValue(' 01/01/2020' ),dispatcher=' ' ,phone=' ' ,amcrew=' ' ,pmcrew=' ' ,amtow=' ' ,pmtow=' ' ,instructor=' ' ,ipilot=' ' , instruct2=' ' WHERE xdate < date()-30 So before this line I was thinking of: INSERT INTO history............. but I cannot seem to find the right syntax Regards, Frank


hhammash Posts: 1064 Joined: 8/19/2002 Status: offline	RE: SQL INSERT Syntax ?? - 9/15/2002 10:11:20 Hi Frank, This is the Format for Insert Query: Example: Table Called Staff. Fields are: Name, Address and Salary. INSERT INTO Staff (Name, Address, Salary) VALUES (' ::Name::' , ' ::Address::' , ' ::Salary::' ) Regards Hisham (in reply to Frank Smith)


Long Island Lune Posts: 2340 Joined: 6/8/2002 From: New York Status: offline	RE: SQL INSERT Syntax ?? - 9/15/2002 13:35:40 Frank, quote: So before this line I was thinking of: INSERT INTO history............. but I cannot seem to find the right syntax Can you do two queries in one DRW??? I would create two different DRW' s to accomoplish your task. The first DRW inserts the data from one table to the other table (see Hisham' s INSERT above). The second DRW erase the original. Or if your Reading-In, Copying, then Erasing, create 3 DRW' s. 1): Read in your data. 2): Copy it to history table. 3): Erase original table record. _____________________________ (in reply to Frank Smith)


hhammash Posts: 1064 Joined: 8/19/2002 Status: offline	RE: SQL INSERT Syntax ?? - 9/15/2002 13:45:07 Hi, LLL' s method is logical, I tried it before in a post, where the requester asked to add the form into two tables at the same time. I created 2 DRWs for her with Insert Queries, each DRW writes to a different table. You can put all DRW' s in one page, and each DRW is a query, and post the form to that page. Regards Hisham Sorry: I thought that you only need the INSERT query structure. (in reply to Frank Smith)


Long Island Lune Posts: 2340 Joined: 6/8/2002 From: New York Status: offline	RE: SQL INSERT Syntax ?? - 9/15/2002 14:02:01 Hi, Hisham is CORRECT. I have 34 DRW' s on one page. As a matter of fact, the " 34 DRW' s Page" concerns the " SQL DISTINCT and Count Problem" question I posted in the ASP/Database forum on this site. The sky' s the limit with DRW' s on a page. _____________________________ (in reply to Frank Smith)

Spooky

Posts: 26597
Joined: 11/11/1998
From: Middle Earth
Status: offline

RE: RE: SQL INSERT Syntax ?? - 9/15/2002 16:02:07

quote:

34 DRW' s on one page

_____________________________

If you arent part of the solution, then there is good money to be made prolonging the problem

��k�

Database / DRW help Check out the Tutorials for Frontpage Databases

Forum FAQ' s

Developers Edition Spooky Login User Registration and Management tool

Personalized Frontpage Hosting

(in reply to Long Island Lune)


Long Island Lune Posts: 2340 Joined: 6/8/2002 From: New York Status: offline	RE: SQL INSERT Syntax ?? - 9/15/2002 18:22:35 Spooky, Yeah, can you imagine??? Each one returns one value for a site operation " stats" page. The page loads in slowly!!! 64K for this page at the server. _____________________________ (in reply to Frank Smith)


Spooky Posts: 26597 Joined: 11/11/1998 From: Middle Earth Status: offline	RE: SQL INSERT Syntax ?? - 9/15/2002 18:35:21 I can imagine [:j] What type of queries are you doing, I think we should look at speeding that up for you :-) Start a new thread and we will have a look? _____________________________ If you arent part of the solution, then there is good money to be made prolonging the problem ��k� Database / DRW help Check out the Tutorials for Frontpage Databases Forum FAQ' s Developers Edition Spooky Login User Registration and Management tool Personalized Frontpage Hosting (in reply to Frank Smith)


Long Island Lune Posts: 2340 Joined: 6/8/2002 From: New York Status: offline	RE: SQL INSERT Syntax ?? - 9/15/2002 18:50:21 Spooky, The queries are strict count() queries. They determine stats. That' s why I couldn' t figure out why DISTINCT and count() wouldn' t work. One after another going down the page. When you view the page on the web, it' s just a table with lots of rows, but when you view the page in FP, DRW after DRW going down the page. I' ll make another post so you can see it tomorrow morning. I' ll put it in ASP/database and title it " Endless DRW' s" Thanks. LLL _____________________________ (in reply to Frank Smith)


Frank Smith Posts: 9 Joined: 9/14/2002 Status: offline	RE: SQL INSERT Syntax ?? - 9/16/2002 13:28:51 Using the following syntax: INSERT INTO history (dispatcher,phone,amcrew,pmcrew,amtow,pmtow,instructor,instruct2,ipilot) VALUES (' ::dispatcher::' ,' ::phone::' ,' ::amcrew::' ,' ::pmcrew::' ,' ::amtow::' ,' ::pmtow::' ,' ::instructor::' ,' ::instruct2::' ,' ::ipilot::' ) WHERE (xdate < date()-30) On Verify, I get: Server error: Unable to retrieve schema information from the query: INSERT INTO history (dispatcher,phone,amcrew,pmcrew,amtow,pmtow,instructor,instruct2,ipilot) VALUES ' 1' ,' 2' ,' 3' ,' 4' ,' 5' ,' 6' ,' 7' ,' 8' ,' 9' ) WHERE (xdate < date()-30) against a database using the connection string DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=data/sched.mdb. The following error message comes from the database driver software; it may appear in a different language depending on how the driver is configured. ------------------------------------------------------- [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. Source: Microsoft OLE DB Provider for ODBC Drivers Number: -2147217900 (0x80040e14) Seems that it does not like my WHERE clause. Took out the WHERE and it Validated ?? (in reply to Frank Smith)

Long Island Lune

Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline

RE: SQL INSERT Syntax ?? - 9/16/2002 14:16:29

Frank,

quote:

Server error: Unable to retrieve schema information from the query:

This means that what your sending to your database does not match what' s there. In other words, your database is expecting something and your sending it something else. Normally it is an incorrect type.

quote:

xdate < date()-30

What TYPE is xdate???

_____________________________

(in reply to Frank Smith)


Frank Smith Posts: 9 Joined: 9/14/2002 Status: offline	RE: RE: SQL INSERT Syntax ?? - 9/16/2002 15:10:25 xdate is a MS Access " Short Date" formatte field in the db. In another segment of my web, the following SQL works fine: UPDATE schedule SET xdate=DateValue(' 01/01/2020' ),dispatcher=' ' ,phone=' ' ,amcrew=' ' ,pmcrew=' ' ,amtow=' ' ,pmtow=' ' ,instructor=' ' ,ipilot=' ' , instruct2=' ' WHERE xdate < date()-30 so I cannot figure out why " this" INSERT INTO ...... WHERE .... does not want to work. (in reply to Long Island Lune)


Long Island Lune Posts: 2340 Joined: 6/8/2002 From: New York Status: offline	RE: SQL INSERT Syntax ?? - 9/16/2002 15:15:03 Frank, Are you SENDING ' short date" type data to the database in the query??? _____________________________ (in reply to Frank Smith)


Frank Smith Posts: 9 Joined: 9/14/2002 Status: offline	RE: RE: SQL INSERT Syntax ?? - 9/16/2002 18:12:58 TIME OUT !! I went to my Visual Foxpro Help and KB - you cannot have a conditional WHERE clause on a SQL INSERT - that is why my UPDATE works and INSERT does not............. So how can I select records from " schedule" that are more than 30 days old and insert them into " history" ?? I am a real novice to ASP or the like (pretty darn good at Visual Fox though) < Message edited by Frank Smith -- 9/15/2002 6:15:37 PM > (in reply to Long Island Lune)

Long Island Lune

Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline

RE: SQL INSERT Syntax ?? - 9/16/2002 20:28:56

Frank,

That never occurred to me. But if you think about it, it makes sense. Why require a WHERE when inputting data???

quote:

So how can I select records from " schedule" that are more than 30 days old and insert them into " history" ??
I am a real novice to ASP or the like (pretty darn good at Visual Fox though)

Here was one of my original post messages:

quote:

Or if your Reading-In, Copying, then Erasing, create 3 DRW' s.

1): Read in your data.
2): Copy it to history table.
3): Erase original table record.

Select the record using the first DRW checking for dates older than 30 days. Once you have the record in the results, create a second DRW that writes it to your history file. You actually need no WHERE clause to write. It' s a copy of a record that you already brought in from DRW 1. You know it' s the right record. Then use the data in the DRW results again to write a SQL DELETE to the table in DRW 3. All the criteria is there for you to DELETE the correct record.
That should do it.

_____________________________

(in reply to Frank Smith)


hhammash Posts: 1064 Joined: 8/19/2002 Status: offline	RE: SQL INSERT Syntax ?? - 9/17/2002 0:30:54 Hi, Try your date as this: WHERE (((xdate)<=Date()-30)); I think your problem is that there is some discripancy between what is available in the Database and what have been sent. Regards Hisham < Message edited by hhammash -- 9/16/2002 12:41:33 AM > (in reply to Frank Smith)


Frank Smith Posts: 9 Joined: 9/14/2002 Status: offline	RE: RE: SQL INSERT Syntax ?? - 9/17/2002 10:04:08 Thanks for your patience and help. OK so on a page, I created a DRW to get the 30 day older records. No problem, got some. Then on the same page, I added another DRW, below the first one. Connection is to " history" , Custom Query, like this: INSERT INTO history (xdate,dispatcher,phone,amcrew,pmcrew,amtow,pmtow,instructor,instruct2,ipilot) VALUES (' ::xdate::' ,' ::dispatcher::' ,' ::phone::' ,' ::amcrew::' ,' ::pmcrew::' ,' ::amtow::' ,' ::pmtow::' ,' ::instructor::' ,' ::instruct2::' ,' ::ipilot::' ) Is this the correct syntax, as no records went to history. No need for the third to delete as the records are " re-used" to keep the db small. (in reply to Long Island Lune)


Long Island Lune Posts: 2340 Joined: 6/8/2002 From: New York Status: offline	RE: SQL INSERT Syntax ?? - 9/17/2002 10:51:08 Frank, Sorry... I did not read your post clearly. I' m glad Hisham has a handle on your needs. Hisham, Nice WHERE. Heavy on nestling. Keep up the good work. LLL _____________________________ (in reply to Frank Smith)


Frank Smith Posts: 9 Joined: 9/14/2002 Status: offline	RE: RE: SQL INSERT Syntax ?? - 9/17/2002 11:32:29 Look guys forget the WHERE clause - Don' t believe me ?? take a blank page and Insert a db region. pick a connection, create a Query INSERT and verify it. NOW add a WHERE clause and Verify it again - will not pass. So can you help me with the correct syntax for the second DBW region so i can write some records to " history" (in reply to Long Island Lune)


Long Island Lune Posts: 2340 Joined: 6/8/2002 From: New York Status: offline	RE: SQL INSERT Syntax ?? - 9/17/2002 12:14:20 Frank, quote: INSERT INTO history (xdate,dispatcher,phone,amcrew,pmcrew,amtow,pmtow,instructor,instruct2,ipilot) VALUES (' ::xdate::' ,' ::dispatcher::' ,' ::phone::' ,' ::amcrew::' ,' ::pmcrew::' ,' ::amtow::' ,' ::pmtow::' ,' ::instructor::' ,' ::instruct2::' ,' ::ipilot::' ) The above is your INSERT query right... OK, it seems that the 2nd DRW is not connected or getting data from the 1st DRW. The 2nd DRW is sending blank data to your history table. This is probably becasue both DRW' s are on the SAME page. The best scenario would be: 1): Keep DRW #1 the way it is. But make sure the data from the query in DRW #1 is displayed in it' s Results Region. 2): Place a form inside the DRW #1 " Results Region" . 3): Remove the [Clear] button and change the other button to say: [Click to Continue]. 4): Now using this form - " POST" the data to the second page. 5): Place the 2nd DRW on the second page. This way your sending all the values from DRW #1 to DRW #2 on page 2. Now the 2nd DRW on page 2 will receive the data POSTED from page 1 and you can send it to the History table with the INSERT query. Any other problems, post them. LLL _____________________________ (in reply to Frank Smith)


Long Island Lune Posts: 2340 Joined: 6/8/2002 From: New York Status: offline	RE: SQL INSERT Syntax ?? - 9/17/2002 12:16:32 Frank, P.S.: Yes, I believe you. It makes sense. I posted that 2 posts ago. No " WHERE" on an " INSERT" . _____________________________ (in reply to Frank Smith)


rdouglass Posts: 9167 From: Biddeford, ME USA Status: offline	RE: SQL INSERT Syntax ?? - 9/17/2002 13:13:05 Pardon the interuption, but shouldn' t the query look like: INSERT INTO history (xdate,dispatcher,phone,amcrew,pmcrew,amtow,pmtow,instructor,instruct2,ipilot) VALUES (#::xdate::#,' ::dispatcher::' ,' ::phone::' ,' ::amcrew::' ,' ::pmcrew::' ,' ::amtow::' ,' ::pmtow::' ,' ::instructor::' ,' ::instruct2::' ,' ::ipilot::' ) Dates take the format #::DateField::#. Text fields use the ' as in ' ::TextField::' . Just a quick thought....... (in reply to Frank Smith)


Long Island Lune Posts: 2340 Joined: 6/8/2002 From: New York Status: offline	RE: SQL INSERT Syntax ?? - 9/17/2002 13:27:50 rdouglass, No pardon' s required my friend. The goal is to solve his problem. And if your input can do it - Bravoooo!!! Plus I had forgotten that tiny golden rule: #:: ::# too. You got me... _____________________________ (in reply to Frank Smith)


Frank Smith Posts: 9 Joined: 9/14/2002 Status: offline	RE: RE: SQL INSERT Syntax ?? - 9/19/2002 13:20:13 Problem solved - thanks for all the help. The light finally came on !! Put both databases in the same mdb file - bingo !! (in reply to Long Island Lune)


Long Island Lune Posts: 2340 Joined: 6/8/2002 From: New York Status: offline	RE: SQL INSERT Syntax ?? - 9/19/2002 13:26:10 Frank, That' s the ticket. Glad you solved your problem. _____________________________ (in reply to Frank Smith)

Page: [1]

All Forums >> Web Development >> ASP and Database >> SQL INSERT Syntax ??

Page: [1]

Jump to: 1

New Messages	No New Messages
Hot Topic w/ New Messages	Hot Topic w/o New Messages
Locked w/ New Messages	Locked w/o New Messages

Post New Thread

Reply to Message

Post New Poll

Submit Vote

Delete My Own Post

Delete My Own Thread

Rate Posts