Variable cn is used before it has assigned a value. in asp.net with VB

Question

Imports System.Data.SqlClient
Imports System.Data.SqlClient.SqlException

Partial Class studreg
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim cn As SqlConnection
        Dim cmd As SqlCommand
        Dim dr As SqlDataReader
        cn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\student.mdf;Integrated Security=True;User Instance=True"
        **cn.Open()**
        cmd.Connection = cn
        **cmd.CommandText** = "select * from stud where studemail= '" & studemail.Text & "' "
        dr = cmd.ExecuteReader
        If (dr.HasRows) Then
            MsgBox("Email Already Regiatered! Please Login.")
            cn.Close()
        Else
            cn.Close()
            cmd = New SqlCommand("INSERT INTO stud values('" & studname.Text & "','" & studemail.Text & "','" & studpass.Text & "', '" & course.SelectedItem.ToString & "')")
            cmd.ExecuteNonQuery()
            MsgBox("Success!")
            Response.Redirect("view.aspx")
            cn.Close()
        End If
    End Sub

End Class

The error message tells you exactly what the issue is. What's not to understand? Where exactly do you think you're assigning an object to the `cn` variable? You're making exactly the same mistake with the `cmd` variable too. — jmcilhinney, Sep 11 '20 at 16:08
For future reference, a title and some code is NEVER an adequate question. You MUST ALWAYS provide a FULL and CLEAR explanation of your problem. Write the question first and explain what you're trying to achieve, how you're trying to achieve it and what happens when you try, then write the title as a summary of the problem last. You have no badges so you must have rejected the invitation to take the site tour, which was a mistake. Take that tour and spend more time reading in the Help Center to learn how to use this site. — jmcilhinney, Sep 11 '20 at 16:10
Does this answer your question? [What is a NullReferenceException, and how do I fix it?](https://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — Craig, Sep 11 '20 at 17:41

score 0 · Answer 1 · answered Sep 12 '20 at 01:27

Your main problem is you have never created a connection object. All you have done is assign a datatype to a variable. The New keyword calls the constructor of the connection to create the object.

To do this in a single connection and command you need to use the Exists method provided by Sql Server.

Using...End Using blocks close and dispose your database objects even if there is an error.

Always use parameters to avoid Sql injection. I had to guess at the datatype and size. Check your database to get the correct values.

Passwords should NEVER be stored as plain text but that is beyond the scope of this question.

Message boxes are not going to work in a web application.

Private ConStr As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\student.mdf;Integrated Security=True;User Instance=True"

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim strSql = "If Not Exists (Select 0 From stud Where studemail= @Email)
                   Insert Into stud (studname, studemail, studpass, course) 
                                    Values( @Name, @Email, @Pass, @Course);"
    Dim RetVal As Integer
    Using cn As New SqlConnection(ConStr),
            cmd As New SqlCommand(strSql, cn)
        With cmd.Parameters
            .Add("@Email", SqlDbType.VarChar, 100).Value = studemail.Text
            .Add("@Name", SqlDbType.VarChar, 100).Value = studname.Text
            .Add("@Course", SqlDbType.VarChar, 100).Value = course.SelectedItem.ToString
            .Add("@Pass", SqlDbType.VarChar, 100).Value = studpass.Text
        End With
        cn.Open()
        RetVal = cmd.ExecuteNonQuery
    End Using
    If RetVal = 1 Then
        MessageTextBox.Text = "New Account Created"
    Else
        MessageTextBox.Text = "Email Already Regiatered! Please Login."
    End If
End Sub

Variable cn is used before it has assigned a value. in asp.net with VB

1 Answers1