Member Avatar for 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????
  • 3 Contributors
  • 5 Replies
  • 129 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by Teme64

Dani AI

Generated

Short answer: stop building the SQL text by hand and use parameters. and gave workable string-based approaches, but parameterized commands remove the need to worry about quoting, embedded apostrophes, or numeric-vs-text mismatches and they protect against SQL injection.

With OleDb the command text uses positional placeholders (question marks) and each parameter must be added in the same order as the placeholders. Prefer adding parameters with an explicit OleDbType (instead of relying on AddWithValue) so numeric columns really get numeric values. Also convert empty strings to DBNull.Value so the database sees NULL when appropriate, and wrap commands in a Using block so resources are disposed.

Example pattern (VB.NET):

Dim sql As String = "INSERT INTO personaldata (name, occupation, address, phone) VALUES (?, ?, ?, ?)"
Using cmd As New OleDb.OleDbCommand(sql, conexion)
    cmd.Parameters.Add("name", OleDbType.VarChar, 100).Value = If(String.IsNullOrWhiteSpace(getname()), DBNull.Value, getname().Trim())
    cmd.Parameters.Add("occupation", OleDbType.VarChar, 100).Value = If(String.IsNullOrWhiteSpace(getoccupation()), DBNull.Value, getoccupation().Trim())
    cmd.Parameters.Add("address", OleDbType.VarChar, 200).Value = If(String.IsNullOrWhiteSpace(getaddress()), DBNull.Value, getaddress().Trim())
    cmd.Parameters.Add("phone", OleDbType.Integer).Value = CInt(getphone()) 'or use VarChar if phone is stored as text
    cmd.ExecuteNonQuery()
End Using

Notes and cautions:

  • If the phone column is actually text (best for numbers with leading zeros, +, or dashes), send it as VarChar; if it is numeric, convert to the correct numeric type before adding the parameter.
  • Parameters handle embedded quotes automatically, so you do not need custom escaping.
  • For more detail on commands and parameters in ADO.NET refer to the official docs: Commands and Parameters (ADO.NET).

Recommended Answers

All 5 Replies

Member Avatar for 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)
Member Avatar for 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

Member Avatar for 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
Member Avatar for 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

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.