SQL INSERT Syntax ?? (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

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




Long Island Lune -> 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.






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



Long Island Lune -> 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.




Spooky -> RE: RE: SQL INSERT Syntax ?? (9/15/2002 16:02:07)

quote:

34 DRW' s on one page
[:@]



Long Island Lune -> 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.



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



Long Island Lune -> 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



Frank Smith -> 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 ??



Long Island Lune -> 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???




Frank Smith -> 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.



Long Island Lune -> RE: SQL INSERT Syntax ?? (9/16/2002 15:15:03)

Frank,

Are you SENDING ' short date" type data to the database in the query???



Frank Smith -> 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)



Long Island Lune -> 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. [:D]




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



Frank Smith -> 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.



Long Island Lune -> 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



Frank Smith -> 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"



Long Island Lune -> 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 [:)]




Long Island Lune -> 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" .




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



Long Island Lune -> 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...




Frank Smith -> 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 !!




Long Island Lune -> RE: SQL INSERT Syntax ?? (9/19/2002 13:26:10)

Frank,

That' s the ticket. Glad you solved your problem.



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.09375