Forms over Data Video Series -- What Next?

Forms over Data Video Series -- What Next?

  • Comments 36

Yesterday I posted the first 8 videos of the Visual Basic Forms over Data "How-to" video series on MSDN. These videos are for those who are just starting out with databases and Windows Forms data binding. I plan on continuing the series with more intermediate topics like custom object binding, adding validation and business logic, and how to manage requirements changes. What kind of topics are you interested in seeing here? Post a comment or drop me a line and let me know.

Those interested in the sample code I created in this series so far, see the attachment to this post.

Enjoy!

Attachment: OrderManager.zip
Leave a Comment
  • Please add 5 and 5 and type the answer here:
  • Post
  • Hi James,

    In that situation I usually add code into the TableAddNew event handler on the DataTable's partial class to fill in an appropriate default value and then just check for that value in the validation.

    The other option is to check the .HasErrors property before allowing a new record to be added.

    HTH,

    -B

  • Hi Janet,

    You'll want to make sure that you have SQL-Server 2005 Express installed in order to access the dabase. You should be able to access it in the Database/Server Explorer in VS.

    HTH,

    -Beth

  • I Beth! I am so Glade I have seen your beautiful Face.... your voice and your inteligence have helpped so many people like me. thanks ever so much

  • Hi beth,

    Its really good to know people like you are available to help dummies like us achieve, i am new to vb2005 an have created a programm to add,delete,update and view when i publish it works fine on the system it was built on but i cant use it on anoda system guess i hard coded the cnxtn strings. please how do set my connction string so i can modyfy from the config.exe file.

  • Hi Steve,

    This video explains how to set up your connection strings in the settings file: http://msdn.microsoft.com/en-us/vbasic/bb643826.aspx

    This is also information on working with settings: http://msdn.microsoft.com/en-us/library/ms379611(VS.80).aspx

    HTH,

    -B

  • hi beth,  

    your last responce was actually really helpful but like oliver twist another issue has come up, now i habe a db where i can view, update and add but when i want to call up what i just added it tells me it cant find it. please what am i doing wrong. here is my code.

    'Option Strict On

    Option Explicit On

    Imports System

    Imports System.data

    Imports System.Data.SqlClient

    Imports System.Data.SqlTypes

    Imports System.Configuration.ConfigurationManager

    Public Class LIB_FORM

       Inherits System.Windows.Forms.Form

       Dim myConnection As New SqlClient.SqlConnection

       Dim myConnString As String = conn_ConnectionString

       Dim conn As New SqlClient.SqlConnection

       Dim connectionstring As String = conn_ConnectionString

       Dim conn_ConnectionString As String = "Data Source=.\STEEVE;AttachDbFilename=C:\Users\SOMALI.FAPBL\Documents\Visual Studio 2005\Projects\Library Indexing\library_standalone.mdf;Integrated Security=True;Connect Timeout=30;User Instance=true"

       Dim SqlConnection As New SqlConnection

       Public Shared Function GetConnectionString(ByVal strConnection As String) As String

           'Declare a string to hold the connection string

           Dim sReturn As New String("")

           If Not String.IsNullOrEmpty(strConnection) Then

               sReturn = ConfigurationManager.ConnectionStrings(strConnection).ConnectionString

           Else

               Return ConfigurationManager.ConnectionStrings("conn_ConnectionString").ConnectionString

           End If

           Return sReturn

       End Function

       Private Sub LIB_FORM_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

       End Sub

       Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click

           Addlibrary_table(conn_ConnectionString)

       End Sub

       Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click

           If MsgBox("ARE YOU SURE YOU WANT TO DELETE THE RECORD", MsgBoxStyle.YesNo, "DELETE") = MsgBoxResult.No Then

           End If

           Deletelibrary_table()

       End Sub

       Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnupdate.Click

           Updatelibrary_table(conn_ConnectionString)

       End Sub

       Private Sub textpinnumber_Validating(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles textpinnumber.Validating

           Try

               If Me.textpinnumber.Text.Length <= 13 Then

                   MsgBox("INCORRECT PIN NUMBER.", MsgBoxStyle.OkOnly, "INVALID")

                   Throw New Exception("Please a Enter a Valid PIN")

                   textpinnumber.Focus()

               ElseIf Not textpinnumber.Text.StartsWith("PEN") Then

                   MsgBox("INCORRECT PIN NUMBER FORMAT.", MsgBoxStyle.OkOnly, "INVALID")

                   Throw New Exception("Please Enter a Valid Pin")

                   textpinnumber.Focus()

               End If

           Catch ex As Exception

               MsgBox(ex.Message)

               myConnection.Close()

               myConnection.Dispose()

           End Try

       End Sub

       Public Sub SelectMyData(ByVal myConnString As String, ByVal pinnumber As String)

           Dim daMyName As New SqlDataAdapter

           'Dim drsql As SqlDataReader

           Dim dsMyName As New DataSet

           Dim mySelectQuery As String = "Select pinnumber, surname, firstname, othernames, organisation, emp_address, rack, ccolumn, row, remark From LIBRARY_TABLE Where ( PINNUMBER like '%" & pinnumber & "' );"

           Dim myConnection As New SqlConnection(myConnString)

           Dim myCommand As New SqlCommand(mySelectQuery, myConnection)

           myConnection.Open()

           myCommand = New SqlCommand(mySelectQuery, myConnection)

           daMyName.SelectCommand = myCommand

           daMyName.Fill(dsMyName)

           If dsMyName.Tables(0).Rows.Count = 0 Then

               MsgBox("Pinnumber not found.", MsgBoxStyle.OkOnly, "VIEW")

               textpinnumber.Focus()

           Else

               textpinnumber.Text = dsMyName.Tables(0).Rows(0)("pinnumber").ToString()

               textsurname.Text = dsMyName.Tables(0).Rows(0)("surname").ToString()

               textfirstname.Text = dsMyName.Tables(0).Rows(0)("firstname").ToString()

               textothernames.Text = dsMyName.Tables(0).Rows(0)("othernames").ToString()

               textorganisation.Text = dsMyName.Tables(0).Rows(0)("organisation").ToString()

               textaddress.Text = dsMyName.Tables(0).Rows(0)("emp_address").ToString()

               textrack.Text = dsMyName.Tables(0).Rows(0)("rack").ToString()

               textccolumn.Text = dsMyName.Tables(0).Rows(0)("ccolumn").ToString()

               textrow.Text = dsMyName.Tables(0).Rows(0)("row").ToString()

               textremark.Text = dsMyName.Tables(0).Rows(0)("remark").ToString()

           End If

           myConnection.Close()

           myConnection.Dispose()

           myCommand.Dispose()

       End Sub

       Private Sub btnview_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnview.Click

           Dim searchParameter As String

           searchParameter = textpinnumber.Text

           SelectMyData(conn_ConnectionString, searchParameter)

       End Sub

       Private Sub Updatelibrary_table(ByVal myConnString As String)

           ' This sub is used to update and existing record with values

           ' from the form.

           Dim updatemydata As String

           Dim myConnection As New SqlConnection(myConnString)

           Dim intRowsAffected As Integer

           myConnection = New SqlConnection(myConnString)

           updatemydata = "Update dbo.library_table Set pinnumber = '" & textpinnumber.Text & "', surname = '" & textsurname.Text & "', firstname = '" & textfirstname.Text & "', othernames = '" & textothernames.Text & "', organisation = '" & textorganisation.Text & "', emp_address = '" & textaddress.Text & "', rack = '" & textrack.Text & "', ccolumn = '" & textccolumn.Text & "', row = '" & textrow.Text & "', remark = '" & textremark.Text & "' Where ( pinnumber = '" & textpinnumber.Text & "' )"

           Dim myCommand As New SqlCommand(updatemydata, myConnection)

           myConnection.Open()

           intRowsAffected = myCommand.ExecuteNonQuery()

           If intRowsAffected < 1 Then

               MsgBox("Update Failed.", MsgBoxStyle.Information, "Update")

               textpinnumber.Focus()

           Else

               MsgBox("Update successfull.", MsgBoxStyle.Information, "Update")

               textpinnumber.Focus()

           End If

           ' Close and Clean up objects

           ClearForm()

           myConnection.Close()

           myCommand.Dispose()

           myConnection.Dispose()

       End Sub

       Private Sub LIB_FORMClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing

           If MsgBox("Are you sure you want to exit?", MsgBoxStyle.YesNo, "Exit") = MsgBoxResult.No Then

               e.Cancel = True

           End If

       End Sub

       Private Sub Deletelibrary_table()

           Dim deletemydata As String

           'Dim delete As String

           Dim intRowsAffected As Integer

           Dim myConnection As New SqlConnection(myConnString)

           'Dim e As DialogResult

           deletemydata = "Delete From dbo.LIBRARY_TABLE Where ( pinnumber = '" & textpinnumber.Text & "' )"

           myConnection.Open()

           Dim myCommand As New SqlCommand(deletemydata, myConnection)

           intRowsAffected = myCommand.ExecuteNonQuery()

           If intRowsAffected < 1 Then

               MsgBox("Delete Failed. pinnumber " & textpinnumber.Text & _

                      " not found.", MsgBoxStyle.OkOnly, "Delete")

           Else

               MsgBox("Delete successfull " & textpinnumber.Text, MsgBoxStyle.OkOnly, "Delete")

           End If

           ClearForm()

           myConnection.Close()

           myConnection.Dispose()

           myCommand.Dispose()

       End Sub

       Private Sub Addlibrary_table(ByVal myConnString As String)

           Dim myInsertQuery As String

           Dim myConnection As New SqlConnection(myConnString)

           Dim pinnumber As String = textpinnumber.Text

           Dim intRowsAffected As Integer

           Try

               If Me.textpinnumber.ToString = True Then

                   MsgBox("PIN ALREADY EXIXT.", MsgBoxStyle.Information, "DUPLICATE")

                   Throw New Exception("Cannot ADD Client")

               End If

           Catch ex As Exception

           End Try

           myInsertQuery = "INSERT INTO LIBRARY_TABLE VALUES ( '" & textpinnumber.Text & "', '" & textsurname.Text & "', '" & _

                   textfirstname.Text & "', '" & textothernames.Text & "', '" & textorganisation.Text & "','" & textaddress.Text & "','" & textrack.Text & "','" & textccolumn.Text & "','" & textrow.Text & "','" & textremark.Text & "' )"

           myConnection = New SqlConnection(myConnString)

           myConnection.Open()

           Dim mycommand As New SqlCommand(myInsertQuery, myConnection)

           mycommand = New SqlCommand(myInsertQuery, myConnection)

           intRowsAffected = mycommand.ExecuteNonQuery()

           mycommand.ExecuteNonQuery()

           If intRowsAffected = 0 Then

               MsgBox("PIN Already Exist.", MsgBoxStyle.Critical, "ADD")

           End If

           If intRowsAffected = 1 Then

               MsgBox("Record added successfully.", MsgBoxStyle.OkOnly, "ADD")

               ClearForm()

               myConnection.Close()

               myConnection.Dispose()

               mycommand.Dispose()

           End If

           ClearForm()

           textpinnumber.Focus()

           myConnection.Close()

           myConnection.Dispose()

           mycommand.Dispose()

       End Sub

       Private Sub ClearForm()

           ' Clear the data entry form.

           textpinnumber.Text = ""

           textsurname.Text = ""

           textfirstname.Text = ""

           textothernames.Text = ""

           textorganisation.Text = ""

           textaddress.Text = ""

           textrack.Text = ""

           textccolumn.Text = ""

           textremark.Text = ""

           textrow.Text = ""

       End Sub

    End Class

Page 3 of 3 (36 items) 123