[lnkForumImage]
TotalShareware - Download Free Software

Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.dotnet.framework.odbcnet

Re: Parameterized Stored Procedure Error

JC

3/2/2005 7:30:00 PM

Hi:

I've had the same problem but I couldn't fix it. The exception message is:

{"ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Línea 1:
incorrect syntax near 'DisableFW'." }."

I have a stored procedure called "DisableFW" and I'm invoking it like this:

******************
connection = new OdbcConnection(Utils.GetConnectionString());

DisableFW (?,?,?,?)
sql = "DisableFW (?,?,?,?)";
cmd = new OdbcCommand(sql, connection);

OdbcParameter odbcParam;
odbcParam = cmd.Parameters.Add("@FWName", firmwareName);
odbcParam = cmd.Parameters.Add("@NewFWName", newFWName);
odbcParam = cmd.Parameters.Add("@NewFilePath", newFilePath);
odbcParam = cmd.Parameters.Add("@RetValue", OdbcType.Int);
odbcParam.Direction = ParameterDirection.Output;

cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
connection.Close();

******************
I tried using "CALL DisableFW(?,?,?,?)" and it doesn't work also. When using
"EXEC DisableFW(..)" the "incorrect syntax" is apparently near "@P1", but I
don't know what that is. Is that the first parameter?

Thanks for your help,
Juan Carlos


"Martin McNally" <martinmcnally@gmail.com> escribió en el mensaje
news:O2Rbvx4DFHA.3416@TK2MSFTNGP09.phx.gbl...
> Solved it my self CommandText requires question marks in the call for each
> parameter
>
> OdbcCommand dbCommand = new OdbcCommand("USA.\"t\" (?)", dbConnection);
>
> Martin
>
>
> "Martin McNally" <martinmcnally@gmail.com> wrote in message
> news:af62bd66.0502100645.344706d8@posting.google.com...
> >I am getting weird results trying to run a stored procedure with
> > parameters using the Microsoft.Odbc.Data .net Data provider connecting
> > to Sybase ASA 8.02
> >
> > It works fine if you do not have parameters on the procedure but when
> > you do the following error occurs:
> >
> > ERROR [42000] [Sybase][ODBC Driver][Adaptive Server Anywhere ] Syntax
> > error or access violation: Wrong number of parameters to function 't'
> >
> > Here is the procedure:
> >
> > ALTER PROCEDURE USA."t"
> > (
> > @CCUSAID char(1)
> > )
> > AS
> > BEGIN
> > PRINT 'hello'
> > END
> >
> > And here is the code I am using to access it:
> >
> > //create connection and command
> > OdbcConnection dbConnection = new OdbcConnection("DSN=CCUSA");
> > OdbcCommand dbCommand = new OdbcCommand("USA.\"t\"", dbConnection);
> > dbCommand.CommandType = CommandType.StoredProcedure;
> >
> > //add the parameter
> > dbCommand.Parameters.Add("@CCUSAID", "d");
> >
> > //open run close
> > dbConnection.Open();
> > dbCommand.ExecuteNonQuery();
> > dbConnection.Close();
> >
> > I have also tried specifying all the parameter options with no avail.
> > The ODBC connection is setup with DSN as CCUSA and is logging into the
> > database using the Administrator account.
> >
> > Any help or light on this would be much appreciated.
>
>


3 Answers

JC

3/2/2005 10:25:00 PM

0

Problem solved.
This URL helped:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/c...
frlrfsystemdataodbcodbccommandclasscommandtexttopic.asp

The error was the missing "{" and "}" before and after the TSQL string, and
the "CALL" sentence.
The final TSQL sentence was something like:

"? = CALL DisableFW(''value1'', ''value2'', ''value3'', ?)"


Juan Carlos

"JC" <jcsc@adinet.com.uy> escribi&#243; en el mensaje
news:uT2IuY1HFHA.3628@TK2MSFTNGP15.phx.gbl...
> Hi:
>
> I''ve had the same problem but I couldn''t fix it. The exception message is:
>
> {"ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]L&#237;nea 1:
> incorrect syntax near ''DisableFW''." }."
>
> I have a stored procedure called "DisableFW" and I''m invoking it like
this:
>
> ******************
> connection = new OdbcConnection(Utils.GetConnectionString());
>
> DisableFW (?,?,?,?)
> sql = "DisableFW (?,?,?,?)";
> cmd = new OdbcCommand(sql, connection);
>
> OdbcParameter odbcParam;
> odbcParam = cmd.Parameters.Add("@FWName", firmwareName);
> odbcParam = cmd.Parameters.Add("@NewFWName", newFWName);
> odbcParam = cmd.Parameters.Add("@NewFilePath", newFilePath);
> odbcParam = cmd.Parameters.Add("@RetValue", OdbcType.Int);
> odbcParam.Direction = ParameterDirection.Output;
>
> cmd.CommandType = System.Data.CommandType.StoredProcedure;
> cmd.ExecuteNonQuery();
> connection.Close();
>
> ******************
> I tried using "CALL DisableFW(?,?,?,?)" and it doesn''t work also. When
using
> "EXEC DisableFW(..)" the "incorrect syntax" is apparently near "@P1", but
I
> don''t know what that is. Is that the first parameter?
>
> Thanks for your help,
> Juan Carlos
>
>
> "Martin McNally" <martinmcnally@gmail.com> escribi&#243; en el mensaje
> news:O2Rbvx4DFHA.3416@TK2MSFTNGP09.phx.gbl...
> > Solved it my self CommandText requires question marks in the call for
each
> > parameter
> >
> > OdbcCommand dbCommand = new OdbcCommand("USA.\"t\" (?)", dbConnection);
> >
> > Martin
> >
> >
> > "Martin McNally" <martinmcnally@gmail.com> wrote in message
> > news:af62bd66.0502100645.344706d8@posting.google.com...
> > >I am getting weird results trying to run a stored procedure with
> > > parameters using the Microsoft.Odbc.Data .net Data provider connecting
> > > to Sybase ASA 8.02
> > >
> > > It works fine if you do not have parameters on the procedure but when
> > > you do the following error occurs:
> > >
> > > ERROR [42000] [Sybase][ODBC Driver][Adaptive Server Anywhere ] Syntax
> > > error or access violation: Wrong number of parameters to function ''t''
> > >
> > > Here is the procedure:
> > >
> > > ALTER PROCEDURE USA."t"
> > > (
> > > @CCUSAID char(1)
> > > )
> > > AS
> > > BEGIN
> > > PRINT ''hello''
> > > END
> > >
> > > And here is the code I am using to access it:
> > >
> > > //create connection and command
> > > OdbcConnection dbConnection = new OdbcConnection("DSN=CCUSA");
> > > OdbcCommand dbCommand = new OdbcCommand("USA.\"t\"", dbConnection);
> > > dbCommand.CommandType = CommandType.StoredProcedure;
> > >
> > > //add the parameter
> > > dbCommand.Parameters.Add("@CCUSAID", "d");
> > >
> > > //open run close
> > > dbConnection.Open();
> > > dbCommand.ExecuteNonQuery();
> > > dbConnection.Close();
> > >
> > > I have also tried specifying all the parameter options with no avail.
> > > The ODBC connection is setup with DSN as CCUSA and is logging into the
> > > database using the Administrator account.
> > >
> > > Any help or light on this would be much appreciated.
> >
> >
>
>


JC

3/3/2005 1:43:00 PM

0

Sorry, the sentence was something like (I forgot again the "brackets"):

"{? = CALL DisableFW(''value1'', ''value2'', ''value3'', ?)}"

And the parameters were set like this:

int resultParam = 0;
int resultSP = 0;
odbcParam = cmd.Parameters.Add("@RetValue", resultParam);
odbcParam.Direction = ParameterDirection.Output;
odbcParam = cmd.Parameters.Add("@RetValue2", resultSP);
odbcParam.Direction = ParameterDirection.ReturnValue;

I hope this helps somebody because I spent some time with this.

Juan Carlos

"JC" <jcsc@adinet.com.uy> escribi&#243; en el mensaje
news:O0ApV62HFHA.4032@TK2MSFTNGP12.phx.gbl...
> Problem solved.
> This URL helped:
>
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/c...
> frlrfsystemdataodbcodbccommandclasscommandtexttopic.asp
>
> The error was the missing "{" and "}" before and after the TSQL string,
and
> the "CALL" sentence.
> The final TSQL sentence was something like:
>
> "? = CALL DisableFW(''value1'', ''value2'', ''value3'', ?)"
>
>
> Juan Carlos
>
> "JC" <jcsc@adinet.com.uy> escribi&#243; en el mensaje
> news:uT2IuY1HFHA.3628@TK2MSFTNGP15.phx.gbl...
> > Hi:
> >
> > I''ve had the same problem but I couldn''t fix it. The exception message
is:
> >
> > {"ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]L&#237;nea 1:
> > incorrect syntax near ''DisableFW''." }."
> >
> > I have a stored procedure called "DisableFW" and I''m invoking it like
> this:
> >
> > ******************
> > connection = new OdbcConnection(Utils.GetConnectionString());
> >
> > DisableFW (?,?,?,?)
> > sql = "DisableFW (?,?,?,?)";
> > cmd = new OdbcCommand(sql, connection);
> >
> > OdbcParameter odbcParam;
> > odbcParam = cmd.Parameters.Add("@FWName", firmwareName);
> > odbcParam = cmd.Parameters.Add("@NewFWName", newFWName);
> > odbcParam = cmd.Parameters.Add("@NewFilePath", newFilePath);
> > odbcParam = cmd.Parameters.Add("@RetValue", OdbcType.Int);
> > odbcParam.Direction = ParameterDirection.Output;
> >
> > cmd.CommandType = System.Data.CommandType.StoredProcedure;
> > cmd.ExecuteNonQuery();
> > connection.Close();
> >
> > ******************
> > I tried using "CALL DisableFW(?,?,?,?)" and it doesn''t work also. When
> using
> > "EXEC DisableFW(..)" the "incorrect syntax" is apparently near "@P1",
but
> I
> > don''t know what that is. Is that the first parameter?
> >
> > Thanks for your help,
> > Juan Carlos
> >
> >
> > "Martin McNally" <martinmcnally@gmail.com> escribi&#243; en el mensaje
> > news:O2Rbvx4DFHA.3416@TK2MSFTNGP09.phx.gbl...
> > > Solved it my self CommandText requires question marks in the call for
> each
> > > parameter
> > >
> > > OdbcCommand dbCommand = new OdbcCommand("USA.\"t\" (?)",
dbConnection);
> > >
> > > Martin
> > >
> > >
> > > "Martin McNally" <martinmcnally@gmail.com> wrote in message
> > > news:af62bd66.0502100645.344706d8@posting.google.com...
> > > >I am getting weird results trying to run a stored procedure with
> > > > parameters using the Microsoft.Odbc.Data .net Data provider
connecting
> > > > to Sybase ASA 8.02
> > > >
> > > > It works fine if you do not have parameters on the procedure but
when
> > > > you do the following error occurs:
> > > >
> > > > ERROR [42000] [Sybase][ODBC Driver][Adaptive Server Anywhere ]
Syntax
> > > > error or access violation: Wrong number of parameters to function
''t''
> > > >
> > > > Here is the procedure:
> > > >
> > > > ALTER PROCEDURE USA."t"
> > > > (
> > > > @CCUSAID char(1)
> > > > )
> > > > AS
> > > > BEGIN
> > > > PRINT ''hello''
> > > > END
> > > >
> > > > And here is the code I am using to access it:
> > > >
> > > > //create connection and command
> > > > OdbcConnection dbConnection = new OdbcConnection("DSN=CCUSA");
> > > > OdbcCommand dbCommand = new OdbcCommand("USA.\"t\"", dbConnection);
> > > > dbCommand.CommandType = CommandType.StoredProcedure;
> > > >
> > > > //add the parameter
> > > > dbCommand.Parameters.Add("@CCUSAID", "d");
> > > >
> > > > //open run close
> > > > dbConnection.Open();
> > > > dbCommand.ExecuteNonQuery();
> > > > dbConnection.Close();
> > > >
> > > > I have also tried specifying all the parameter options with no
avail.
> > > > The ODBC connection is setup with DSN as CCUSA and is logging into
the
> > > > database using the Administrator account.
> > > >
> > > > Any help or light on this would be much appreciated.
> > >
> > >
> >
> >
>
>


Malachias Invictus

11/19/2007 5:11:00 AM

0


"Keith Davies" <keith.davies@kjdavies.org> wrote in message
news:slrnfkf7nn.11u.keith.davies@kjdavies.org...
> Malachias Invictus <invictusebay@yahoo.com> wrote:
>> "Keith Davies" <keith.davies@kjdavies.org> wrote:
>>> Malachias Invictus <invictusebay@yahoo.com> wrote:
>>>> "Keith Davies" <keith.davies@kjdavies.org> wrote

>>>>> 4e, what I've seen so far doesn't always appeal to me.

>>>> Agreed. However, most of it does so far. I like Mearls' style, so
>>>> this edition should mostly fit my taste. On the other hand, improving
>>>> something that is already as good as 3.5E carries some potential
>>>> pitfalls. In fact, we may be approaching a point of diminishing
>>>> returns regarding publishing new editions.

>>> Unless, as it looks like they're *trying* to do, a shift in how things
>>> are done.

>> I think there is that, as well. Even given that, you can reach a
>> point of diminishing returns (e.g., fixing things that are not broken,
>> making "improvements" that are inferior to the methods used before the
>> change, etc.).

> Right now, yes. Unless, as I said, there's a 'change in technology'
> that gets a big gain in effectiveness/playability/pleasure. I have
> difficulty seeing how that could be, without changing to a different
> game entirely, but that's usually the case when these things happen.

Yeah, that is what I think as well.

>>> 1e -> 2e was an example of the former. It tidied things up, offered
>>> a few more options (thief skills could be built up as desired, rather
>>> than all thieves of the same level having the same skill levels). It
>>> was still damn near the same game, though, including many of the
>>> annoying warts. Better, but not a *lot* better.
>>
>> Agreed. Also, they took things out (e.g., the Monk) that I feel
>> enhanced the game.
>
> Well... I don't particularly *like* the monk as it's presented. But
> that's mostly because
>
> 1. it brings a lot of baggage
> 2. it is better at unarmed-and-unarmored combat than a fighter *can* be
>
> I'd rather see swordsage than monk.

You already know I am in full agreement with that.

> Or a martial artist (without the
> mystical baggage) that leads to a PrC that can take you into the mystic
> stuff. Or even -- or to be honest, especially -- fighter options that
> can let him be the best non-mystical martial artist (which may lead to
> the mystical stuff via PrC, feats, etc.
>
> 'Unarmed martial artist' is a good archetype. I don't think it belongs
> as a class with as much baggage as the monk has.

Once again, full agreement. Ideally I would like to see the current Monk
class buildable by exercising specific character creation choices, but I
want some variety.

>>> 2e -> 3e, a big shift. Dump a lot of the cruft and crap, arrange
>>> things for better ease of play (dumping THAC0 for BAB, making
>>> multiclassing *way* better, and more consistent between races).
>>> Mathematically it was still quite close to the original (a Ftr10 has
>>> about the same base chance to hit someone wearing a suit of
>>> chainmail, /fireball/ does the normal damage, etc.). Overall, a huge
>>> step up on 2e... while still 'being D&D'.
>>
>> Sure. Then again, I don't think they went quite far enough with the
>> execution of sacred cows.
>
> When I first read about PrCs, paladin and ranger were up for the chop.
> Barbarian and druid weren't far behind them. Bard... I wasn't so sure
> about this one, I didn't see how it could really be done well at first,
> but now I'd make it a PrC too.
>
> But hey, I'm sure you're familiar with where this is going.

Heh. Yep.

>>> 3e -> 4e, I think they *want* to repeat the size of jump they had from
>>> 2e -> 3e.
>>
>> I am still thinking about that. I think what would benefit the game
>> the most is a change between 50%-75% of the size of the 2E -> 3E
>> change. That is just personal preference, of course. The game is
>> perfectly serviceable as-is, especially with a few house rules.
>
> I'm not sure it needs to be that big.

You are probably right.

> Again, though, I'm pretty happy with where d20 is. Most of my changes
> are around organization and application, rather than redesigning rules.

Sure. The redesigns you tend to favor generally match my preferences as
well.

>>> I don't know that they'll succeed because 3.x is damn near
>>> what I want out of D&D.
>>
>> I actually agree with that. If they could take the best of Iron
>> Heroes and seamlessly integrate it into D&D, then tweak a few other
>> things, I think it would be just right.
>
> Odd you should say that. I'm grabbing goodies out of Iron Heroes skill
> applications and feats right now. Saga skills + Iron Heroes challenges
> (probably using 'training' and maybe 'focus' to govern access to special
> bits, since ranks go away), possibly incorporate 'skill tricks' from
> Complete Scoundrel ("Training gives you one, Focus gives you one" maybe)
> and I think there's some possible goodness here. I'm trying to get
> something written up and into the wiki (and posted here) this weekend.

Nice.

> (and you forgot 'mix in some Bo9S', an oversight I'm sure)

It is part of D&D, no? ;-)

>>> Believe it or not, considering how many posts
>>> I've made about tweaking the game, but it's more or less true.
>>
>> Dude, you are a gearhead. You are not going to be happy if there is
>> nothing to tinker with.
>
> Some truth there. Even if it was perfect at some point, my definition
> of perfect shifts.

I am right with you.

>>> Skills? From SWS; had I looked there I probably would've snaffled
>>> them a while ago.
>>
>> Yeah. As much as I love the Iron Heroes system, I have to say that
>> Saga is damned elegant.
>
> More than Iron Heroes, I think. IH skills (the skill groups, one point
> gives a rank in a bunch of related skills[1]) was an attempt to get
> around the skill points problem in core rules. Still a lot of tracking.
>
> Saga's elegance gets rid of the concern. You can be halfway competent
> at 'everyadventurer' tasks for free,

....and *that* is one of the major fixes the game needs.

> then actually get *good* at things
> from there. You're likely to build up the bits that fit your character
> design but you can't entirely neglect things.
>
> As I said, mix in some skill challenges and make 'training' give you
> something (more than just +5 on the roll), and possibly have 'focus'
> give you something similarly good, and you've got some joy happening.

Exactly. I especially like the idea of multiple skill levels
(Untrained/Trained/Skill Focus) to give skillmonkeys some juicy abilities.

> For instance, I might do:
>
> Spot + Listen = Notice. Search is a full-round action instead of a free
> action, Notice + 5. Training gets you +5 to all Notice checks, and
> (possibly, I haven't decided) Trapfinding.
>
> Similarly, I might fold Open Locks and Disarm Device into a single skill
> (impractical to use without proper training though, the DCs are usually
> too high). Training gets you the ability to disable the hard traps (and
> is needed for practical purposes anyway; see 'too damn hard').
>
> Either way, any adventurer is likely to be *passable* at these things,
> eventually, but will often be outclassed by lower-level characters who
> actually focus on these things.
>
> Add in challenges, and... yeah, I'm repeating myself. It's been a long
> week and I'm short on sleep.

Still, well put all around.

> [1] yes, I know you know IH skill rules, I'm just saying which bit I'm
> referring to here

Understood.

>>> Dumbing down monsters, it sounds like they want to make them "set of
>>> stats appropriate for the level, plus one trick"... makes them easy
>>> to play, but it doesn't grab me.
>>
>> I really want to see the implementation, because I get a totally
>> different impression. I actually really like the idea of "set of
>> stats plus appropriate tricks" being easy to assess for power level,
>> because I love to make my own monsters. I did that a lot in 1E & 2E,
>> but not so much in 3E & 3.5E.
>
> It might be okay, it may make monster creation easier... but it doesn't
> leap out at me as a Great Thing.

If it is similar to what I think it is going to be, it will be a definite
Great Thing for me. I really like creating new monsters, and I really did
not like the tools offered for this in 3E/3.5E.

> Not the way Saga-type skills do (especially with IH chall... *record
> skips*), not the way multiclassing in 3e did, not the way saves were
> changed in 3e did, not the way BAB + increasing AC in 3e did, etc.

;-)

>>> Still, "this is *your* trick, and *you* can do *this*, and *this* is
>>> something just for *you*"... meh.
>>
>> I like it. I prefer unique abilities over spell-likes. There is
>> still room for other special abilities that are mostly used
>> out-of-combat, though, and I hope they keep those for the more
>> powerful creatures (e.g., demons, devils, etc.).
>
> Well... one thing about everything having unique abilities is that I
> think they probably *shouldn't*. I think there should be abilities
> common between similar creatures. All demons might be able to do
> certain things, all dragons will have some similarities, and so on.

That may still be the case.

> What I've seen so far suggests that they will be limited to *one* thing,
> *unique* to them. This doesn't seem right to me.

It may be that each thing *does* have at least one unique thing, but that
does not eliminate the possibility of other similar or identical
abilities/powers.

> Meh. Six months until I have it in hand. No rush.

Exactly. My soon-to-be Half-Fey Whisper Gnome Warlock has a lot of playing
to do before then, and hopefully he will end up with some Hellfire Warlock
levels.

>>> And I don't much like what I see about the class rearrangement...
>>
>> I actually like that quite a bit. Defining a class by what they *do*
>> is a big thing in Iron Heroes, and it looks like what they are doing
>> here. I found that it worked well in that system, and I think it can
>> work even better in 4E.
>
> I can't argue against that, given the work I've put into my class
> framework. I've just split things up on different lines, and WotC has
> arguably gone *more* abstract than I have.
>
> However, as a result it seems even *more* structured than I had planned.
> Almost as if "you probably want to fill all four positions on this axis,
> and all four on that axis; how you do it is up to you". Admittedly,
> this does strike me as a nice thing from an engineering standpoint...
> but I don't know that I want to try to *engineer* fantasy.

I know what you mean.

>>> Same with the weapon abilities and whatnot.
>>
>> This is another Iron Heroes thing, with special moves only available
>> to appropriate weapons. I really enjoyed the flavor, and I hope the
>> implementation for 4E is good.
>
> I think here it's how it was presented. Different weapons with outright
> different abilities *does* appeal to me. I can't say otherwise, given
> how much I've posted on the matter, without embarassing myself. I think
> it's just a bit of crankiness based on how it was presented -- which
> was, AFAICT, somewhat obscured, deliberately.

I think it will be good. Mearls had great ideas along those lines,
including stuff that never made it into Iron Heroes.

>>> *cough* Anyway. I'm holding off final judgment about 4e until I
>>> actually see it. I will say that 3e excited me a lot more than 4e
>>> does.
>>
>> Given that I had switched to GURPS prior to the coming of 3E, I have
>> to agree. I am not looking forward to revamping my carefully crafted
>> freakshow menagerie of characters for 4E, either.
>
> That could be a lot of work. Thankfully I'm a lazy sod and rarely
> bothered writing them up mechanically.

It is more about the fact that the selection of classes and races is going
to shrink again. That is going to be a pain.

--
^v^v^Malachias Invictus^v^v^

It matters not how strait the gate,
How charged with punishment the scroll,
I am the Master of my fate:
I am the Captain of my soul.

from _Invictus_, by William Ernest Henley