Page 1
Page 2
Page 3
If we get an error, we display this using a generalized error handler for database errors. We supply it with the exception, and the source of the error.
Public Sub DisplaySQLErrors(ByVal ex As SqlException, ByVal Src As String)
Dim I As Int32
Dim strX As String
strX = "Error from " & Src & vbCrLf
For I = 0 To ex.Errors.Count - 1
strX = strX & "Index #" & I & vbCrLf & "Error:" & ex.Errors(I).ToString() & vbCrLf
Next
strX = strX & "(" & Hex(Err.Number) & ")"
MsgBox(strX, MsgBoxStyle.Information, "Sql Error")
End Sub
Connected
Once connected, we display buttons allowing us to list tables, and add records. When we have listed the tables, we have a context menu on the list box, allowing us to list all the records from a table. If the listed table is ‘stores’, then we have a context menu allowing us to delete records.
Add allows us to add records to the ‘stores’ table. Figure 2 shows us the ‘connected’ screen.
Figure 1 - Connected to SQL Server
Listing the tables
To list the tables, we select from ‘information_schema.tables’, and put the records we retrieve in a listbox. We read the records using a data adapter, and then fill a dataset with the results.
strSql = "Select Table_Name from information_schema.tables where table_Type = 'Base Table' order by Table_Name"
Try
sqlCmd = New SqlCommand(strSql, sqlConn)
sqlCmd.CommandType = CommandType.Text
sqlda = New SqlDataAdapter(sqlCmd)
dsGeneral = New DataSet
sqlda.Fill(dsGeneral, "General")
Catch Ex As SqlException
Cursor.Current = Cursors.Default
DisplaySQLErrors(Ex, strSql)
Exit Sub
End Try
sqlCmd = Nothing
sqlda = Nothing
At this point, we have any records retrieved in our dataset, dsGeneral. We read down the dataset, and insert all our records into the listbox.
If dsGeneral.Tables("General").Rows.Count <> 0 Then
Dim ict As Integer
lstTables.Items.Clear()
With dsGeneral.Tables("General")
For ict = 0 To .Rows.Count - 1
lstTables.Items.Add(.Rows(ict).Item("Table_Name"))
Next
End With
End If
We now have a listbox containing all the user tables from our ‘pubs’ database, as shown in Figure 2. Tap and hold on an item in the listbox to activate the context menu, allowing us to list all the records in the selected table.
Figure 2 – Listing of user tables
Listing records
When we list the records, this time we will put them in a datagrid. We will ‘bind’ the datagrid to the dataset. We pass the function a table name, and then list the records.
strSql = "Select * from " & strTable
Try
sqlCmd = New SqlCommand(strSql, sqlConn)
sqlCmd.CommandType = CommandType.Text
sqlda = New SqlDataAdapter(sqlCmd)
dsGeneral = New DataSet
sqlda.Fill(dsGeneral, "General")
grdResults.Enabled = False
grdResults.DataSource = dsGeneral.Tables(0)
grdResults.Enabled = True
grdResults.Location = New System.Drawing.Point(0, 0)
grdResults.Visible = True
grdResults.BringToFront()
If strTable = "stores" Then
grdResults.ContextMenu = ctxDelete
Else
grdResults.ContextMenu = Nothing
End If
Catch Ex As SqlException
Cursor.Current = Cursors.Default
DisplaySQLErrors(Ex, strSql)
Exit Sub
End Try
sqlCmd = Nothing
sqlda = Nothing
dsGeneral = Nothing
As the datagrid is ‘bound’ to our dataset, the data appears in the grid automatically when we set the datasource of the grid to the dataset.
Figure 3 shows the records listed from the ‘stores’ table.
Figure 3 – Data from ‘stores’ table.
Previous Page
Next Page