7,763
社区成员
发帖
与我相关
我的任务
分享sql语句问题
过滤条件 FCategory ='ic' and FKey ='currentperiod'和 FCategory ='ic' and FKey ='currentyear'
想要结果为2014年8月 然后在vb里直接引用这个期间
...全文
请发表友善的回复…
发表回复
lz_xiaohai 2018-06-01
- 打赏
- 举报
Private Function Sub_SaveBill() As Boolean '保 存 单 据
Dim rstemp As New ADODB.Recordset
Dim fdate As String
Dim id As String
If Text1(0).Text = "" Then MsgBox ("车牌号没有输入"): Exit Function
If Val(Text1(4).Text) = 0 Then MsgBox ("重量不能为零"): Exit Function
Set rstemp = ExecuteSQL("select 1 from z_bill where 车牌号='" & Text1(0).Text & "' and isnull(制单人2,'')=''", "")
If rstemp.EOF = False Then
MsgBox ("该车牌号已存在记录，不能重复称重")
Exit Function
End If
conn.BeginTrans
On Error GoTo err1
Set rstemp = conn.Execute("exec sp_getbillnumno '" & Date & "','1'")
fbillno = rstemp.Fields(0).Value
conn.Execute "insert into z_bill(单据号,公司,品名,供应商,车牌号,进厂,出厂 ,净重 ,制单人1 ,制单日期1 ,型号) values('" _
& fbillno & "','" & Combo1(2).Text & "','" & Combo1(0).Text & "','" & Combo1(1).Text & "','" & Text1(0).Text & "','" & Val(Text1(4).Text) & "',0,0,'" & LoginName & "',getdate(),'" & Text1(2).Text & "')"
conn.CommitTrans
MsgBox "保存成功"
'Select Case Combo2.Text
'Case "中文合格证"
'Unload frpt1
'With frpt1.adors
'.Connection = conn
'.Source = "select * from V_合格证 Where 条码='" & Text1(9).Text & "'"
'End With
'frpt1.PrintReport False
'Case "COVEME合格证"
'Unload frpt2
'With frpt2.adors
'.Connection = conn
'.Source = "select * from V_合格证 Where 条码='" & Text1(9).Text & "'"
'End With
'frpt2.PrintReport False
'Case "英文合格证"
'Unload frpt3
'With frpt3.adors
'.Connection = conn
'.Source = "select * from V_合格证 Where 条码='" & Text1(9).Text & "'"
'End With
'frpt3.PrintReport False
'End Select
Call Sub_OperStatus("10")
'Text1(4).Text = ""
Sub_SaveBill = True
MSComm1.InBufferCount = 0
MSComm1.OutBufferCount = 0
Exit Function
err1:
MsgBox Err.Description
conn.RollbackTrans
End Function
他这一段代码不用 cnn.open 吗？为什么
lz_xiaohai 2018-06-01
- 打赏
- 举报
select t1.FValue + '年' + t2.FValue + '月' as fname
from t_SystemProfile t1
inner join t_SystemProfile t2 on t1.FCategory =t2.FCategory
where t1.FCategory ='ic' and t1.FKey ='currentyear' and t2.FKey ='currentperiod'
脆皮大雪糕 2018-05-31
- 打赏
- 举报
select t1.FValue || '年' || t2.FValue || '月'
from
tablename as t1,
tablename as t2
where t1.FCategory = t2.FCategory
and t1.FKey ='currentyear'
and t2.FKey ='currentperiod'
脆皮大雪糕 2018-05-31
- 打赏
- 举报
哦，是sqlserver 把|| 改成+
lz_xiaohai 2018-05-31
- 打赏
- 举报
if exists(select * From sysobjects where type='p' and name='sp_GetCurrPeriod')
Drop Procedure sp_GetCurrPeriod
GO
CREATE proc [dbo].[sp_GetCurrPeriod]
@FCategory nvarchar(30)
as
SET NOCOUNT ON
create table #Tmp1(
FYear nvarchar(255) ,
FPeroid nvarchar(255) ,
)
declare @FPeroid nvarchar(255)
insert into #Tmp1 (FYear)
select fvalue+'年' from t_SystemProfile where FCategory =@FCategory and FKey='CurrentYear'
select @FPeroid=fvalue+'月' from t_SystemProfile where FCategory ='ic' and FKey='CurrentPeriod'
update #Tmp1 set FPeroid=@FPeroid
select FYear+Fperoid as Fperoid from #Tmp1
drop table #Tmp1
GO
存储过程解决但感觉太麻烦，有简单高效的办法吗？
