ASP.NET SqlConnection error: "The ConnectionString property has not been initialized"

Question

What's wrong this T-SQL query :

 Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim SQLData As New System.Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True")
        Dim cmdSelect As New System.Data.SqlClient.SqlCommand("SELECT COUNT(*) FROM Table1 WHERE Name ='" + TextBox1.Text + "'", SQLData)
        SQLData.Open()
        If cmdSelect.ExecuteScalar > 0 Then
            Label1.Text = "You have already voted this service"
            Return
        End If
        Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        con.Open()
        cmd.Connection = con
        cmd.CommandText = "INSERT INTO Tabel1 (Name) VALUES('" & Trim(Label1.Text) & "')"
        cmd.ExecuteNonQuery()
        Label1.Text = "Thank You !"
        SQLData.Close()
    End Sub

Showing an erro message in Con.open The ConnectionString property has not been initialized. — ClixNCash
– ClixNCash, Commented Jan 15, 2011 at 7:40
ya i wanna run two queries in single button clixk ..if record found then show mag in label else ...insert record ... — ClixNCash
– ClixNCash, Commented Jan 15, 2011 at 7:40
SQL Injection Heaven! And your answer is in your error message: con has no connection string. — Andrew Barber
– Andrew Barber, Commented Jan 15, 2011 at 7:43

Gabe · Accepted Answer · 2011-01-15 07:45:16Z

1

Your problem is that you are opening a connection (SQLData), ignoring it, then trying to open a new connection (con) without giving it a connection string. Instead of this:

     Dim con As New SqlConnection
     Dim cmd As New SqlCommand
     con.Open()
     cmd.Connection = con

you should have:

     Dim cmd As New SqlCommand
     cmd.Connection = SQLData

Also, it is very bad practice to insert string value inline in SQL as you have.

answered Jan 15, 2011 at 7:45

Gabe

87.1k13 gold badges144 silver badges238 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marc_s · Accepted Answer · 2011-01-15 09:51:12Z

I would recommend an approach something like this:

Protected Function Button1_Click(sender As Object, e As System.EventArgs)
    ' define and create your one single SqlConnection and protect it by using a "using()....." block
    Using _connection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True")
        ' define and craete your SqlCommand to count your occurences and make it a proper, parametrized query
        Using cmdSelect As New SqlCommand("SELECT COUNT(*) FROM dbo.Table1 WHERE Name = @Name", _connection)
            ' add the parameter to your SqlCommand, define the datatype and length
            cmdSelect.Parameters.Add("@Name", SqlDbType.VarChar, 100)

            ' set the value for that parameter
            cmdSelect.Parameters("@Name").Value = TextBox1.Text.Trim()

            ' open connection, execute query, set return value
            _connection.Open()
            If cmdSelect.ExecuteScalar() > 0 Then
                            Label1.Text = "You have already voted this service"
                Return 
            End If
        End Using

        ' define second query to insert data reusing the existing connection
        Using cmdInsert As New SqlCommand("INSERT INTO dbo.Table1(Name) VALUES(@Name)", _connection)
            ' add the parameter to your SqlCommand, define the datatype and length
            cmdInsert.Parameters.Add("@Name", SqlDbType.VarChar, 100)

            ' set the value for that parameter
            cmdInsert.Parameters("@Name").Value = Label1.Text.Trim()

            cmdInsert.ExecuteNonQuery()
        End Using

        _connection.Close()
    End Using

    Label1.Text = "Thank You !"
End Function

Points to consider:

you have one SqlConnection - that's good enough for both queries, reuse it!
always put your disposable objects like SqlConnection, SqlCommand into Using..... blocks to protect them and make sure they get properly disposed
always use parametrized queries - do NOT under any circumstances just concatenate together your SQL statements - that's a big huge gaping security hole, inviting SQL injection attacks - just DON'T do it - EVER!
if I could, I would try to separate your UI elements from the code - try to put this code into a separate method that will take in the string values from the caller, and will return a result string to be set on the UI (Label1.Text=). Mixing code that queries the database and setting the UI at the same time is messy and leads to spaghetti code - try to separate those things
put your connection string into the web.config into the <connectionStrings> section and read it from there - don't have your connection string as a string literal all throughout your code!

icktoofay · Accepted Answer · 2011-01-15 07:48:16Z

0

There's a few things I see wrong there. First, (other than the SQL injection vulnerability) is that you typed Table1 once, and Tabel1 the other time. While that could be what you want, I doubt it. Next you're creating a second connection. That doesn't seem to be needed. Use the existing SQLData object instead of con. You can also reduce the lines starting from the declaration of cmd (inclusive) to the ExecuteNonQuery call (exclusive) with this:

Dim cmd As New SqlCommand("INSERT INTO Tabel1 (Name) VALUES('" & Trim(Label1.Text) & "')", SQLData)

Now back to that SQL injection vulnerability. What if someone's name is "James O'Brian" (or something else with an apostrophe in it)?

answered Jan 15, 2011 at 7:48

icktoofay

130k23 gold badges261 silver badges239 bronze badges

Collectives™ on Stack Overflow

ASP.NET SqlConnection error: "The ConnectionString property has not been initialized"

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related