Please Help - Add Record Ms Access

Question

0.00/5 (No votes)

See more:

Hi
I have 10 textbox on a form that allow user to fill in and adds to the msaccess database. but i am recieving an error " Object reference not set to an instance of an object. - NullReferenceExcption Was Unhandled" at the following line
cmd.Connection = con - Please could you help with what is causing this issue?

 Dim con As New OleDb.OleDbConnection
        Dim cmd As OleDb.OleDbCommand
        Dim eep As New String("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\FHand\Db\FedUp.mdb")
        con.ConnectionString = eep

        If con.State = ConnectionState.Open Then
            con.Open()
        End If

        'Add To Table
        cmd.Connection = con
        cmd.CommandText = "INSERT INTO Field_Yard(ID, Field_ID, Field_Name, Field_Title, Field_Description, Field_Type, Field_Spec, CON_Nodeid, Field_Lenght, Field_Width)" & _
" VALUES (" & Me.txtid.Text & ",'" & Me.txtFID.Text & "','" & Me.txtfname.Text & "','" & Me.txtftitle.Text & "','" & Me.txtfdescription.Text & "','" & Me.txtftype.Text & "','" & Me.txtfspec.Text & "','" & Me.txtconnode.Text & "','" & Me.TXTlenght.Text & "','" & Me.txtWidth.Text & "')"
        cmd.ExecuteNonQuery()

        con.Close()

    End Sub

Posted 25-Oct-11 3:15am

SIFNOk

Add a Solution

3 solutions

Solution 1

Don't know much of VB but at 1st look i think problem is here:-

VB

If con.State = ConnectionState.Open Then
            con.Open()

I suppose to check equality you need to use (==)and if connection is already open what's the need of opening it again

so try something like this:-

VB

If con.State==ConnectionState.Closed Then
con.Open()

Best of luck;

Posted 25-Oct-11 3:24am

Mantu Singh

Comments

SIFNOk 25-Oct-11 9:30am

Hi Mantu,

Thanks for the reply...im not sure if thats the issue, because even when i remove that IF Statement and just uses Con.Open() i get the same error messagee..

itt erroring out at the line ------- CMD.Connection = CON

Kschuler 25-Oct-11 9:40am

In VB the single equal sign is correct. That is not the problem.

SIFNOk 25-Oct-11 9:42am

Thanks for the reply kschuler, i thought i was right with a single (=)....it seems the probly alays with connection string :S

SIFNOk 25-Oct-11 9:46am

Thanks for both of your assistance!

Mantu Singh 25-Oct-11 9:50am

Thanks for updating got to learn little VB

Solution 3

Use following code structure to add values in access database

VB

'Creating Connection object
Public con As New OleDb.OleDbConnection
'Creating data adapter object
Public objda As New OleDb.OleDbDataAdapter
If con.State = ConnectionState.Open Then       'Checking Connection State
con.Close()                                    'Closing Connection
End If
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\DatabaseName.mdb"
Try
con.Open()                                     'Opening Connection
Catch ex As Exception                  'Providing Error Message
MessageBox.Show("Please Contact Application Developer", "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End                                            'Closing Application
End Try
objda.SelectCommand = New OleDbCommand
objda.SelectCommand.CommandText = "Insert into TableName(Field1, Field2) values(@Field1, @Field2)"
objda.SelectCommand.Parameters.AddWithValue("@Field1", Textbox1.Text)
objda.SelectCommand.Parameters.AddWithValue("@Field2",Textbox2.Text) 
'Assigning value of TextField to variable @Eid
Try                                         'To handle OleDb Exceptions
objda.SelectCommand.ExecuteNonQuery()
Catch ex As OleDb.OleDbException
MessageBox.Show(ex.Message)
End Try

Posted 26-Oct-11 19:18pm

Manoj K Bhoir

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kschuler · Accepted Answer · 2011-10-25T03:42:00

Solution 2

I think your problem is that the command object.

You have this:

Dim cmd As OleDb.OleDbCommand

But you need a new instance of the object, so try this instead:

Dim cmd As New OleDb.OleDbCommand

Hope this helps.

Posted 25-Oct-11 3:42am

Kschuler

Comments

SIFNOk 25-Oct-11 9:45am

Thank you soo much for that Kshuler! i totally missed that! makes Sense nowww! thanks again for your contribution it has really helped.