SQl update data problem

[gechin]

hello everybody,
i have a problem using sql in updating my data to the access database.
my code is like this:

Public Sub CmdUpdate()
Dim name as String
Dim sql as String
name = txtname.Text
sql="UPDATE my_table SET name=' " & name & " ' "

my_cn.BeginTrans
my_cn.Execute(SQL)
my_cn.CommitTrans

End Sub

but i got this error message:

Run-time error '91':
Object variable or with block variable not set.

i have no problem in insert the data to the database but have problem in updating it.
i don't know where i have code wrong. maybe you guy can have a look.

thanks,
gechin

[Cimperiali]

seems as if "my_cn" has been set to nothing (or is out of scope here), or it might be "txtname" which is not visible from this sub.

You can code:

sql= FormName.txtname.text

and

if my_cn is nothing then
    set my_cn=new adodb.Connection
   '...set up the connection string again
   'and open it
end if

before trying to use my_cn

[gechin]

well, "my_cn" is ok. i did not set it to nothing.
this is because as my project load, i retrieve value from my_table and it is ok. but when i want to update, that's the problem i get.

is my snytax wrong some where?

gechin

[dinesh123]

Can u paste my_cn declartions

[gechin]

er.... actually i'm now in the internet cafe and i did not copy down the connection on my paper.

i have my project at my house PC. so it might be a problem here.

gechin

[gechin]

ok how about this, assume the connection is ok.

is there anything wrong with my SQL syntax?
gechin

[dinesh123]

Nothing wrong with SQL
as the error suggest it is someting releted to the connection

[gechin]

oh i see.
ok i will go back home and check it out.

there is one more thing to ask. is there any diffrent between this SQL?

sql = "UPDATE my_table SET my_table!name = ' " & name & " ' "

well this get the same result as the previous sql statement?

gechin

[dinesh123]

Nothing. It is the same

[gechin]

ok i try on the connection this time and wait for my reply.
thanks for the help. will get back to you as soon as i can.

thanks,
gechin

[doofusboy]

Another possibility is this:

Dim sql as String

sql="UPDATE my_table SET name=' " & name & " ' "

my_cn.Execute(SQL)

Variable was lower case when declared and when value was assigned to it, but upper case when used.

[dinesh123]

is VB variables are case sensetive???

[doofusboy]

Not sure, was just a suggestion. Ergo use of the word "possibility".

[dinesh123]

I checked
If u difine variable as sql and if u type it SQL it will convert it to sql automattically

[gechin]

VB variables are case sensetive.

sorry for the late reply. i have fix the problem according to dinesh's suggestion. it is the connection's problem.

i did not close my connection until the program end. that is why i think i still have the connection.

i fix the program by creating a new connection inside the same function and it done.

thank you guys for solving my problem.

gechin