vb.net ExecuteNonQuery：连接属性尚未初始化 VB

ExecuteNonQuery: Connection property has not been initialized VB

提问by Brandon Scott

Getting an error here not sure why its not opening the connection. Hoping someone can help me.

在这里出现错误，不知道为什么它没有打开连接。希望有人可以帮助我。

 Protected Sub Btn_Submit_Click(ByVal sender As System.Object, e As System.EventArgs) Handles Btn_Submit.Click
    Dim Sqlstr As String
    Dim con As SqlConnection
    Dim connectionString As String = "Data Source=DB\TEST;Initial Catalog=Orders;Integrated Security=True"
    Dim cmdInsert As New SqlCommand(Sqlstr, con)
    Sqlstr = "insert into customers(FirstName,LastName,Email,Phone,Address,City,State,Zip) values (@FirstName,@LastName,@Email,@Phone,@Address,@City,@State,@Zip)"
    Try
        Using connection As New SqlConnection(connectionString)
            connection.Open()
            cmdInsert.Parameters.Add("@FirstName", Data.SqlDbType.NVarChar).Value = FirstName.Text()
            cmdInsert.Parameters.Add("@LastName", Data.SqlDbType.NVarChar).Value = LastName.Text
            cmdInsert.Parameters.Add("@Email", Data.SqlDbType.NVarChar).Value = Email.Text
            cmdInsert.Parameters.Add("@Phone", Data.SqlDbType.NChar).Value = Phone.Text
            cmdInsert.Parameters.Add("@Address", Data.SqlDbType.NVarChar).Value = Address.Text
            cmdInsert.Parameters.Add("@City", Data.SqlDbType.NVarChar).Value = City.Text
            cmdInsert.Parameters.Add("@State", Data.SqlDbType.NVarChar).Value = State.Text
            cmdInsert.Parameters.Add("@Zip", Data.SqlDbType.NChar).Value = Zip.Text
            cmdInsert.ExecuteNonQuery()
            connection.Close()
        End Using
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

回答by Craig Shearer

You have two SqlConnection objects - one in Dim con As SqlConnection and then another in your using statement. Create the SqlCommand after the using statement and pass the connection in to the constructor.

您有两个 SqlConnection 对象 - 一个在 Dim con As SqlConnection 中，另一个在您的 using 语句中。在 using 语句之后创建 SqlCommand 并将连接传递给构造函数。

回答by John Kroetch

You have the wrong scope -- you're instantiating your SqlCommandbefore instantiating and opening the actual SQL connection you're trying to use to execute the command.

你有错误的范围——你在实例SqlCommand化和打开你试图用来执行命令的实际 SQL 连接之前实例化你的。

I believe this is what will fix your code (I moved the insert call into the usingscope):

我相信这将修复您的代码（我将插入调用移到了using作用域中）：

Protected Sub Btn_Submit_Click(ByVal sender As System.Object, e As System.EventArgs) Handles Btn_Submit.Click
Dim Sqlstr As String
Dim connectionString As String = "Data Source=DB\TEST;Initial Catalog=Orders;Integrated Security=True"
Sqlstr = "insert into customers(FirstName,LastName,Email,Phone,Address,City,State,Zip) values (@FirstName,@LastName,@Email,@Phone,@Address,@City,@State,@Zip)"

Try
    Using connection As New SqlConnection(connectionString)

        connection.Open()

        Dim cmdInsert As New SqlCommand(Sqlstr, connection)  <----- **** Moved this here, changed the connection

        cmdInsert.Parameters.Add("@FirstName", Data.SqlDbType.NVarChar).Value = FirstName.Text()
        cmdInsert.Parameters.Add("@LastName", Data.SqlDbType.NVarChar).Value = LastName.Text
        cmdInsert.Parameters.Add("@Email", Data.SqlDbType.NVarChar).Value = Email.Text
        cmdInsert.Parameters.Add("@Phone", Data.SqlDbType.NChar).Value = Phone.Text
        cmdInsert.Parameters.Add("@Address", Data.SqlDbType.NVarChar).Value = Address.Text
        cmdInsert.Parameters.Add("@City", Data.SqlDbType.NVarChar).Value = City.Text
        cmdInsert.Parameters.Add("@State", Data.SqlDbType.NVarChar).Value = State.Text
        cmdInsert.Parameters.Add("@Zip", Data.SqlDbType.NChar).Value = Zip.Text
        cmdInsert.ExecuteNonQuery()

        connection.Close()
    End Using
Catch ex As Exception
    MsgBox(ex.Message)
End Try

回答by fnostro

 Protected Sub Btn_Submit_Click(ByVal sender As System.Object, e As System.EventArgs) Handles Btn_Submit.Click
    Dim Sqlstr As String

    ' =================================================
    ' This is a declaration, not an instantiation
    ' =================================================
    Dim con As SqlConnection      

    Dim connectionString As String = "Data Source=DB\TEST;Initial Catalog=Orders;Integrated Security=True"

    ' =================================================
    ' con is Nothing here
    ' =================================================
    Dim cmdInsert As New SqlCommand(Sqlstr, con)     

    Sqlstr = "insert into customers(FirstName,LastName,Email,Phone,Address,City,State,Zip) values (@FirstName,@LastName,@Email,@Phone,@Address,@City,@State,@Zip)"
    Try

        ' =================================================
        ' connection is not what you passed to cmdInsert
        ' =================================================
        Using connection As New SqlConnection(connectionString)
            connection.Open()

回答by SMHasnain

Connection Sting Property Error always occur when the connection is not declared/renewed

连接未声明/更新时总是发生连接刺属性错误

the solution is simple

解决办法很简单

dim con as new SqlConnection 
con.connectionString = "Provide your SQL connection"
con.open
'write code or action you want to perform
con.close

Hope it helps.

希望能帮助到你。