CODEWITHSUNDEEP
c#asp.netms-accessinsertmax
c0ldbru
c0ldbru

Reputation: 33

insert MAX()+1 and other values

I'm very new to C# and trying to insert multiple values to an access database, one of which will increment by 1. I cannot use auto-number in access because the entries start at 99001 and I will need the ability to edit/skip numbers easily. Ive been trying different posts all day that were similar to this, but all of them cause errors or just don't work. My code is currently as follows.

AccessDataSource1.InsertCommand = "INSERT INTO [1U] ([serial], [model], [motherboard], [mobogroup], [bios]) SELECT ((SELECT MAX([serial])+1 FROM [1U]), ?, ?, ?, ?)";
AccessDataSource1.InsertParameters["serial"].DefaultValue = "1";
AccessDataSource1.InsertParameters["model"].DefaultValue = "D";
AccessDataSource1.InsertParameters["motherboard"].DefaultValue = "D";
AccessDataSource1.InsertParameters["mobogroup"].DefaultValue = "D";
AccessDataSource1.InsertParameters["bios"].DefaultValue = "D";

with the button calling this as

<asp:Button ID="InsertButton" runat="server"  OnClick="update_click" CausesValidation="True" CommandName="Insert" Text="Insert" />

but this gives me an error saying "Number of query values and destination fields are not the same." any ideas on how to fix this or alternate ways to achieve the result I am looking for?

Upvotes: 1

Views: 1248

Answers (4)

AndrewR
AndrewR

Reputation: 6748

You have 4 placeholders and 5 insert parameters, which is the reason for your error message. Since you aren't using serial in your query, remove that InputParameter.

AccessDataSource1.InsertCommand = "INSERT INTO [1U] ([serial], [model], [motherboard], [mobogroup], [bios]) SELECT ((SELECT MAX([serial])+1 FROM [1U]), ?, ?, ?, ?)";
AccessDataSource1.InsertParameters["model"].DefaultValue = "D";
AccessDataSource1.InsertParameters["motherboard"].DefaultValue = "D";
AccessDataSource1.InsertParameters["mobogroup"].DefaultValue = "D";
AccessDataSource1.InsertParameters["bios"].DefaultValue = "D";

Also, the documentation shows named input parameters in the query, not question marks. If the code above doesn't work, try changing your InsertCommand to this.

AccessDataSource1.InsertCommand = "INSERT INTO [1U] ([serial], [model], [motherboard], [mobogroup], [bios]) SELECT ((SELECT MAX([serial])+1 FROM [1U]), @model, @moetherboard, @mobogroup, @bios)";

Upvotes: 1

Rikki
Rikki

Reputation: 3518

I think you were doing the right thing. AutoNumber (Identity Column in SQL) has been made for numbering records and generating manual numbers might cause some problems for you, as you say you are a beginner. Also you need to know how Seed and Start number of an AutoNumber column work.

By the way, what is your problem does not relate to anything you mentioned in your comment. It simply says that columns you are inserting in the Insert clause is not the same as the columns in the target table.

Cheers

Upvotes: 0

James
James

Reputation: 82096

I cannot use auto-number in access because the entries start at 99001

You can trick MS Access into using a seeded autonumber, see Create an AutoNumber field that starts with a number greater than 1 for an example on how to do this.

Upvotes: 1

Daniel
Daniel

Reputation: 13122

What if you just hardcode the values?

AccessDataSource1.InsertCommand = "INSERT INTO [1U] ([serial], [model], [motherboard], [mobogroup], [bios]) SELECT ((SELECT MAX([serial])+1 FROM [1U]), "D", "D", "D", "D")";

Upvotes: 0

Related Questions