Solved

how to pull selected items from rich text box to sql query

Posted on 2014-12-19
23
251 Views
Last Modified: 2014-12-23
Currently I am trying to get the user to input text from textbox to rich text box with a comma separating the numbers.  When the user clicks the button, the commas are gone.  I just wanted to have the last comma in the text remove so it looks like 14170, 18036, which is how it looks in the text box.  The database see the numbers as 1417018036, which cause the query not to work.  When I use one number 14170, the code works great.  Any suggestions.



outputfirst output






Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb

Public Class Form1

    'DATABASE CONNECTION'
    Private Const connectionString As String = "Data Source=mddbsqlpfqa.loe.corp; Database=PowerFaids; " _
& "Trusted_Connection=Yes;"


    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    End Sub

    'Add Roster Token Button'

    Private Sub btnAddRosterToken_Click(sender As Object, e As EventArgs) Handles btnAddRosterToken.Click
        RichTextBox1.SelectionStart = RichTextBox1.TextLength
        RichTextBox1.SelectedText = txtboxRosterToken.Text & ","


        txtboxRosterToken.Clear()
    End Sub

    'Clear Rich Text Box Values'

    Private Sub btnClearRosterTokens_Click(sender As Object, e As EventArgs) Handles btnClearRosterTokens.Click
        RichTextBox1.Clear()
    End Sub

   

    Private Sub CreateUpdate(ByVal rtf As Integer)


        Using cnn As New SqlConnection(connectionString)
            cnn.Open()

            'Run SQL'
            Using mysda As New SqlDataAdapter()

                Using mycmd As New SqlCommand(My.Resources.update_ud, cnn)

                    mycmd.CommandTimeout = 0

                    mysda.SelectCommand = mycmd
                    mycmd.Parameters.AddWithValue("@ROSTER_TOKEN", rtf)
                    mysda.SelectCommand = mycmd

                    Dim RowsEffected As Integer = mycmd.ExecuteNonQuery()
                    MsgBox("The no.of rows effected by update query are " & RowsEffected.ToString)

                End Using
            End Using
        End Using
    End Sub

    Private Sub btnUpdateUD_Click(sender As Object, e As EventArgs) Handles btnUpdateUD.Click



        Dim RT As Integer = 0
        RT = RichTextBox1.Text

        RichTextBox1.Text = RichTextBox1.Text.Remove(RichTextBox1.TextLength - 1, 1)


        MsgBox(RT)

        CreateUpdate(RT)

     

    End Sub



    Private Sub btnExitApplication_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExitApplication.Click
        Dim x As Integer = MsgBox("Are you sure you want to quit?", MsgBoxStyle.YesNo)
        If x = DialogResult.Yes Then
            Me.Close()
        End If
    End Sub

End Class

Open in new window

0
Comment
Question by:cmed
  • 14
  • 7
  • 2
23 Comments
 
LVL 35

Assisted Solution

by:Kimputer
Kimputer earned 166 total points
Comment Utility
You caused the problem yourself by using:

Dim RT As Integer = 0
RT = RichTextBox1.Text
An integer cannot have multiple comma's!

You can solve it by using a String (and thereby changing it to String in other related places in your code)

for instance, can rtf be a string in this piece of code?

mycmd.Parameters.AddWithValue("@ROSTER_TOKEN", rtf)

Also, change some order of code if you decide String can be used:


        Dim RT As String = ""

If strings.right(RichTextBox1.Text,1) = "," then
        RichTextBox1.Text = RichTextBox1.Text.Remove(RichTextBox1.TextLength - 1, 1)
end if

        RT = RichTextBox1.Text

        MsgBox(RT)

        CreateUpdate(RT)
0
 
LVL 32

Assisted Solution

by:it_saige
it_saige earned 334 total points
Comment Utility
You could do something like this:
Private Sub btnUpdateUD_Click(sender As Object, e As EventArgs) Handles btnUpdateUD.Click
	Dim RT As Integer = 0
	Dim rtSplit() As String = RichTextBox1.Split(New String() {","}, StringSplitOptions.RemoveEmptyEntries)
	For Each item In rtSplit
		If Integer.TryParse(item, tempInt) Then
			MsgBox(item)
			CreateUpdate(tempInt)
		End If
	Next
End Sub

Open in new window


-saige-
0
 

