Can you please tell me what's wrong with this code?
Do I need to use
Dim mydao As New Connection
Dim connectionString As String = mydao.GetConnectionString()
Dim connection As New SqlConnection(connectionString)
Dim cmd As New SqlCommand
Public Function add(ByVal area As String, ByVal user As String) As Integer
cmd.CommandText = "INSERT into Area (Area, user) VALUES ('" + area + "','" + user + "')"
Dim cant As Integer = cmd.ExecuteNonQuery()'it throws exception here
Catch ex As Exception
AREA varchar(100) NOT NULL ,
USER varchar(100) NOT NULL
Connection property has not initialized
There's a few issues with this code.
The most significant is that you aren't setting the Command's Connection property, so the command has no way of knowing how to connect to the database.
Finally, don't declare the connection and command outside of the function unless you need to. You should only keep the connection and command around for as long as you need them.
So your function would end up looking like:
Public Function add(ByVal area As String, ByVal user As String) As Integer Dim mydao As New Connection Using connection As New SqlConnection(mydao.ConnectionString()) Using command As New SqlCommand() ' Set the connection command.Connection = connection ' Not necessary, but good practice command.CommandType = CommandType.Text ' Example query using parameters command.CommandText = "INSERT into Area (Area, user) VALUES (@area, @user)" ' Adding the parameters to the command command.Parameters.AddWithValue("@area", area) command.Parameters.AddWithValue("@user", user) connection.Open() Return command.ExecuteNonQuery() End Using ' Dispose Command End Using ' Dispose (and hence Close) Connection End Function
Note that currently, you will be returning 0 all the time. Rather than having to check the value returned from the function, the above example will simply throw an exception. This makes for slightly cleaner code (as the caller would have to understand that 0 is an error condition), and, if you needed to handle the exception, simply wrap the call to this function in a