- [VB.NET] DataGridの行タイトルを表示: (2006/01/06)
- [VB.NET] StringBuilderの使用: (2005/12/27)
- [VB.NET] 日付の存在チェック: (2005/12/27)
- [VB.NET] Excelの操作: (2005/12/16)
- [VB.NET] Tab遷移をEnterで: (2005/12/13)
- [VB.NET] Control の選択: (2005/11/28)
- [VB.NET] 文字列作成: (2005/11/28)
- [VB.NET] アプリケーション構成ファイル（App.config）: (2005/11/21)
- [VB.NET] DataTableの作成: (2005/11/10)
- [VB.NET] DataSet 一行追加・一行削除: (2005/11/10)
- [VB.NET] クラス・構造体・モジュール: (2005/11/09)
- [VB.NET|SQLServer] レコードの削除・挿入・更新（トランザクション有）: (2005/11/01)
- [VB.NET] Sub Main（二重起動防止・スタートフォーム）: (2005/10/24)
- [VB.NET] TreeViewへ項目の追加: (2005/10/21)
- [VB.NET] ListViewへ項目の追加: (2005/10/21)
- [VB.NET|Access] レコードの削除・挿入・更新（ODBC）: (2005/10/21)
- [VB.NET] DataSetの連結: (2005/10/20)
- [VB.NET|Access] レコードの削除・挿入・更新（OleDb）: (2005/10/20)
- [VB.NET] DataSet内のデータの取り出し方: (2005/10/14)
- [VB.NET|Access] DataGrid に表示: (2005/10/14)
- [VB.NET] 二重起動防止: (2005/10/14)
- [VB.NET] 配列ソート: (2005/10/14)
[VB.NET] DataGridの行タイトルを表示
http://www.syncfusion.com/FAQ/WindowsForms/FAQ_c44c.aspx#q896q
5.61 How can I place text in the rowheader column of my datagrid?
There is no text property exposed for a rowheader cell. But you can handle the Paint event and draw header text yourself. You can download sample projects (C#, VB) that illustrate one technique for doing so.
The sample loads the datagrid in the form's Load event. In addition, this event is used to set the rowheaderwidth of the datagrid, and to remember the point where cell 0,0 is located. This point will allow us to find the toprow number when we need it to start drawing the header text. A handler for the datagrid's Paint event is used to draw the text. It finds the toprow using the point from the original cell 0,0, and using the toprow determines the correct text for each rowheader. Finally, to avoid the complication of the user changing rowheights, we derive a new grid to prevent this.
private void dataGrid1_Paint(object sender, System.Windows.Forms.PaintEventArgs e)
{
int row = TopRow();
int yDelta = dataGrid1.GetCellBounds(row, 0).Height + 1;
int y = dataGrid1.GetCellBounds(row, 0).Top + 2;
CurrencyManager cm = (CurrencyManager) this.BindingContext[dataGrid1.DataSource, dataGrid1.DataMember];
while(y < dataGrid1.Height - yDelta && row < cm.Count)
{
//get & draw the header text...
string text = string.Format("row{0}", row);
e.Graphics.DrawString(text, dataGrid1.Font, new SolidBrush(Color.Black), 12, y);
y += yDelta;
row++;
}
}
2006 / 01 / 06
[VB.NET] StringBuilderの使用
Dim Str As System.Text.StringBuilder
Try
Str = New System.Text.StringBuilder
Str.Append("あいうえお")
Str.Append(vbCrLf)
Str.Append("かきくけこ")
Label1.Text = Str.ToString
Catch ex As Exception
MessageBox.Show(ex.ToString)
Finally
If IsNothing(Str) = False Then Str = Nothing
End Try
2005 / 12 / 27
[VB.NET] 日付の存在チェック
'--- 正しい日付=True、不正な日付=False
Public Function IsExistDate(ByVal CheckDate As String) As Boolean
Try
Dim dt As Date
Dim CheckFormat As String = "yyyyMMdd" '例：20051227
'CheckDateの文字数が0の場合
If Trim(CheckDate).Length = 0 Then
Return False
End If
'日付の形式にフォーマットする
dt = DateTime.ParseExact(CheckDate, CheckFormat, Nothing)
Return IsDate(dt)
Catch ex As Exception
Return False
End Try
End Function
2005 / 12 / 27
[VB.NET] Excelの操作
'Excel設定値
Const xlLandscape = 2 '用紙向き 横
Const xlCenter = -4108 '中央揃え
Const xlRight = -4152 '右揃え
Const xlLeft = -4131 '左揃え
Const xlInsideHorizontal = 12
Const xlContinuous = 1
Const xlThin = 2
'Excel列番号
Const cnsCol1 As Integer = 1
Const cnsCol2 As Integer = 2
Const cnsCol3 As Integer = 3
'Excel保存先
Const FilePath As String = "C:\Excel\Test.xls"
'Excelオブジェクト
Dim oExcel As Object 'Excel.Application
Dim oBooks As Object 'Excel.Workbooks
Dim oBook As Object 'Excel.Workbook
Dim oSheets As Object 'Excel.Sheets
Dim oSheet As Object 'Excel.Worksheet
Dim oRange As Object 'Excel.Range
Dim oCells As Object 'Excel.Range（１回の解放でOK）
Dim oBorders As Object 'Excel.Borders
Dim oPageSetup As Object 'Excel.PageSetup
Dim GridData(,) As String
Dim intRow As Integer
Dim intRowCount As Integer
Try
'Excelオブジェクトセット
oExcel = CreateObject("Excel.Application")
oBooks = oExcel.Workbooks
oBook = oBooks.Add
oSheets = oBook.Worksheets
oSheet = oSheets(1)
oCells = oSheet.Cells
'----- １行目 -----
intRow = 1
'列幅設定
oRange = oCells(intRow, cnsCol1)
oRange.ColumnWidth = 10.25
Call MRComObject(oRange)
oRange = oCells(intRow, cnsCol2)
oRange.ColumnWidth = 10.25
Call MRComObject(oRange)
oRange = oCells(intRow, cnsCol3)
oRange.ColumnWidth = 10.25
Call MRComObject(oRange)
'列タイトル 中央揃え
oRange = oSheet.Range(R1C1ToA1(intRow, cnsCol1, intRow, cnsCol3))
With oRange
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Call MRComObject(oRange)
'列タイトル 表示
oCells(intRow, cnsCol1) = "列名１"
oCells(intRow, cnsCol2) = "列名２"
oCells(intRow, cnsCol3) = "列名３"
'----- ２行目以降 -----
intRow += 1
'データを表示する
ReDim GridData(1, 2)
GridData(0, 0) = "A1" : GridData(0, 1) = "B1" : GridData(0, 2) = "C1"
GridData(1, 0) = "A2" : GridData(1, 1) = "B2" : GridData(1, 2) = "C2"
oRange = oSheet.Range(R1C1ToA1(intRow, cnsCol1, intRow + 1, cnsCol3))
oRange.value = GridData
Call MRComObject(oRange)
'罫線を引く
oRange = oSheet.Range(R1C1ToA1(1, cnsCol1, GridDataNum + 1, cnsCol3))
oBorders = oRange.borders(xlInsideHorizontal) '横線
With oBorders
oBorders.LineStyle = xlContinuous
oBorders.Weight = xlThin
End With
Call MRComObject(oBorders)
Call MRComObject(oRange)
'ページヘッダ部設定
oPageSetup = oSheet.PageSetup
With oPageSetup
.LeftHeader = ""
.CenterHeader = "&20 入出庫履歴"
.RightHeader = "&10" & "日付 &D" & " 現在"
.Orientation = xlLandscape
.ZOOM = 75
.LeftMargin = 0
.RightMargin = 0
End With
Call MRComObject(oPageSetup)
'Excelを保存する
oExcel.DisplayAlerts = False '警告メッセージ非表示
oBook.SaveAs(FilePath) 'Excel保存
oExcel.DisplayAlerts = True '警告メッセージ表示
Catch ex As Exception
MsgBox(Err.Description)
Finally
'Excelオブジェクトの解放
Call MRComObject(oCells) 'oCells の解放
Call MRComObject(oSheet) 'oSheet の解放
Call MRComObject(oSheets) 'oSheets の解放
oBook.Close(False) 'oBook を閉じる
Call MRComObject(oBook) 'oBook の解放
Call MRComObject(oBooks) 'oBooks の解放
oExcel.Quit() 'oExcelを閉じる
Call MRComObject(oExcel) 'oExcel を解放
GC.Collect() 'ガーベジコレクト起動
'-----------------------------------------------------------
'Excelプロセスチェック
System.Threading.Thread.Sleep(1000)
Dim localByName As Process() = Process.GetProcessesByName("Excel")
If localByName.Length > 0 Then
MessageBox.Show("まだ Excel.EXE が " & localByName.Length & " 個 起動しています。")
Else
MessageBox.Show("起動している Excel.EXE はありません。")
End If
'-----------------------------------------------------------
End Try
'***** R1C1形式のアドレスをA1形式に変換する *****
Private Function R1C1ToA1( _
ByVal R1 As Integer, _
ByVal C1 As Integer, _
ByVal R2 As Integer, _
ByVal C2 As Integer) As String
Try
Dim i1, i2 As Integer
Dim d1, d2 As String
i1 = Math.Floor((C1 - 1) \ 26)
d1 = IIf(C1 > 26, Strings.Chr(64 + i1), "")
i1 = C1 - 26 * i1
d1 &= Strings.Chr(64 + i1) & CStr(R1)
i2 = Math.Floor((C2 - 1) \ 26)
d2 = IIf(C2 > 26, Strings.Chr(64 + i2), "")
i2 = C2 - 26 * i2
d2 &= Strings.Chr(64 + i2) & CStr(R2)
Return d1 & ":" & d2
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Function
'***** COMオブジェクトへの参照を解放する *****
Private Sub MRComObject(ByRef ComObject As Object)
Try
'提供されたランタイム呼び出し可能ラッパーの参照カウントをデクリメントする
System.Runtime.InteropServices.Marshal.ReleaseComObject(ComObject)
Catch
Finally
'参照を解除する
ComObject = Nothing
End Try
End Sub
2005 / 12 / 16
[VB.NET] Tab遷移をEnterで
Protected Overrides Function ProcessDialogKey(ByVal iKeyData As Keys) As Boolean Select Case iKeyData Case Keys.Enter iKeyData = Keys.TabCase Keys.Enter Or Keys.Shift
iKeyData = Keys.Tab Or Keys.Shift
End SelectReturn MyBase.ProcessDialogKey(iKeyData)
End Function
2005 / 12 / 13
[VB.NET] Control の選択
If TypeOf Me.ActiveControl Is TextBox Then
'テキストボックスが選択されています。
End If
2005 / 11 / 28
[VB.NET] 文字列作成
Dim x As String = "ひらがな" Dim y As String = "カタカナ" Dim z As String = "漢字"Console.Write(String.Format("「あ」は{0}です。「ア」は{1}です。「亜」は{2}です。", x, y, z))
'「あ」はひらがなです。「ア」はカタカナです。「亜」は漢字です。
2005 / 11 / 28
[VB.NET] アプリケーション構成ファイル（App.config）
[ App.config ] の書き方 （大文字小文字に注意！）<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="Server" value="サーバー名" />
<add key="UserID" value="ユーザー名" />
<add key="Password" value="パスワード" />
<add key="Database" value="DB名" />
</appSettings>
</configuration>
'[ App.config ] の読み込み方Dim Server As String = System.Configuration.ConfigurationSettings.AppSettings("Server")
Dim UserID As String = System.Configuration.ConfigurationSettings.AppSettings("UserID")
Dim Password As String = System.Configuration.ConfigurationSettings.AppSettings("Password")
Dim Database As String = System.Configuration.ConfigurationSettings.AppSettings("Database")
2005 / 11 / 21
[VB.NET] DataTableの作成
'データテーブルの作成
DT = New DataTable
DT.Columns.Add(New DataColumn("列１", System.Type.GetType("System.Int16")))
DT.Columns.Add(New DataColumn("列２", System.Type.GetType("System.String")))
DT.Columns.Add(New DataColumn("列３", System.Type.GetType("System.String")))
2005 / 11 / 10
[VB.NET] DataSet 一行追加・一行削除
'--- 一行追加
Dim DR As DataRow
DR = DS.Tables("テーブル名").NewRow
DR(0) = txtCol1 'DR(列名) でもOK
DR(1) = txtCol2
DR(2) = txtCol3
DS.Tables("テーブル名").Rows.Add(DR)
'--- 一行削除
'DataSetに主キーを設定
Dim pKey(0) As DataColumn
pKey(0) = DS.Tables("テーブル名").Columns("DBの列名")
DS.Tables("テーブル名").PrimaryKey = pKey
Dim DR As DataRow
DR = DS.Tables("テーブル名").Rows.Find(CType([文字列], Object)) 'DataSetの検索
If IsNothing(DR) = False Then
DS.Tables("テーブル名").Rows.Remove(DR) '一行削除
End If
2005 / 11 / 10
[VB.NET] クラス・構造体・モジュール
クラス・構造体・モジュール（『.NETでいきまっしょい』より）
'クラス(参照型)Class RefPerson
Public Name As String
End Class
'構造体(値型)Structure ValPerson
Public Name As String
End Structure
2005 / 11 / 09
[VB.NET|SQLServer] レコードの削除・挿入・更新（トランザクション有）
'*** 宣言
Imports System.Data.SqlClient
Private Conn As SqlConnection
Private DA As SqlDataAdapter
Private DS As DataSet
Dim strConn As String = _
"Server=localhost;User ID=***;Password=***;database=Test;"
Dim SqlTran As SqlTransaction
Dim strSQL As String
Dim cmdSQL As SqlCommand
Dim dRow As DataRow
Dim dCol As DataColumn
Dim i As Integer
Dim j As Integer
'*** Form_Load
Me.Conn = New SqlConnection(strConn)
Me.DA = New SqlDataAdapter
Me.DS = New DataSet
Me.Conn.Open()
'*** Form_Closed
Me.DS.Dispose()
Me.DA.Dispose()
Me.Conn.Close()
Me.Conn.Dispose()
'排他処理する場合？ Me.SqlTran = Me.Conn.BeginTransaction(IsolationLevel.Serializable) strSQL = "SELECT * FROM TestTable WITH(ROWLOCK, UPDLOCK) ORDER BY TestCol1 ASC"
'--- SELECT
Try
Me.SqlTran = Me.Conn.BeginTransaction()
strSQL = "SELECT * FROM TestTable ORDER BY TestCol1 ASC"
Me.cmdSQL = New SqlCommand(strSQL, Me.Conn, Me.SqlTran)
Me.DA.SelectCommand = Me.cmdSQL
Me.DS.Clear()
Me.DA.Fill(Me.DS, "TestTable")
Call ListView_Disp(Me.DS)
Me.SqlTran.Commit()
Catch ex As Exception
Debug.WriteLine(ex.ToString)
MessageBox.Show(ex.ToString)
Me.SqlTran.Rollback()
Finally
Me.cmdSQL.Dispose()
Me.SqlTran.Dispose()
End Try
'--- INSERT
dRow = Me.DS.Tables("TestTable").NewRow
dRow(0) = CInt(Me.TextBox1.Text)
dRow(1) = CStr(Me.TextBox2.Text)
dRow(2) = CStr(Me.TextBox3.Text)
Me.DS.Tables("TestTable").Rows.Add(dRow)
Try
Me.SqlTran = Me.Conn.BeginTransaction()
strSQL = "INSERT INTO TestTable VALUES (@Col1, @Col2, @Col3)"
Me.cmdSQL = New SqlCommand(strSQL, Me.Conn, Me.SqlTran)
Me.cmdSQL.Parameters.Add("@Col1", SqlDbType.Int, 4, "TestCol1")
Me.cmdSQL.Parameters.Add("@Col2", SqlDbType.VarChar, 50, "TestCol2")
Me.cmdSQL.Parameters.Add("@Col3", SqlDbType.VarChar, 50, "TestCol3")
Me.DA.InsertCommand = Me.cmdSQL
Me.DA.InsertCommand.Parameters("@Col1").Value = CInt(Me.TextBox1.Text)
Me.DA.InsertCommand.Parameters("@Col2").Value = CStr(Me.TextBox2.Text)
Me.DA.InsertCommand.Parameters("@Col2").Value = CStr(Me.TextBox3.Text)
Me.DA.Update(Me.DS, "TestTable")
Call ListView_Disp(Me.DS)
Me.SqlTran.Commit()
Catch ex As Exception
Debug.WriteLine(ex.ToString)
MessageBox.Show(ex.ToString)
Me.SqlTran.Rollback()
Finally
Me.cmdSQL.Dispose()
Me.SqlTran.Dispose()
End Try
'--- DELETE
For Each dRow In DS.Tables("TestTable").Rows
If CInt(dRow(0)) = CInt(TextBox1.Text) Then
dRow.Delete()
Exit For
End If
Next
Try
Me.SqlTran = Me.Conn.BeginTransaction
strSQL = "DELETE FROM TestTable WHERE TestCol1 = @Col1"
Me.cmdSQL = New SqlCommand(strSQL, Me.Conn, Me.SqlTran)
Me.cmdSQL.Parameters.Add("@Col1", SqlDbType.Int, 4, "TestCol1")
Me.DA.DeleteCommand = Me.cmdSQL
Me.DA.DeleteCommand.Parameters("@Col1").Value = CInt(Me.TextBox1.Text)
Me.DA.Update(Me.DS, "TestTable")
Call ListView_Disp(Me.DS)
Me.SqlTran.Commit()
Catch ex As Exception
Debug.WriteLine(ex.ToString)
MessageBox.Show(ex.ToString)
Me.SqlTran.Rollback()
Finally
Me.cmdSQL.Dispose()
Me.SqlTran.Dispose()
End Try
'--- UPDATE
For Each dRow In DS.Tables("TestTable").Rows
If CInt(dRow(0)) = CInt(TextBox1.Text) Then
dRow(1) = TextBox2.Text
dRow(2) = TextBox3.Text
Exit For
End If
Next
Try
Me.SqlTran = Me.Conn.BeginTransaction
strSQL = "UPDATE TestTable SET TestCol2 = @Col2, TestCol3 = @Col3 WHERE TestCol1 = @Col1"
Me.cmdSQL = New SqlCommand(strSQL, Me.Conn, Me.SqlTran)
Me.cmdSQL.Parameters.Add("@Col1", SqlDbType.Int, 4, "TestCol1")
Me.cmdSQL.Parameters.Add("@Col2", SqlDbType.VarChar, 50, "TestCol2")
Me.cmdSQL.Parameters.Add("@Col3", SqlDbType.VarChar, 50, "TestCol3")
DA.UpdateCommand = Me.cmdSQL
Me.DA.UpdateCommand.Parameters("@Col1").Value = CInt(Me.TextBox1.Text)
Me.DA.UpdateCommand.Parameters("@Col2").Value = CStr(Me.TextBox2.Text)
Me.DA.UpdateCommand.Parameters("@Col2").Value = CStr(Me.TextBox3.Text)
Me.DA.Update(Me.DS, "TestTable")
Call ListView_Disp(Me.DS)
Me.SqlTran.Commit()
Catch ex As Exception
Debug.WriteLine(ex.ToString)
MessageBox.Show(ex.ToString)
Me.SqlTran.Rollback()
Finally
Me.cmdSQL.Dispose()
Me.SqlTran.Dispose()
End Try
2005 / 11 / 01
[VB.NET] Sub Main（二重起動防止・スタートフォーム）
Sub main()
'二重起動チェック
Dim insProcess As Process() 'プロセスインスタンス
Dim strProcessName As String 'プロセス名
strProcessName = Process.GetCurrentProcess.ProcessName
insProcess = Process.GetProcessesByName(strProcessName)
If UBound(insProcess) > 0 Then
MessageBox.Show("すでに起動しています")
End
Else
'スタートフォーム設定
Dim f_Main As New frm_Main
f_Main.ShowDialog()
f_Main.Dispose()
End If
End Sub
2005 / 10 / 24
[VB.NET] TreeViewへ項目の追加
'ノード作成
Dim A_02_01 As New TreeNode("A0201")
Dim A_02_02 As New TreeNode("A0202")
Dim A_02_Folder() As TreeNode = {A_02_01, A_02_02}
Dim A_02 As New TreeNode("A02", A_02_Folder)
Dim A_01 As New TreeNode("A01")
Dim A_03 As New TreeNode("A03")
Dim A_Folder() As TreeNode = {A_01, A_02, A_03}
Dim A As New TreeNode("A", A_Folder)
Dim B As New TreeNode("B")
Dim C_01 As New TreeNode("C01")
Dim C_02 As New TreeNode("C02")
Dim C_Folder() As TreeNode = {C_01, C_02}
Dim C As New TreeNode("C", C_Folder)
Dim Root() As TreeNode = {A, B, C}
TreeView1.Nodes.Clear()
'最上位階層（Root）に対してまとめてノードを追加
TreeView1.Nodes.AddRange(Root)
'最初のノード（TopNode）を開く
TreeView1.TopNode.ExpandAll()
+--[A]
| |--[A01]
| +--[A02]
| | |--[A0201]
| | +--[A0202]
| |
| +--[A03]
|
+--[B]
|
+--[C]
|--[C01]
+--[C01]
2005 / 10 / 21
[VB.NET] ListViewへ項目の追加
ListView1.Clear()
'項目を表示する方法
ListView1.View = View.Details
'ヘッダー部の追加（ヘッダー名, 幅ピクセル, 左寄せ）
ListView1.Columns.Add("商品ID", 70, HorizontalAlignment.Left)
ListView1.Columns.Add("商品名", 100, HorizontalAlignment.Left)
ListView1.Columns.Add("個数", 50, HorizontalAlignment.Left)
'アイテム・サブアイテムを追加
ListView1.Items.Add("ID01", 0)
ListView1.Items(0).SubItems.Add("商品A")
ListView1.Items(0).SubItems.Add("150")
ListView1.Items.Add("ID02", 1)
ListView1.Items(1).SubItems.Add("商品B")
ListView1.Items(1).SubItems.Add("100")
ListView1.Items.Add("ID03", 2)
ListView1.Items(2).SubItems.Add("商品C")
ListView1.Items(2).SubItems.Add("200")
2005 / 10 / 21
[VB.NET|Access] レコードの削除・挿入・更新（ODBC）
Imports System.Data.Odbc
Private Conn As OdbcConnection
Private DA As OdbcDataAdapter
Private DS As DataSet
Private Sub frm_ODBC_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim Cmd_Select As OdbcCommand
Dim Cmd_Update As OdbcCommand
Dim Cmd_Delete As OdbcCommand
Dim Cmd_Insert As OdbcCommand
Dim strConn As String = "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\テストDB.mdb"
Dim SQL_Select As String
Dim SQL_Update As String
Dim SQL_Insert As String
Dim SQL_Delete As String
Try
Conn = New OdbcConnection(strConn)
DA = New OdbcDataAdapter
'--- SELECT
SQL_Select = "SELECT * FROM TEST ORDER BY TESTFIELD1 ASC"
Cmd_Select = New OdbcCommand(SQL_Select, Conn)
DA.SelectCommand = Cmd_Select
'--- UPDATE
SQL_Update = "UPDATE TEST SET TESTFIELD2 = ? WHERE TESTFIELD1 = ?"
Cmd_Update = New OdbcCommand(SQL_Update, Conn)
Cmd_Update.Parameters.Add("@Field2", OdbcType.VarChar, 50, "TESTFIELD2")
Cmd_Update.Parameters.Add("@Field1", OdbcType.Int, 2, "TESTFIELD1")
DA.UpdateCommand = Cmd_Update
'--- DELETE
SQL_Delete = "DELETE FROM TEST WHERE TESTFIELD1 = ?"
Cmd_Delete = New OdbcCommand(SQL_Delete, Conn)
Cmd_Delete.Parameters.Add("@Field1", OdbcType.Int, 2, "TESTFIELD1")
DA.DeleteCommand = Cmd_Delete
'--- INSERT
SQL_Insert = "INSERT INTO TEST VALUES (?, ?)"
Cmd_Insert = New OdbcCommand(SQL_Insert, Conn)
Cmd_Insert.Parameters.Add("@Field1", OdbcType.Int, 2, "TESTFIELD1")
Cmd_Insert.Parameters.Add("@Field2", OdbcType.VarChar, 50, "TESTFIELD2")
DA.InsertCommand = Cmd_Insert
'--- DataSet
DS = New DataSet
DA.Fill(DS, "TEST")
'--- Bind
'TextBox1.DataBindings.Add("Text", DS, "TEST.TESTFIELD1")
'TextBox2.DataBindings.Add("Text", DS, "TEST.TESTFIELD2")
DataGrid1.SetDataBinding(DS, "TEST")
Catch ex As Exception
MessageBox.Show(ex.ToString, "エラーメッセージ")
Finally
If Not IsNothing(Conn) Then Conn.Close()
End Try
End Sub
'--- 削除ボタン
Private Sub btnDELETE_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDELETE.Click
Try
Dim Pos As Integer 'リスト内のポジション
Pos = BindingContext(DS, "TEST").Position
BindingContext(DS, "TEST").RemoveAt(Pos)
DA.DeleteCommand.Parameters("@Field1").Value = CType(TextBox1.Text, Integer)
DA.Update(DS, "TEST")
Catch ex As Exception
MessageBox.Show(ex.ToString, "エラーメッセージ")
End Try
End Sub
'--- 挿入ボタン
Private Sub btnINSERT_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnINSERT.Click
Try
Dim Row As DataRow = DS.Tables("TEST").NewRow
Row(0) = CType(TextBox1.Text, Integer)
Row(1) = CType(TextBox2.Text, String)
DS.Tables("TEST").Rows.Add(Row)
DA.InsertCommand.Parameters("@Field1").Value = CType(TextBox1.Text, Integer)
DA.InsertCommand.Parameters("@Field2").Value = CType(TextBox2.Text, String)
DA.Update(DS, "TEST")
Catch ex As Exception
MessageBox.Show(ex.ToString, "エラーメッセージ")
End Try
End Sub
'--- 更新ボタン
Private Sub btnUPDATE_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUPDATE.Click
Try
Dim Row As DataRow
Dim RowCnt As Integer
RowCnt = 0
For Each Row In DS.Tables("TEST").Rows
If DS.Tables("TEST").Rows(RowCnt).Item(0) = CType(TextBox1.Text, Integer) Then
DS.Tables("TEST").Rows(RowCnt).Item(1) = CType(TextBox2.Text, String)
Exit For
End If
RowCnt = RowCnt + 1
Next
DA.UpdateCommand.Parameters("@Field1").Value = CType(TextBox1.Text, Integer)
DA.UpdateCommand.Parameters("@Field2").Value = CType(TextBox2.Text, String)
DA.Update(DS, "TEST")
Catch ex As Exception
MessageBox.Show(ex.ToString, "エラーメッセージ")
End Try
End Sub
2005 / 10 / 21
[VB.NET] DataSetの連結
'--- テキストボックスに連結
TextBox1.DataBindings.Add("Text", DS, "TEST.TESTFIELD1")
TextBox2.DataBindings.Add("Text", DS, "TEST.TESTFIELD2")
'--- データグリッドに連結 DataGrid1.SetDataBinding(DS, "TEST")
2005 / 10 / 20
[VB.NET|Access] レコードの削除・挿入・更新（OleDb）
http://support.microsoft.com/kb/301248/
Imports System.Data.OleDb
Private Conn As OleDbConnection
Private DA As OleDbDataAdapter
Private DS As DataSet
Private Sub frm_OleDB_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim Cmd_Select As OleDbCommand
Dim Cmd_Update As OleDbCommand
Dim Cmd_Delete As OleDbCommand
Dim Cmd_Insert As OleDbCommand
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\テストDB.mdb"
Dim SQL_Select As String
Dim SQL_Update As String
Dim SQL_Insert As String
Dim SQL_Delete As String
Try
Conn = New OleDbConnection(strConn)
DA = New OleDbDataAdapter
'--- SELECT
SQL_Select = "SELECT * FROM TEST ORDER BY TESTFIELD1 ASC"
Cmd_Select = New OleDbCommand(SQL_Select, Conn)
DA.SelectCommand = Cmd_Select
'--- UPDATE
SQL_Update = "UPDATE TEST SET TESTFIELD2 = ? WHERE TESTFIELD1 = ?"
Cmd_Update = New OleDbCommand(SQL_Update, Conn)
Cmd_Update.Parameters.Add("@Field2", OleDbType.VarChar, 50, "TESTFIELD2")
Cmd_Update.Parameters.Add("@Field1", OleDbType.Integer, 2, "TESTFIELD1")
DA.UpdateCommand = Cmd_Update
'--- DELETE
SQL_Delete = "DELETE FROM TEST WHERE TESTFIELD1 = ?"
Cmd_Delete = New OleDbCommand(SQL_Delete, Conn)
Cmd_Delete.Parameters.Add("@Field1", OleDbType.Integer, 2, "TESTFIELD1")
DA.DeleteCommand = Cmd_Delete
'--- INSERT
SQL_Insert = "INSERT INTO TEST VALUES (?, ?)"
Cmd_Insert = New OleDbCommand(SQL_Insert, Conn)
Cmd_Insert.Parameters.Add("@Field1", OleDbType.Integer, 2, "TESTFIELD1")
Cmd_Insert.Parameters.Add("@Field2", OleDbType.VarChar, 50, "TESTFIELD2")
DA.InsertCommand = Cmd_Insert
'--- DataSet
DS = New DataSet
DA.Fill(DS, "TEST")
'--- Bind
'TextBox1.DataBindings.Add("Text", DS, "TEST.TESTFIELD1")
'TextBox2.DataBindings.Add("Text", DS, "TEST.TESTFIELD2")
DataGrid1.SetDataBinding(DS, "TEST")
Catch ex As Exception
MessageBox.Show(ex.ToString, "エラーメッセージ")
Finally
If Not IsNothing(Conn) Then Conn.Close()
End Try
End Sub
'--- 削除ボタン
Private Sub btnDELETE_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDELETE.Click
Try
Dim Pos As Integer 'リスト内のポジション
Pos = BindingContext(DS, "TEST").Position
BindingContext(DS, "TEST").RemoveAt(Pos)
DA.DeleteCommand.Parameters("@Field1").Value = CType(TextBox1.Text, Integer)
DA.Update(DS, "TEST")
Catch ex As Exception
MessageBox.Show(ex.ToString, "エラーメッセージ")
End Try
End Sub
'--- 挿入ボタン
Private Sub btnINSERT_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnINSERT.Click
Try
Dim Row As DataRow = DS.Tables("TEST").NewRow
Row(0) = CType(TextBox1.Text, Integer)
Row(1) = CType(TextBox2.Text, String)
DS.Tables("TEST").Rows.Add(Row)
DA.InsertCommand.Parameters("@Field1").Value = CType(TextBox1.Text, Integer)
DA.InsertCommand.Parameters("@Field2").Value = CType(TextBox2.Text, String)
DA.Update(DS, "TEST")
Catch ex As Exception
MessageBox.Show(ex.ToString, "エラーメッセージ")
End Try
End Sub
'--- 更新ボタン
Private Sub btnUPDATE_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUPDATE.Click
Try
Dim Row As DataRow
Dim RowCnt As Integer
RowCnt = 0
For Each Row In DS.Tables("TEST").Rows
If DS.Tables("TEST").Rows(RowCnt).Item(0) = CType(TextBox1.Text, Integer) Then
DS.Tables("TEST").Rows(RowCnt).Item(1) = CType(TextBox2.Text, String)
Exit For
End If
RowCnt = RowCnt + 1
Next
DA.UpdateCommand.Parameters("@Field1").Value = CType(TextBox1.Text, Integer)
DA.UpdateCommand.Parameters("@Field2").Value = CType(TextBox2.Text, String)
DA.Update(DS, "TEST")
Catch ex As Exception
MessageBox.Show(ex.ToString, "エラーメッセージ")
End Try
End Sub
2005 / 10 / 20
[VB.NET] DataSet内のデータの取り出し方
Dim RowCnt As Integer = DS.Tables("テーブル名").Rows.Count 'DataSetの列数
Dim ColCnt As Integer = DS.Tables("テーブル名").Columns.Count 'DataSetの行数
Dim Row As DataRow
Dim Col As DataColumn
Dim Data(RowCnt - 1, ColCnt - 1) As String
Dim i As Integer
Dim j As Integer
'例１：
i = 0
For Each Row In DS.Tables("テーブル名").Rows
j = 0
For Each Col In DS.Tables("テーブル名").Columns
Data(i, j) = Row(Col)
j = j + 1
Next
i = i + 1
Next
'例２：
i = 0
For Each Row In DS.Tables("テーブル名").Rows
j = 0
For Each Col In DS.Tables("テーブル名").Columns
Data(i, j) = DS.Tables("テーブル名").Rows(i).Item(j)
j = j + 1
Next
i = i + 1
Next
2005 / 10 / 14
[VB.NET|Access] DataGrid に表示
Imports System.Data.OleDb
'*** Form1
Dim Conn As OleDbConnection
Dim DA As OleDbDataAdapter
Dim DS As DataSet
Dim strSQL As String
Dim DBPath As String
Dim TableName As String
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try '--- DB接続 Try
'1行おきの背景色を設定する
DataGrid1.AlternatingBackColor = Color.FromArgb(120, 255, 255)
'DB接続設定
DBPath = "C:\YAMADA\テストDB.mdb"
TableName = "ユーザーマスタ"
strSQL = "SELECT * FROM UserMst"
'DB接続
Call JetConn.DB_Connect(Conn, DA, DS, strSQL, DBPath, TableName)
'データグリッドにテーブルを表示する
DataGrid1.SetDataBinding(DS, TableName)
'オブジェクトの開放
Call JetConn.DB_Cut(Conn, DA, DS)
Catch '--- DB接続 Catch
'オブジェクトの開放
Call JetConn.DB_Cut(Conn, DA, DS)
MessageBox.Show("値を取得できませんでした。", "エラータイトル")
End Try
End Sub
'*** JetConn.vb
'-----------------------------------------
' ■ DB_Connect ■ DB接続
' Conn = コネクションオブジェクト
' DA = データアダプタオブジェクト
' DS = データセットオブジェクト
' strSQL = SQL文
' mdbPath = Accessパス
' TableName = データセットに設定するテーブル名
'-----------------------------------------
Public Sub DB_Connect( _
ByRef Conn As OleDbConnection, _
ByRef DA As OleDbDataAdapter, _
ByRef DS As DataSet, _
ByVal strSQL As String, _
ByVal mdbPath As String, _
ByVal TableName As String)
Conn = New OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=" & mdbPath)
DA = New OleDbDataAdapter(strSQL, Conn)
DS = New DataSet
DA.Fill(DS, TableName)
End Sub
'-----------------------------------------
' ■ DB_Connect ■ DB切断
' Conn = コネクションオブジェクト
' DA = データアダプタオブジェクト
' DS = データセットオブジェクト
'-----------------------------------------
Public Sub DB_Cut( _
ByRef Conn As OleDbConnection, _
ByRef DA As OleDbDataAdapter, _
ByRef DS As DataSet)
If Not DS Is Nothing Then DS.Dispose()
If Not DA Is Nothing Then DA.Dispose()
If Not Conn Is Nothing Then Conn.Dispose()
End Sub
2005 / 10 / 14
[VB.NET] 二重起動防止
' アプリケーション固定名
Private Shared strAppConstName As String = "daSampleApp"
' 二重起動を禁止するミューテックス
Private Shared mutexObject As System.Threading.Mutex
' アプリケーションのメイン・エントリ・ポイントです。
' _
Shared Sub Main()
' Windows 2000（NT 5.0）以降のみグローバル・ミューテックス利用可
Dim os As OperatingSystem = Environment.OSVersion
If ((os.Platform = PlatformID.Win32NT) And (os.Version.Major >= 5)) Then
strAppConstName = "Global\" + strAppConstName
End If
Try
' ミューテックスを生成する
mutexObject = New System.Threading.Mutex(False, strAppConstName)
Catch e As ApplicationException
' グローバル・ミューテックスによる多重起動禁止
MessageBox.Show("すでに起動しています。2つ同時には起動できません。", "多重起動禁止")
Return
End Try
' ミューテックスを取得する
If (mutexObject.WaitOne(0, False)) Then
' アプリケーションを実行
Application.Run(New Form1)
' ミューテックスを解放する
mutexObject.ReleaseMutex()
Else
' 警告を表示して終了
MessageBox.Show("すでに起動しています。2つ同時には起動できません。", "二重起動禁止")
End If
' ミューテックスを破棄する
mutexObject.Close()
End Sub
2005 / 10 / 14
[VB.NET] 配列ソート
' ---通常のソート（クイックソート）Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim strArray As String() = { _
"いろは ", _
"にほへと ", _
"ちりぬるを ", _
"わか ", _
"よたれそ ", _
"つねならむ " _
}Array.Sort(strArray)
Label1.Text = ""
For i = 0 To 5
Label1.Text = Label1.Text & strArray(i) & vbCrLf
Next i' 出力：
' いろは
' ちりぬるを
' つねならむ
' にほへと
' よたれそ
' わか
End Sub
'--- 独自ソート（Compareメソッド使用）Private Sub Button2_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button2.ClickDim strArray As String() = { _
"いろは ", _
"にほへと ", _
"ちりぬるを ", _
"わか ", _
"よたれそ ", _
"つねならむ " _
}Dim Comp As IComparer = New StrLenComparer
Array.Sort(strArray, Comp)
Label1.Text = ""
For i = 0 To 5
Label1.Text = Label1.Text & strArray(i) & vbCrLf
Next i' 出力：
' わか
' いろは
' よたれそ
' にほへと
' つねならむ
' ちりぬるをEnd Sub
'文字列数比較クラス
Public Class StrLenComparer
Implements IComparerPublic Function Compare(ByVal x As Object, ByVal y As Object) As Integer Implements IComparer.Compare
Dim str1 As String = CType(x, String)
Dim str2 As String = CType(y, String)Return str1.Length - str2.Length
End Function
End Class
'--- 独自ソート（自作クラス使用）Private Sub Button3_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button3.ClickDim strArray As StrLenComparer2() = { _
New StrLenComparer2("いろは "), _
New StrLenComparer2("にほへと "), _
New StrLenComparer2("ちりぬるを "), _
New StrLenComparer2("わか "), _
New StrLenComparer2("よたれそ "), _
New StrLenComparer2("つねならむ ") _
}Array.Sort(strArray)
Label1.Text = ""
For Each WordData As StrLenComparer2 In strArray
Label1.Text = Label1.Text & WordData.strData & vbCrLf
Next' 出力：
' わか
' いろは
' よたれそ
' にほへと
' つねならむ
' ちりぬるを
End Sub
End Class'自作文字列数比較クラス
Public Class StrLenComparer2
Implements IComparable
Public strData As StringPublic Sub New(ByVal str As String)
strData = str
End SubPublic Function Compare(ByVal objWord As Object) As Integer Implements IComparable.CompareTo
Dim str1 As StrLenComparer2 = Me
Dim str2 As StrLenComparer2 = CType(objWord, StrLenComparer2)Return str1.strData.Length - str2.strData.Length
End Function
End Class
2005 / 10 / 14