Ad:

VB.NET Discussion Thread
Unsolved
Views: 3635

Jan 20th, 2009

Problems with executeNonQuery and connection

Expand Post »

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:

VB.NET Syntax (Toggle Plain Text)
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
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():

VB.NET Syntax (Toggle Plain Text)
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
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

Last edited by Ancient Dragon; Jan 20th, 2009 at 11:03 pm. Reason: add code tags

Similar Threads

Updated : Simple ASP.Net Login Page in ASP.NET
SQL AddWithValue problems in VB.NET
VB.net/ASP.net SELECT problems with SQL in ASP.NET
INSERT INTO SQL database in ASP.NET
update the db using values in a datagrid in VB.NET

ireneotom

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Jan 2009

Permalink

Jan 20th, 2009

Re: Problems with executeNonQuery and connection

Wrong forum

GrimJack

Featured Poster

Reputation Points: 1448

Solved Threads: 21

Posting Maven

Offline

2,735 posts
since Feb 2004

Permalink

Jan 21st, 2009

Re: Problems with executeNonQuery and connection

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

VB.NET Syntax (Toggle Plain Text)
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
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

VB.NET Syntax (Toggle Plain Text)
Try
  Con.Open()
  cmd.ExecuteNonQuery()
  Con.Close()
  ' Success
Catch ex As Exception
  ' Handle error
End try
Try
  Con.Open()
  cmd.ExecuteNonQuery()
  Con.Close()
  ' Success
Catch ex As Exception
  ' Handle error
End try

Teme64

Reputation Points: 218

Solved Threads: 201

Veteran Poster

Offline

1,024 posts
since Aug 2008

Permalink

Feb 4th, 2009

Re: Problems with executeNonQuery and connection

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

ireneotom

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Jan 2009

Permalink

Feb 4th, 2009

Re: Problems with executeNonQuery and connection

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

try this:

VB.NET Syntax (Toggle Plain Text)
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
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

Last edited by 4advanced; Feb 4th, 2009 at 9:47 am. Reason: cleanup code added

4advanced

Reputation Points: 33

Solved Threads: 10

Junior Poster in Training

Offline

67 posts
since Nov 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in VB.NET Forum Timeline: Add Dynmaic Data in DataGrid

Next Thread in VB.NET Forum Timeline: .Net Function & API

DaniWeb Message

Cancel Changes

User Name
Password