Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Record set troubles 1
- Thread starter mousematt
- Start date
-
1
- #2
Best way to do this is to use a Stored Procedure to INSERT the record, and then right after your INSERT, ask for:
SELECT @@identity FROM inserted
and assign that to an output parameter, which you can then use on your ASP page.
That is assuming that you are using the identity (autonumber) feature to assign that pk.
paul
SELECT @@identity FROM inserted
and assign that to an output parameter, which you can then use on your ASP page.
That is assuming that you are using the identity (autonumber) feature to assign that pk.
paul
- Thread starter
- #3
confused!!! (i'm no SQL expert)
ok below is code i use, basically the page submits to itself using ServerVairables("CONTENT_LENGTH". I would like to pull back the 'id' field so after this codes adds the info to the record i can then show the user the id for that record. (it will be a fault reference number for there own information and process tracking.)
Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset"
objRS.ActiveConnection = connHelpdesk_STRING
objRS.Source = "SELECT * FROM dbo.Faults"
objRS.CursorType = adOpenKeyset
objRS.LockType = adLockPessimistic
objRS.Open
objRS.AddNew
objRS.Fields("field" = Trim(Request.Form("field")
objRS.Update
objRS.Close()
Please explain in a bit of detail.
Thanx
ok below is code i use, basically the page submits to itself using ServerVairables("CONTENT_LENGTH"
. I would like to pull back the 'id' field so after this codes adds the info to the record i can then show the user the id for that record. (it will be a fault reference number for there own information and process tracking.)Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset"
objRS.ActiveConnection = connHelpdesk_STRING
objRS.Source = "SELECT * FROM dbo.Faults"
objRS.CursorType = adOpenKeyset
objRS.LockType = adLockPessimistic
objRS.Open
objRS.AddNew
objRS.Fields("field"
= Trim(Request.Form("field")objRS.Update
objRS.Close()
Please explain in a bit of detail.
Thanx
I would like to suggest to you a better way to handle this, but I'll need some more information before I can do so.
Please tell me what the table, Faults, looks like that you are INSERTing into... what fields there are and such. And tell me what you will want to INSERT.
There should be one identity field, judging from your post up there, so let me know what that is, too.
Please tell me what the table, Faults, looks like that you are INSERTing into... what fields there are and such. And tell me what you will want to INSERT.
There should be one identity field, judging from your post up there, so let me know what that is, too.
- Thread starter
- #5
Ok...
I have a page with 7 input boxes,
Username, userphonenumber, location, fault, category, operator, sla
These then go into a MS Sql database table with field names the same. There is a field called 'faultref' this is an auto identity feild that is created with each new record.
Once the data is inserted I would like that the FaultRef value to be shown on the same page that inserts the data.
is that enough?
I have a page with 7 input boxes,
Username, userphonenumber, location, fault, category, operator, sla
These then go into a MS Sql database table with field names the same. There is a field called 'faultref' this is an auto identity feild that is created with each new record.
Once the data is inserted I would like that the FaultRef value to be shown on the same page that inserts the data.
is that enough?
Consider this:
In order to get the ID, you'll need some sort of transaction to be sure that no one else is adding a record at the same time, because in the webpage, if you ask for @@inserted, and then someone has made an entry in the split second since you added yours, then BAM! You've got yourself a dirty read.
Instead, consider using a Stored Procedure. They are a much better way to go.
I have made my best guesses on your datatypes. You'll need to modify them to match your table setup.
--------------------------------------------------
--------------------------------------------------
And then to use it on your web page, try this:
And then you can use your local variable, newID (which holds your newly created identity), in whatever way you wish.
Just as an aside, let me say this:
There's a big misconception out there that a recordset is the only way to interract with a database. This is so very wrong. In fact, it could stand to reason (with a few exceptions, granted) that a recordset should only be used when you're grabbing some data that you want to display to a user. You may want to use it from time to time (albeit rarely) to do updates, using their built in record locking mechanisms to prevent those dirty reads.
If you want to do a simple insert (w/out needing the ID), then do it like this:
dim sql
sql = "INSERT INTO someTable (col1, col2) VALUES (val1, val2)"
connection.execute sql
Likewise, if you just need to update something:
dim sql
sql = "UPDATE someTable SET col1 = val1, col2 = val2 WHERE someColumn = someValue"
connection.execute sql
Even if you need to delete something, you could (and should) use the same type of method. It's so much cleaner and keeps all those objects from floating around in your memory, slowing your applications down.
Anyway, hope this helped you more than it confused you. If you have any problems w/ the method, post back and we'll hash them out.
Paul Prewett
In order to get the ID, you'll need some sort of transaction to be sure that no one else is adding a record at the same time, because in the webpage, if you ask for @@inserted, and then someone has made an entry in the split second since you added yours, then BAM! You've got yourself a dirty read.
Instead, consider using a Stored Procedure. They are a much better way to go.
I have made my best guesses on your datatypes. You'll need to modify them to match your table setup.
--------------------------------------------------
Code:
CREATE PROCEDURE [spAddNewFaultsRecord]
@userName varchar(150),
@userPhoneNumber varchar(15),
@location varchar(50),
@fault int,
@category smallint,
@operator int,
@sla int,
@newID int = 0 OUTPUT
AS
/*START A NEW TRANSACTION*/
BEGIN trans insertNewField
/*INSERT THE NEW RECORD*/
INSERT INTO Faults (userName,userPhoneNumber,location,fault,category,operator,sla) VALUES (@userName,@userPhoneNumber,@location,@fault,@category,@operator,@sla)
/*GRAB YOUR NEWLY CREATED IDENTITY FIELD*/
SELECT @newID = @@identity FROM inserted
/*COMMIT THE TRANSACTION*/
COMMIT trans insertNewField--------------------------------------------------
And then to use it on your web page, try this:
Code:
dim comObj, userName, userPhoneNumber, location, fault, category, operator, sla, newID
userName = trim(request.form("userName"))
userPhoneNumber = trim(request.form("userPhoneNumber"))
location = trim(request.form("location"))
fault = trim(request.form("fault"))
category = trim(request.form("category"))
operator = trim(request.form("operator"))
sla = trim(request.form("sla"))
'CREATE THE NEW COMMAND OBJECT, WHICH WILL USE YOUR STORED PROCEDURE (SPROC)
set comObj = server.createObject("ADODB.Command")
'ASSIGN THE ACTIVE CONNECTION
comObj.activeConnection = connHelpdesk_STRING
'TELL IT TO USE A SPROC
comObj.commandType = 4 'adCmdStoredProcedure
'TELL IT WHICH SPROC TO USE
comObj.commandText = "spAddNewFaultsRecord"
'ASSIGN YOUR INPUT PARAMETERS
comObj.parameters("@userName").value = userName
comObj.parameters("@userPhoneNumber").value = userPhoneNumber
comObj.parameters("@location").value = location
comObj.parameters("@fault").value = clng(fault)
comObj.parameters("@category").value = clng(category)
comObj.parameters("@operator").value = clng(operator)
comObj.parameters("@sla").value = clng(sla)
'EXECUTE
comObj.execute
'GRAB YOUR OUTPUT PARAMETER
newID = comObj.parameters("@newID").valueAnd then you can use your local variable, newID (which holds your newly created identity), in whatever way you wish.
Just as an aside, let me say this:
There's a big misconception out there that a recordset is the only way to interract with a database. This is so very wrong. In fact, it could stand to reason (with a few exceptions, granted) that a recordset should only be used when you're grabbing some data that you want to display to a user. You may want to use it from time to time (albeit rarely) to do updates, using their built in record locking mechanisms to prevent those dirty reads.
If you want to do a simple insert (w/out needing the ID), then do it like this:
dim sql
sql = "INSERT INTO someTable (col1, col2) VALUES (val1, val2)"
connection.execute sql
Likewise, if you just need to update something:
dim sql
sql = "UPDATE someTable SET col1 = val1, col2 = val2 WHERE someColumn = someValue"
connection.execute sql
Even if you need to delete something, you could (and should) use the same type of method. It's so much cleaner and keeps all those objects from floating around in your memory, slowing your applications down.
Anyway, hope this helped you more than it confused you. If you have any problems w/ the method, post back and we'll hash them out.
Paul Prewett
- Thread starter
- #7
Added the SP but get this error.
Microsoft OLE DB Provider for ODBC Drivers error '80040e37'
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'inserted'
Here is the SP.
--------------------------------------------
CREATE PROCEDURE [spAddNewFaultsRecord]
@userName varchar(150),
@userPhoneNumber varchar(15),
@location varchar(50),
@fault varchar(500),
@category varchar(50),
@operator varchar(50),
@sla varchar(50),
@slatarget varchar(50),
@dateallocated varchar(50),
@timeallocated varchar(50),
@newID int = 0 OUTPUT
AS
/*START A NEW TRANSACTION*/
BEGIN transaction insertNewField
/*INSERT THE NEW RECORD*/
INSERT INTO Faults (userName,userPhoneNumber,location,fault,category,operator,sla,slatarget,dateallocated,timeallocated) VALUES (@userName,@userPhoneNumber,@location,@fault,@category,@operator,@sla,@slatarget,@dateallocated,@timeallocated)
/*GRAB YOUR NEWLY CREATED IDENTITY FIELD*/
SELECT @newID = @@identity FROM inserted
/*COMMIT THE TRANSACTION*/
COMMIT transaction insertNewField
--------------------------------------
And here is the HTML
--------------------------------------
Dim comObj, userName, userPhoneNumber, location, fault, category, operator, sla, newID
userName = trim(request.form("userName")
userPhoneNumber = trim(request.form("userPhoneNumber")
location = trim(request.form("location")
fault = trim(request.form("fault")
category = trim(request.form("category")
operator = trim(request.form("operator")
sla = trim(request.form("sla")
'CREATE THE NEW COMMAND OBJECT, WHICH WILL USE YOUR STORED PROCEDURE (SPROC)
set comObj = server.createObject("ADODB.Command"
'ASSIGN THE ACTIVE CONNECTION
comObj.activeConnection = MM_connHelpdesk_STRING
'TELL IT TO USE A SPROC
comObj.commandType = 4
'TELL IT WHICH SPROC TO USE
comObj.commandText = "spAddNewFaultsRecord"
'ASSIGN YOUR INPUT PARAMETERS
comObj.parameters("@userName".value = userName
comObj.parameters("@userPhoneNumber".value = userPhoneNumber
comObj.parameters("@location".value = location
comObj.parameters("@fault".value = fault
comObj.parameters("@category".value = category
comObj.parameters("@operator".value = operator
comObj.parameters("@sla".value = sla
comObj.parameters("@slatarget".value = sla
comObj.parameters("@dateallocated".value = sla
comObj.parameters("@timeallocated".value = sla
'EXECUTE
comObj.execute
'GRAB YOUR OUTPUT PARAMETER
newID = comObj.parameters("@newID".value
response.write newID
--------------------------------------
Any ideas why this error is happening?
Microsoft OLE DB Provider for ODBC Drivers error '80040e37'
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'inserted'
Here is the SP.
--------------------------------------------
CREATE PROCEDURE [spAddNewFaultsRecord]
@userName varchar(150),
@userPhoneNumber varchar(15),
@location varchar(50),
@fault varchar(500),
@category varchar(50),
@operator varchar(50),
@sla varchar(50),
@slatarget varchar(50),
@dateallocated varchar(50),
@timeallocated varchar(50),
@newID int = 0 OUTPUT
AS
/*START A NEW TRANSACTION*/
BEGIN transaction insertNewField
/*INSERT THE NEW RECORD*/
INSERT INTO Faults (userName,userPhoneNumber,location,fault,category,operator,sla,slatarget,dateallocated,timeallocated) VALUES (@userName,@userPhoneNumber,@location,@fault,@category,@operator,@sla,@slatarget,@dateallocated,@timeallocated)
/*GRAB YOUR NEWLY CREATED IDENTITY FIELD*/
SELECT @newID = @@identity FROM inserted
/*COMMIT THE TRANSACTION*/
COMMIT transaction insertNewField
--------------------------------------
And here is the HTML
--------------------------------------
Dim comObj, userName, userPhoneNumber, location, fault, category, operator, sla, newID
userName = trim(request.form("userName"
)userPhoneNumber = trim(request.form("userPhoneNumber"
)location = trim(request.form("location"
)fault = trim(request.form("fault"
)category = trim(request.form("category"
)operator = trim(request.form("operator"
)sla = trim(request.form("sla"
)'CREATE THE NEW COMMAND OBJECT, WHICH WILL USE YOUR STORED PROCEDURE (SPROC)
set comObj = server.createObject("ADODB.Command"
'ASSIGN THE ACTIVE CONNECTION
comObj.activeConnection = MM_connHelpdesk_STRING
'TELL IT TO USE A SPROC
comObj.commandType = 4
'TELL IT WHICH SPROC TO USE
comObj.commandText = "spAddNewFaultsRecord"
'ASSIGN YOUR INPUT PARAMETERS
comObj.parameters("@userName"
.value = userNamecomObj.parameters("@userPhoneNumber"
.value = userPhoneNumbercomObj.parameters("@location"
.value = locationcomObj.parameters("@fault"
.value = faultcomObj.parameters("@category"
.value = categorycomObj.parameters("@operator"
.value = operatorcomObj.parameters("@sla"
.value = slacomObj.parameters("@slatarget"
.value = slacomObj.parameters("@dateallocated"
.value = slacomObj.parameters("@timeallocated"
.value = sla'EXECUTE
comObj.execute
'GRAB YOUR OUTPUT PARAMETER
newID = comObj.parameters("@newID"
.valueresponse.write newID
--------------------------------------
Any ideas why this error is happening?
ChainsawJoe
Programmer
it's because you're
into table
, but attempting to get the identity from table
.
change the lines
to
should sort it.
good luck!
Code:
INSERTing
Code:
Faults
Code:
insertedchange the lines
Code:
/*GRAB YOUR NEWLY CREATED IDENTITY FIELD*/
SELECT @newID = @@identity FROM inserted
Code:
/*GRAB YOUR NEWLY CREATED IDENTITY FIELD*/
SELECT @newID = @@identity FROM Faultsshould sort it.
good luck!
- Thread starter
- #9
Surely --
Just need to replace your SQL statement in your SPROC up there w/ an update statement.
You could even give all of your input variables default values and check the values of those in order to figure out which variables were sent to the SPROC that need to be updated:
CREATE PROCEDURE [spUpdateFaultsRecord]
@userName varchar(150),
@userPhoneNumber varchar(15) = '',
@location varchar(50) = '',
@fault varchar(500) = '',
@category varchar(50) = '',
@operator varchar(50) = '',
@sla varchar(50) = '',
@slatarget varchar(50) = '',
@dateallocated varchar(50) = '',
@timeallocated varchar(50) = ''
AS
/*START A NEW TRANSACTION*/
BEGIN transaction updateTable
IF @userPhoneNumber <> ''
UPDATE Faults SET userphonenumber = @userPhoneNumber WHERE userName = @userName
IF @location <> ''
UPDATE Faults SET location = @location WHERE userName = @userName
/*And so on, and so on -- one for each of your parameters*/
/*COMMIT THE TRANSACTION*/
COMMIT transaction updateTable
So notice the default vales I gave your parameters up there. That makes them optional. If you don't send values, then they will retain the default value of ''. Then, you check on that to see what was sent to the SPROC and update any field that has a value based on the one and only required parameter, @userName.
The code for the SPROC could be made more elegant, building up a single dynamic SQL statement, but this will work just fine for what you need it for.
paul
Just need to replace your SQL statement in your SPROC up there w/ an update statement.
You could even give all of your input variables default values and check the values of those in order to figure out which variables were sent to the SPROC that need to be updated:
CREATE PROCEDURE [spUpdateFaultsRecord]
@userName varchar(150),
@userPhoneNumber varchar(15) = '',
@location varchar(50) = '',
@fault varchar(500) = '',
@category varchar(50) = '',
@operator varchar(50) = '',
@sla varchar(50) = '',
@slatarget varchar(50) = '',
@dateallocated varchar(50) = '',
@timeallocated varchar(50) = ''
AS
/*START A NEW TRANSACTION*/
BEGIN transaction updateTable
IF @userPhoneNumber <> ''
UPDATE Faults SET userphonenumber = @userPhoneNumber WHERE userName = @userName
IF @location <> ''
UPDATE Faults SET location = @location WHERE userName = @userName
/*And so on, and so on -- one for each of your parameters*/
/*COMMIT THE TRANSACTION*/
COMMIT transaction updateTable
So notice the default vales I gave your parameters up there. That makes them optional. If you don't send values, then they will retain the default value of ''. Then, you check on that to see what was sent to the SPROC and update any field that has a value based on the one and only required parameter, @userName.
The code for the SPROC could be made more elegant, building up a single dynamic SQL statement, but this will work just fine for what you need it for.
paul
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question