SQL server Insert

D

Dataman

Newbie needs help

I would like to get the last PatientID (Identity) number from the
dbo.tblPatients table and place the value on the form where I just added the
record from. There will be other people entering data as well so I need the
ID for the record I just added. I believe I need to use scope_identity() in
some fashion.

Can someone modify the procedures below for me.

PROCEDURE FOR INSERTING A NEW RECORD. COPIED FROM THE PROCEDURE.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go


ALTER PROCEDURE [dbo].[ProcPatientInsert]

@PFirstName nvarchar(25),

@PLastName nvarchar(25),

@DOB nvarchar(25),

@MedicalRecNumber nvarchar(25),

@EmployeeID nvarchar(25),

@Notes nvarchar(255)=null,

@ResultMessage varchar(20)=Null Output

AS

INSERT INTO tblPatients

(PFirstName,PLastName,DOB,MedicalRecNumber,EmployeeID,Notes)

VALUES

(@PFirstName,@PLastName,@DOB,@MedicalRecNumber,@EmployeeID,@Notes)

SELECT @ResultMessage=Convert(varchar(20),@@RowCount)+' Records Added'

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

CALLING PROCEDURE

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

Dim cmd As ADODB.Command
Set gcnn = New ADODB.Connection
gcnn.Open "Provider=SQLOLEDB;data Source=DATACORP-SERVER;Initial
Catalog=RadiologyBESQL;User Id=sa;Password=sa"

Set cmd = New ADODB.Command
If Not IsNull(PFirstName) And Not IsNull(PLastName) Then
With cmd
.ActiveConnection = gcnn
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@PFirstName", adVarChar,
adParamInput, 25, Me.PFirstName.Value)
.Parameters.Append .CreateParameter("@PLastName", adVarChar,
adParamInput, 25, Me.PLastName.Value)
.Parameters.Append .CreateParameter("@DOB", adVarChar,
adParamInput, 25, Me.DOB.Value)
.Parameters.Append .CreateParameter("@MedicalRecNumber",
adVarChar, adParamInput, 25, Me.MedicalRecNumber.Value)
.Parameters.Append .CreateParameter("@EmployeeID", adInteger,
adParamInput, 25, Me.EmployeeID.Value)
.Parameters.Append .CreateParameter("@Notes", adVarChar,
adParamInput, 255, Me.Notes.Value)
.Parameters.Append .CreateParameter("@ResultMessage", adVarChar,
adParamOutput, 20)

If AddMode = True Then
.CommandText = "ProcPatientInsert"
ElseIf AddMode = False Then
.CommandText = "ProcPatientUpdate"
End If
.Execute
MsgBox .Parameters("@ResultMessage").Value
End With
blnAddMode = False
Else
MsgBox "You must fill in all fields in order to save this record."
Exit Sub
End If

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

THANKS FOR ANY HELP

KURT
 
R

ryguy7272

I did this recently. My Table and Fields are different, but concept is
almost exactly the same.
INSERT INTO tblPubs
(PubName, Address, City, State, Zip, OnDate,
CrawlOrder, NeighborhoodID, PubID)
VALUES
(@PubName,@Address,@City,@State,@Zip,@OnDate,@CrawlOrder,@NeighborhoodID,@PubID)

I think you need to include the PK, or ID Field, as I demonstrate above.
Try that and see if it works for you.

HTH,
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


Dataman said:
Newbie needs help

I would like to get the last PatientID (Identity) number from the
dbo.tblPatients table and place the value on the form where I just added the
record from. There will be other people entering data as well so I need the
ID for the record I just added. I believe I need to use scope_identity() in
some fashion.

Can someone modify the procedures below for me.

PROCEDURE FOR INSERTING A NEW RECORD. COPIED FROM THE PROCEDURE.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go


ALTER PROCEDURE [dbo].[ProcPatientInsert]

@PFirstName nvarchar(25),

@PLastName nvarchar(25),

@DOB nvarchar(25),

@MedicalRecNumber nvarchar(25),

@EmployeeID nvarchar(25),

@Notes nvarchar(255)=null,

@ResultMessage varchar(20)=Null Output

AS

INSERT INTO tblPatients

(PFirstName,PLastName,DOB,MedicalRecNumber,EmployeeID,Notes)

VALUES

(@PFirstName,@PLastName,@DOB,@MedicalRecNumber,@EmployeeID,@Notes)

SELECT @ResultMessage=Convert(varchar(20),@@RowCount)+' Records Added'

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

CALLING PROCEDURE

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

Dim cmd As ADODB.Command
Set gcnn = New ADODB.Connection
gcnn.Open "Provider=SQLOLEDB;data Source=DATACORP-SERVER;Initial
Catalog=RadiologyBESQL;User Id=sa;Password=sa"