Author Comment

by:cmed
Comment Utility
That part works, but when i want to see the output it still show that 0 lines was updated when there are suppose to be lines updated. first outputsecond output
0
 
LVL 35

Expert Comment

by:Kimputer
Comment Utility
It depends if there should be more queries made when more comma's are used (I didn't know). The second solution by @it_saige takes care of that.
I didn't make the link, as I didn't understand why you'd need to remove the end comma if you'd take the data only. I focused on the comma, and hence layout (as your question seemed to be about only the last comma as a layout problem), while @it_saige focused on the actual data (and hence has queries per data).
0
 

Author Comment

by:cmed
Comment Utility
The query works fine.  The problem that I am having is the message box that not displaying the rows that were updated.  If I input one token, then I am able to see the rows updated (see picture 1), but if i enter more than one, the message will say 0 rows have been updated (see picture2).  I know that it has something to do with this line in my code:

Dim RowsEffected As Integer = mycmd.ExecuteNonQuery()
                    MsgBox("The no.of rows effected by update query are " & RowsEffected.ToString)

Any suggestion?


picture 1picture 2
0
 
LVL 32

Accepted Solution

by:
it_saige earned 334 total points
Comment Utility
Which version of the code to call CreateUpdate are you using?

-saige-
0
 

Author Comment

by:cmed
Comment Utility
I use this version

 Dim RT As String = 0
        RT = RichTextBox1.Text

        RT = RichTextBox1.Text.Remove(RichTextBox1.TextLength - 1, 1)
 
I wanted to use the version you sent, but I had some issues.  I declare tempInt as a string, was trying to figure out the richboxtext1.split issue. See picture.  picture 1
0
 

Author Comment

by:cmed
Comment Utility
@ it Saige

It worked.  I had to fix a couple of things on my end. Thanks.
0
 
LVL 32

Expert Comment

by:it_saige
Comment Utility
Not a problem.  Glad you got it sorted out.

-saige-
0
 

Author Comment

by:cmed
Comment Utility
@i it_saige

I was trying to get the code to show the total lines updated, but I like your example better.  Is there a way to only add commas to the rich text box if the user chooses to input another token?
0
 
LVL 32

Expert Comment

by:it_saige
Comment Utility
Could you perhaps expand on that a little bit.  Only add commas?

-saige-
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:cmed
Comment Utility
I am change the design format to allow user to input tokens into the rich text box instead of the textbox
0
 
LVL 32

Expert Comment

by:it_saige
Comment Utility
Ahh.  I get what you are asking now.  Making the design changes that you want actually makes it more difficult to complete your task.  Questions like -
1.  How does a user indicate that they have finished entering data?
2.  How do we determine the token value?
3.  Is the token value well-defined?
4.  Is the token value the correct data-format?

Come into play.  Your current design answers most of these questions.  Although I would suggest design changes that check the value when entered.  I would also recommend using a ListBox instead of a RichTextBox.

Ultimately, I would probably implement something like:

