Problems with executeNonQuery and connection

[ireneotom]

Hello Friends please i am in trouble with ExecuteNonQuery() and my connection and i need your help. I have been trying to build a small program and seem to have been lost as to how to solve my problem.

The exception details are
Exception Details:InvalidOperationException was Unhandled
ExecuteNonQuery: Connection property has not been initialized

The Source of error is
cmd.ExecuteNonQuery()

and my relevant code is shown below

For Connection:

Private Sub FrmStudent_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        OleDbConnection1.ConnectionString = strCon
        Refresh_Form()

    End Sub

Module ModuleConnection
    Public Con As System.Data.OleDb.OleDbConnection
    Public U, P, S, D, strCon As String
    Public ObjCon As FrmConnection
    Public Sub prcConnect(ByVal U, ByVal P, ByVal S, ByVal D)
        Try
            strCon = "provider=SQLOLEDB;User id=" & U & ";Password=" & P & ";Server=" & S & ";database=" & D
            Con = New System.Data.OleDb.OleDbConnection(strCon)
            Con.Open()
            MsgBox("Connection to the Database is Successful", MsgBoxStyle.Information, "Welcome")
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error Connecting To Database")
            ObjCon = New FrmConnection
            ObjCon.Show()
        End Try
    End Sub
    
End Module

For ExecuteNonQuery():

Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click

        If Len(Trim(txtstudCd.Text)) = 0 Then
            MessageBox.Show("Please Student Id cannot be blank", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End If
        If Len(Trim(txtFirstName.Text)) = 0 Then
            MsgBox("Please FirstName cannot be blank", MsgBoxStyle.OkOnly, "Enter Name")
            Exit Sub
        End If
        If Len(Trim(txtLastName.Text)) = 0 Then
            MsgBox("Please LastName cannot be blank", MsgBoxStyle.OkOnly, "Enter Name")
            Exit Sub
        End If

        If Not IsNumeric(txtAge.Text) Then
            MessageBox.Show("Please Age must be Numeric value", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtAge.Text = ""
            txtAge.Focus()
            Exit Sub
        End If

        If Not IsNumeric(txtstudCd.Text) Then
            MessageBox.Show("Please Student Id must be Numeric Value", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtstudCd.Text = ""
            txtstudCd.Focus()

        End If


        Dim Con As New OleDb.OleDbConnection(strCon)
        Dim strSQL As String = _
        "INSERT INTO Student (StudentCode, FirstName, LastName, Age)" & _
        " VALUES(@StudentCode, @FirstName, @LastName, @Age )"

        Dim cmd As New OleDb.OleDbCommand(strCon)
        With cmd
            .Parameters.Add(New OleDb.OleDbParameter("@StudentCode", SqlDbType.NVarChar, 6)).Value = txtstudCd.Text
            .Parameters.Add(New OleDb.OleDbParameter("@FirstName", SqlDbType.NVarChar, 30)).Value = txtFirstName.Text
            .Parameters.Add(New OleDb.OleDbParameter("@LastName", SqlDbType.NVarChar, 30)).Value = txtLastName.Text
            .Parameters.Add(New OleDb.OleDbParameter("@Age", SqlDbType.NVarChar, 3)).Value = txtAge.Text
        End With
        Con.Open()
        cmd.ExecuteNonQuery()
        Con.Close()
        MsgBox("Data Saved", MsgBoxStyle.Information, "Hello")
        Refresh_Form()

End Sub

[GrimJack]

Wrong forum

[Teme64]

Is prcConnect called before cmdSave_Click ? If so, close the connection

Try
  strCon = "provider=SQLOLEDB;User id=" & U & ";Password=" & P & ";Server=" & S & ";database=" & D
  Con = New System.Data.OleDb.OleDbConnection(strCon)
  Con.Open()
  MsgBox("Connection to the Database is Successful", MsgBoxStyle.Information, "Welcome")
Catch ex As Exception
  MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error Connecting To Database")
  ObjCon = New FrmConnection
  ObjCon.Show()
Finally
  If Con IsNot Nothing AndAlso Con.State = ConnectionState.Open Then
    Con.Close()
  End If
End Try

Put a breakpoint in cmdSave_Click to line Dim Con As New OleDb.OleDbConnection(strCon) and check that you actually have a valid connection string.

Add a connection object to the command object Dim cmd As New OleDb.OleDbCommand(strSQL, Con) .

Finally, put your DB code inside Try...Catch block

Try
  Con.Open()
  cmd.ExecuteNonQuery()
  Con.Close()
  ' Success
Catch ex As Exception
  ' Handle error
End try

[ireneotom]

Hello Friends,
i am back again Please my code is still not working.
i placed a breakpoint to my connection string and added a connection object to the command object and i put the DB code inside a Try...Catch Block. as Team64 suggested.
It is not gennerating any error but when i preview the data through the dataadapter it does not indicated that the data is in the database.
I am counting on your contributions

Thanks

ireneotom

[4advanced]

First, try it in the same sub/function. If it works you can export the functionality to some global functions/subs

try this:

Try
            strCon = "provider=SQLOLEDB;User id=" & U & ";Password=" & P & ";Server=" & S & ";database=" & D
            Con = New System.Data.OleDb.OleDbConnection(strCon)
            Con.Open()

            'EXECUTE YOUR COMMAND HERE
            Dim strSQL As String = _
        "INSERT INTO Student (StudentCode, FirstName, LastName, Age)" & _
        " VALUES(@StudentCode, @FirstName, @LastName, @Age )"

            Dim cmd As New OleDb.OleDbCommand(Con)
            With cmd
                .Parameters.AddWithValue("@StudentCode", txtstudCd.Text)
                .Parameters.AddWithValue("@FirstName", txtFirstName.Text)
                .Parameters.AddWithValue("@LastName", txtLastName.Text)
                .Parameters.AddWithValue("@Age" txtAge.Text)
            End With
            cmd.ExecuteNonQuery()


        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error Connecting To Database")

        Finally
            If Con IsNot Nothing AndAlso Con.State = ConnectionState.Open Then
                Con.Close() 
                'cleanup 
                Con.Dispose
                Con = Nothing
                cmd = nothing
            End If
        End Try