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 ?

• Lloyd: Use parameterized queries and NEVER construct your query string like that, FYI - stackoverflow.com/questions/542510/…

Answer 1

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.

Answer 2

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

• Brian: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
• Steve: 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)