Set cmd = New ADODB.Command
If Not IsNull(PFirstName) And Not IsNull(PLastName) Then
With cmd
.ActiveConnection = gcnn
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@PFirstName", adVarChar,
adParamInput, 25, Me.PFirstName.Value)
.Parameters.Append .CreateParameter("@PLastName", adVarChar,
adParamInput, 25, Me.PLastName.Value)
.Parameters.Append .CreateParameter("@DOB", adVarChar,
adParamInput, 25, Me.DOB.Value)
.Parameters.Append .CreateParameter("@MedicalRecNumber",
adVarChar, adParamInput, 25, Me.MedicalRecNumber.Value)
.Parameters.Append .CreateParameter("@EmployeeID", adInteger,
adParamInput, 25, Me.EmployeeID.Value)
.Parameters.Append .CreateParameter("@Notes", adVarChar,
adParamInput, 255, Me.Notes.Value)
.Parameters.Append .CreateParameter("@ResultMessage", adVarChar,
adParamOutput, 20)

If AddMode = True Then
.CommandText = "ProcPatientInsert"
ElseIf AddMode = False Then
.CommandText = "ProcPatientUpdate"
End If
.Execute
MsgBox .Parameters("@ResultMessage").Value
End With
blnAddMode = False
Else
MsgBox "You must fill in all fields in order to save this record."
Exit Sub
End If

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

THANKS FOR ANY HELP

KURT
Click to expand...
 
D

Dataman

The PatientID get assigned on the server side when the record is inserted. I
need the procedure to return that identity to my Access front end to
populate the PatiendID on the form.

Kurt

ryguy7272 said:
I did this recently. My Table and Fields are different, but concept is
almost exactly the same.
INSERT INTO tblPubs
(PubName, Address, City, State, Zip, OnDate,
CrawlOrder, NeighborhoodID, PubID)
VALUES
(@PubName,@Address,@City,@State,@Zip,@OnDate,@CrawlOrder,@NeighborhoodID,@PubID)

I think you need to include the PK, or ID Field, as I demonstrate above.
Try that and see if it works for you.

HTH,
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


Dataman said:
Newbie needs help

I would like to get the last PatientID (Identity) number from the
dbo.tblPatients table and place the value on the form where I just added
the
record from. There will be other people entering data as well so I need
the
ID for the record I just added. I believe I need to use scope_identity()
in
some fashion.

Can someone modify the procedures below for me.

PROCEDURE FOR INSERTING A NEW RECORD. COPIED FROM THE PROCEDURE.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go


ALTER PROCEDURE [dbo].[ProcPatientInsert]

@PFirstName nvarchar(25),

@PLastName nvarchar(25),

@DOB nvarchar(25),

@MedicalRecNumber nvarchar(25),

@EmployeeID nvarchar(25),

@Notes nvarchar(255)=null,

@ResultMessage varchar(20)=Null Output

AS

INSERT INTO tblPatients

(PFirstName,PLastName,DOB,MedicalRecNumber,EmployeeID,Notes)

VALUES

(@PFirstName,@PLastName,@DOB,@MedicalRecNumber,@EmployeeID,@Notes)

SELECT @ResultMessage=Convert(varchar(20),@@RowCount)+' Records Added'

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

CALLING PROCEDURE

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

Dim cmd As ADODB.Command
Set gcnn = New ADODB.Connection
gcnn.Open "Provider=SQLOLEDB;data Source=DATACORP-SERVER;Initial
Catalog=RadiologyBESQL;User Id=sa;Password=sa"

Set cmd = New ADODB.Command
If Not IsNull(PFirstName) And Not IsNull(PLastName) Then
With cmd
.ActiveConnection = gcnn
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@PFirstName", adVarChar,
adParamInput, 25, Me.PFirstName.Value)
.Parameters.Append .CreateParameter("@PLastName", adVarChar,
adParamInput, 25, Me.PLastName.Value)
.Parameters.Append .CreateParameter("@DOB", adVarChar,
adParamInput, 25, Me.DOB.Value)
.Parameters.Append .CreateParameter("@MedicalRecNumber",
adVarChar, adParamInput, 25, Me.MedicalRecNumber.Value)
.Parameters.Append .CreateParameter("@EmployeeID", adInteger,
adParamInput, 25, Me.EmployeeID.Value)
.Parameters.Append .CreateParameter("@Notes", adVarChar,
adParamInput, 255, Me.Notes.Value)
.Parameters.Append .CreateParameter("@ResultMessage",
adVarChar,
adParamOutput, 20)

If AddMode = True Then
.CommandText = "ProcPatientInsert"
ElseIf AddMode = False Then
.CommandText = "ProcPatientUpdate"
End If
.Execute
MsgBox .Parameters("@ResultMessage").Value
End With
blnAddMode = False
Else
MsgBox "You must fill in all fields in order to save this
record."
Exit Sub
End If

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

THANKS FOR ANY HELP

KURT
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sql Server and Un-Bound Forms 2
How do you pass parameters to a stored procedure 2
How do you call a stored procedure that returns a value? 3
ADO: how to execute SQL Server stored procedure and get return val 1
ADODB - parameters with date 6
Trouble Retrieving Recordset from Stored Procedure 4
Numeric Data type 3
Stored procedure problem 1

Top