[b]Best Practice for Inserting a New Record[\b]

[tis9700]

Hi Everyone,
I done a lot of reading and I find I'm more confused than ever.

I have an ADP frontend with a SQL Server 2k Backend. I created a form to enter new records into a table. What would be the best way for users to enter new records without directly compromising the table? My understanding is that the record source of a form should not be set directly to the table.

This form is for creating new records only. They won't need to have any data returned.

I created a stored procedure to INSERT a record but I'm a little lost after that point.

CREATE PROC NEW_STUDENT_RECORD AS
INSERT dbo.HunterEd_Student_Registration_Table (LastName, FirstName, MI, Generations,
StreetAddress, City, State, ZipCode, Phone,FinalGrade)
VALUES ('DOE', 'JOHN', 'A', 'SR', '1205 FALSE DR', 'NOWHERE', 'TN', '33522',
'5555555555','S')

This works fine from Query Analyzer but everytime a record needed to be added, I would have to go in and change the values myself, DOH!

Where do I go from here? REALLLLLY don't know what I doing!!!!

Thanks for the help and sorry for the desperation in my tone.

Tracy

 

[danvlas]

Assuming you have an ID - identity field in your table:

CREATE PROC NEW_STUDENT_RECORD
(@LastName nvarchar(50), 
@FirstName nvarchar(50),
@MI nvarchar(50), 
@Generations nvarchar(50),
@StreetAddress nvarchar(50), 
@City nvarchar(50), 
@State nvarchar(50),
@ZipCode nvarchar(50),
@Phone nvarchar(50),
@FinalGrade nvarchar(50),
@ID int out)
 AS
set nocount on

INSERT dbo.HunterEd_Student_Registration_Table (LastName, FirstName, MI, Generations,
StreetAddress, City, State, ZipCode, Phone,FinalGrade)
VALUES (@LastName, @FirstName, @MI, @Generations,
@StreetAddress, @City, @State, @ZipCode, @Phone,@FinalGrade)
select @ID= scope_identity()

Create a function in a module:
Function fAdd(Conn As ADODB.Connection, {ListOfInputParams}) As Long

Dim oCmd As New ADODB.Command
With oCmd
.ActiveConnection=Conn
.CommandText = "NEW_STUDENT_RECORD"
.CommandType = adStoredProc
.Parameters.Append .CreateParameter("@LastName", adVarChar, , 50, LastName)
'...continue with all other input params
.Parameters.Append .CreateParameter("@ID", adInteger, adParamOutput)
.Execute
fAdd = .Parameters("@ID")
End With

End Function

In the 'add' button event procedure:

Private Sub cmdButton_Click()
Dim i As Long
i = fAdd(CurrentProject.Connection, Me.txtLastName....)

if i<>0 then
Msgbox "Record added with the ID: " & i
Else
Record NOT added!"
End If


End Sub


HTH




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[tis9700]

Hi Dan,
Thanks for the help. I've got everything created as you suggested but I think I have a property setting wrong on the form. When I enter a record I get this error...

"Key column information is insufficient or incorrect.
Too many rows were affected by tupdate"

Then it adds the record but I noticed the ID was skipping.
For example, 22 then 24. No 23 assigned.

When I click the button again I get ......
Runtime error '3001'
"Arguments are of the wrong type, are out of acceptable range or are in conflict with one another"

When I click Debug it goes to this line in the module....
.CommandType = adStoredProc

What do you think?
Thanks,
Tracy