trying to add data in SQL Server 2005 database in vb.net

Question

Could someone please help me in here, I'm just a beginner who want to learn on manipulating SQL Server data using vb.net. I have already experienced manipulating data in ms access. So I just recycled the code that I used in here. But unfortunately I got this error:

Object reference not set to an instance of an object.

And another problem is that, I'm not really sure if what I have inputted in the SqlConnection matches what is in SQL Server. Here is a screen shot of sql server management studio express:

[http://screencast.com/t/Y2Q0NWRhYTA][1]

Imports system.data.sqlclient

'declarations
 Dim idnum As String
    Dim lname As String
    Dim fname As String
    Dim skul As String

    Dim sqlcon As SqlConnection
    Dim sqlcom As SqlCommand  

idnum = TextBox1.Text
        lname = TextBox2.Text
        fname = TextBox3.Text
        skul = TextBox4.Text

sqlcon.open

            sqlcon = New SqlConnection("Data Source=SENBONZAKURA\SQLEXPRESS;Initial Catalog=testing;User ID=SenbonZakura/Rew;")
            sqlcom = New SqlCommand("select * from [student]", sqlcon)

            Dim strsql As String = "insert into [student]([ID], [LASTNAME], [FIRSTNAME], [SCHOOL]) values('" + idnum + "','" + lname + "','" + fname + "','" + skul + "')"


            sqlcom.ExecuteNonQuery()

Answer 1

You're calling:

sqlcon.open

before:

sqlcon = New SqlConnection("Data Source=SENBONZAKURA\SQLEXPRESS;Initial Catalog=testing;User ID=SenbonZakura/Rew;")

That's where your null ref is coming from. You need to "new" sqlcon before calling open on it.

Try this:

sqlcon = New SqlConnection("Data Source=SENBONZAKURA\SQLEXPRESS;Initial Catalog=testing;User ID=SenbonZakura/Rew;")

sqlcon.open

sqlcom = New SqlCommand("select * from [student]", sqlcon)

Dim strsql As String = "insert into [student]([ID], [LASTNAME], [FIRSTNAME], [SCHOOL]) values('" + idnum + "','" + lname + "','" + fname + "','" + skul + "')"


sqlcom.ExecuteNonQuery()

It's tougher to help you with the connection string since we don't know your environment. But here is a link to a great reference: http://connectionstrings.com/

Answer 2

Paul is right, but in addition to Paul answer, since SqlConnection implements interface IDisposable, I suggest you to enclosure it in a using statement. See more about using in this post

Another important suggestion is to use parameters to pass the values to your query. It gives you security, the code is simplier...

The complete code would be something like this:

Using sqlcon As New SqlConnection("Data Source=SENBONZAKURA\SQLEXPRESS;Initial Catalog=testing;User ID=SenbonZakura/Rew;")

    sqlcon.Open()
    Dim sqlcom As New SqlCommand()
    sqlcom.Connection = sqlcon

    sqlcom.CommandText = "INSERT INTO [student](ID, LASTNAME, FIRSTNAME, SCHOOL) VALUES (@ParameterID, @ParameterLastName, @ParameterFirstName, @ParameterSchool)"

    sqlcom.Parameters.AddWithValue("@ParameterID", TextBox1.Text)
    sqlcom.Parameters.AddWithValue("@ParameterLastName", TextBox2.Text)
    sqlcom.Parameters.AddWithValue("@ParameterFirstName", TextBox3.Text)
    sqlcom.Parameters.AddWithValue("@ParameterSchool", TextBox4.Text)

    sqlcom.ExecuteNonQuery()

End Using

• EDIT

Note that I´ve omitted "select * from [student]", you didn´t use this query!