PDA

View Full Version : Solved: Table Autonumber and Visual Studio VBA



Rlb53
12-24-2012, 08:46 PM
Would you please assist with this code?

I have a table configured to autonumber each row under the "ID" Column.
The balance of the data (3 additional columns) to fill the row is transferred from a Visual Studio User Form.
The VBA Code incorporated into the userform "Save Button" is as follows:

AdminTableAdapter.Insert(ID:="This is where I have a conflict",AdminFN:=AdminSetupFormFNBox.Text, AdminLN:=AdminSetupFormLNBox.Text, AdminPword:=AdminSetupFormPwordBox.Text)
MessageBox.Show("Database updated!")
Me.Close()



Everything transfers fine except the "ID:=" column which I want to autonumber. Visual Studio will not allow me to leave out a call to place data within the column of the table within the VBA Code.

I do not know how to configure the VBA so that the table may autonumber as configured, or disable the table configuration and allow the VBA to provide the autonumber.

Would you assist with this correction please?

Thank You.

Tommy
12-27-2012, 12:08 PM
The ID:= you are passing is a string not a number.

See this http://msdn.microsoft.com/en-us/library/yctw654b(v=vs.80).aspx

Rlb53
12-27-2012, 03:45 PM
Thank you Tommy,

The presentation " ID:= "This is where I have a conflict" " is certainly a string, but it was a failed attempt to convey my confusion. I apologize.

In using Microsoft SQL Server Management Studio to generate my tables, the Table SQL is described as:
SET ANSI_NULLS ON
GO
SET QUOTED IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Admin](
[ID] BIGINT IDENTITY (1,1) NOT FOR REPLICATION NOT NULL,
[AdminFN] VARCHAR(50) NOT NULL,
[AdminLN] VARCHAR(50) NOT NULL,
[AdminPword] VARCHAR(50) NOT NULL,
CONSTRAINT [PK_Admin] PRIMARY KEY CLUSTERED
([ID] ASC))

To the best of my comprehension this configures column [ID] as the Primary Key with Auto Number.

The Userform generated with Visual Studio 2012 gathers three points of data into the Textbox's.
After all three have been filled, the Command Button Code includes:


AdminTableAdapter.Insert(AdminFN:=AdminSetupFormFNBox.Text, AdminLN:=AdminSetupFormLNBox.Text, AdminPword:=AdminSetupFormPwordBox.Text)
MessageBox.Show("Database updated!")
Me.Close()


The error message shown below indicates that it will not allow me to exempt an INSERT reference to column [ID] within the userform command button code. (which... I thought exemption would allow the autonumber to fire when the balance of the columns were filled since the column [ID] is a Primary Key and configured to AutoNumber.)

===============================

"Error 1 Argument not specified for parameter 'ID' of 'Public Overridable Overloads Function Insert(ID As Long, AdminFN As String, AdminLN As String, AdminPword As String) As Integer'. C:\Users\rlb53\Documents\Visual Studio 2012\Projects\BOL\WindowsApplication1\AdminSetupForm.vb 7 13 WindowsApplication1"

================================

When Data is entered directly into the table, the auto number setting performs as intended.

================================

When a distinct value is entered after "ID:=" within the command code, an appropriate error message appears indicating that INSERT is set to OFF for column [ID] and the value is not recieved.

================================

Is there a keyword that should follow "ID:=" within the Visual Studio 2012 command code that will cause the autonumber to fire and the data to be stored?

================================

Is there another Syntax which would be more appropriate for use within the Visual Studio 2012 Form Command Code to insert the data into the Table?

================================

When I remove the IDENTITY command from [ID] and modify the Command Button Code to:



AdminTableAdapter.Insert(ID:=1,AdminFN:=AdminSetupFormFNBox.Text, AdminLN:=AdminSetupFormLNBox.Text, AdminPword:=AdminSetupFormPwordBox.Text)
MessageBox.Show("Database updated!")
Me.Close()


All of the information is stored within the table with no errors but it does not provide the auto number feature.

=================================

I reviewed the Link that you shared. The table settings appear to be in order.

There is obviously a loose nut between the seat and the steering wheel here, but the nut is hanging on as best it can. If you could help tighten it.... it would be greatly appreciated.

I know the solution is going to be so friggin' simple.. I'll be slapping myself for being so blind.
=================================
I have a cross post at:

http://social.msdn.microsoft.com/Forums/en-US/visualstudiogeneral/thread/76d2e5d0-d814-4bcf-9d41-9e2d06d48cfb

in an effort to reach out for a resolve to my blindness which has yet to be achieved. If for any reason the resolution is acquired, It will be posted ASAP.
=================================
Thank you for your continued support !

Rlb53
12-27-2012, 11:38 PM
Solved.

Regenerated the Table and Form.
Hiccup in the Table Adapter

justinbiber
06-17-2013, 11:37 PM
In Microsoft Access tables, the AutoNumber field type allows you to assign a unique sequential number to each row in a table. AutoNumber fields are often used as the Primary Key, since they are unique and permanent (i.e. the value cannot be changed once it is assigned). For new tables, AutoNumbering begins with 1, and is incremented by 1 with each new row. However, we occasionally want to start the AutoNumber at a number higher than 1.......