Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: .NET
I want update value of textbox to ms access db but i dont want make any action if textbox left empty
 
this my code
 
   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 3-Jul-13 10:51am
Comments
ThePhantomUpvoter at 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 at 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
ThePhantomUpvoter at 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 at 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

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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]
 
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
  Permalink  
v2
Comments
mohannadzayyad at 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 at 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 at 3-Jul-13 17:15pm
   
I've updated my solution with an alternative approach
mohannadzayyad at 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)



Advertise | Privacy | Mobile
Web04 | 2.8.1411022.1 | Last Updated 3 Jul 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100