VB.NET
Code
(Use these code snippets at your own risk. No guarantee is included.)
Override
DataGrid Behavior to Highlight Entire Row
Public Class MyDataGrid
Inherits DataGridS
Protected Overrides Sub OnMouseDown(ByVal e As MouseEventArgs)
Dim hit As DataGrid.HitTestInfo = Me.HitTest(e.X, e.Y)
MyBase.OnMouseDown(e)
If hit.Row < 0 Then
Return
End If
[Select](hit.Row)
End Sub
End Class
Make the DataGrid
on the form an instance of "MyDataGrid" rather
than the standard DataGrid. Go in an edit the form designer
code declaration from:
Friend WithEvents
DataGrid1 As System.Windows.Forms.DataGrid to
Friend WithEvents
DataGrid1 As MyDataGrid
Connection
String and Public Declarations
Public Class Form1
Inherits System.Windows.Forms.Form
Public myConn As New SqlConnection("Data Source=localhost;Initial
Catalog=Northwind;Integrated Security=True")
Public ds As New DataSet
Public strSelect = "SELECT * FROM Categories"
Public da As New SqlDataAdapter(strSelect, myConn)
Public autogen As New SqlCommandBuilder(da)
Fill
a DataGrid from a DataSet (selected above)
da.Fill(ds, "Categories")
DataGrid1.SetDataBinding(ds, "Categories")
DataGrid1.AllowSorting = False
DataGrid1.ReadOnly = True
Add
a New Record and Refresh the DataGrid
Dim row As DataRow
Dim dt As DataTable = ds.Tables("Categories")
row = dt.NewRow()
'row("CategoryID") is an autonumber
row("CategoryName") = "VB.NET App"
row("Description") = "Description of
Application"
dt.Rows.Add(row)
da.Update(ds, "Categories")
ds.Clear()
da.Fill(ds, "Categories")
SQL
DELETE from SqlCommand
Dim sSQL As String
Dim cmd As New SqlCommand(sSQL, myConn)
Try
If myConn.State = ConnectionState.Closed Then
myConn.Open()
End If
cmd.CommandText = "DELETE FROM Categories WHERE
[CategoryID] >= '9'"
cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message)
End Try
ds.Clear()
da.Fill(ds, "Categories")
myConn.Close()
SQL
CREATE TABLE Code
Dim sql As String
Dim myCmd As New SqlCommand(sql, myConn)
Try
If myConn.State = ConnectionState.Closed Then myConn.Open()
'DropTable()
myCmd.CommandText = "CREATE TABLE b_HL ("
_
& "[Code] varchar(10) NOT NULL," _
& "[Desc] varchar(20) NOT NULL," _
& "PRIMARY KEY ([Code])) "
myCmd.ExecuteNonQuery()
MsgBox("Table has been created.")
Catch ex As Exception
MsgBox(ex.Message)
End Try
ds.Clear()
da.Fill(ds, "Categories")
myConn.Close()
SQL
DROP TABLE Code
Dim sql As String
Dim myCmd As New SqlCommand(sql, myConn)
Try
If myConn.State = ConnectionState.Closed Then myConn.Open()
myCmd.CommandText = "DROP TABLE b_HL"
myCmd.ExecuteNonQuery()
MsgBox("Table has been deleted.")
Catch ex As Exception
MsgBox(ex.Message)
End Try
myConn.Close()
TextBox
Select Text0
txtCatID.SelectionLength = Len(txtCatID.Text)
txtCatID.SelectionStart = 0
Find
Row in a DataTable, Change a Field, and Save
Dim dt As DataTable = ds.Tables("Categories")
Dim row As DataRow = dt.Select("[CategoryID] =
8")(0)
If row Is Nothing Then
MsgBox("Row is nothing.")
Else
MsgBox("Row is something.")
End If
If myConn.State = ConnectionState.Closed
Then myConn.Open()
row("Description") = "45678"
da.Update(ds, "Categories")
ds.Clear()
da.Fill(ds, "Categories")
myConn.Close()
Fill
a ListBox from Items in a DataSet
Dim h As Short
With ds
For h = 0 To ds.Tables("categories").Rows.Count
- 1
ListBox1.Items.Add(ds.Tables("Categories").Rows(h).Item(1))
Next
End With
Console.WriteLine
All ColumnNames in a DataSet
Private Sub btnListAll_Click(ByVal
sender As System.Object, ByVal e As System.EventArgs)
Handles btnListAll.Click
Dim sSQL As String
Dim cmd As New SqlCommand(sSQL, myConn)
Dim ds As New DataSet
Dim strSelect = "SELECT * FROM Categories"
Dim da As New SqlDataAdapter(strSelect, myConn)
Try
If myConn.State = ConnectionState.Closed Then
myConn.Open()
End If
cmd.CommandText = "SELECT * FROM Categories"
cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message)
End Try
ds.Clear()
da.Fill(ds, "Categories")
myConn.Close()
Dim h As Short
With ds
For h = 0 To ds.Tables("Categories").Columns.Count
- 1
Console.WriteLine("Col Name: " & .Tables("Categories").Columns(h).ColumnName)
Next
End With
Stop
With ds
For h = 0 To ds.Tables("categories").Rows.Count
- 1
ListBox1.Items.Add(ds.Tables("Categories").Rows(h).Item(1))
Next
End With
'For h = 0 To ds.Tables("categories").Rows.Count
- 1
' Console.WriteLine(h & " " & ds.Tables("Categories").Rows(h).Item(1))
' Stop
'Next
End Sub
List
All Rows From a DataSet Through a DataAdapter
Private Sub btnDataAdapter_Click(ByVal
sender As System.Object, ByVal e As System.EventArgs)
Handles btnDataAdapter.Click
Dim da As New SqlDataAdapter("SELECT
* FROM Categories", myConn)
Dim ds As New DataSet
da.Fill(ds, "Categories")
Dim x As Short
For x = 0 To ds.Tables("Categories").Rows.Count
- 1
Console.WriteLine(ds.Tables("Categories").Rows(x).Item(1))
Next
End Sub
DataReader
to Read from a Table
Dim a As String
myConn.Open()
Dim myCommand = New SqlCommand("Select * from tblPlanes",
myConn)
Dim dr = myCommand.ExecuteReader()
While dr.Read()
a = a & dr("Code").ToString() & "
" & dr("Description").ToString()
& vbCrLf
End While
MsgBox(a, MsgBoxStyle.Information, "Boeing Jets")
dr.Close()
myConn.Close()
SQL
Command and ExecuteReader to Fill ListBox
Dim myCommand = New SqlCommand("Select
* from Categories", myConn)
Dim dr = myCommand.ExecuteReader()
While dr.Read()
ListBox2.Items.Add(dr("CategoryName"))
End While
dr.Close()
SQL
Command INSERT into Table and Refresh DataGrid
Dim ra As Integer
Dim myCommand As New SqlCommand
myCommand.Connection = myConn
myCommand.CommandText = "INSERT INTO Categories
(CategoryName, Description) VALUES ('VB.Net Insert','"
& Now & "')"
ra = myCommand.ExecuteNonQuery()
myCommand.Dispose()
MsgBox(ra & " record(s) inserted into database
table.")
'Refresh DataGrid to show new records
Dim da As New SqlDataAdapter("SELECT * FROM Categories",
myConn)
Dim ds As New DataSet
ds.Clear()
da.Fill(ds, "Categories")
DataGrid1.SetDataBinding(ds, "Categories")
DataGrid1.Refresh()
SQL
Command DELETE FROM Table and Refresh DataGrid
Dim ra As Integer
Dim myCommand As New SqlCommand
myCommand.Connection = myConn
myCommand.CommandText = "DELETE FROM Categories
WHERE CategoryID > '100'"
ra = myCommand.ExecuteNonQuery()
myCommand.Dispose()
MsgBox(ra & " record deleted from database
table.")
'Refresh DataGrid to show new records
Dim da As New SqlDataAdapter("SELECT * FROM Categories",
myConn)
Dim ds As New DataSet
ds.Clear()
da.Fill(ds, "Categories")
DataGrid1.SetDataBinding(ds, "Categories")
DataGrid1.Refresh()
SQL
Command UPDATE Table and Refresh DataGrid
Dim ra As Integer
Dim myCommand As New SqlCommand
myCommand.Connection = myConn
myCommand.CommandText = "UPDATE Categories SET
Description = 'Edited: " & Format(Now, "hh:mm")
& "' WHERE CategoryID = '4115'"
ra = myCommand.ExecuteNonQuery()
myCommand.Dispose()
'MsgBox(ra & " records have been updated.")
'Refresh DataGrid to show new records
Dim da As New SqlDataAdapter("SELECT * FROM Categories",
myConn)
Dim ds As New DataSet
ds.Clear()
da.Fill(ds, "Categories")
DataGrid1.SetDataBinding(ds, "Categories")
DataGrid1.Refresh()
SQL
Command COUNT Number of Records in Query
Dim myCommand As New SqlCommand("SELECT
COUNT(CategoryID) FROM Categories", myConn)
Dim ra As Short = myCommand.ExecuteScalar()
MsgBox(ra)
|
