调用存储过程.

Question

exec sp_executesql N'torder_Add', N'@torderid bigint,@orderno nvarchar(18),@orderuserid nvarchar(8),@ordefor int,@orderway int,@orderfrmcmy nvarchar(3),@ordertocmy nvarchar(3),@countmoney real,@isapplied bigint,@applyno nvarchar(4000),@applytime nvarchar(4000),@ischecked bigint,@checktime nvarchar(4000),@issended bigint,@sendtime nvarchar(4000),@enabled int,@ordertime nvarchar(9),@re nvarchar(5)',
@torderid = default, @orderno = N'ON2011082309192123', @orderuserid = N'zhangsan', @ordefor = 1, @orderway = 1, @orderfrmcmy = N'001', 
@ordertocmy = N'001', @countmoney = 7.770000000000000e+002, @isapplied = default, @applyno = N'', @applytime = default, @ischecked = default, 
@checktime = default, @issended = default, @sendtime = default, @enabled = 1, @ordertime = N'getdate()', @re = N'77777'这句提示服务器: 消息 170，级别 15，状态 1，行 5
第 1行: 'torder_Add' 附近有语法错误。
请高手看一下,调用是在c#中调用的
方法如下://add new order
        string OrderNo = "ON" + DateTime.Now.ToString("yyyyMMddHHmmssff");//
        string OrderUserId = Convert.ToString(HttpContext.Current.Session["akusername"]);//
        int OrderFor = ddlapplyfor.SelectedIndex + 1;
        int OrderWay = ddlapplyway.SelectedIndex + 1;
        string OrderFrmCmy = Convert.ToString(aksql.GetScalar("select belongcmy from tuser where userid = '" + OrderUserId + "'"));
        string OrderToCmy = Convert.ToString(aksql.GetScalar("select upcmyid from tcompany where cmyid = '" + OrderFrmCmy + "'"));
        float CountMoney = Convert.ToSingle(txtmoney.Text);
        string ReMark = txtre.Text;
        
        SqlParameter[] parms = {
            new SqlParameter("@torderid",0),
            new SqlParameter("@orderno",OrderNo),
            new SqlParameter("@orderuserid",OrderUserId),
            new SqlParameter("@ordefor",OrderFor),
            new SqlParameter("@orderway",OrderWay),
            new SqlParameter("@orderfrmcmy",OrderFrmCmy),
            new SqlParameter("@ordertocmy",OrderFrmCmy),
            new SqlParameter("@countmoney",CountMoney),
            new SqlParameter("@isapplied",0),
            new SqlParameter("@applyno",""),
            new SqlParameter("@applytime",null),
            new SqlParameter("@ischecked",0),
            new SqlParameter("@checktime",null),
            new SqlParameter("@issended",0),
            new SqlParameter("@sendtime",null),
            new SqlParameter("@enabled",1),
            new SqlParameter("@ordertime","getdate()"),
            new SqlParameter("@re",ReMark)
        };
        ADOX.ExecuteNonQuery("torder_Add", CommandType.Text, parms);