Form1.Designer.vb -
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Partial Class Form1
	Inherits System.Windows.Forms.Form

	'Form overrides dispose to clean up the component list.
	<System.Diagnostics.DebuggerNonUserCode()> _
	Protected Overrides Sub Dispose(ByVal disposing As Boolean)
		Try
			If disposing AndAlso components IsNot Nothing Then
				components.Dispose()
			End If
		Finally
			MyBase.Dispose(disposing)
		End Try
	End Sub

	'Required by the Windows Form Designer
	Private components As System.ComponentModel.IContainer

	'NOTE: The following procedure is required by the Windows Form Designer
	'It can be modified using the Windows Form Designer.  
	'Do not modify it using the code editor.
	<System.Diagnostics.DebuggerStepThrough()> _
	Private Sub InitializeComponent()
		Me.bntAdd = New System.Windows.Forms.Button()
		Me.btnClear = New System.Windows.Forms.Button()
		Me.btnUpdate = New System.Windows.Forms.Button()
		Me.Label1 = New System.Windows.Forms.Label()
		Me.btnExit = New System.Windows.Forms.Button()
		Me.TextBox1 = New System.Windows.Forms.TextBox()
		Me.ListBox1 = New System.Windows.Forms.ListBox()
		Me.SuspendLayout()
		'
		'bntAdd
		'
		Me.bntAdd.Location = New System.Drawing.Point(199, 12)
		Me.bntAdd.Name = "bntAdd"
		Me.bntAdd.Size = New System.Drawing.Size(125, 23)
		Me.bntAdd.TabIndex = 0
		Me.bntAdd.Text = "Add Roster Token"
		Me.bntAdd.UseVisualStyleBackColor = True
		'
		'btnClear
		'
		Me.btnClear.Location = New System.Drawing.Point(199, 41)
		Me.btnClear.Name = "btnClear"
		Me.btnClear.Size = New System.Drawing.Size(125, 23)
		Me.btnClear.TabIndex = 1
		Me.btnClear.Text = "Clear Roster Tokens"
		Me.btnClear.UseVisualStyleBackColor = True
		'
		'btnUpdate
		'
		Me.btnUpdate.Location = New System.Drawing.Point(199, 70)
		Me.btnUpdate.Name = "btnUpdate"
		Me.btnUpdate.Size = New System.Drawing.Size(125, 23)
		Me.btnUpdate.TabIndex = 2
		Me.btnUpdate.Text = "Update UD"
		Me.btnUpdate.UseVisualStyleBackColor = True
		'
		'Label1
		'
		Me.Label1.AutoSize = True
		Me.Label1.Location = New System.Drawing.Point(12, 16)
		Me.Label1.Name = "Label1"
		Me.Label1.Size = New System.Drawing.Size(75, 13)
		Me.Label1.TabIndex = 5
		Me.Label1.Text = "Roster Token:"
		'
		'btnExit
		'
		Me.btnExit.Location = New System.Drawing.Point(199, 227)
		Me.btnExit.Name = "btnExit"
		Me.btnExit.Size = New System.Drawing.Size(125, 23)
		Me.btnExit.TabIndex = 9
		Me.btnExit.Text = "Exit"
		Me.btnExit.UseVisualStyleBackColor = True
		'
		'TextBox1
		'
		Me.TextBox1.Location = New System.Drawing.Point(93, 13)
		Me.TextBox1.Name = "TextBox1"
		Me.TextBox1.Size = New System.Drawing.Size(100, 20)
		Me.TextBox1.TabIndex = 10
		'
		'ListBox1
		'
		Me.ListBox1.FormattingEnabled = True
		Me.ListBox1.Location = New System.Drawing.Point(15, 39)
		Me.ListBox1.Name = "ListBox1"
		Me.ListBox1.Size = New System.Drawing.Size(178, 212)
		Me.ListBox1.TabIndex = 11
		'
		'Form1
		'
		Me.AcceptButton = Me.bntAdd
		Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
		Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
		Me.ClientSize = New System.Drawing.Size(332, 262)
		Me.Controls.Add(Me.ListBox1)
		Me.Controls.Add(Me.TextBox1)
		Me.Controls.Add(Me.btnExit)
		Me.Controls.Add(Me.Label1)
		Me.Controls.Add(Me.btnUpdate)
		Me.Controls.Add(Me.btnClear)
		Me.Controls.Add(Me.bntAdd)
		Me.Name = "Form1"
		Me.Text = "Form1"
		Me.ResumeLayout(False)
		Me.PerformLayout()

	End Sub
	Friend WithEvents bntAdd As System.Windows.Forms.Button
	Friend WithEvents btnClear As System.Windows.Forms.Button
	Friend WithEvents btnUpdate As System.Windows.Forms.Button
	Friend WithEvents Label1 As System.Windows.Forms.Label
	Friend WithEvents btnExit As System.Windows.Forms.Button
	Friend WithEvents TextBox1 As System.Windows.Forms.TextBox
	Friend WithEvents ListBox1 As System.Windows.Forms.ListBox

End Class

Open in new window


Form1.vb -
Imports System.ComponentModel
Imports System.Data.SqlClient

