error updating access database using vb..

[vikas1111]

Hi All..
I am trying to access records and edit and save the changes .. Here is the code

Private Sub cmd_edit_Click()
Dim addnew As Boolean
rsnew3.Open "select * from company", c, adOpenDynamic, adLockOptimistic
rsnew3.addnew
If text1.Text = "" Then
MsgBox " enter the company name"
text1.SetFocus
rsnew3.Fields("text1").Value = text1.Text
End If
If text2.Text = "" Then
MsgBox " enter the company address"
text2.SetFocus
rsnew3.Fields("text2").Value = text2.Text
End If
rsnew3.Update
rsnew3.Close
MsgBox " add new successful ", vbInformation, "successful"
End Sub

Private Sub Form_Load()
Dim cs As String
c.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\COMION.MDB;Persist Security Info=False")
rsnew2.Open "select * from company", c, adOpenDynamic
Me.text1.Text = rsnew2(2)
Me.text2.Text = rsnew2(3)

End Sub

I am getting message that "add new successful" but records are not actually updated...
What should i do ..? can any1 help me pls???

[choudhuryshouvi]

there will be a little bit modification on your code and everything will be ok.

try this :-

Dim addnew As Boolean

addnew=true

on error goto err1

If text1.Text = "" Then
   MsgBox " enter the company name"
   text1.SetFocus
   exit sub
end if

If text2.Text = "" Then
   MsgBox " enter the company address"
   text2.SetFocus
   exit sub
end if

rsnew3.Open "select * from company", c, adOpenDynamic, adLockOptimistic
rsnew3.addnew
rsnew3!text1 = text1.Text
rsnew3!text2 = text2.Text
rsnew3.Update

if addnew=true then MsgBox " add new successful ", vbInformation, "successful"

if rsnew3.state=adstateopen then rsnew3.close
set rsnew3=nothing

exit sub

err1:
     err.clear
     addnew=false
     msgbox "error in creating the new record."
     set rsnew3=nothing
     exit sub 
End Sub

get back with your result.

regards
Shouvik

[vikas1111]

Hi Shouvik..
Thanks .. I tried your code but i am getting the error msg "error in creating the new record."

[werks]

I get the problem if save the record with NULL value then it will save Empty String and if you save the record with a value it will not save any record because you don't specify the code to save the record..

[werks]

Hi Shouvik..
Thanks .. I tried your code but i am getting the error msg "error in creating the new record."

In what line of code you get the error?

[werks]

Try removing the error handler to detect which line the error occur

[vikas1111]

Hi Kenneth...
I am getting error in these lines..
rsnew3!text1 = text1.Text
rsnew3!text2 = text2.Text

[vikas1111]

"I get the problem if save the record with NULL value then it will save Empty String and if you save the record with a value it will not save any record because you don't specify the code to save the record.. " You are right....

[werks]

Do you have a field name text1 in your table in the database?

[werks]

Hi Kenneth...
I am getting error in these lines..
rsnew3!text1 = text1.Text
rsnew3!text2 = text2.Text

BTW what kind of error?

[vikas1111]

Hi once again

No i dont have field name text1 in my table in the database
.. error 3265
Item cannot be found in the collection corresponding to the request name or ordinal

[werks]

Now i see.. You should learn first the basic Database Programming in VB 6..

[cometburn]

Private Sub cmd_edit_Click()
'I'd advice you to use SQL statements
'Put here your validations
'the code below inserts a record to the database, obviously...
'Here is the syntax on inserting a record on a table
'INSERT INTO <tablename>(Field1,Field2,Field3...) VALUES (Value1, value2, value3)

rs.Open "INSERT INTO tblAccess(Access) VALUES ('" & Text1.Text & "')", c, adOpenDynamic
MsgBox " add new successful ", vbInformation, "successful"
End Sub


Private Sub Form_Load()
Dim cs As String
c.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\COMION.MDB;Persist Security Info=False")
rsnew2.Open "select * from company", c, adOpenDynamic
Me.text1.Text = rsnew2(2)
Me.text2.Text = rsnew2(3)
End Sub

Hope this helps...

[choudhuryshouvi]

Hi Shouvik..
Thanks .. I tried your code but i am getting the error msg "error in creating the new record."

might the error is occurring in these lines :-

rsnew3!text1=text1.text
rsnew3!text2=text2.text

when i was rectifying your code i assumed that you have the fields named "text1" and "text2" in your table. so i wrote the code. but if you are getting the error then try this solution :-
just replace "text1" and "text2" with actual field names present in your table and make sure you are not sending any empty or null value if your fields have required constraints.

try it and know me what have you finally got.

regards
Shouvik

[debasisdas]

Simply try to use this sample code .
This is the best option.

con.begin trans
con.execute "update table_name set field_name= text1.text"
con.committrrans