SQL Server 2012 - ASP.NET - VB.NET - Insert Data into database

Question

I am trying to insert data to a db table using SQL statement with VB.NET.

This is my code:

Registration.aspx:

Imports dbConnect
Imports System.Data.SqlClient


Partial Class Registration
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load

    End Sub

    Protected Sub btnRegister_Click(sender As Object, e As EventArgs) Handles btnRegister.Click

        register()


    End Sub


    Public Sub register()



        Dim Username As String = txtUsername.ToString
        Dim Surname As String = txtSurname.ToString
        Dim Password As String = txtPassword.ToString
        Dim Name As String = txtName.ToString
        Dim Address1 As String = txtAddress1.ToString
        Dim Address2 As String = txtAddress2.ToString
        Dim City As String = txtCity.ToString
        Dim Email As String = txtEmail.ToString
        Dim Country As String = drpCountry.ToString
        Dim DOB As Date = calDOB.SelectedDate
        Dim Occupation As String = txtOccupation.ToString
        Dim WorkLocation As String = txtWorkLocation.ToString
        Dim Age As Integer = "20"

        Dim ProjectManager As String = "test"
        Dim TeamLeader As String = "test"
        Dim TeamLeaderID As Integer = 1
        Dim ProjectManagerID As Integer = 1

        Dim RegistrationDate As Date = Today
        Dim ContractType As String = "test"
        Dim ContractDuration As Integer = 6
        Dim Department As String = "test"
        Dim conn As New SqlConnection("Data Source=BRIAN-PC\SQLEXPRESS;Initial Catalog=master_db;Integrated Security=True")
        Dim registerSQL As SqlCommand
        Dim sqlComm As String

        sqlComm = "INSERT INTO users(Username, Password, Name, Surname, Address1, Address2, City, Country, date_of_birth, age, Occupation, department, work_location, project_manager,team_leader, team_leader_id, project_manager_id, date_registration, contract_type, contract_duration) VALUES('" + Username + "','" + Password + "','" + Name + "','" + Surname + "','" + Address1 + "','" + Address2 + "','" + City + "','" + Country + "','" + DOB + "','" + Age + "','" + Occupation + "','" + Department + "','" + WorkLocation + "','" + ProjectManager + "','" + TeamLeader + "','" + TeamLeaderID + "','" + ProjectManager + "','" + RegistrationDate + "','" + ContractType + "','" + ContractDuration + "')"

        conn.Open()

        registerSQL = New SqlCommand(sqlComm, conn)

        registerSQL.ExecuteNonQuery()
        conn.Close()


    End Sub



End Class

This is my database 'users' table:

Users Table

I am getting this error message:

Error   1   Operator '+' is not defined for types 'Double' and 'Date'.  C:\Users\Brian\Documents\Visual Studio 2012\WebSites\WebSite1\Registration.aspx.vb  51  19  WebSite1(1)

Can anyone tell me whats going on ?

Use parameterized queries and NEVER construct your query string like that, FYI - http://stackoverflow.com/questions/542510/how-do-i-create-a-parameterized-sql-query-why-should-i — Lloyd, Mar 25 '13 at 20:06

jrummell · Answer 1 · 2013-03-26T12:32:37.893

As Lloyd pointed out, parameterize your queries. E.g. (shortened for readability)

sqlComm = "INSERT INTO users(Username, Password, Name) VALUES(@Username, @Password, @Name)"
registerSQL = New SqlCommand(sqlComm, conn)
registerSQL.Parameters.AddWithValue("@Username", Username)
registerSQL.Parameters.AddWithValue("@Password", Password)
registerSQL.Parameters.AddWithValue("@Name", Name)

But to answer your question, use & instead of + to concatenate a String.

score 0 · Answer 2 · answered Mar 25 '13 at 20:10

Just to give you a starting point

  sqlComm = "INSERT INTO users(Username, Password, Name, Surname, Address1, Address2, " + 
            "City, Country, date_of_birth, age, Occupation, department, work_location, " + 
            "project_manager,team_leader, team_leader_id, project_manager_id, " + 
            "date_registration, contract_type, contract_duration) " + 
            "VALUES(@p1, @p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14,@p15," +
            "@p16,@p17,@p18,@p19,@p20)"
    conn.Open()
    registerSQL = New SqlCommand(sqlComm, conn)
    registerSQL.Parameters.AddWithValue("@p1", Username)
    ..... 
    registerSQL.ExecuteNonQuery()

And when the value to pass to the AddWithValue method is not a string, try to convert to the correct datatype expected by the database field.

    registerSQL.Parameters.AddWithValue("@p9", Convert.ToDateTime(DOB))

In this way you don't have to worry about parsing strings with double quotes or automatic conversion of strings to date, moreover, you don't have problems with Sql Injection attacks

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. — Brian, Mar 25 '13 at 20:36
Can't say, I have assumed that DOB is a string representing a date, could you check with the debugger what is the real value of DOB at the point of the conversion (not to mention the other date at parameter @p18) — Steve, Mar 25 '13 at 20:44

SQL Server 2012 - ASP.NET - VB.NET - Insert Data into database

2 Answers2