Link to home
Start Free TrialLog in
Avatar of jensjakobsen
jensjakobsenFlag for Denmark

asked on

Easy Insert = difficult for me

Hi I have a very simple insert code that doesn't work. I'm sure it's a syntax error:

        ok_fkIntUserID3 = rsUser("pkIntUserID")
        ok_intAccessLevel3 = 2
            Dim con3, sql3, data_source3
            data_source3 = "Provider=SQLOLEDB;Data;Initial Catalog=dsts_org_db;User ID=dsts_org;Password=qawsedrf"
            sql3 = "UPDATE dbo.tbl103TransactionsManual (intAccessLevel) VALUES " & "('"& ok_intAccessLevel3 &"') where pkIntUserID = ok_fkIntUserID3" 
            ' Creating the Connection Object and opening the database
            Set con3 = Server.CreateObject("ADODB.Connection")
            con3.Open data_source3
            ' Executing the sql insertion code
            con3.Execute sql3
            ' Done. Now Close the connection
            Set con3 = Nothing		'execute sql

Open in new window

The error is:
Microsoft OLE DB Provider for SQL Server error '80040e14'

Incorrect syntax near '('.
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

change Update for Insert Into and remove the where clause:

 sql3 = "INSERT INTO dbo.tbl103TransactionsManual (intAccessLevel) VALUES " & "('"& ok_intAccessLevel3 &"') "
Avatar of E C
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
by the way you should probably change your password   ;-)
Avatar of jensjakobsen


Wow...... that's about the dumbest thing I have ever done... thank you. I will test your SQL sentence.
But I still get the same error :(

I'm using:

sql3 = "UPDATE dbo.tbl103TransactionsManual (intAccessLevel) VALUES ('" & ok_intAccessLevel3 & "') WHERE pkIntUserID = ok_fkIntUserID3"

Open in new window

By the way - I'm trying to do an UPDATE (not insert......)
your question was asking for an Insert!

try this:

sql3 = "UPDATE dbo.tbl103TransactionsManual set intAccessLevel = '"& ok_intAccessLevel3 &"' where pkIntUserID = ok_fkIntUserID3"

it says:

Invalid column name 'ok_fkIntUserID3'.

ok_fkIntUserID3 is supposed to be a variable containing a value.
Then how about this:
sql3 = "UPDATE dbo.tbl103TransactionsManual set intAccessLevel = '"& ok_intAccessLevel3 &"' where pkIntUserID = " & CStr(ok_fkIntUserID3)

That is of course assuming that ok_fkIntUserID3 is a numeric value.
I don't understand why you accepted ecarbone solution when it is clearly not it!
Not even close.  The answer accepted is not valid SQL code.  I am actually more surprised that someone who has been here more than 10 years would have posted an untested and invalid solution, than the author not even bothering to test it.