Results 1 to 6 of 6

Thread: How to Trim the values before it is saved to the database?

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    326

    How to Trim the values before it is saved to the database?

    I would like to trim the values (to get rid of extra spaces) before it is saved to the database. Using the following code to Save & Update.

    Code:
    Dim StrSql = "INSERT INTO NewCustomersDBTable (A1,A2,A3,A4,A5,A6,A7,A8,A9,UDO,UDB) VALUES('" _
                             + Me.txt1.Text + " ','" _
                             + Me.txt2.Text + " ','" _
                             + Me.txt3.Text + " ','" _
                             + Me.txt4.Text + " ','" _
                             + Me.txt5.Text + " ','" _
                             + Me.txt6.Text + " ','" _
                             + Me.txt7.Text + " ','" _
                             + Me.txt8.Text + " ','" _
                             + Me.txt9.Text + " ','" _
                             + Me.txtUDO.Text + " ','" _
                             + Me.txtUDB.Text + " ')"
    Code:
    Dim StrSql = "UPDATE NewCustomersDBTable SET " _
                             + "A1='" + Me.txt1.Text + "'," _
                             + "A2='" + Me.txt2.Text + "'," _
                             + "A3='" + Me.txt3.Text + "'," _
                             + "A4='" + Me.txt4.Text + "'," _
                             + "A5='" + Me.txt5.Text + "'," _
                             + "A6='" + Me.txt6.Text + "'," _
                             + "A7='" + Me.txt7.Text + "'," _
                             + "A8='" + Me.txt8.Text + "'," _
                             + "A9='" + Me.txt9.Text + "'," _
                             + "UDO='" + Me.txtUDO.Text + "'," _
                             + "UDB='" + Me.txtUDB.Text + "'Where [CUSTID] = " & txtID.Text

  2. #2
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: How to Trim the values before it is saved to the database?

    TextBox1.Text.Trim

  3. #3
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: How to Trim the values before it is saved to the database?

    By the way, you should be using parameterized queries rather than concatenating in the fields. Parameters likely won't deal with spaces on a string, but they will prevent SQL injection exploits.
    My usual boring signature: Nothing

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: How to Trim the values before it is saved to the database?

    Do you think that the fact that you are saving the data to a database changes how to trim leading and trailing spaces? Of course it doesn't. All you had to do was search the web for how to trim leading and trailing spaces in VB.NET and you'd have found the answer you needed. If you think like that in future, you'll be able to solve many of your own problems. Forums are great for the hard stuff that you can't solve. They shouldn't be your first option for the easy stuff.

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: How to Trim the values before it is saved to the database?

    For a fuller explanation of why you should be using parameters (and links to code examples), see the article Why should I use Parameters instead of putting values into my SQL string? from our Database Development FAQs/Tutorials (at the top of the Database Development forum).

  6. #6
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: How to Trim the values before it is saved to the database?

    Here is an example for an INSERT for a SQL-Server table, for other database providers the select after the insert would not apply, it would be done differently dependent on the database.

    To create the INSERT statement for SQL-Server with SSMS (SQL-Management Studio) installed, right click on the table and there will be an option to create a skeleton INSERT statement which you can then (yeah it's tedious) add a DECLARE variable for each parameter, set values for each variable and run the query to ensure it works then take the INSERT statement minus the parameters and use it in your code e.g

    Code:
    Dim insertCommandStatement As String =
        <SQL>
            INSERT INTO Orders 
            (
                CustomerIdentifier,
                EmployeeID,
                OrderDate,
                RequiredDate,
                ShippedDate,
                ShipVia,
                Freight,
                ShipAddress,
                ShipCity,
                ShipRegion,
                ShipPostalCode,
                ShipCountry
        ) VALUES 
        (
            @CustomerIdentifier,
            @EmployeeID,
            @OrderDate,
            @RequiredDate,
            @ShippedDate,
            @ShipVia,
            @Freight,
            @ShipAddress,
            @ShipCity,
            @ShipRegion,
            @ShipPostalCode,
            @ShipCountry
        ); 
        SELECT CAST(scope_identity() AS int);
        </SQL>.Value
    Code sample
    Code:
    '
    ' Insert statement along with select statement to get new primary
    ' key for record just added.
    '
    Dim insertCommandStatement As String =
        <SQL>
            INSERT INTO Orders 
            (
                CustomerIdentifier,
                EmployeeID,
                OrderDate,
                RequiredDate,
                ShippedDate,
                ShipVia,
                Freight,
                ShipAddress,
                ShipCity,
                ShipRegion,
                ShipPostalCode,
                ShipCountry
        ) VALUES 
        (
            @CustomerIdentifier,
            @EmployeeID,
            @OrderDate,
            @RequiredDate,
            @ShippedDate,
            @ShipVia,
            @Freight,
            @ShipAddress,
            @ShipCity,
            @ShipRegion,
            @ShipPostalCode,
            @ShipCountry
        ); 
        SELECT CAST(scope_identity() AS int);
        </SQL>.Value
    
    Dim NewIdentifier As Integer = 0
    
    Using cn As New SqlConnection With {.ConnectionString = "TODO"}
        Using cmd As New SqlCommand With {.Connection = cn}
            cmd.CommandText = insertCommandStatement
            cmd.Parameters.AddWithValue("@ShipAddress", txtShipAddress.Text.Trim)
            '
            ' do other parameters
            '
            cn.Open()
            NewIdentifier = CInt(cmd.ExecuteScalar)
        End Using
    End Using
    If you find yourself writing a lot of INSERT, DELETE, SELECT statements you could write a utility to generate them for you, the following I use to reply to forum questions which was I created in C# using SQL-Server SMO namespace, one could be done for MS-Access but I've never seen a need.

    Name:  1.jpg
Views: 365
Size:  84.6 KB

    If you were dealing with MS-Access you would write the query in the MS-Access query editor and write code using similar logic as above (code from this code sample)

    Name:  2.jpg
Views: 346
Size:  105.1 KB

    Lastly, if for some odd reason you just don't like to use parameters (and there should not be a reason for not using them) we can go cave man style, write way too much code (but heck it looks cool yet is not cool).

    Code:
    Public Class SqlFormatProvider
        Implements IFormatProvider
    
        Private ReadOnly _formatter As New SqlFormatter()
    
        Public Function GetFormat(ByVal formatType As Type) As Object Implements IFormatProvider.GetFormat
            If formatType Is GetType(ICustomFormatter) Then
                Return _formatter
            End If
            Return Nothing
        End Function
    
        Private Class SqlFormatter
            Implements ICustomFormatter
            Public Function Format(ByVal pFormatType As String, ByVal pArg As Object, ByVal pFormatProvider As IFormatProvider) As String Implements ICustomFormatter.Format
                If pArg Is Nothing Then
                    Return "NULL"
                End If
                If TypeOf pArg Is String Then
                    Return "'" & CStr(pArg).Trim.Replace("'", "''") & "'"
                End If
                If TypeOf pArg Is Date Then
                    Return "'" & CDate(pArg).ToString("MM/dd/yyyy") & "'"
                End If
                If TypeOf pArg Is IFormattable Then
                    Return CType(pArg, IFormattable).ToString(pFormatType, CultureInfo.InvariantCulture)
                End If
                Return pArg.ToString()
            End Function
        End Class
    End Class
    Public Module CreateSql
        Public Function GenerateSqlStatement(ByVal formattable As FormattableString) As String
            Return formattable.ToString(New SqlFormatProvider())
        End Function
    End Module
    Code:
    Dim StrSql = GenerateSqlStatement($"INSERT INTO NewCustomersDBTable (A1,A2,A3,A4,A5,A6,A7,A8,A9,UDO,UDB) VALUES({txt1.Text}, {txt2.Text},{txt3.Text},{txt4.Text},{txt5.Text},{txt6.Text},{txt7.Text},{txt8.Text},{txt9.Text},{txtUDO.Text},{txtUDB.Text})")
    Console.WriteLine(StrSql)
    The following, all text boxes have leading, trailing or both in the text box controls. Here is the result and take note all were evaluated as strings and that is incorrect e.g. the first two params are integers, third is a date and so on so that means you need conversions first while going with the data provider parameters e.g. AddWithValue is the first step to working in the right direction.
    Code:
    INSERT INTO NewCustomersDBTable (A1,A2,A3,A4,A5,A6,A7,A8,A9,UDO,UDB) VALUES('aaa', 'bbb','ccccc','ddd','eeee','ffff','gggg','in 8','12313131','udo','last')

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width