-------------adox---------------
public static void ExecuteNonQuery(string cmdText, CommandType cmdType, SqlParameter[] pars)
        {
            SqlCommand cmd = new SqlCommand();            using (SqlConnection conn = new SqlConnection(ConSqlServerStr))
            {
                try
                {
                    // if (conn.State != ConnectionState.Open)
                    conn.Open();                    cmd.Connection = conn;
                    cmd.CommandText = cmdText;
                    cmd.CommandType = cmdType;                    if (pars != null)
                    {
                        foreach (SqlParameter parm in pars)
                            cmd.Parameters.Add(parm);
                    }                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
                catch (ConfigurationErrorsException ex)
                {   //关闭连接，抛出异常
                    conn.Close();
                    //throw;
                    throw ex;
                    
                }
            }
        }

Answer 1

存储过程torder_Add/*
用途：增加、更新一条记录
使用方法：0： 增加 ；大于0：更新
时间：08 18 2011  5:09PM
*/
CREATE PROCEDURE torder_Add@torderid int,
@orderno varchar(50),
@orderuserid varchar(50),
@orderfor int,
@orderway int,
@orderfrmcmy varchar(50),
@ordertocmy varchar(50),
@countmoney float,
@isapplied int,
@applyno varchar(50),
@applytime datetime,
@ischecked int,
@checktime datetime,
@issended int,
@sendtime datetime,
@enabled int,
@ordertime datetime,
@re varchar(50),
@id_out  INT OUTPUT AS
IF(@torderid=0)
BEGIN INSERT INTO torder ( 
[orderno],[orderuserid],[orderfor],[orderway],[orderfrmcmy],[ordertocmy],[countmoney],[isapplied],[applyno],[applytime],[ischecked],[checktime],[issended],[sendtime],[enabled],[ordertime],[re]
 ) VALUES ( 
@orderno,@orderuserid,@orderfor,@orderway,@orderfrmcmy,@ordertocmy,@countmoney,@isapplied,@applyno,@applytime,@ischecked,@checktime,@issended,@sendtime,@enabled,@ordertime,@re ) 
 SET @id_out = @@IDENTITY 
 END
ELSE
BEGIN UPDATE torder SET 
[orderno] = @orderno,[orderuserid] = @orderuserid,[orderfor] = @orderfor,[orderway] = @orderway,[orderfrmcmy] = @orderfrmcmy,[ordertocmy] = @ordertocmy,[countmoney] = @countmoney,[isapplied] = @isapplied,[applyno] = @applyno,[applytime] = @applytime,[ischecked] = @ischecked,[checktime] = @checktime,[issended] = @issended,[sendtime] = @sendtime,[enabled] = @enabled,[ordertime] = @ordertime,[re] = @re
 WHERE [torderid] = @torderid
 SET @id_out = @@ROWCOUNT 
 END
GO
表torderif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[torder]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[torder]
GOCREATE TABLE [dbo].[torder] (
[torderid] [int] IDENTITY (1, 1) NOT NULL ,
[orderno] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[orderuserid] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[orderfor] [int] NULL ,
[orderway] [int] NULL ,
[orderfrmcmy] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[ordertocmy] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[countmoney] [float] NULL ,
[isapplied] [int] NULL ,
[applyno] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[applytime] [datetime] NULL ,
[ischecked] [int] NULL ,
[checktime] [datetime] NULL ,
[issended] [int] NULL ,
[sendtime] [datetime] NULL ,
[enabled] [int] NULL ,
[ordertime] [datetime] NULL ,
[re] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

Answer 2

exec sp_executesql N'torder_Add',楼主这是做什么，调用自己新建的存储过程是：exec 存储过程名 参数1,参数2,...

Answer 3

没太看懂你sp_executesql掉用的是什么存储过程。

Answer 4

exec sp_executesql torder_Add 参数1，参数2.。。

Answer 5

不看函数，看你SQL调用的，调用时是exec torder_Add '','','','',...

Answer 6

C#中的问题建议去 .NET问问

Answer 7

CREATE PROCEDURE torder_Add
(
@torderid int,
@orderno varchar(50),
@orderuserid varchar(50),
@orderfor int,
@orderway int,
@orderfrmcmy varchar(50),
@ordertocmy varchar(50),
@countmoney float,
@isapplied int,
@applyno varchar(50),
@applytime datetime,
@ischecked int,
@checktime datetime,
@issended int,
@sendtime datetime,
@enabled int,
@ordertime datetime,
@re varchar(50),
@id_out  INT OUTPUT 
)
AS

Answer 8

exec sp_executesql N'EXECUTE ProcName @P1, @P2', ---ProcName存儲過程名字和參數
N'@P1 varchar(10),@P2 varchar(10)',    ---參數類型
'ABC','ABC'   ---各參數值--或
exec ProcName 'ABC','ABC'

Answer 9

唉.晓得了.调用的时候commandtype.type,应该设置为StoredProcedure..就这么简单.

Answer 10

去修改存储过程，话说你把当前时间存到哪里？alter proc proname
你的所有参数,
@new datetime
as......insert into date select @new

Answer 11

 数据库有函数getdate()，获取当前时间

Answer 12

直接在存储过程中使用getdate即可

Answer 13

ADOX.ExecuteNonQuery("torder_Add", CommandType.Text, parms);  
ADOX.ExecuteNonQuery("torder_Add", CommandType.StoredProcedure, parms); 

Answer 14

恩.感谢大家.
再问一下.
--------------------------------------
这个存储过程的@id_out INT OUTPUT,在存储过程中是INSERT INTO torder ( 
        [orderno],[orderuserid],[orderfor],[orderway],[orderfrmcmy],[ordertocmy],[countmoney],[isapplied],[applyno],[applytime],[ischecked],[checktime],[issended],[sendtime],[enabled],[ordertime],[re]
         ) VALUES ( 
        @orderno,@orderuserid,@orderfor,@orderway,@orderfrmcmy,@ordertocmy,@countmoney,@isapplied,@applyno,@applytime,@ischecked,@checktime,@issended,@sendtime,@enabled,@ordertime,@re ) 
         SET @id_out = @@IDENTITY 
在c#中.该如何获得此参数?
我这样写的
参数中:new SqlParameter("@id_out",Convert.ToInt32 (0))
然后这样调用:        int id_out = 0;//return id_out
        id_out = Convert.ToInt32(ADOX.ExecuteOutput("torder_Add", "id_out", parms));程序报错.说
此 SqlParameterCollection 中未包含带有 ParameterName“id_out”的 SqlParameter。

Answer 15

                        
cmd.Parameters[parameterName].Direction = ParameterDirection.Output;
cmd执行后
                        
id_out =cmd.Parameters[parameterName];

Answer 16

id_out = Convert.ToInt32(ADOX.ExecuteOutput("torder_Add", "@id_out", parms));

Answer 17

晓得了.
这样就可以
parms[18].Direction = ParameterDirection.Output;