Insert problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Insert problem

Author

Topic

sqlWhat
Starting Member

12 Posts

Posted - 2006-09-28 : 10:13:50

I have this template system once the user decides to apply the template I created several arrays with the data from the template to insert into their account. I first check to see if the record exists in their account if so then update the record else insert. I am having trouble reinserting data that is already in the DB into another record. Please advise me on what is driving me nuts. error is as foolows
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '#ulButtons li a'.

/scripts/dragndrop/templates.asp, line 137

line 137 is
rsCss.Open SQL, conn
the sql statement is
SQL = "INSERT INTO FolioPages (Title, Contents, Element) VALUES '" & arrayCss(0, posCss) & "', '" & replace(arrayCss(1, posCss), "1000", ID) & "', '" & replace(arrayCss(2, posCss), "1000", ID) & "' WHERE Type='css' AND IVID='" & ID & "' AND Page='" & pageArray(position) & "';"
Thanks in advance for the help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-28 : 10:35:45

You can not OPEN a recordset with INSERT statement.
Try to do a cnCss.EXECUTE SQL, , ,adExecutrNoRecords instead.

Peter Larsson
Helsingborg, Sweden

sqlWhat
Starting Member

12 Posts

Posted - 2006-09-28 : 11:26:10

Peso

Thanks for the reply. I tried to change the statement to what you suggested but still get the same error.
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '#ulButtons li a'.

/scripts/dragndrop/templates.asp, line 138

line 138
conn.EXECUTE SQL
sql statement
SQL = "INSERT INTO FolioPages (Title, Contents, Element) VALUES '" & arrayCss(0, posCss) & "', '" & replace(arrayCss(1, posCss), "1000", ID) & "', '" & replace(arrayCss(2, posCss), "1000", ID) & "' WHERE Type='css' AND IVID='" & ID & "' AND Page='" & pageArray(position) & "';"
thanks in advance for the help

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-28 : 12:29:28

The error is simply you missed parenthesis after VALUES:

SQL = "INSERT INTO FolioPages (Title, Contents, Element) VALUES ('" & arrayCss(0, posCss) & "', '" & replace(arrayCss(1, posCss), "1000", ID) & "', '" & replace(arrayCss(2, posCss), "1000", ID) & "' WHERE Type='css' AND IVID='" & ID & "' AND Page='" & pageArray(position) & "')"

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-28 : 12:30:02

Use stored procedure with parameters. Avoid having concatenated SQL statements

Madhivanan

Failing to plan is Planning to fail

sqlWhat
Starting Member

12 Posts

Posted - 2006-09-28 : 14:48:07

I out the perenthesis in and got this error

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'WHERE'.

/scripts/dragndrop/templates.asp, line 138

madhivanan I totally agree that a stored procedure would be much better but for lack of time and lack of total familiarity in creating I just assume using an inline sql statement. It may seem a little simple but if I create a stored procedure it would be complex because it would need to handle variables as well as possibly having some sort of case statement and therefore being beyong my experience at this time I am stuck with the way I am doing it.
Thanks for the help and suggestions

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-28 : 21:14:04

Print your SQL statement and post it here

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-29 : 03:54:46

quote:
Originally posted by sqlWhat

I out the perenthesis in and got this error

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'WHERE'.

/scripts/dragndrop/templates.asp, line 138

You can't use WHERE clause with Insert..Values statement....Use Insert...select if you want to include where clause as well:

SQL = "INSERT INTO FolioPages (Title, Contents, Element) VALUES ('" & arrayCss(0, posCss) & "', '" & replace(arrayCss(1, posCss), "1000", ID) & "', '" & replace(arrayCss(2, posCss), "1000", ID) & "')"

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-29 : 04:50:48

No point either in using WHERE of there is no FROM part!
Do as Madhivanan wrote and instead of EXECUTE the stringm, PRINT the string and post here.

Peter Larsson
Helsingborg, Sweden

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-09-29 : 04:54:49

Or do as Madi said even before that - use a stored proc with parameters rather than building your sql code in the app.
Stored procs are just so much more secure and quicker and....... it's just the right thing to do.

Duane.

sqlWhat
Starting Member

12 Posts

Posted - 2006-09-29 : 08:53:38

Ditch and everyone else thanks for the help and suggestions. I totally agree that stored procedures are going to be better. But the fact is time is limited and my knowledge of creation of SP's are limited and there for I found it a temporary fix to insert into the application itself. In regaurds to the sql statment There was a major DAAAHHH factor if you will in the point that the insert statement can not use a where. I since removed the where and inserted the () around the values and it executes.

Again I really appreciate the helpful hints and the help period. I hope in the future I can help some in this forum like you helped me.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-29 : 08:56:49

>>I hope in the future I can help some in this forum like you helped me.

Thats Good

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources