Question

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:

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 -stackoverflow.com/questions/542510/…

jrummell · Answer 1 · 2013-03-25 20:07:59Z

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

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

Pages

Monday, 1 April 2013

Registration database

2 Answers

No comments:

Post a Comment

Blog Archive