Question connexion string property was not initialized?

armand21

Member
Joined
Jan 9, 2010
Messages
12
Location
casablanca
Programming Experience
3-5
Hi Everyone
I have ended coding a little WinForm project containing 5 tables (SqlServer2008) I m about to test it....When i m trying to Loggin ( through Login form) I m receiving the following error message

InvalidOperationException was unhandled : The connexion string property was not initialized

I have added a double arrow to point out where VS compiler is highlighting the error . The connexion variable is defined inside a Module file

I have spent several time trying to find out what is wrong with the connexion string i could not figure out

Please help

Following is an excerpt of code.

Public Class LogInForm

Private Sub LogInForm_Load(sender As Object, e As System.EventArgs) Handles Me.Load
Dim strConnectionString As String = "ARMAND\HP;" + _
".\SQL2008EXPRESS;Database=CSE_DEPT;" + _
"Integrated Security=SSPI"
SqlConnection = New SqlConnection(strConnectionString)
Try
SqlConnection.Open()
Catch SqlExceptionErr As SqlException
MessageBox.Show(SqlExceptionErr.Message, "Access Error")
Catch InvalidOperationExceptionErr As InvalidOperationException
MessageBox.Show(InvalidOperationExceptionErr.Message, "Access Error")
End Try
If SqlConnection.State <> ConnectionState.Open Then
MessageBox.Show("Database Connection is Failed")
Exit Sub
End If
End Sub


Private Sub TabLogin_Click(sender As Object, e As System.EventArgs) Handles TabLogin.Click
Dim cmdString1 As String = "SELECT user_name, pass_word, faculty_id, student_id FROM LogIn "
Dim cmdString2 As String = "WHERE (user_name=@Param1 ) AND (pass_word=@Param2)"
Dim cmdString As String = cmdString1 & cmdString2
Dim paramUserName As New SqlParameter
Dim paramPassWord As New SqlParameter
Dim LogInTableAdapter As New SqlDataAdapter
Dim SqlDataTable As New DataTable
Dim SqlCommand As New SqlCommand
Dim selForm As New SelectionForm


paramUserName.ParameterName = "@Param1"
paramUserName.Value = txtUserName.Text
paramPassWord.ParameterName = "@Param2"
paramPassWord.Value = txtPassWord.Text

SqlCommand.Connection = New SqlConnection
SqlCommand.CommandType = CommandType.Text
SqlCommand.CommandText = cmdString
SqlCommand.Parameters.Add(paramUserName)
SqlCommand.Parameters.Add(paramPassWord)


LogInTableAdapter.SelectCommand = SqlCommand
----> LogInTableAdapter.Fill(SqlDataTable) <----------
If SqlDataTable.Rows.Count > 0 Then
'MessageBox.Show("LogIn is successful")
selForm.Show()
Me.Hide()
Else
MessageBox.Show("No matched username/password found!")
End If
SqlDataTable.Dispose()
SqlDataTable = Nothing
SqlCommand.Dispose()
SqlCommand = Nothing
LogInTableAdapter.Dispose()
LogInTableAdapter = Nothing
End Sub
 

IanRyder

Well-known member
Joined
Sep 9, 2012
Messages
1,130
Location
Healing, NE Lincs, UK
Programming Experience
10+
The connexion variable is defined inside a Module file

Hi,

That maybe so but you are certainly not using it? What?s wrong with this code in your click event:-

VB.NET:
SqlCommand.Connection = New SqlConnection

If you check this against your code in the load event you will see the difference.

Hope that helps.

Cheers,

Ian

NB. For the future, please use Code Tags when posting code so that the code keeps its formatting and is a lot easier to read.

BTW,

This is a great example of why NOT to choose Variable Names which are the same as Existing Class Names.

Cheers,

Ian
 

armand21

Member
Joined
Jan 9, 2010
Messages
12
Location
casablanca
Programming Experience
3-5
Hello thanks for your reply

I did not use Variable names having the same name like Existing class names : i face the same problem.
I do not understand your first suggestion since it does not help....I added the keyword New hoping that it will help it does not..I remove it and still have the same trouble.
Since the project has 5 forms i preffered to use a Connection variable defined inside a Module rather than define it inside each Form.vb file

I tried several ideas no use.....I m still in trouble....
 

IanRyder

Well-known member
Joined
Sep 9, 2012
Messages
1,130
Location
Healing, NE Lincs, UK
Programming Experience
10+
Hi,

