vb.net foreign key insert sql statement

Question

I have encounter a problem where it tell me I have an Invalid SQL statement; expected 'DELETE','INSERT','PROCEDURE', 'SELECT', or 'UPDATE'.

I am trying to insert data that use foreign key.

Table pemain contain ID MABOPA PKBM MBA MBEIA PPPBBM MAPIM

Table Ahli contain ID company_name name address poscode state email phone fax company_reg website remarks pemain

where field pemain in table Ahli is the foreign key of table pemain

the code im using are

    Dim Pos As Integer
    Dim Pemain As Integer

    Int32.TryParse(TxtBoxPoscode.Text, Pos)
    Int32.TryParse(TxtBoxPemainId.Text, Pemain)

    Access.AddParam("@MABOPA", TextBox1.Text)
    Access.AddParam("@PKBM", TextBox2.Text)
    Access.AddParam("@MBA", TextBox3.Text)
    Access.AddParam("@MBEIA", TextBox4.Text)
    Access.AddParam("@PPPBBM", TextBox5.Text)
    Access.AddParam("@MAPIM", TextBox6.Text)

    Access.AddParam("@companyname", TxtBoxComName.Text)
    Access.AddParam("@name", TxtBoxName.Text)
    Access.AddParam("@address", TxtBoxAdd.Text)
    Access.AddParam("@poscode", Pos)
    Access.AddParam("@state", CboBoxState.Text)
    Access.AddParam("@email", TxtBoxEmail.Text)
    Access.AddParam("@phone", TxtBoxPhone.Text)
    Access.AddParam("@fax", TxtBoxFax.Text)
    Access.AddParam("@companyreg", TxtBoxComName.Text)
    Access.AddParam("@web", TxtBoxWebsite.Text)
    Access.AddParam("@remarks", TxtBoxRemarks.Text)
    Access.AddParam("@pemain", Pemain)

    'Execute Insert Command

                  Access.ExecQuery(
                 "START TRANSACTION;" & _
                 "INSERT INTO pemain (MABOPA, PKBM, MBA, MBEIA, PPPBBM, MAPIM);" & _
                 "VALUES (@MABOPA, @PKBM, @MBA, @MBEIA, @PPPBBM, @MAPIM);" & _
                 "DECLARE @NewID INT;" & _
                 "SELECT @NewID = SCOPE_IDENTITY();" & _
                 "INSERT INTO Ahli (company_name, name, address, poscode, state, email, phone, fax, company_reg, website, remarks, pemain);" & _
                 "VALUES (@companyname, @name, @address, @poscode, @state, @email, @phone, @fax, @companyreg, @web, @remarks, @NewID);" & _
                 "COMMIT;")

It would be a really great if anyone could help me.

Your code is T-SQL and is far away from what Access SQL understands. You will have to rewrite this completely using Access SQL. — Gustav
– Gustav, Commented Mar 7, 2017 at 8:08

jmcilhinney · Accepted Answer · 2017-03-07 05:56:11Z

2

Neither the Jet nor ACE OLE DB providers support multiple SQL statements per command. If you want to execute multiple SQL statements then you must execute multiple commands (or the same command more than once with different SQL statements) and if you want to wrap them in a transaction then you call BeginTransaction on your OleDbConnection to create an OleDbTransaction.

By the way, I'm not sure that SCOPE_IDENTITY exists in Access. I could be wrong but I think that that's specific to SQL Server. I think Access requires using @@IDENTITY.

answered Mar 7, 2017 at 5:56

jmcilhinney

54.9k5 gold badges30 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Over a year ago

Yep, Access uses @@IDENTITY. The whole query needs writing to be honest, Access will quake in dear trying to work out what to do with that!

Collectives™ on Stack Overflow

vb.net foreign key insert sql statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related