Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

how to use StoreProc of Sql Server 7.0 in delphi 4 application?

my storeproc is show as£º
CREATE PROCEDURE  editguzi
( @ofn    varchar(20),
  @cardno varchar(20),
  @usetime datetime,
  @num  int
)
AS
update guzi set  
       ofn=@ofn,usetime=@usetime,num=@num
where cardno=@cardno  

in DELPHI 4 application(use storeproc componnent) £ºi use some code to call the storeproc:  


with DM_guzi.sp_edit do
         begin
          //1.
          parambyname('ofn').asstring:=EditOfn.Text ;//'
          parambyname('cardno').asstring:=Editcardno.Text ;
          parambyname('usetime').asdate:=Editusetime.date;
 //or     parambyname('usetime').asstring:=DateToStr(Editusetime.date);
          parambyname('num').asstring:=Editnum.text;
         
          Prepare;
          execproc;
         end;


but when i run application ,there is error occured .the error is show as
"parameter 'ofn','cardno'... no find",but when i modify 'ofn' to '@ofn',
'cardno' to '@cardno',however the error is also occure.
when i move 'Prepare' to position '1.',the error is disappear,but the 'update '
action is not really execute.

can you tell me what should i do ,thanks.
0
qzlmq
Asked:
qzlmq
  • 4
  • 3
  • 2
  • +3
1 Solution
 
MotazCommented:
Try to use StoredProc.Refresh befor using ParamByName
0
 
umitdeCommented:
procedure name of stored procedure property must be named editguzi.
All you have to do is to delete all ';1' that are automaticaly given by delphi. The problem that appeared in the program occurs because of this ';1' not because of using '@'. But you also have to use '@' at the beginning of the paramater.
0
 
umitdeCommented:
procedure name of stored procedure property must be named editguzi.
All you have to do is to delete all ';1' that are automaticaly given by delphi. The problem that appeared in the program occurs because of this ';1' not because of using '@'. But you also have to use '@' at the beginning of the paramater.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
umitdeCommented:
procedure name of stored procedure property must be named editguzi.
All you have to do is to delete all ';1' that are automaticaly given by delphi. The problem that appeared in the program occurs because of this ';1' not because of using '@'. But you also have to use '@' at the beginning of the paramater.
0
 
DonBartholomewCommented:
Try this :

..ParamByName('@name').Value :=

You declare the parameters in the stored procedure; no need to specify the type here. AsString, AsInteger are intended for retrieving data from an active dataset.

edit1.Text := dataset1.Fields.FieldbyName('Name').AsString;
0
 
qzlmqAuthor Commented:
  if i used storeproc.refresh,the error 'can't perform this operation' must be
show.i remove ';1' and change '.asstring' to '.value' and put
'prepare' before using ParamByNmae,i would get a error as
'invalid character value for cast specification'.if no change '.asstring'
,however the error  occured.
    can you give me a succeed example?
thanks.
0
 
DonBartholomewCommented:
Mostly I use a dataset with a stored procedure. Here's an example of a stored proc alone.

with sp_Add_user do
  begin
    Parameters.ParamByName('@pers_nr').Value := srcTodo.DataSet.Fields.FieldbyName('Pnumber').Asstring;
    Parameters.ParamByName('@adm_nr').Value := datasetAdmins.Fields.Fieldbyname('Pnumber').Asstring;
    ExecProc;
  end;

If you want to use a sp with a dataset, set the command type prop to cmdStoredProc, then select the procedure in CommandText prop.

How do you connect to your database? Do you use ADO, BDE ...?
0
 
qzlmqAuthor Commented:
i use  odbc
0
 
shenqwCommented:
I use odbc,and set storedprocname is delphi' default(like testproc;1).you must set the params to correct value at design time(server times it display not correct and corret it by myself). then i use the code you give and update the database successfully
0
 
qzlmqAuthor Commented:
sorry ,i mayn't be listen clearly,how to 'corret it by myself' ,can you give me the codes? thanks!
0
 
shenqwCommented:
It maybe show wrong params.ie. Delphi add a param named '0--Return_Value' to the Parmas's dialog(In design-time).
0
 
qzlmqAuthor Commented:
sorry,i found key of the problem.
i use chinese word as parameter name,but delphi don't use chinese parameter name.when i change the parameter name from chinese  to english.
everything is ok!
because i can't write the problem in chinese ,so cause the result!sorry!
thinks for  everyone who help me.
 
0
 
simonetCommented:
Rewrite the stored procedure like this (note that there are no "(" and ")" in there):

CREATE PROCEDURE  editguzi
   @ofn    varchar(20),
   @cardno varchar(20),
   @usetime datetime,
   @num  int
 
AS
set nocount on
update guzi set ofn=@ofn, usetime=@usetime, num=@num
where cardno=@cardno  


Then in the TStoredProc component, set the databasename accordingly, then the storeprocedure name and then click on the ellipsis in the Parameters property. All the 4 parameters will be displayed.

You need now to set the DIRECTION and DATA TYPE of each parameter.

NOTE: don't change the info for the @RETURN_VALUE parameter.

All the four parameters are Input. The datatypes are string for the first 2 and datetime and integer for the other 2.

Then , in the Delphi code that calls it, pay attention to the data types of each parameter, and call it like this:

with DM_guzi.sp_edit do
         begin
          //1.
          parambyname('ofn').asstring:=EditOfn.Text ;//'
          parambyname('cardno').asstring:=Editcardno.Text ;
          parambyname('usetime').asdate:=Editusetime.date;
 //or     parambyname('usetime').asdatetime:=Editusetime.date;
          parambyname('num').asinteger:= strtoint(Editnum.text);
         
          Prepare;
          execproc;
         end;



*****************
yours,

Alex

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now