Public Class Form1
	Private source As New BindingSource()
	Private tokens As BindingList(Of Token)

	Private Sub OnLoad(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
		tokens = New BindingList(Of Token)
		source.DataSource = tokens
		ListBox1.DisplayMember = "Value"
		ListBox1.ValueMember = "Value"
		ListBox1.DataSource = source
		TextBox1.Focus()
	End Sub

	Private Sub OnClosing(ByVal sender As Object, ByVal e As FormClosingEventArgs) Handles MyBase.FormClosing
		If tokens.Count > 0 Then
			Dim result = MessageBox.Show("There are still tokens to be updated.  Are you sure you want to exit?", "Verify Exit", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
			If result = DialogResult.No Then
				e.Cancel = True
			End If
		End If
	End Sub

	Private Sub OnClick(ByVal sender As Object, ByVal e As EventArgs) Handles bntAdd.Click, btnUpdate.Click, btnClear.Click, btnExit.Click
		If TypeOf sender Is Button Then
			Dim btn As Button = DirectCast(sender, Button)
			If btn.Equals(bntAdd) Then
				Dim tempInt As Integer = 0
				If Integer.TryParse(TextBox1.Text, tempInt) Then
					tokens.Add(New Token() With {.Value = tempInt, .IsUpdated = False})
				Else
					MessageBox.Show("Invalid token entered.  Token value must be a number", "Invalid Token Entered", MessageBoxButtons.OK, MessageBoxIcon.Stop)
				End If
				TextBox1.Text = String.Empty
				TextBox1.Focus()
			ElseIf btn.Equals(btnClear) Then
				Dim updated = (From token In tokens Where token.IsUpdated)
				If tokens.Count > 0 AndAlso updated.Count <> tokens.Count Then
					Dim result = MessageBox.Show("There are still tokens to be updated.  Are you sure you want to clear all of the tokens?", "Verify Clear", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
					If result = DialogResult.Yes Then
						tokens.Clear()
					Else
						For Each token In updated.Reverse()
							tokens.Remove(token)
						Next
					End If
				End If
			ElseIf btn.Equals(btnUpdate) Then
				For Each token In tokens
					token.IsUpdated = CreateUpdate(token.Value)
				Next

				For Each token In tokens.Reverse()
					If token.IsUpdated Then
						tokens.Remove(token)
					End If
				Next
			ElseIf btn.Equals(btnExit) Then
				Close()
			End If
		End If
	End Sub

	Private Function CreateUpdate(ByVal rtf As Integer) As Boolean
		Dim isUpdated = False
		Try
			Using cnn As New SqlConnection(connectionString)
				cnn.Open()
				'Run SQL'
				Using mysda As New SqlDataAdapter()
					Using mycmd As New SqlCommand(My.Resources.update_ud, cnn)
						mycmd.CommandTimeout = 0
						mysda.SelectCommand = mycmd
						mycmd.Parameters.AddWithValue("@ROSTER_TOKEN", rtf)
						mysda.SelectCommand = mycmd
						Dim RowsEffected As Integer = mycmd.ExecuteNonQuery()
						MsgBox("The no.of rows effected by update query are " & RowsEffected.ToString)
						If RowsEffected > 0 Then isUpdated = True
					End Using
				End Using
			End Using
		Catch ex As Exception
			isUpdated = False
		End Try
		Return isUpdated
	End Function
End Class

Public Class Token
	Public Property Value() As Integer
	Public Property IsUpdated() As Boolean
End Class

Open in new window


Produces the following output -Capture.JPG
When a token is updated, it is marked as such an removed from the list.  If a token is not updated it stays in the list.  If the user attempts to input and an invalid value they get an error message box.  If the user attempts to leave or clear the form and there are un-updated tokens, they receive a warning message box.

-saige-
0
 

Author Comment

by:cmed
Comment Utility
@ Saige,

I am learning a lot here.  Beginner here.  I was going in a different directions, but will have the users take a look this design also.  I was have the user input the token themselves(see picture) and then create a message box to show them which token they have chosen and ask them to proceed.  I am now trying to figure out how to create a scenario where if the user chooses the correct token then update the code.  if the user chooses the wrong code then do clear selections and start over.
Imports System.Data
Imports System.Data.SqlClient


Public Class Form1

    'DATABASE CONNECTION'
    Private Const connectionString As String = "Data Source=mddbsqlpfqa.loe.corp; Database=PowerFaids; " _
& "Trusted_Connection=Yes;"

    Private Sub btnReset_Click(sender As Object, e As EventArgs) Handles btnReset.Click
        rtbdisplay.Clear()
    End Sub

    'Clear Rich Text Box Values'

    'FUNCTION FOR BUTTON'

    Private Sub CreateUpdate(ByVal rtf As String)

        Using cnn As New SqlConnection(connectionString)
            cnn.Open()

            'Run SQL'
            Using mysda As New SqlDataAdapter()

                Using mycmd As New SqlCommand(My.Resources.update_ud, cnn)

                    mycmd.CommandTimeout = 0

                    mysda.SelectCommand = mycmd
                    mycmd.Parameters.AddWithValue("@ROSTER_TOKEN", rtf)
                    mysda.SelectCommand = mycmd

                    Dim RowsEffected As Integer = mycmd.ExecuteNonQuery()
                    MsgBox(RowsEffected.ToString & " " & "rows have been updated")

                End Using
            End Using
        End Using
    End Sub

    'UPDATE UD'

    Dim tempInt As String
    Private Sub btnUpdateUd_Click(sender As Object, e As EventArgs) Handles btnUpdateUd.Click

        Dim RT As Integer = 0
        Dim rtSplit() As String = rtbdisplay.Text.Split(New String() {","}, StringSplitOptions.RemoveEmptyEntries)
        For Each item In rtSplit
            If Integer.TryParse(item, tempInt) Then
                MsgBox("Roster Token" & " " & item & " " & "has been selected!" & vbCrLf & vbCrLf & "DO YOU WANT TO PROCEED?", MsgBoxStyle.YesNo)
                CreateUpdate(tempInt)
            End If
        Next
    End Sub

    'EXIT APPLICATION'
    Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
        Dim x As Integer = MsgBox("Are you sure you want to quit?", MsgBoxStyle.YesNo)
        If x = DialogResult.Yes Then
            Me.Close()
        End If
    End Sub
End Class

Open in new window



picture
0
 

Author Comment

by:cmed
Comment Utility
Dim RT As Integer = 0
        Dim rtSplit() As String = rtbdisplay.Text.Split(New String() {","}, StringSplitOptions.RemoveEmptyEntries)
        For Each item In rtSplit
            If Integer.TryParse(item, tempInt) Then
                If MsgBox("Roster Token" & " " & item & " " & "has been selected!" & vbCrLf & vbCrLf & "DO YOU WANT TO PROCEED?", MsgBoxStyle.YesNo) = DialogResult.No Then
                  Else
                        CreateUpdate(tempInt)
                    End If
                End If
        Next
    End Sub

Is there an easier way to say this?
Imports System.Data
Imports System.Data.SqlClient


Public Class Form1

    'DATABASE CONNECTION'
    Private Const connectionString As String = "Data Source=mddbsqlpfqa.loe.corp; Database=PowerFaids; " _
& "Trusted_Connection=Yes;"

    Private Sub btnReset_Click(sender As Object, e As EventArgs) Handles btnReset.Click
        rtbdisplay.Clear()
    End Sub

    'Clear Rich Text Box Values'

    'FUNCTION FOR BUTTON'

    Private Sub CreateUpdate(ByVal rtf As String)

        Using cnn As New SqlConnection(connectionString)
            cnn.Open()

            'Run SQL'
            Using mysda As New SqlDataAdapter()

                Using mycmd As New SqlCommand(My.Resources.update_ud, cnn)

                    mycmd.CommandTimeout = 0

                    mysda.SelectCommand = mycmd
                    mycmd.Parameters.AddWithValue("@ROSTER_TOKEN", rtf)
                    mysda.SelectCommand = mycmd

                    Dim RowsEffected As Integer = mycmd.ExecuteNonQuery()
                    MsgBox(RowsEffected.ToString & " " & "rows have been updated")

                End Using
            End Using
        End Using
    End Sub

    'UPDATE UD'

    Dim tempInt As String
    Private Sub btnUpdateUd_Click(sender As Object, e As EventArgs) Handles btnUpdateUd.Click

        Dim RT As Integer = 0
        Dim rtSplit() As String = rtbdisplay.Text.Split(New String() {","}, StringSplitOptions.RemoveEmptyEntries)
        For Each item In rtSplit
            If Integer.TryParse(item, tempInt) Then
                MsgBox("Roster Token" & " " & item & " " & "has been selected!" & vbCrLf & vbCrLf & "DO YOU WANT TO PROCEED?", MsgBoxStyle.YesNo)
                CreateUpdate(tempInt)
            End If
        Next
    End Sub

    'EXIT APPLICATION'
    Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
        Dim x As Integer = MsgBox("Are you sure you want to quit?", MsgBoxStyle.YesNo)
        If x = DialogResult.Yes Then
            Me.Close()
        End If
    End Sub
End Class

Open in new window

0
 

Author Comment

by:cmed
Comment Utility
i did not add everything to last post.  here is the yes or no statement
[embed=snippet 8267547]

Open in new window

Imports System.Data
Imports System.Data.SqlClient


Public Class Form1

    'DATABASE CONNECTION'
    Private Const connectionString As String = "Data Source=mddbsqlpfqa.loe.corp; Database=PowerFaids; " _
& "Trusted_Connection=Yes;"

    Private Sub btnReset_Click(sender As Object, e As EventArgs) Handles btnReset.Click
        rtbdisplay.Clear()
    End Sub

    'Clear Rich Text Box Values'

    'FUNCTION FOR BUTTON'

    Private Sub CreateUpdate(ByVal rtf As String)

        Using cnn As New SqlConnection(connectionString)
            cnn.Open()

            'Run SQL'
            Using mysda As New SqlDataAdapter()

                Using mycmd As New SqlCommand(My.Resources.update_ud, cnn)

                    mycmd.CommandTimeout = 0

                    mysda.SelectCommand = mycmd
                    mycmd.Parameters.AddWithValue("@ROSTER_TOKEN", rtf)
                    mysda.SelectCommand = mycmd

                    Dim RowsEffected As Integer = mycmd.ExecuteNonQuery()
                    MsgBox(RowsEffected.ToString & " " & "rows have been updated")

                End Using
            End Using
        End Using
    End Sub

    'UPDATE UD'

    Dim tempInt As String
    Private Sub btnUpdateUd_Click(sender As Object, e As EventArgs) Handles btnUpdateUd.Click

        Dim RT As Integer = 0
        Dim rtSplit() As String = rtbdisplay.Text.Split(New String() {","}, StringSplitOptions.RemoveEmptyEntries)
        For Each item In rtSplit
            If Integer.TryParse(item, tempInt) Then
                MsgBox("Roster Token" & " " & item & " " & "has been selected!" & vbCrLf & vbCrLf & "DO YOU WANT TO PROCEED?", MsgBoxStyle.YesNo)
                CreateUpdate(tempInt)
            End If
        Next
    End Sub

    'EXIT APPLICATION'
    Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
        Dim x As Integer = MsgBox("Are you sure you want to quit?", MsgBoxStyle.YesNo)
        If x = DialogResult.Yes Then
            Me.Close()
        End If
    End Sub
End Class

Open in new window

Private Sub btnUpdateUd_Click(sender As Object, e As EventArgs) Handles btnUpdateUd.Click

        Dim RT As Integer = 0
        Dim rtSplit() As String = rtbdisplay.Text.Split(New String() {","}, StringSplitOptions.RemoveEmptyEntries)
        For Each item In rtSplit
            If Integer.TryParse(item, tempInt) Then
                If MsgBox("Roster Token" & " " & item & " " & "has been selected!" & vbCrLf & vbCrLf & "DO YOU WANT TO PROCEED?", MsgBoxStyle.YesNo) = DialogResult.No Then
                    rtbdisplay.ResetText()
                Else
                    CreateUpdate(tempInt)
                End If
                End If
        Next
    End Sub

Open in new window

0
 

Author Comment

by:cmed
Comment Utility
Currently, I am trying to enhance this code to have users types numbers into rich text box to do an update.  I would like to have the user just type in the numbers without commas and create a code to add the commas before performing the update.  Have been searching to no avail. I will continue to research, but any suggestions?this how i would like it to lookthis is how it currently look
0
 
LVL 32

Expert Comment

by:it_saige
Comment Utility
As I originally stated, this is a bad design.  It introduces far too many gotcha's with regards to entering data and providing a consistent parse to ensure that:
1.  The user has entered a correct data element.
2.  The user has entered a valid data element.
3.  Where do we split the users entry and how long are the tokens (if a max length).

It is so much easier to use your original design or take the design elements that I had recommended.

-saige-
0
 

Author Comment

by:cmed
Comment Utility
@ it_saige
I did not see where you said this was a bad design, but I will take a look at the other design again.  i will look into the old design thanks
0
 

Author Comment

by:cmed
Comment Utility
@ it_saige

I understand now why my original design was better, and the list box was a great idea.  This is the first time that I used it, so thanks.
0
 
LVL 32

Expert Comment

by:it_saige
Comment Utility
Not a problem...  Believe me it is far too easy to fall on love with a design element.  ;)

-saige-
0
 

Author Comment

by:cmed
Comment Utility
@saige

Everything is working fine.  I wound up using your suggestion, but I do have a question.  Is there a way to highlight the token that has been updated? I would like to show the roster token that has been updated (see picture).  




output
Imports System.ComponentModel
Imports System.Data.SqlClient

Public Class Form1

    'DATABASE CONNECTION'
    Private Const connectionString As String = "Data Source=mddbsqlpfqa.loe.corp; Database=PowerFaids; " _
& "Trusted_Connection=Yes;"

    Private source As New BindingSource()
    Private tokens As BindingList(Of Token)

    Private Sub OnLoad(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
        tokens = New BindingList(Of Token)
        source.DataSource = tokens
        lbo1.DisplayMember = "Value"
        lbo1.ValueMember = "Value"
        lbo1.DataSource = source
        txtbox1.Focus()
    End Sub

    Private Sub OnClosing(ByVal sender As Object, ByVal e As FormClosingEventArgs) Handles MyBase.FormClosing
        If tokens.Count > 0 Then
            Dim result = MessageBox.Show("There are still tokens to be updated.  Are you sure you want to exit?", "Verify Exit", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
            If result = DialogResult.No Then
                e.Cancel = True
            End If
        End If
    End Sub

    Private Sub OnClick(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click, btnUpdate.Click, btnClear.Click, btnExit.Click
        If TypeOf sender Is Button Then
            Dim btn As Button = DirectCast(sender, Button)
            If btn.Equals(btnAdd) Then
                Dim tempInt As Integer = 0
                If Integer.TryParse(txtbox1.Text, tempInt) Then
                    tokens.Add(New Token() With {.Value = tempInt, .IsUpdated = False})
                Else
                    MessageBox.Show("Invalid token entered.  Token value must be a number", "Invalid Token Entered", MessageBoxButtons.OK, MessageBoxIcon.Stop)
                End If
                txtbox1.Text = String.Empty
                txtbox1.Focus()
            ElseIf btn.Equals(btnClear) Then
                Dim updated = (From token In tokens Where token.IsUpdated)
                If tokens.Count > 0 AndAlso updated.Count <> tokens.Count Then
                    Dim result = MessageBox.Show("There are still tokens to be updated.  Are you sure you want to clear all of the tokens?", "Verify Clear", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
                    If result = DialogResult.Yes Then
                        tokens.Clear()
                    Else
                        For Each token In updated.Reverse()
                            tokens.Remove(token)
                        Next
                    End If
                End If
            ElseIf btn.Equals(btnUpdate) Then
                For Each token In tokens
                    token.IsUpdated = CreateUpdate(token.Value)
                Next
                For Each token In tokens.Reverse()
                    If token.IsUpdated Then
                        tokens.Remove(token)
                    End If
                Next
            ElseIf btn.Equals(btnExit) Then
                Dim x As Integer = MessageBox.Show("Are you sure you want to quit?", "Verify Exit", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
                If x = DialogResult.Yes Then
                    Me.Close()
                End If
            End If
        End If
    End Sub

    Private Function CreateUpdate(ByVal rtf As Integer) As Boolean
        Dim isUpdated = False
        Try
            Using cnn As New SqlConnection(connectionString)
                cnn.Open()
                'Run SQL'
                Using mysda As New SqlDataAdapter()
                    Using mycmd As New SqlCommand(My.Resources.update_ud, cnn)
                        mycmd.CommandTimeout = 0
                        mysda.SelectCommand = mycmd
                        mycmd.Parameters.AddWithValue("@ROSTER_TOKEN", rtf)
                        mysda.SelectCommand = mycmd
                        Dim RowsEffected As Integer = mycmd.ExecuteNonQuery()
                        MsgBox(RowsEffected.ToString & " " & "rows have been updated for" & " ", MsgBoxStyle.Information, "Update User Date")
                        If RowsEffected > 0 Then isUpdated = True
                    End Using
                End Using
            End Using
        Catch ex As Exception
            isUpdated = False
        End Try
        Return isUpdated
    End Function

  End Class

Public Class Token
    Public Property Value() As Integer
    Public Property IsUpdated() As Boolean
End Class

Open in new window

0
 

Author Comment

by:cmed
Comment Utility
@saige,

Never mind. I figure it out.
Imports System.ComponentModel
Imports System.Data.SqlClient

Public Class Form1

    'DATABASE CONNECTION'
    Private Const connectionString As String = "Data Source=mddbsqlpfqa.loe.corp; Database=PowerFaids; " _
& "Trusted_Connection=Yes;"

    Private source As New BindingSource()
    Private tokens As BindingList(Of Token)

    Private Sub OnLoad(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
        tokens = New BindingList(Of Token)
        source.DataSource = tokens
        lbo1.DisplayMember = "Value"
        lbo1.ValueMember = "Value"
        lbo1.DataSource = source
        txtbox1.Focus()
    End Sub

    Private Sub OnClosing(ByVal sender As Object, ByVal e As FormClosingEventArgs) Handles MyBase.FormClosing
        If tokens.Count > 0 Then
            Dim result = MessageBox.Show("There are still tokens to be updated.  Are you sure you want to exit?", "Verify Exit", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
            If result = DialogResult.No Then
                e.Cancel = True
            End If
        End If
    End Sub

    Private Sub OnClick(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click, btnUpdate.Click, btnClear.Click, btnExit.Click
        If TypeOf sender Is Button Then
            Dim btn As Button = DirectCast(sender, Button)
            If btn.Equals(btnAdd) Then
                Dim tempInt As Integer = 0
                If Integer.TryParse(txtbox1.Text, tempInt) Then
                    tokens.Add(New Token() With {.Value = tempInt, .IsUpdated = False})
                Else
                    MessageBox.Show("Invalid token entered.  Token value must be a number", "Invalid Token Entered", MessageBoxButtons.OK, MessageBoxIcon.Stop)
                End If
                txtbox1.Text = String.Empty
                txtbox1.Focus()
            ElseIf btn.Equals(btnClear) Then
                Dim updated = (From token In tokens Where token.IsUpdated)
                If tokens.Count > 0 AndAlso updated.Count <> tokens.Count Then
                    Dim result = MessageBox.Show("There are still tokens to be updated.  Are you sure you want to clear all of the tokens?", "Verify Clear", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
                    If result = DialogResult.Yes Then
                        tokens.Clear()
                    Else
                        For Each token In updated.Reverse()
                            tokens.Remove(token)
                        Next
                    End If
                End If
            ElseIf btn.Equals(btnUpdate) Then
                For Each token In tokens
                    token.IsUpdated = CreateUpdate(token.Value)
                Next
                For Each token In tokens.Reverse()
                    If token.IsUpdated Then
                        tokens.Remove(token)
                    End If
                Next
            ElseIf btn.Equals(btnExit) Then
                Dim x As Integer = MessageBox.Show("Are you sure you want to quit?", "Verify Exit", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
                If x = DialogResult.Yes Then
                    Me.Close()
                End If
            End If
        End If
    End Sub

    Private Function CreateUpdate(ByVal rtf As Integer) As Boolean
        Dim isUpdated = False
        Try
            Using cnn As New SqlConnection(connectionString)
                cnn.Open()
                'Run SQL'
                Using mysda As New SqlDataAdapter()
                    Using mycmd As New SqlCommand(My.Resources.update_ud, cnn)
                        mycmd.CommandTimeout = 0
                        mysda.SelectCommand = mycmd
                        mycmd.Parameters.AddWithValue("@ROSTER_TOKEN", rtf)
                        mysda.SelectCommand = mycmd
                        Dim RowsEffected As Integer = mycmd.ExecuteNonQuery()
                        MsgBox(RowsEffected.ToString & " " & "rows have been updated for" & " ", MsgBoxStyle.Information, "Update User Date")
                        If RowsEffected > 0 Then isUpdated = True
                    End Using
                End Using
            End Using
        Catch ex As Exception
            isUpdated = False
        End Try
        Return isUpdated
    End Function

  End Class

Public Class Token
    Public Property Value() As Integer
    Public Property IsUpdated() As Boolean
End Class

Open in new window

0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now