|
 |
asp_databases thread: How to insert a sql text in the database?
Message #1 by "Nagendra Pandey" <npandey@h...> on Fri, 23 Feb 2001 22:26:50
|
|
Hi Everybody,
I am working on a site where the a sql code is generated by a form. This
sql code needs to be saved in the database so that if can retreived later
by using the name used to save the sql code. Here is the one example:
sqlcode :
SELECT * FROM eoffers WHERE State LIKE 'CA%';
This sqlcode is to be inserted in the table. I am trying to do that by
using below sql statement:
Insert Into queries (Queryname,Sqlcode) Values ('cavisitors','SELECT *
FROM eoffers WHERE State LIKE 'CA%';');
Somehow this is not working and I am getting following errors:
Microsoft JET Database Engine error '80040e14'
Syntax error in INSERT INTO statement.
/tolladmin/insertsql.asp, line 50
I cannot find where is the error. Please help me.
Thanks in advance for your time and help
Regards to all
Nagendra Pandey
Message #2 by "Nagendra Pandey" <npandey@h...> on Fri, 23 Feb 2001 23:54:24
|
|
I made some changes to try to fix the error but I am still getting the
error. Here is all the code.
----------CODE STARTS HERE---------------
<%
Queryname = Trim(Request.Form("Queryname"))
Sqlcode = Trim(Request.Form("Sqlcode"))
' function for avoiding error due to single quotes in sql text
Function SQLQuote(var)
If InStr(var, "'") <> 0 Then
var = Replace(var, "'", "''")
End If
SQLQuote = var
End Function
sDBName = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=E:\database\tcaoffers.mdb"
Set objDB = Server.CreateObject("ADODB.Connection")
objDB.Open sDBName
'Code to add a new record...
sql = "Insert Into queries ("
sql = sql & "Queryname,"
sql = sql & "Sqlcode"
sql = sql & ") "
sql = sql & "Values ("
sql = sql & "'" & SqlQuote(Queryname) & "',"
sql = sql & "'" & SqlQuote(Sqlcode) & "'"
sql = sql & ");"
Response.Write sql
ObjDB.Execute(sql)
objDB.Close
Set objDB = Nothing
%>
----------CODE ENDS HERE---------------
I am getting following errors:
Insert Into queries (Queryname,Sqlcode) Values ('causers','SELECT * FROM
eoffers WHERE State LIKE ''CA%'';');
Microsoft JET Database Engine error '80040e14'
Syntax error in INSERT INTO statement.
/tolladmin/insertquery.asp, line 28
Somebody please help me
Regards to all
Nagendra Pandey
Message #3 by Maciej Warszawski <Maciej_Warszawski@i...> on Sat, 24 Feb 2001 04:05:24 +0100
|
|
Hi
I think the problem is in "'"
this is yours:
Insert Into queries (Queryname,Sqlcode) Values ('cavisitors','SELECT * FROM
eoffers WHERE State LIKE 'CA%';');
should be:
Insert Into queries (Queryname,Sqlcode) Values ('cavisitors','SELECT * FROM
eoffers WHERE State LIKE ''CA%'';');
it is like in JS and C "\" it is escape chracter in JS if you want to use a
\ in string you mast write "\\"
hint:
run query analizer from SQL server 7 ro 2000 it can colour SQL syntax
greets
MW
-----Original Message-----
From: Nagendra Pandey [mailto:npandey@h...]
Sent: Friday, February 23, 2001 11:27 PM
To: ASP Databases
Subject: [asp_databases] How to insert a sql text in the database?
Hi Everybody,
I am working on a site where the a sql code is generated by a form. This
sql code needs to be saved in the database so that if can retreived later
by using the name used to save the sql code. Here is the one example:
sqlcode :
SELECT * FROM eoffers WHERE State LIKE 'CA%';
This sqlcode is to be inserted in the table. I am trying to do that by
using below sql statement:
Insert Into queries (Queryname,Sqlcode) Values ('cavisitors','SELECT *
FROM eoffers WHERE State LIKE 'CA%';');
Somehow this is not working and I am getting following errors:
Microsoft JET Database Engine error '80040e14'
Syntax error in INSERT INTO statement.
/tolladmin/insertsql.asp, line 50
I cannot find where is the error. Please help me.
Thanks in advance for your time and help
Regards to all
Nagendra Pandey
Message #4 by =?iso-8859-1?q?Bhanu=20Shanker=20Menon?= <bhanu271978@y...> on Sat, 24 Feb 2001 02:41:27 -0800 (PST)
|
|
Hi there,
Could n't you store the sql code in a variable and
place the variable in your statement .
Have u tried that????
BHANU
--- Nagendra Pandey <npandey@h...> wrote: >
Hi Everybody,
>
> I am working on a site where the a sql code is
> generated by a form. This
> sql code needs to be saved in the database so that
> if can retreived later
> by using the name used to save the sql code. Here is
> the one example:
>
> sqlcode :
> SELECT * FROM eoffers WHERE State LIKE 'CA%';
>
> This sqlcode is to be inserted in the table. I am
> trying to do that by
> using below sql statement:
>
> Insert Into queries (Queryname,Sqlcode) Values
> ('cavisitors','SELECT *
> FROM eoffers WHERE State LIKE 'CA%';');
>
> Somehow this is not working and I am getting
> following errors:
>
> Microsoft JET Database Engine error '80040e14'
>
> Syntax error in INSERT INTO statement.
>
> /tolladmin/insertsql.asp, line 50
>
> I cannot find where is the error. Please help me.
>
> Thanks in advance for your time and help
>
> Regards to all
> Nagendra Pandey
>
>
Message #5 by Maciej Warszawski <Maciej_Warszawski@i...> on Sat, 24 Feb 2001 18:47:52 +0100
|
|
Hi again
every think is clear, SQLCODE is a reserved word, try to delimit with []
this is right sql statement:
Insert Into queries (Queryname,[Sqlcode]) Values ('causers','SELECT * FROM
eoffers WHERE State LIKE ''CA%'';');
btw on SQL 2000 works fine without any delimiters however enterprise mameger
delimited SQLCODE word when I tried to create table, access 2000 did not.
btw2 list of reserved keywords in ODBC or Jet SQL or whatever you can find
on MSDN http://msdn.microsoft.com/library/
greetz
MW
ps
Access sux, buy SQL Server :)))))
-----Original Message-----
From: Nagendra Pandey [mailto:npandey@h...]
Sent: Saturday, February 24, 2001 12:54 AM
To: ASP Databases
Subject: [asp_databases] Re: How to insert a sql text in the database?
I made some changes to try to fix the error but I am still getting the
error. Here is all the code.
----------CODE STARTS HERE---------------
<%
Queryname = Trim(Request.Form("Queryname"))
Sqlcode = Trim(Request.Form("Sqlcode"))
' function for avoiding error due to single quotes in sql text
Function SQLQuote(var)
If InStr(var, "'") <> 0 Then
var = Replace(var, "'", "''")
End If
SQLQuote = var
End Function
sDBName = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=E:\database\tcaoffers.mdb"
Set objDB = Server.CreateObject("ADODB.Connection")
objDB.Open sDBName
'Code to add a new record...
sql = "Insert Into queries ("
sql = sql & "Queryname,"
sql = sql & "Sqlcode"
sql = sql & ") "
sql = sql & "Values ("
sql = sql & "'" & SqlQuote(Queryname) & "',"
sql = sql & "'" & SqlQuote(Sqlcode) & "'"
sql = sql & ");"
Response.Write sql
ObjDB.Execute(sql)
objDB.Close
Set objDB = Nothing
%>
----------CODE ENDS HERE---------------
I am getting following errors:
Insert Into queries (Queryname,Sqlcode) Values ('causers','SELECT * FROM
eoffers WHERE State LIKE ''CA%'';');
Microsoft JET Database Engine error '80040e14'
Syntax error in INSERT INTO statement.
/tolladmin/insertquery.asp, line 28
Somebody please help me
Regards to all
Nagendra Pandey
Message #6 by "Ken Schaefer" <ken@a...> on Sun, 25 Feb 2001 20:29:04 +1100
|
|
> 'SELECT * FROM eoffers WHERE State LIKE 'CA%';'
Look at the above.
The string starts with 'SELECT...
As soon as you get to LIKE '
the string *stops* at the next ', and the database doesn't know what to do
with the CA%'
Instead you need to double all the ' which you want to insert into the
database.
eg
INSERT INTO table1 (field1)
VALUES( 'This is John''s car')
Cheers
Ken
> Hi Everybody,
>
> I am working on a site where the a sql code is generated by a form. This
> sql code needs to be saved in the database so that if can retreived later
> by using the name used to save the sql code. Here is the one example:
>
> sqlcode :
> SELECT * FROM eoffers WHERE State LIKE 'CA%';
>
> This sqlcode is to be inserted in the table. I am trying to do that by
> using below sql statement:
>
> Insert Into queries (Queryname,Sqlcode) Values ('cavisitors','SELECT *
> FROM eoffers WHERE State LIKE 'CA%';');
>
> Somehow this is not working and I am getting following errors:
>
> Microsoft JET Database Engine error '80040e14'
>
> Syntax error in INSERT INTO statement.
>
> /tolladmin/insertsql.asp, line 50
>
> I cannot find where is the error. Please help me.
>
> Thanks in advance for your time and help
>
> Regards to all
> Nagendra Pandey
Message #7 by "Nagendra Pandey" <npandey@h...> on Sun, 25 Feb 2001 09:22:41
|
|
Hi Everybody,
Thanks to all of you for helping me.
To me the code was looking alright. Finally I thought there must be some
reserved words which I am using in the code which I am not aware of it. I
just change the name of variables and fields of table. Sqlcode and
QueryName were change to Sql_Code and Query_Name respectively and WOW the
code worked.
Esp. thanks to MW for pointing out Sqlcode is reserved work and sending me
the link. I am learning SQL 2000 but most of the my customers are small
business and they do not have license for SQL 2000.
Thanks to all of you
Cheers
Nagendra Pandey
Message #8 by "Wally Burfine" <oopconsultant@h...> on Mon, 26 Feb 2001 03:44:23 -0000
|
|
change the single quotes to 2 single quotes
strSQL = replace(strSQL,"'","''")
>From: "Nagendra Pandey" <npandey@h...>
>Reply-To: "ASP Databases" <asp_databases@p...>
>To: "ASP Databases" <asp_databases@p...>
>Subject: [asp_databases] How to insert a sql text in the database?
>Date: Fri, 23 Feb 2001 22:26:50
>
>Hi Everybody,
>
>I am working on a site where the a sql code is generated by a form. This
>sql code needs to be saved in the database so that if can retreived later
>by using the name used to save the sql code. Here is the one example:
>
>sqlcode :
>SELECT * FROM eoffers WHERE State LIKE 'CA%';
>
>This sqlcode is to be inserted in the table. I am trying to do that by
>using below sql statement:
>
>Insert Into queries (Queryname,Sqlcode) Values ('cavisitors','SELECT *
>FROM eoffers WHERE State LIKE 'CA%';');
>
>Somehow this is not working and I am getting following errors:
>
>Microsoft JET Database Engine error '80040e14'
>
>Syntax error in INSERT INTO statement.
>
>/tolladmin/insertsql.asp, line 50
>
>I cannot find where is the error. Please help me.
>
>Thanks in advance for your time and help
>
>Regards to all
>Nagendra Pandey
>
>
Message #9 by "Tomm Matthis" <matthis@b...> on Mon, 26 Feb 2001 09:09:07 -0500
|
|
The backtick (') is what is causing you problems in the insert
statement.
You need to use the replace function to find all occurences of the
backtick (') and to "double it up" as in ('')
The INSERT statement will accept the string then.
Tomm
> -----Original Message-----
> From: Nagendra Pandey [mailto:npandey@h...]
> Sent: Friday, February 23, 2001 10:27 PM
> To: ASP Databases
> Subject: [asp_databases] How to insert a sql text in the database?
>
>
> Hi Everybody,
>
> I am working on a site where the a sql code is generated by a form.
This
> sql code needs to be saved in the database so that if can retreived
later
> by using the name used to save the sql code. Here is the one example:
>
> sqlcode :
> SELECT * FROM eoffers WHERE State LIKE 'CA%';
>
> This sqlcode is to be inserted in the table. I am trying to do that by
> using below sql statement:
>
> Insert Into queries (Queryname,Sqlcode) Values ('cavisitors','SELECT *
> FROM eoffers WHERE State LIKE 'CA%';');
>
> Somehow this is not working and I am getting following errors:
>
> Microsoft JET Database Engine error '80040e14'
>
> Syntax error in INSERT INTO statement.
>
> /tolladmin/insertsql.asp, line 50
>
> I cannot find where is the error. Please help me.
>
> Thanks in advance for your time and help
>
> Regards to all
> Nagendra Pandey
>
>
>
>
|
|
|
View Cart