I did not use Variable names having the same name like Existing class names : i face the same problem.

If that is so then how and why do you have:-

SqlConnection = New SqlConnection(strConnectionString)
?and
Dim SqlCommand As New SqlCommand


My first point was that you got the basic concept correct in your first snippet of code being:-

Dim strConnectionString As String = "ARMAND\HP;" + _
".\SQL2008EXPRESS;Database=CSE_DEPT;" + _
"Integrated Security=SSPI"
SqlConnection = New SqlConnection(strConnectionString)


But until you give your variable names a more meaningful name that can be distinguished from the Classes that you are using then it will be difficult to help you further since we can easily end up misunderstanding each other when it comes to describing and explaining where your issues may reside.

Cheers,

Ian
 

armand21

Member
Joined
Jan 9, 2010
Messages
12
Location
casablanca
Programming Experience
3-5
a little expanation

Hello Ian

what i m trying to say is.

1) i did type the code with different variables at first place; i face the same problem

2) Since the exercise has a correction. I went to it . I read it : it has the actual variable...Then i asked myself may be i should remove mine paste the one used by the book s writer and see what happen ? I did and the error message was still there


So actually you have the book code (with the same mistake)


So what i m trying to tell you is twice simple

I have faced the same error message with both kind of variable.

thanks for answering.
 

IanRyder

Well-known member
Joined
Sep 9, 2012
Messages
1,130
Location
Healing, NE Lincs, UK
Programming Experience
10+
Hi,

OK, I see your predicament. Unfortunately and regardless of what the book may say, your issue is with your first line here:-

SqlCommand.Connection = New SqlConnection
SqlCommand.CommandType = CommandType.Text
SqlCommand.CommandText = cmdString
SqlCommand.Parameters.Add(paramUserName)
SqlCommand.Parameters.Add(paramPassWord)


Even though you may have created a Global variable in a module which is supposed to be your connection object, the first line above ignores your global variable due to the use of the New keyword with the SqlConnection Class which has now created and instantiated a new sql connection object, which as you can see above, does not have a "Connection String" associated with it therefore you get the error you are seeing.

If you wanted to have a Global variable as a connection object then you could create a module and then put something like the following in:-

Public myGlobalSqlConn As New SqlConnection("Your Connection String Here!")


Then you could say:-

SqlCommand.Connection = myGlobalSqlConn
SqlCommand.CommandType = CommandType.Text
SqlCommand.CommandText = cmdString
SqlCommand.Parameters.Add(paramUserName)
SqlCommand.Parameters.Add(paramPassWord)


If you do not want to do that then you could just say:-

SqlCommand.Connection = New SqlConnection("Your Connection String Here")
SqlCommand.CommandType = CommandType.Text
SqlCommand.CommandText = cmdString
SqlCommand.Parameters.Add(paramUserName)
SqlCommand.Parameters.Add(paramPassWord)


However, that said and as you learn more about programming in .NET, you will find that that your style and techniques may change to a point whereby you could say something like:-

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
  Dim sqlConnString As String = "Your Connection String Goes Here"
  Dim cmdString As String = "SELECT user_name, pass_word, faculty_id, student_id FROM LogIn WHERE user_name=@UserName AND pass_word=@Password"
 
  Using sqlConn As New SqlConnection(sqlConnString), logInTableAdapter As New SqlDataAdapter(cmdString, sqlConn), sqlDataTable As New DataTable
    With logInTableAdapter
      .SelectCommand.Parameters.AddWithValue("@UserName", "Some User Name Value")
      .SelectCommand.Parameters.AddWithValue("@Password", "Some Password Value")
      .Fill(sqlDataTable)
    End With
 
    'Now do what you want
    If sqlDataTable.Rows.Count > 0 Then
      'MessageBox.Show("LogIn is successful")
    Else
      'MessageBox.Show("No matched username/password found!")
    End If
  End Using
End Sub


In this case I have used Using Blocks to take care of Disposing, I have instantiated the SQLConnection Object and SqlDataAdapter Object with the correct Constructors to set the connection string of the SqlConnection object and the Select Statement and SQL Object of the SqlDataAdapter. I then add the parameters, fill the Datatable and do whatever from there.

As you go on you should find that the use of Global variables should be non-existent since your connection string should really be in the Config file or at the very least set as a property in My.Settings

Hope that helps.

Cheers,

Ian
 
Top Bottom