|
Method 1:<br />
myProc <br />
@outparam INT OUTPUT<br />
AS<br />
[insert sql here]<br />
SELECT @outparam == @@IDENTITY
Method 2:<br />
<br />
myProc<br />
<br />
[insert sql here]<br />
SELECT @@IDENTITY
What would be the prefered method? Is there a best practice? I know method 2 is faster and I'm trying to educate myself if there is a better way.
|
|
|
|
|
I don't think it really matters, depending on what db library your clients are using the second would probably be easier to code on the client side, no parameters to define.
|
|
|
|
|
Those are pretty much your options if you choose to have your Id's autoincremented by the database.
The alternative is to generate ids in your code, and then assign the id before saving. For inserts, this is much faster. It also makes writing code easier, because you can assign foreign keys before ever saving the "parent".
There are a few techniques for doing this, but all of them require using a bigger number than an INT.
my blog
|
|
|
|
|
I normally do this:
CREATE PROCEDURE dbo.data_ins
@Ident bigint OUTPUT = NULL,
@Data1 varchar(10),
@Yadda int
AS
-- Set the OUTPUT parameter
-- (Note that SCOPE_IDENTITY() is trigger-safe,
-- whereas @@identity isn't.)
SET @Ident = SCOPE_IDENTITY()
-- Select the record just inserted
-- can be used to retrieve new identity and check
-- defaults, etc.
SELECT * FROM myTable WHERE ident = @Ident
RETURN @Ident
GO
This gives the developer using the procedure three separate options for retrieving the new identity.
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
A bit over kill if you ask me.
RETURN @Ident should fail. RETURN only supports INT not BIGINT.
|
|
|
|
|
It will only fail if the identity ever reaches a point where it's actually too large to be CAST as an int.
If you already know how you want to do it, then why don't you just do it that way instead of asking for advice you aren't going to even consider?
We have 8 developers here and about 30 separate database applications that in some cases share stored procs. What you think of as overkill, some think of as tyrannically restrictive.
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
Grimolfr wrote:
It will only fail if the identity ever reaches a point where it's actually too large to be CAST as an int.
Since the identity field is defined as BIGINT I think there is a strong possibility that it may reach a point where it is larger than an INT. If you don't expect it to reach this point, then don't make it a BIGINT. Any code that uses this proc will either need to be rewritten when identity does become larger, or written up front to handle the exception. But why design an app with know flaw or code for a preventable error?
Grimolfr wrote:
If you already know how you want to do it, then why don't you just do it that way instead of asking for advice you aren't going to even consider?
I did not ask for opinions or advice. In any case, giving all possible answers is neither an opinion nor advice.
|
|
|
|
|
In the datagrid.aspx, there is a Edit Hyperlink. When you click on the current page, it will go to customer.aspx.
Whatever is in the datagrid - "data binded", will be bind on the (i.e, txtPCID.text, txtName.text, txtPrice.text, txtDescription.text).
Is there anyway to do that?
Can I bind more than 1 field in a HyperlinkColumn?
If so, how?
|
|
|
|
|
Although I'm not exactly sure what you want,
you could always use a template column and put whatever controls you want to appear in the column there.
|
|
|
|
|
Using a template column, as suggested, allows you to control your binding pretty precisely. So you can have as many asp controls inside a single column as you want, with appropriate databinding expressions for each.
Keep in mind, that you can also have a databinding expression that calls a function in your code behind file.
For example, you could have this function in your code:
Public Function GetAutoAssignIndex(ByVal AutoAssign As Boolean) As Integer
Dim index As Integer = 0
If AutoAssign = True Then
index = 1
End If
Return index
End Function
Your databind might look similar to this:
<asp:dropdownlist id="ddlEditAutoAssign" cssclass="Normal" selectedindex="<%# GetAutoAssignIndex(DataBinder.eval(Container.DataItem, "AutoAssign")) %>" width="90%" runat="server">
This example is for a dropdown containing objects with an "AutoAssign" property defined.
|
|
|
|
|
I would like to start working with OODBs and OQL, are there any suggestions on where to get started? Also can you use ADO.NET to connect to an Object Oriented Database, or are there other methods? Also is there any free Object Oriented Databases available like mySQL for relational databases?
Thanks in advance,
Sef
|
|
|
|
|
I can't guide you for OODB And OQL. Cause i am not in a position to say something about that. About Ado.net, In Asp.Net 2.0 microsoft included ObjectSpace (technology to access table in the form of objects) for Object Oriented Databases. Beta version of 2.0 is avilable. I am not sure about full version of Ado.Net 2.0
Sreejith Nair
[ My Articles ]
|
|
|
|
|
Hi,
I have some strange problems with the update of a dataadapter.
First, I read a row in a table, through a dataset :
da = New SqlDataAdapter(sql, conn)<br />
ds = New DataSet<br />
da.Fill(ds, "MyTable")<br />
da.SelectCommand.CommandText = "select * from address"<br />
Dim sqlBuilder As SqlCommandBuilder = New SqlCommandBuilder(da)<br />
da.InsertCommand = sqlBuilder.GetInsertCommand()<br />
da.DeleteCommand = sqlBuilder.GetDeleteCommand()<br />
da.UpdateCommand = sqlBuilder.GetUpdateCommand()<br />
<br />
ta = ds.Tables("MyTable")<br />
<br />
pka(0) = ta.Columns("address_id")<br />
ta.PrimaryKey = pka<br />
<br />
dim dr As DataRow<br />
<br />
dr = ta.Rows.Find(1) 'get the address with id 1
Then I update a field :
dr("country_id") = system.dbnull.value
and now I commit the change :
da.update(ds)
But changes are not reflected in the database....
The update command doesn't work with this update. If country_id value is not null, it works...
I precise that country_id is nullable in the DB.
Thanks for any help.
Best regards.
Arthur
|
|
|
|
|
See some senario we can't predict that user will give correct, valid data. So i recommand you to define an update,delete command by yourself and execute the command. And assign this defined queary to respective delete,update command of your data adapter
Sreejith Nair
[ My Articles ]
|
|
|
|
|
I dont know whether my suggestion is right. first check the entries are null or not. If null update with ''. ie blank. does this answer the question. sreejiths answer is a good way but working with insert,delete and update query in data adapter is not that easy.
(Behind the truth)
|
|
|
|
|
I have a chat application. It requires that all information of users, rooms and messages have to stored in database. However, users edit their messages with richtextbox control, so I want to save messages with their format. How can I do? Somebody said that I should save them using XML format or HTML script, but I never do it. Please help me!!!
Thanks a lot.
kunkun
|
|
|
|
|
Have you thought about storing the RTF from the Rich Text Box in an image field in the database?
Store your favourite bookmarks online: my-faves.co.uk
|
|
|
|
|
Thanks a lot, Davey
I've tried it and I find it good.
kunkun
|
|
|
|
|
Hi !
(Sorry for my bad english  )
I use datatable and i would search if a row exist, i've tried with the Select method but in some rows contains {null} values and i c'ant find how i'll passed this values in the Select string
<br />
#I Work in VB.NET<br />
<br />
dim filter as String = "COL1 = 1 AND COL2 = null"<br />
If ldataset.Tables("Table1").Select(filter).length Then<br />
....:doh:<br />
End If<br />
Thanks in advance 
|
|
|
|
|
Try This
<br />
filter = "COL1 = 1 AND IsNull(COL2,-1)=-1" (if COL2 is numeric)<br />
<br />
filter = "COL1 = 1 AND IsNull(COL2,'')=''" (if COL2 is string)<br />
Free your mind...
|
|
|
|
|
Shouldn't it be "COL2 IS NULL" ?
|
|
|
|
|
thanks to everyone....
it's so easy .... 
|
|
|
|
|
Hi,
I am new to OLE DB. I created a simple stored procedure that insert
given data to a table. when I tried to call Open function of the wizard
generated ole db class, it fails with a HRESULT value : [-2147217887]. (the
error lookup tool returns 'No such Message' for this value). While debuging
i found, the Execute function is returning this value.
My code is given below. Is it correct ?
CdbospTestTable1 m_db;
m_db.m_test1 = 111;
m_db.m_test2 = 222;
HRESULT hr = m_db.Open();
if(FAILED(hr))
AfxMessageBox("Error : Open");
When I created OLE DB consumer class, selecting table, then the insertion
succeeded.
The creation of the class was :
Insert => New Atl Object .
Data Access : Consumer
OLE DB provider : OLE DB provider for Sql Server
Type property : Command
Support : change, insert and delete
Then selected the Stored Procedure name.
Thanks and Regards
Sudheer
|
|
|
|
|
Suppose if you are discussing about error message then you need to include what that message is. Without that we can't figurout that. what is the reason. Please try to include the error message.
Sreejith Nair
[ My Articles ]
|
|
|
|
|
sreejith ss nair wrote:
if you are discussing about error message then you need to include what that message is. Without that we can't figurout that. what is the reason. Please try to include the error message.
He did include the error message: "it fails with a HRESULT value : [-2147217887]. (the error lookup tool returns 'No such Message' for this value)."
Do you want to know more?
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
