- Foxtable(狐表) (http://foxtable.net/bbs/index.asp)
-- 专家坐堂 (http://foxtable.net/bbs/list.asp?boardid=2)
---- select语句动态添加列名 (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=186539)
-- 发布时间：2023/5/10 13:34:00
-- select语句动态添加列名
Dim cmd As new SQLCommand
cmd.CommandText = "Se lect 部门,职务 from {员工} Wh ere 姓名 = \'王伟\'"
cmd.ConnectionName = "数据源名称"
Dim Values = cmd.ExecuteValues
中sele ct 后面的列名能够用代码动态添加吗？比如所有表中除操作员和操作日期两列不添加，其他的列都添加
请指导
-- 发布时间：2023/5/10 13:40:00
--
没有必要吧，就那么2个列，都加载出来又有什么问题呢
dim s as string
for each c as col in tables("a").cols
s = s & "," & c.name
next
cmd.CommandText = "Se lect " & s.trim(",") & " from {员工} Wh ere 姓名 = \'王伟\'"
-- 发布时间：2023/5/10 13:49:00
--
多谢老师
-- 发布时间：2023/5/10 14:05:00
--
Dim s As String
For Each c As Col In maintable.cols
If c.Name <> "编辑" And c.Name <> "删除" Then
s = s & "," & c.name
End If
Next
MessageBox.Show("2")
Dim cmd As New SQLCommand
cmd.C
Dim nms() As String = {s.trim(", ")}
MessageBox.Show("3")
If Filter > "" Then
cmd.CommandText = "Se lect " & s.trim(",") & " From {maintable.NAME} Where " & Filter
Else
cmd.CommandText = "Se lect " & s.trim(",") & "From {maintable.NAME} "
End If
MessageBox.Show("4")
Dim dt As DataTable = cmd.ExecuteReader()
MessageBox .Show("5")
-- 发布时间：2023/5/10 14:05:00
--
此主题相关图片如下：1.png
-- 发布时间：2023/5/10 14:05:00
--
出现上面的提示
-- 发布时间：2023/5/10 14:07:00
--
If Filter > "" Then
cmd.CommandText = "Se lect " & s.trim(",") & " From {} Where " & Filter
Else
cmd.CommandText = "Se lect " & s.trim(",") & " From {“ & maintable.NAME & ”} "
End If
-- 发布时间：2023/5/10 14:31:00
--
老师，这是全部的代码，我自已写的下面还是有错误，还是得帮我该一下。
Dim Filter As String
With e.Form.Controls("上游客户")
If .text > "" Then
Filter = "上游客户 like \'%" & .text & "%\'"
End If
End With
With e.Form.Controls("品牌")
If .text > "" Then
If Filter > "" Then
Filter = Filter & " And "
End If
Filter = Filter & "品牌 like \'%" & .text & "%\'"
End If
End With
With e.Form.Controls("商品车")
If .text > "" Then
If Filter > "" Then
Filter = Filter & " And "
End If
Filter = Filter & "商品车 like \'%" & .text & "%\'"
End If
End With
With e.Form.Controls("备注")
If .text > "" Then
If Filter > "" Then
Filter = Filter & " And "
End If
Filter = Filter & "备注 like \'%" & .text & "%\'"
End If
End With
Dim s As String
For Each c As Col In maintable.cols
If c.Name <> "编辑" And c.Name <> "删除" Then
s = s & "," & c.name
End If
Next
Dim cmd As New SQLCommand
cmd.C
Dim nms() As String = {" & s.trim(", ") & " }
If Filter > "" Then
cmd.CommandText = "Se lect " & s.trim(",") & " From {} Where " & Filter
Else
cmd.CommandText = "Se lect " & s.trim(",") & " From {“ & maintable.NAME & ”} "
End If
Dim dt As DataTable = cmd.ExecuteReader()
Dim Book As New XLS.Book
Dim dlg As New SaveFileDialog \'定义一个新的SaveFileDialog
dlg.Filter = "Excel文件|*.xlsx" \'设置筛选器
If dlg.ShowDialog = DialogResult.Ok Then
Book.Save(dlg.FileName)
End If
Book = New XLS.Book(dlg.FileName)
Dim Sheet As XLS.Sheet = Book.Sheets(0)
Dim st As XLS.Style = Book.NewStyle \'日期列的显示格式
st.Format = "yyyy-MM-dd"
For c As Integer = 0 To nms.length - 1
If MainTable.Cols(nms(c)).IsDate Then \'如果是日期列
Sheet.Cols(c).Style = st \'设置显示格式
End If
Next
For c As Integer = 0 To nms.Count - 1
Sheet(0, c).Value = nms(c)
Next
For r As Integer = 0 To dt.DataRows.Count - 1
For c As Integer = 0 To nms.Count - 1
Sheet(r + 1, c).Value = dt.DataRows(r)(nms(c))
Next
Next
Book.Save(dlg.FileName)
Dim Result As DialogResult
Result = MessageBox.Show("导出成功,是否打开所导出文件?", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
If Result = DialogResult.Yes Then
Dim Proc As New Process
Proc.File = dlg.FileName
Proc.Start()
End If
-- 发布时间：2023/5/10 14:32:00
--
此主题相关图片如下：2.png
-- 发布时间：2023/5/10 14:34:00
--
Dim nms() As String = s.trim(", ").split(",")
If Filter > "" Then
cmd.CommandText = "Se lect " & s.trim(",") & " From {“ & maintable.NAME & ”} Where " & Filter
Else
cmd.CommandText = "Se lect " & s.trim(",") & " From {“ & maintable.NAME & ”} "
End If