-2
Private Sub recruit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles recruit.Click
    Dim query3 As String
    Dim n As Integer
    Dim query2 As String = "select max(stag) from agent"
    con.ConnectionString = ("Data Source=DESKTOP-CTN5IJ3\SQLEXPRESS;Integrated Security=True")
    Dim autono As New SqlCommand(query2, con)
    con.Open()
    If IsDBNull(autono.ExecuteScalar) Then
        n = 7
    Else
        n = autono.ExecuteScalar + 5
    End If
    con.Close()
    query3 = "insert into agent values(" + n + ",'" + ncrypt(txtssn.Text) + "','" + ncrypt(txtname.Text) + "','" + ncrypt(txtadd.Text) + "',0,0,'newbpwd')"
    Dim save As New SqlCommand(query3, con)
    con.Open()
    save.ExecuteNonQuery()
    con.Close()
End Sub

//query3 = "insert into agent values(" + n + ",'" + ncrypt(txtssn.Text) + "','" + ncrypt(txtname.Text) + "','" + ncrypt(txtadd.Text) + "',0,0,'newbpwd')" this is wher the problem is said to be

Rohit Ron
  • 1
  • 2
  • Possible duplicate of [The difference between + and & for joining strings in VB.NET](https://stackoverflow.com/questions/734600/the-difference-between-and-for-joining-strings-in-vb-net) – Visual Vincent Oct 15 '17 at 14:09
  • 1
    Your code is also vulnerable to SQL Injection. Please use [**parameterized queries**](https://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/) instead. – Visual Vincent Oct 15 '17 at 14:10
  • Re SQL Injection, see: [Exploits of a Mom](https://xkcd.com/327/) aka "Little Bobby Tables". – zaph Oct 15 '17 at 14:13

3 Answers3

3

Your main problem is you are using wrong string concatenation operator. Use & instead of +.

Another approach is using string interpolation

Dim n As Integer = 101
Dim query = $"INSERT INTO table VALUES ({n})"

But if you from beginning have correctly using SqlParameters for passing values to the sql query - you will not need to concatenate strings at all.
By using SqlParameter you will keep your safe from possible Sql Injection with your current code. 

Private Sub recruit_Click(sender As Object, e As EventArgs) Handles recruit.Click
    Dim connectionString = _
        "Data Source=DESKTOP-CTN5IJ3\SQLEXPRESS;Integrated Security=True"
    Dim newId As Integer = 7

    Using connection As New SqlConnection(connectionString)
        Dim query As String = "select max(stag) from agent"
        Using command As New SqlCommand(query, connection)
            connection.Open()
            Dim result = command.ExecuteScalar()
            If result IsNot DbNull.Value Then
                newId = DirectCast(result, Integer) + 5
            End If
        End Using
    End Using


    Using connection As New SqlConnection(connectionString)
        Dim query As String = _
            "insert into agent values (@Id, @SSN, @Name, @Add, 0, 0, 'newbpwd')"
        Using command As New SqlCommand(query, connection)
            Dim parameters As New List(Of SqlParameter) From
            {
                New SqlParameter With { .ParameterName = "@Id", .SqlDbType = SqlDbType.Int, .Value = newId },
                New SqlParameter With { .ParameterName = "@SSN", .SqlDbType = SqlDbType.VarChar, .Value = ncrypt(txtssn.Text)},
                New SqlParameter With { .ParameterName = "@Name", .SqlDbType = SqlDbType.VarChar, .Value = ncrypt(txtname.Text)},
                New SqlParameter With { .ParameterName = "@Add", .SqlDbType = SqlDbType.VarChar, .Value = ncrypt(txtadd.Text)},
            }
            command.Parameters.AddRange(parameters)

            connection.Open()
            command.ExecuteNonQuery()
        End Using
    End Using
End Sub
Fabio
  • 31,528
  • 4
  • 33
  • 72
  • any header files i need to be using? – Rohit Ron Oct 15 '17 at 15:14
  • What you mean by "header" files? – Fabio Oct 15 '17 at 15:16
  • System.Data.SqlClient is the header file i have imported in my program – Rohit Ron Oct 15 '17 at 15:18
  • I think Visual Studio will suggest which "using" directives should be added to the file. `System.Data.SqlClient` on of them – Fabio Oct 15 '17 at 15:20
  • found errors at two lines in the code 1. If result <> DbNull.Value Then here "opertor "<>" is not defined for objects and system.dbnull.values 2. command.Parameters.AddRange(parameters) here "'parameters ' is not declared" – Rohit Ron Oct 15 '17 at 15:25
  • For checking of `DbNull.Value` use `If result IsNot DbNull.Value Then`. For second, you need declare that variable – Fabio Oct 15 '17 at 15:32
2

In vb, you use '&' symbol rather than '+' to concatenate Strings

Kerage Chan
  • 116
  • 1
  • 9
0

My way is to convert the integer to string before you insert it into a table, it works for me. e.g:

    Dim n As Integer = 33
    Dim s As String = n.ToString
Sami Alkendi
  • 77
  • 8