Member Avatar for Edmondo

Hello everyone, can you help?

I am trying to build a site using visual studio with a access database back end, basically two of the tables in the database are linked and I want to update the both of them at the same time.

What I have is a "Formview" data connection where three of the input boxes link to a "Names" table and one input box links to the "Group" table. I want to insert data into both tables on the same button click.

When I try though I can't seem to get this to work, I thought you could put a standard INSERT query in the code referencing the two tables as below

INSERT INTO Names (person1, Address, Postcode)
VALUES (@person1, @Address, @Postcode) AND
INSERT INTO Group (GroupName)
VALUES (@GroupName)

I keep getting a error about inserting a ";" at the end but tried that and nothing works.

Does anyone have any thoughts at all? Am I missing something obvious?

Thanks

Edited by peter_budo because: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)
  • 4 Contributors
  • 6 Replies
  • 100 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by kameswari
Member Avatar for kameswari

Hi you can write two seperate insert queries on single button click... y r u opting for single query.. if u need that let me know.. please follow this..

INSERT INTO Names (person1, Address, Postcode)
VALUES (@person1, @Address, @Postcode)  //this is first query

INSERT INTO Group (GroupName)
VALUES (@GroupName)
//this is second query...

just split your query...

Edited by peter_budo because: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)
Member Avatar for sknake

I don't think it is possible to run multiple queries in a single transaction with access. You have to split it out to two commands in code.

Member Avatar for Edmondo

Hi you can write two seperate insert queries on single button click... y r u opting for single query.. if u need that let me know.. please follow this..

INSERT INTO Names (person1, Address, Postcode)
VALUES (@person1, @Address, @Postcode) //this is first query

INSERT INTO Group (GroupName)
VALUES (@GroupName)
//this is second query...

just split your query...

Hello, thanks for reply

This is my code

<asp:SqlDataSource ID="AdminDataSource1" runat="server" ConnectionString="<%$ConnectionStrings:itAlertsDatabaseConnectionString %>"
 
InsertCommand="INSERT INTO SMS_GroupRecipients (emailaddress, mobilenumber, Name) VALUES (@emailaddress, @mobilenumber, @Name) INSERT INTO SMS_GroupNames (GroupName) VALUES (@GroupName)" 
                                
ProviderName=<%$ConnectionStrings:itAlertsDatabaseConnectionString.ProviderName %>" 
                                
SelectCommand="SELECT SMS_GroupRecipients.*, SMS_GroupNames.* FROM SMS_GroupRecipients, SMS_GroupNames">
</asp:SqlDataSource>

But I'm still getting the error about the ";" sorry any thoughts?

Member Avatar for reach_yousuf

Sknake is right!! Two separate insert statements is required

Member Avatar for Edmondo

Sknake is right!! Two separate insert statements is required

Hello! Have I not done two seperates ones as shown above?

Member Avatar for kameswari

Hi...
You have written continuous query.. splitting means... You need to take 2 seperate insert queries... please dont concatenate them...

hope you understand..

Best Regards,
Kameswari Chavali

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.