-
Dec 27th, 2017, 02:52 PM
#1
Thread Starter
Hyperactive Member
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
-
Dec 27th, 2017, 03:29 PM
#2
Re: How to Trim the values before it is saved to the database?
-
Dec 27th, 2017, 04:53 PM
#3
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
-
Dec 27th, 2017, 10:35 PM
#4
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.
-
Dec 28th, 2017, 04:14 AM
#5
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).
-
Dec 30th, 2017, 10:10 AM
#6
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.
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)
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|