Click here to Skip to main content
15,893,486 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I want update value of textbox to ms access db but i dont want make any action if textbox left empty

this my code

VB
Dim SavInto As New OleDb.OleDbCommand
     Dim adapter2 As New OleDbDataAdapter(SQLstr, Conn)
     SavInto.Connection = Conn
     SavInto.CommandType = CommandType.Text
     SavInto.CommandText = "UPDATE Table1 SET Place = '" & Trim(ComboBox2.SelectedItem) & "' WHERE FilNum ='" & (TextBox1.Text) & "'"
     SavInto.CommandText = "UPDATE Table1 SET Place = '" & Trim(ComboBox2.SelectedItem) & "' WHERE FilNum ='" & (TextBox2.Text) & "'"
     SavInto.CommandText = "UPDATE Table1 SET Place = '" & Trim(ComboBox2.SelectedItem) & "' WHERE FilNum ='" & (TextBox3.Text) & "'"
     SavInto.CommandText = "UPDATE Table1 SET Place = '" & Trim(ComboBox2.SelectedItem) & "' WHERE FilNum ='" & (TextBox4.Text) & "'"
     SavInto.CommandText = "UPDATE Table1 SET Place = '" & Trim(ComboBox2.SelectedItem) & "' WHERE FilNum ='" & (TextBox5.Text) & "'"
     SavInto.CommandText = "UPDATE Table1 SET Place = '" & Trim(ComboBox2.SelectedItem) & "' WHERE FilNum ='" & (TextBox6.Text) & "'"
     SavInto.CommandText = "UPDATE Table1 SET Place = '" & Trim(ComboBox2.SelectedItem) & "' WHERE FilNum ='" & (TextBox7.Text) & "'"
     SavInto.CommandText = "UPDATE Table1 SET Place = '" & Trim(ComboBox2.SelectedItem) & "' WHERE FilNum ='" & (TextBox8.Text) & "'"
     Conn.Open()
     SavInto.ExecuteNonQuery()
     Conn.Close()

i want finish it today maximum any help?
Posted
Comments
[no name] 3-Jul-13 15:59pm    
Any help with what? You have 8 textboxes there in 8 different queries that only one is used. So pick a textbox and if the content is empty do not do anything with it. What exactly is the problem here?
mohannadzayyad 3-Jul-13 16:06pm    
thanks for answer first,,the problem if the user entered just 2 values in textbox 1 and 2 the other textboxs will update the fields in empty values so will be from textbox 3 - 8 empty place
i want the program just presses the filled textbox only
[no name] 3-Jul-13 16:10pm    
Afraid not. The only textbox that would do anything according to your code is TextBox8 since you are overwriting the SQL query over and over.
mohannadzayyad 3-Jul-13 16:19pm    
i got you my fault so any help? i want update all texts entries to the similar with db
you have code and ty

1 solution

Just surround the CommandText bits with a test on the contents of the text box ...

If Trim(TextBox8.Text).Length > 0 Then
     SavInto.CommandText = "UPDATE Table1 SET Place = '" & Trim(ComboBox2.SelectedItem) & "' WHERE FilNum ='" & (TextBox8.Text) & "'"
End If

Note that only your last command will actually be executed as you're overwriting SavInto.CommandText each time.

Finally, do some research on SQL Injection and replace the string concatenation with proper parameters ... http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters.aspx[^]

[Edit - an alternative approach that is extensible, avoids SQL injection and uses a single update statement]

VB
Dim tbList As StringBuilder = New StringBuilder("(")    'More efficient than string concatenation
        For Each t As Control In Me.GroupBox1.Controls          'I placed all the relevent TextBoxes into an (invisible) group box
            If TypeOf (t) Is TextBox Then
                If Trim(t.Text).Length > 0 Then                 'Only interested in textboxes with content
                    tbList.Append(Trim("'" & t.Text) & "',")    'Build up a list of the textbox contents
                End If
            End If
        Next
        If tbList.Length > 1 Then   'Effectively checks for all boxes being empty
            Dim cmdList As String = tbList.ToString()
            If cmdList.EndsWith(",") Then cmdList = Strings.Left(cmdList, cmdList.Length - 1) 'Remove last comma
            cmdList += ")"          'Put the closing bracket in place
            SavInto.CommandText = "UPDATE Table1 SET Place = ? WHERE FilNum in " & cmdList
            SavInto.Parameters.Add(ComboBox2.SelectedItem)  'Use parameters to avoid SQL Injection
            Conn.Open()
            SavInto.ExecuteNonQuery()       'Execute the single query
            Conn.Close()
        End If
 
Share this answer
 
v2
Comments
mohannadzayyad 3-Jul-13 16:20pm    
yes i got it,, thanks so how can i do update for all text entries should i open and close conn every presses ?
CHill60 3-Jul-13 16:25pm    
You could - it might give performance problems though ... Or you could just open the connection at the top of that block of code, then issue the SavInto.ExecuteNonQuery() after each update of the CommandText ... Or you could rewrite the code so that you only have a single Update statement
CHill60 3-Jul-13 17:15pm    
I've updated my solution with an alternative approach
mohannadzayyad 3-Jul-13 18:18pm    
yes it's work perfectly thank you mr.CHill

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900