为何存储过程在 SQL2000查询分析器中有结果显示,而在VB 中调用无结果?以下为VB 中调用的存储过程:
1、设定参数
as_from = Format(Trim(dtp_top.Value), "yyyy-mm-dd ")
as_to = Format(Trim(dtp_end.Value), "yyyy-mm-dd")
as_supno = Left(Trim(cbo_supcust.Text), InStr(Trim(cbo_supcust.Text), "-") - 1)
as_itemcls = Left(Trim(cbo_cls.Text), InStr(Trim(cbo_cls.Text), "-") - 1)
as_branchno = Left(Trim(cbo_branch.Text), 2)
as_itemno = Trim(txt_sp.Text)2、调用存储过程 pr_jxc_itemcls_amt 把结果插入临时表
On Error Resume Next
str = "insert into pos_t_jxc_tmp EXECUTE dbo.pr_jxc_itemcls_amt ;1 @as_from ='" & as_from & "' ,@as_to ='" & as_to & "',@as_branchno ='" & as_branchno & "%',@as_supno ='" & as_supno & "%',@as_itemcls ='" & as_itemcls & "%'"
rs.Open str, connrs, 3, 23、rs 作为数据源把结果显示到表格 grid_master中
str = "SELECT stock_flag, sup_no, item_clsno, init_qty, init_amt, pi_qty, pi_amt, gi_qty, gi_amt, ro_qty, ro_amt, ioo_qty, ioo_amt, ioi_qty, ioi_amt, pf_qty, pf_amt_s, pf_amt, ri_qty, " & _
"ri_amt_s , ri_amt, pf_profit, ls_qty, ls_amt_s, ls_amt, ls_profit, jo_qty, jo_amt, ko_qty, ko_amt, check_qty, check_amt, auto_adjcost, settle_qty, settle_amt FROM pos_t_jxc_tmp "
rs.Open str, connrs, 3, 2
lastrow = 1
lastcol = 1
With grid_master
.Editable = flexEDNone
.AllowUserResizing = flexResizeBoth
.AllowUserFreezing = flexFreezeBoth
.BackColorFrozen = RGB(200, 200, 255)
.ExplorerBar = flexExMoveRows Or flexExSortShowAndMove
Set .DataSource = rs
.SelectionMode = flexSelectionByRow
End With
数据库连接正常,语句执行完毕,无结果显示,原因系 临时表p_t_jxc_tmp 中无数据,存储过程未能正确执行。
而同样的语句用在 SQL2000 查询分析器中执行 临时表中有结果,语句如下:execute dbo.pr_jxc_itemcls_amt;1 @as_from = '2011-03-18', @as_to = '2011-03-21', @as_branchno = '02%', @as_supno = '%', @as_itemcls = '0203%'不明白原因,请各位高手赐教!!!!!
不明白原因,请各位高手赐教
1、设定参数
as_from = Format(Trim(dtp_top.Value), "yyyy-mm-dd ")
as_to = Format(Trim(dtp_end.Value), "yyyy-mm-dd")
as_supno = Left(Trim(cbo_supcust.Text), InStr(Trim(cbo_supcust.Text), "-") - 1)
as_itemcls = Left(Trim(cbo_cls.Text), InStr(Trim(cbo_cls.Text), "-") - 1)
as_branchno = Left(Trim(cbo_branch.Text), 2)
as_itemno = Trim(txt_sp.Text)2、调用存储过程 pr_jxc_itemcls_amt 把结果插入临时表
On Error Resume Next
str = "insert into pos_t_jxc_tmp EXECUTE dbo.pr_jxc_itemcls_amt ;1 @as_from ='" & as_from & "' ,@as_to ='" & as_to & "',@as_branchno ='" & as_branchno & "%',@as_supno ='" & as_supno & "%',@as_itemcls ='" & as_itemcls & "%'"
rs.Open str, connrs, 3, 23、rs 作为数据源把结果显示到表格 grid_master中
str = "SELECT stock_flag, sup_no, item_clsno, init_qty, init_amt, pi_qty, pi_amt, gi_qty, gi_amt, ro_qty, ro_amt, ioo_qty, ioo_amt, ioi_qty, ioi_amt, pf_qty, pf_amt_s, pf_amt, ri_qty, " & _
"ri_amt_s , ri_amt, pf_profit, ls_qty, ls_amt_s, ls_amt, ls_profit, jo_qty, jo_amt, ko_qty, ko_amt, check_qty, check_amt, auto_adjcost, settle_qty, settle_amt FROM pos_t_jxc_tmp "
rs.Open str, connrs, 3, 2
lastrow = 1
lastcol = 1
With grid_master
.Editable = flexEDNone
.AllowUserResizing = flexResizeBoth
.AllowUserFreezing = flexFreezeBoth
.BackColorFrozen = RGB(200, 200, 255)
.ExplorerBar = flexExMoveRows Or flexExSortShowAndMove
Set .DataSource = rs
.SelectionMode = flexSelectionByRow
End With
数据库连接正常,语句执行完毕,无结果显示,原因系 临时表p_t_jxc_tmp 中无数据,存储过程未能正确执行。
而同样的语句用在 SQL2000 查询分析器中执行 临时表中有结果,语句如下:execute dbo.pr_jxc_itemcls_amt;1 @as_from = '2011-03-18', @as_to = '2011-03-21', @as_branchno = '02%', @as_supno = '%', @as_itemcls = '0203%'不明白原因,请各位高手赐教!!!!!
不明白原因,请各位高手赐教
解决方案 »
- 数据库和控件问题,急啊
- 跪求帮助!!!!高手请进!!!!
- 请教如何将datagrid中修改过的数据保存到数据库中
- 求解:vb+sql用SQL语句导出数据到mdb中,求正确语句.....
- 如何不使用Winsock控件实现两个VB程序(.exe)间的数据交换?
- dim a as long:a=18*3600 就会溢出为什么??? a=3600:a=a*18却正常.谁能解释一下吗?
- 救命啊!救命啊~~~~~~~~~~~~~~~~~~~~~
- 怎样将richtextbox中的内容存入sql server数据库?
- 怎样得到剪切板中的HTML格式的内容??
- 请kimryo(kimryo)来拿分
- 在线考试系统如何实现在一个页面中显示多种、多个题目呢?
- 【请求推荐】在CSDN上别的朋友请你“断帖”是什么意思?
str = "insert into pos_t_jxc_tmp EXECUTE dbo.pr_jxc_itemcls_amt ;1 @as_from ='" & as_from & "' ,@as_to ='" & as_to & "',@as_branchno ='" & as_branchno & "%',@as_supno ='" & as_supno & "%',@as_itemcls ='" & as_itemcls & "%'"
rs.Open str, connrs, 3, 2调用出错了........
insert into pos_t_jxc_tmp select * from (EXECUTE dbo.pr_jxc_itemcls_amt ……)A
在关键字 'EXECUTE' 附近有语法错误。
消息 170,级别 15,状态 1,第 2 行
第 2 行: ')' 附近有语法错误。
将str在查询器中运行 看错误取消 On Error Resume Next 跟踪错误
str = "insert into pos_t_jxc_tmp EXECUTE dbo.pr_jxc_itemcls_amt ;1 @as_from ='" & as_from & "' ,@as_to ='" & as_to & "',@as_branchno ='" & as_branchno & "%',@as_supno ='" & as_supno & "%',@as_itemcls ='" & as_itemcls & "%'"
rs.Open str, connrs, 3, 2
connrs.ConnectionTimeout = 0 '加上连接时间限制为零结果还是不能顺利执行,依然提示超时而同样的句子在SQL 查询分析器中可顺利通过,求助?????
connrs.CommandTimeout = 120 '加上命令时间限制为120秒
str = "insert into pos_t_jxc_tmp EXECUTE dbo.pr_jxc_itemcls_amt ;1 @as_from ='" & as_from & "' ,@as_to ='" & as_to & "',@as_branchno ='" & as_branchno & "%',@as_supno ='" & as_supno & "%',@as_itemcls ='" & as_itemcls & "%'"
debug.print str
rs.Open str, connrs, 3, 2