none
Query input must contain at least one table or query?

    Question

  • I have two tables:

    Table1
    PrimaryKey
    Name

    Table2
    PrimaryKey
    ForeignKey
    BackColor

    I'm trying to insert Primary key from Table1 to Table2 as a Foreign key by doing this:

    cmd = new OleDbCommand("INSERT INTO [Table2] (ForeignKey, BackColor) VALUES ((SELECT [ID] FROM [Table1] WHERE [NAME] = @name), @backColor)", connection);
                        cmd.Parameters.AddWithValue("@name", name);
                        cmd.Parameters.AddWithValue("@backColor", backColor);
                        cmd.ExecuteNonQuery();           

    Then I get the error:

    "Query input must contain at least one table or query." 

    What could be wrong?

    Friday, February 09, 2018 2:21 AM

All replies

  • Try following code

    StringBuilder query = new StringBuilder();
    query.AppendLine("INSERT INTO [Table2] (ForeignKey, BackColor)");
    query.AppendLine("SELECT [ID], @backColor FROM [Table1] WHERE [NAME] = @name");
    cmd = new OleDbCommand(query.ToString(), connection);
    cmd.Parameters.AddWithValue("@name", name);
    cmd.Parameters.AddWithValue("@backColor", backColor);
    cmd.ExecuteNonQuery();   


    Gaurav Khanna | Microsoft MVP | Microsoft Community Contributor

    Friday, February 09, 2018 6:06 AM
  • Hi Jonas,

    Please do not use a new query in the insert statement, in addition to Gaurav, also please refer to the following solution:

        using (cm = new OleDbCommand())
        {
            cm.Connection = cn;
            cm.CommandType = CommandType.Text;
    
            cm.CommandText = "(SELECT [ID] FROM [Table11] WHERE [Name1] = @name)";
            cm.Parameters.AddWithValue("@name", name);
            int id = Convert.ToInt32(cm.ExecuteScalar());
    
            cm.CommandText = "INSERT INTO [Table2] (ForeignKey, BackColor) VALUES (@ID, @backColor)";
            cm.Parameters.AddWithValue("@ID", id);
            cm.Parameters.AddWithValue("@backColor", backColor);
    
            cm.ExecuteNonQuery();
            MessageBox.Show("Inserted successfully");
        }

    Regards,

    Frankie


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 09, 2018 6:52 AM
    Moderator
  • I would suggest looking at the following syntax.

    Write the INSERT in ms access (if that is the target database) to ensure it works e.g.

    INSERT INTO Customer1 (CompanyName,ContactName )
    SELECT  CompanyName,ContactName 
    FROM Customer 
    WHERE ContactTitle='Owner'

    If you want to be dynamic in ms access we can do

    INSERT INTO Customer1 ( CompanyName, ContactName )
    SELECT Customer.CompanyName, Customer.ContactName
    FROM Customer
    WHERE Customer.[ContactTitle]=[Title];
    

    Bring the query into your code as a one-liner, add a parameter to the command object for the where condition and try it out.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, February 09, 2018 10:48 AM
  • Your syntax is not correct for inserting the values from select, your variable @backColor should go in the select statement, not after the Select statement.

    It should be something like:

    INSERT INTO [Table2] (ForeignKey, BackColor) VALUES ((SELECT [ID], @backColor FROM [Table1] WHERE [NAME] = @name))

    Hope it helps!


    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered"Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


    Blog | LinkedIn | Stack Overflow | Facebook
    profile for Ehsan Sajjad on Stack Exchange, a network of free, community-driven Q&A sites


    Friday, February 09, 2018 11:40 AM
  • Hi Jonas,

    Have you solved this problem now?

    I think the above replies can provide you with a solution, have you tried them?

    If so, hope you can close this thread by marking the helpful reply as answer as this will help others looking for the same or similar issues down the road.

    Best Regards,

    Frankie


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 12, 2018 9:27 AM
    Moderator
  • Are you sure the name of the Tables is Table1 and Table2 it looks so weird.

    Success Cor

    Monday, February 12, 2018 4:55 PM