SQL EXEC syntax errors

SQL EXEC syntax errors

Post by Cha » Tue, 07 May 2002 00:53:24



I'm creating a website that is executing a stored procedure from my
SQL database.  I can get this to work fine if I only have one input
parameter.  The problem though is that all of my stored procedures
need several parameters inputted in order to execute (for example:
entering the month and year).

Since I can get this statement to work just fine if I eliminate the
second input parameter I beleive that I must have the sytax incorrect
for the second parameter.

Here's what I have that DOESN'T work:

Set rsd1 = DSC.RecordsetDefs.AddNew("EXEC up_AvgMBByStanding " &
txtMonth.value & txtYear.value)
ChartSpace1.DataSource = DSC
ChartSpace1.DataMember = rsd1.Name

Here's what I have that DOES work:  (if I eliminate the second input
parameter)

Set rsd1 = DSC.RecordsetDefs.AddNew("EXEC up_AvgMBByStanding " &
txtMonth.value)
ChartSpace1.DataSource = DSC
ChartSpace1.DataMember = rsd1.Name

The 2 parameters are being inputted from txt boxes within my website.

 
 
 

SQL EXEC syntax errors

Post by todhsal » Tue, 07 May 2002 01:02:17


Try

Set rsd1 = DSC.RecordsetDefs.AddNew("EXEC up_AvgMBByStanding " &
txtMonth.value & ", " & txtYear.value)

Tod H Sals


Quote:> I'm creating a website that is executing a stored procedure from my
> SQL database.  I can get this to work fine if I only have one input
> parameter.  The problem though is that all of my stored procedures
> need several parameters inputted in order to execute (for example:
> entering the month and year).

> Since I can get this statement to work just fine if I eliminate the
> second input parameter I beleive that I must have the sytax incorrect
> for the second parameter.

> Here's what I have that DOESN'T work:

> Set rsd1 = DSC.RecordsetDefs.AddNew("EXEC up_AvgMBByStanding " &
> txtMonth.value & txtYear.value)
> ChartSpace1.DataSource = DSC
> ChartSpace1.DataMember = rsd1.Name

> Here's what I have that DOES work:  (if I eliminate the second input
> parameter)

> Set rsd1 = DSC.RecordsetDefs.AddNew("EXEC up_AvgMBByStanding " &
> txtMonth.value)
> ChartSpace1.DataSource = DSC
> ChartSpace1.DataMember = rsd1.Name

> The 2 parameters are being inputted from txt boxes within my website.


 
 
 

SQL EXEC syntax errors

Post by Chad Hollande » Tue, 07 May 2002 02:50:39


I tried the new code below that was suggest to me, but then I started
gettig a new "expected value" error.

New code:

Set rsd1 = DSC.RecordsetDefs.AddNew("EXEC up_AvgMBByStanding " &
txtMonth.value ", " & txtYear.value)

Error:
 Expected ')' statement

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

SQL EXEC syntax errors

Post by Erland Sommarsko » Tue, 07 May 2002 03:03:59


[posted and mailed, please reply in news]


> I'm creating a website that is executing a stored procedure from my
> SQL database.  I can get this to work fine if I only have one input
> parameter.  The problem though is that all of my stored procedures
> need several parameters inputted in order to execute (for example:
> entering the month and year).

> Since I can get this statement to work just fine if I eliminate the
> second input parameter I beleive that I must have the sytax incorrect
> for the second parameter.

> Here's what I have that DOESN'T work:

> Set rsd1 = DSC.RecordsetDefs.AddNew("EXEC up_AvgMBByStanding " &
> txtMonth.value & txtYear.value)
> ChartSpace1.DataSource = DSC
> ChartSpace1.DataMember = rsd1.Name

Unless there are trailing spaces in txtMonth.Value, the values will be
concatenated into one. If there are spaces, you will indeed get a
syntax error. You must separate the parameters with commas. That is
however only your small problem:

Quote:> The 2 parameters are being inputted from txt boxes within my website.

If a user in the txtYear box enter

   1999; DROP TABLE some_tbl

what will happen? Your web interface will actually pass a DROP TABLE
command to SQL Server, so you are only dependent that the account from
which the web server access SQL Server does not have permission to
drop tables. Or any other security-sensitive operations.

You should rewrite your application to not build SQL commands on the
fly, but use RPC calls or prepared queries. There is more than security
to gain from this. You win performance, and you are likely to get better
handling of bad input from the user. For instance if the user accidently
hits the space key in the middle of the year, he will get back an ugly
SQL error (unless you have some general handling of this).

Unfortunately, I cannot assist by describing how to write RPC calls or
prepared queries, as I am not a client programmer. But there are plenty-
ful of people who should be able to help you. Or simple search the
documentation for the client library you are using.

--
Erland Sommarskog, SQL Server MVP

Books Online (updated!) for SQL 2000 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

 
 
 

SQL EXEC syntax errors

Post by Steve Kas » Tue, 07 May 2002 04:50:29


Chad,

  It looks like you're missing an &

Steve Kass
Drew University


> I tried the new code below that was suggest to me, but then I started
> gettig a new "expected value" error.

> New code:

> Set rsd1 = DSC.RecordsetDefs.AddNew("EXEC up_AvgMBByStanding " &
> txtMonth.value ", " & txtYear.value)

> Error:
>  Expected ')' statement

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

1. syntax problem: How to EXEC within EXEC

If someone can write an easier way, please let me know!!!.

What I would like to do is read particular table names from sysobjects
and using sp_spaceused, populate the table TmpTblSpaceUsed.
I'd rather not have to open a cursor and was wondering if I could do
this using only a couple of lines.

PS. Is there also an easy way to convert the varchar(18) eg '18 KB'
results from sp_spaceused to a numeric value ... eg 18

CREATE TABLE #TmpTblSpaceUsed
 (table_name    sysname,
  nrows         int default 0,
  reserved      varchar(18),
  data          varchar(18),
  index_size    varchar(18),
  unused        varchar(18)
 )



    sysobjects.id >  50099219 '


Thanks
TwoPlusTwo

Sent via Deja.com http://www.deja.com/
Before you buy.

2. HELP!!!!!!! CRYSTAL REPORTS!

3. Error trying to use INSERT...EXEC syntax to insert values into a timestamped table

4. US-IL-ORACLE DBA (SENIOR)

5. SQL Server 6.5 EXEC(ute) Syntax?

6. How to Insert the Date of today into a table

7. Syntax error w/ no error in syntax.

8. MDStore... Setting permissions

9. SQL Exec /Cmd Exec Fails

10. Syntax problem in exec statement

11. variable in exec select - incorrect syntax

12. Exec syntax

13. EXEC Syntax Help