inserting a new row into an access table by vb.net, but not added in table

Question

4.00/5 (1 vote)

See more:

VB

Public Shared Function TempInsert(ByVal TempInfo As Business_Layer.cls_Mine_Business.TempMine) As Int16
    Dim Lo_cmd As OleDb.OleDbCommand
    'Dim Po_dt As DataTable
    'Dim Lo_DataAdaptor As OleDb.OleDbDataAdapter
    Dim lo_connection As New Common_Layer.cls_Connection

    Lo_cmd = New OleDb.OleDbCommand

    Common_Layer.cls_Public_Objects.Go_Connection.SetConnection(Lo_cmd)

    Lo_cmd.CommandText = "INSERT INTO tbl_Mine ( MINE_NAME, Mojaveze_Bahrebardari_No, Mojaveze_Bahrebardari_Date, Geo_Location, Personel_No, Capacity)" & _
                         "values ('" & TempInfo.Mine_Name & "','" & TempInfo.Permit_No & "','" & TempInfo.Permit_date & "','" & TempInfo.geo & "'," & TempInfo.Personel & "," & TempInfo.capacity & ");"
    Try
        Lo_cmd.ExecuteNonQuery()
        Return 0
    Catch ex As Exception
        Return Common_Layer.cls_Messages.Message_Codes.ErrGetList
    Finally
        Lo_cmd = Nothing

    End Try
End Function

the function returns 0 (means it have been done without any errors), but the data will not insert in the table(tbl_mine).

why?

Posted 15-Aug-11 0:13am

vahid_gian

Add a Solution

2 solutions

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)

Simon_Whale · Accepted Answer · 2011-08-15T00:36:00

I would do the following

1. Get the SQL Statement that is dynamically generated and run it directly on your access database to make sure there is no errors in your sql string. i.e.

SQL

INSERT INTO tbl_Mine ( MINE_NAME, Mojaveze_Bahrebardari_No, Mojaveze_Bahrebardari_Date, Geo_Location, Personel_No, Capacity) values ("fred","Test",15/08/2011,"Worcester",12345,1)

2. I would change your SQL Statement to use parameterized queries.

Parameterized queries with MS Access[^]

Wendelius · Accepted Answer · 2011-08-15T09:26:00

Few things to check:

put a breakpoint to Lo_cmd.ExecuteNonQery, what does this return. The returned number is the number of rows affected. If it's 0 nothing is inserted.
in your catch block, it looks like you're gathering the errors from the connection. Does that gather errors also from the command? When debugging the code check that the code isn't actually hitting the catch block and possibly returning 0 erroneously.
where is the database file you use. Is it in a static location or is it included in your project. Could it be that when you run the project the database file in debug or release directory gets overwritten with the initial version included with the project.

And yes, do use parameters instead if string concatenations. For example, if the user would write to TempInfo.Mine_Name:
Some's name
you would have a statement like:

SQL

INSERT INTO tbl_Mine 
( MINE_NAME, Mojaveze_Bahrebardari_No, Mojaveze_Bahrebardari_Date, Geo_Location, Personel_No, Capacity) 
values ('Some's name',...

That would certainly cause an error.

inserting a new row into an access table by vb.net, but not added in table

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0