上次的贴子地址:http://topic.csdn.net/u/20110611/11/d1edfd90-1ce7-4116-947a-eda9b4d96584.html?1098860996之前发贴的时候还没有找到我要的那张表,现在找到之后发现又有问题。
上次测试可行的代码为:
Private Sub Command1_Click()
Dim Cn As New ADODB.Connection
Dim Rs As New ADODB.Recordset'sql server数据库连接过程
Cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=UFDATA_002_2011;Data Source=192.168.1.6;Connect Timeout=30"
Cn.Open
strsql = "select cInvCode,iQuantity from CurrentStock where cInvCode like '%" & Text1.Text & "%'"
If Cn.State = adStateOpen Then
Rs.CursorLocation = adUseClient
Rs.Open strsql, Cn, adOpenDynamic, adLockOptimistic
Else
MsgBox "SQL Server数据库连接错误!"
End
End IfSet DataGrid1.DataSource = Rs
Set Rs = Nothing
End Sub我要让程序列出的是存货编码、物料名称、现存量
这段代码CurrentStock表中,cInvCode是物料编码,iQuantity是现存量
这张表里没有物料名称,只有编码和现存量,另外有一张表是存货档案,里面有所有的物料编码和物料名称所以,现在要先在Inventory表物料名称中查询出包含text1的所有行,再根据列出的物料编码到CurrentStock表中查询出现存量,然后再依次列出物料编码、物料名称、现存量Inventory表中cInvCode是物料编码,cInvName是物料名称
CurrentStock表中cInvCode是物料编码,iQuantity是现存量不知道说明白没有,不明白的可加我QQ:88580324谢各位赐教、、、、
上次测试可行的代码为:
Private Sub Command1_Click()
Dim Cn As New ADODB.Connection
Dim Rs As New ADODB.Recordset'sql server数据库连接过程
Cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=UFDATA_002_2011;Data Source=192.168.1.6;Connect Timeout=30"
Cn.Open
strsql = "select cInvCode,iQuantity from CurrentStock where cInvCode like '%" & Text1.Text & "%'"
If Cn.State = adStateOpen Then
Rs.CursorLocation = adUseClient
Rs.Open strsql, Cn, adOpenDynamic, adLockOptimistic
Else
MsgBox "SQL Server数据库连接错误!"
End
End IfSet DataGrid1.DataSource = Rs
Set Rs = Nothing
End Sub我要让程序列出的是存货编码、物料名称、现存量
这段代码CurrentStock表中,cInvCode是物料编码,iQuantity是现存量
这张表里没有物料名称,只有编码和现存量,另外有一张表是存货档案,里面有所有的物料编码和物料名称所以,现在要先在Inventory表物料名称中查询出包含text1的所有行,再根据列出的物料编码到CurrentStock表中查询出现存量,然后再依次列出物料编码、物料名称、现存量Inventory表中cInvCode是物料编码,cInvName是物料名称
CurrentStock表中cInvCode是物料编码,iQuantity是现存量不知道说明白没有,不明白的可加我QQ:88580324谢各位赐教、、、、
解决方案 »
- vb写的把数据库某张表的内容输出到excel,excel打开后发现里面的一个字段,就是那个日期列,刚打开时大部分都不能正常显示,都为######,双击
- 内码转换高手请进:如何把“銅”从Big5转换到GB2312的“铜”
- excle 导出数据问题
- 菜单顶层快捷键的使用?
- vb做得activeDll不能接受参数,怎么解决这个问题
- References问题
- TimerSetEvent如何触发事件?
- 选择查询,请教!请进。。。
- 请帮我看看这段代码有错误吗,谢谢
- To enmity(灵感之源) 你能给我解答吗?
- vb如何实现google那样的下拉列表,当输入字母时,下方出现可以选择的内容?
- 对数据库不太熟悉,求一段查询SQL的代码,谢谢
嗯,可以显示仓库名称,明确是材料还是成品,或者在搜索时选择搜索那一个仓库
select inv.cInvCode as cInvCode,...
if rs.state<>adstateclosed then rs.close
rs.open strsql,cn,adopenkeyset,adlockreadonly
set datagrid1.datasource=rs
Private Sub Command1_Click()
Dim CN As New ADODB.Connection
Dim Rs As New ADODB.Recordset
CN.ConnectionString = "driver={sql server};server=MYSQLSERVER;uid=sa;pwd=mypass;database=test"
CN.Open
Rs.CursorLocation = adUseClient
Rs.Open "select * from employee", CN, adOpenDynamic, adLockOptimistic Set DataGrid1.DataSource = Rs
End Sub
Dim Cn As New ADODB.Connection
Dim Rs As New ADODB.Recordset'sql server数据库连接过程
Cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=UFDATA_002_2011;Data Source=192.168.1.6;Connect Timeout=30"
Cn.Open
'***********下面是语句修改部份strsql = "select i.cInvCode as [存货编码],i.cInvName as [存货名称]" & _
",c.iQuantity as [现存量] from Inventory i left join CurrentStock c " & _
" on i.cInvCode=c.cInvCode where i.cInvCode like '% & Text1.Text& %'"
'************
If Cn.State = adStateOpen Then
Rs.CursorLocation = adUseClient
Rs.Open strsql, Cn, adOpenDynamic, adLockOptimistic
Else
MsgBox "SQL Server数据库连接错误!"
End
End IfSet DataGrid1.DataSource = Rs
Set Rs = Nothing
End Sub
Private Sub Command1_Click()
Dim Cn As New ADODB.Connection
Dim Rs As New ADODB.Recordset'sql server数据库连接过程
Cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=UFDATA_002_2011;Data Source=192.168.1.6;Connect Timeout=30"
Cn.Open
'***********下面是语句修改部份strsql = "select i.cInvCode as [存货编码],i.cInvName as [存货名称]" & _
",c.iQuantity as [现存量] from Inventory i left join CurrentStock c " & _
" on i.cInvCode=c.cInvCode where i.cInvCode like '% & Text1.Text& %'"
'************
If Cn.State = adStateOpen Then
Rs.CursorLocation = adUseClient
Rs.Open strsql, Cn, adOpenDynamic, adLockOptimistic
Else
MsgBox "SQL Server数据库连接错误!"
End
End IfSet DataGrid1.DataSource = Rs
Set Rs = Nothing
End Sub
'....
strsql="select Inventory.cInvCode,Inventory.cInvName,CurrentStock.iQuantity from Inventory,CurrentStock where Inventory.cInvName like '" & trim(text1.text) & "' and Inventory.cInvCode=CurrentStock.cInvCode"
'....