HELP To concatenate

[gouki2005]

this

Dim comando As New OleDb.OleDbCommand("Insert into personaldata (name,occupation,address,phone) values " , conexion)


Values is the problem because i going to use a function  getname(), getoccupation() etc 

how to concatenate????

[Teme64]

It goes like this:

Dim comando As OleDb.OleDbCommand()
Dim strSQL As String
strSQL = "Insert into personaldata (name,occupation,address,phone) values ("
strSQL = strSQL & "'" & getname() & "',"
strSQL = strSQL & "'" & getoccupation() & "',"
strSQL = strSQL & "'" & getaddress() & "',"
strSQL = strSQL & "'" & getphone() & "'"
strSQL = strSQL & ")"
comando = New OleDb.OleDbCommand(strSQL, conexion)

[gouki2005]

It goes like this:

Dim comando As OleDb.OleDbCommand()
Dim strSQL As String
strSQL = "Insert into personaldata (name,occupation,address,phone) values ("
strSQL = strSQL & "'" & getname() & "',"
strSQL = strSQL & "'" & getoccupation() & "',"
strSQL = strSQL & "'" & getaddress() & "',"
strSQL = strSQL & "'" & getphone() & "'"
strSQL = strSQL & ")"
comando = New OleDb.OleDbCommand(strSQL, conexion)

Thx..I find another soltuion using string format..is more..easy..at least for..me thx anyway

[schnack72]

I have found using the String.Format method to be a gem. In my VB class that I took a few months back, they recommended this method over concatenation for memory impact. Being a newbie, I'm not sure how it all works, so I took their word on it.

I also came across a neat little function to encase string values with single quotes. You can hard code the single quotes, but using the function make readability of the code a lot easier.

Dim comando As OleDb.OleDbCommand()
Dim strSQL As String = String.Empty
strSQL = String.Format("INSERT personaldata (name, occupation, address, phone)  VALUES ({0},{1},{2},{3})",  PrepareStr(getname()), PrepareStr(getoccupation()), prepareStr(getaddress()), PrepareStr(getphone()))
comando = New OleDb.OleDbCommand(strSQL, conexion)

    Private Function PrepareStr(ByVal strValue As String) As String
        ' This function accepts a string and creates a string that can
        ' be used in a SQL statement by adding single quotes around
        ' it and handling empty values.
        If strValue.Trim() = "" Then
            Return "''"
        Else
            Return "'" & strValue.Trim() & "'"
        End If
    End Function

[gouki2005]

I have found using the String.Format method to be a gem. In my VB class that I took a few months back, they recommended this method over concatenation for memory impact. Being a newbie, I'm not sure how it all works, so I took their word on it.

I also came across a neat little function to encase string values with single quotes. You can hard code the single quotes, but using the function make readability of the code a lot easier.

Dim comando As OleDb.OleDbCommand()
Dim strSQL As String = String.Empty
strSQL = String.Format("INSERT personaldata (name, occupation, address, phone)  VALUES ({0},{1},{2},{3})",  PrepareStr(getname()), PrepareStr(getoccupation()), prepareStr(getaddress()), PrepareStr(getphone()))
comando = New OleDb.OleDbCommand(strSQL, conexion)

    Private Function PrepareStr(ByVal strValue As String) As String
        ' This function accepts a string and creates a string that can
        ' be used in a SQL statement by adding single quotes around
        ' it and handling empty values.
        If strValue.Trim() = "" Then
            Return "''"
        Else
            Return "'" & strValue.Trim() & "'"
        End If
    End Function

nice function but you cant use this function with Getphone because is numeric and if you add single quotes to a numeric value in a sql stament VB throws an exception

[Teme64]

Use getphone() without PrepareStr() function call.

Gouki's PrepareStr() function is good but it fails in one case. That is if you have a quote-character in your string originally, like "Robert de Niro's Cafe", will cause error.
So here's a small fix to the function just in case somebody uses it: Return "'" & strValue.Trim().Replace("'", "''") & "'" i.e. replace '-character with two '-characters in the strValue string.