SQL Server 2012 - ASP.NET - VB.NET - 将数据插入数据库

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

提问by Brian

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

我正在尝试使用带有 VB.NET 的 SQL 语句将数据插入到 db 表中。

This is my code:

这是我的代码：

Registration.aspx:

注册.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 ?

谁能告诉我这是怎么回事？

回答by jrummell

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.

但是要回答您的问题，请使用&而不是+连接字符串。

回答by Steve

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.

当传递给 AddWithValue 方法的值不是字符串时，尝试转换为数据库字段所需的正确数据类型。

    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

这样你就不用担心解析带双引号的字符串或自动将字符串转换为日期，而且你也不会遇到Sql Injection 攻击的问题