asked on

insert into database probs using sql

sql = "UPDATE Customers " & _
"SET CompanyName = '" & Text2.Text & "'," & _
"CompanyAddress = '" & Text3.Text & "', " & _
"Town or City = '" & Text4.Text & "'," & _
"County = '" & Text5.Text & "'," & _
"PostalCode = '" & Text6.Text & "'," & _
"Country = '" & Text7.Text & "'," & _
"PhoneNumber = '" & Text8.Text & "'," & _
"FaxNumber = '" & Text9.Text & "'," & _
"Notes = '" & Text10.Text & "'" & _
"WHERE CustomerID= '" & Text1.Text & "'"
dbAddress.Execute sql

i keep getting a run-time error when i run this the error is -2147217900(80040e14)

caraf_g

Put a space before the "WHERE...

change it to " WHERE....

caraf_g

PS, you should also replace embedded single quotes with two single quotes

_
"SET CompanyName = '" & Text2.Text & "'," & _

Should be

_
"SET CompanyName = '" & Replace(Text2.Text, "'", "''") & "'," & _

Please note "'" = A string consisting of one single quotes
"''" = A string consisting of two single quotes, *NOT* one double quote...

guyss

also, the final sql string should look like:

UPDATE tbl set companyname='blablabla'
so put single quotes before and after you textfield.

Éric Moreau

This line:
"Town or City = '" & Text4.Text & "',"

Should be read as:
"[Town or City] = '" & Text4.Text & "',"

Because queries can't have spaces in name of fields or tables or ...

Slightly_Imperfect

ASKER

i sussed out what the problem was

town and city should have been put like this

`Town and City` that was it !!!

works fine now thanks for all your help !!!!!

:>)

ASKER CERTIFIED SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Slightly_Imperfect

ASKER

i will give you the points just because i am feeling nice !!!! but i had sorted it out before i logged back on :p

cheers anyway !!!!