--
--某table的column存在,傳回1;column不存在,傳回0 select count(name) from syscolumns where id=(select id from sysobjects where name='tableName')and name='columnName'
--某table的column存在,傳回1;column不存在,傳回0 select count(name) from syscolumns where id=(select id from sysobjects where name='tableName')and name='columnName'
SET ROWCOUNT 1 /*要先下此條件*/ GO UPDATE table_name set field_1 = GETDATE()WHERE field_2 = 'Y'
SELECT col2,col3,count(*) qty FROM table2 where col2 is nullgroup by col2,col3 having count(*) > 1
UPDATE TABLEA SET (b, c, d) = (SELECT b1, c1, d1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40) WHERE EXISTS (SELECT 1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40) update ( select a.fileld, b.fileld from TABLEA a, TABLE b where a.key = b.key) set fileld = fileld
select COLUMN2,COLUMN3 from TABLENAMEwhere len(COLUMN3) != datalength(convert(varchar(1000),COLUMN3))
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click '將所選取的反過來讀取 For i As Integer = DataGridView1.SelectedCells.Count - 1 To 0 Step -1 MsgBox(DataGridView1.SelectedCells(i).Value) Next End Sub Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click For i As Integer = DataGridView1.SelectedRows.Count - 1 To 0 Step -1 MsgBox(DataGridView1.SelectedRows(i).Cells(0).Value) Next End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Try '建立暫存Table!! Dim dt As New DataTable dt.Columns.Add(New DataColumn("tItem", System.Type.GetType("System.String"))) dt.Columns.Add(New DataColumn("tDescription", System.Type.GetType("System.String"))) dt.Columns.Add(New DataColumn("tMemon", System.Type.GetType("System.String"))) '建立暫存資料!! Dim dr As DataRow = dt.NewRow dr.Item("tItem") = "1" dr.Item("tDescription") = "你好嗎?" dr.Item("tMemon") = "...." dt.Rows.Add(dr) '建立暫存資料!! dr = dt.NewRow dr.Item("tItem") = "2" dr.Item("tDescription") = "你是誰?" dr.Item("tMemon") = ",,,,," dt.Rows.Add(dr) '呼叫匯出Excel Function If Not ExportExcel(dt, "c:\temp\123.xls", "測試") Then MessageBox.Show("匯出Excel 時發生錯誤!!") Else MessageBox.Show("匯出Excel 成功!!") End If Catch ex As Exception '錯誤則秀出錯誤訊息!! MessageBox.Show(ex.Message) End Try End Sub 'DT: Data Table 'FullFileName: 匯出Excel 的完整路徑 Ex: C:\123.xls 'TableName: 匯出Excel 的Sheet Name Private Function ExportExcel(ByVal DT As DataTable, ByVal FullFileName As String, ByVal TableName As String) As Boolean Dim connection As New Data.OleDb.OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0""", FullFileName)) Try '建立Excel Connection connection.Open() '產生Table Dim strCreate As String For i As Int32 = 0 To DT.Columns.Count - 1 strCreate += String.Format(",{0} Text(255)", DT.Columns(i).ColumnName) Next strCreate = String.Format("Create Table [{0}] ({1})", TableName, strCreate.Substring(1)) Dim command As New Data.OleDb.OleDbCommand(strCreate, connection) command.ExecuteNonQuery() '讀取Excel Table (Sheet) Dim queryString As String = String.Format("Select * From [{0}]", TableName) '建立Excel 配接器 Dim adapter As New Data.OleDb.OleDbDataAdapter() '宣告一個Dataset Dim ds As New DataSet adapter.SelectCommand = New Data.OleDb.OleDbCommand(queryString, connection) Dim builder As Data.OleDb.OleDbCommandBuilder = New Data.OleDb.OleDbCommandBuilder(adapter) '產生新增語法 adapter.Fill(ds, TableName) For i As Int32 = 0 To DT.Rows.Count - 1 Dim dr As DataRow = ds.Tables(TableName).NewRow For j As Int32 = 0 To DT.Columns.Count - 1 dr.Item(DT.Columns(j).ColumnName) = DT.Rows(i).Item(j) Next ds.Tables(TableName).Rows.Add(dr) Next builder.GetInsertCommand(True) '執行新增語法 adapter.Update(ds, TableName) Return True Catch ex As Exception '擲出錯誤!! Throw ex Finally '關閉連線 connection.Close() End TryEnd Function