Norbi
Posts: 8
|
| Posted: 08/27/2007, 6:56 AM |
|
Hi,
Could you please tell me how to achieve the following really simple functionality in .NET
1. Insert the record to the database (via record form)
2. After record was inserted - redirect the form end continue
3. DB is MSSQL
In PHP it would be really simple, in short something like this
$db->query("select @@identity as identity");
$db->next_record();
$id = $db->f("identity");
$Redirect = "page.php?param=" . $id;
Based on some examples I tried to approach it in a different ways in .Net, eg.
//Record kontrahent Event AfterInsert. Action Custom Code @62-2A29BDB7
// -------------------------
DataAccessObject NewDao = Settings.MSDataAccessObject;
int i_id_kontrahent;
SqlCommand Select = new SqlCommand("SELECT @@IDENTITY as i_id_kontrahent",NewDao);
DataRowCollection newDr = Select.Execute().Tables[0].Rows;
i_id_kontrahent = newDr[0]["i_id_kontrahent"].ToInt();
RedirectUrl = "kontrahent_edit.aspx?i_id=" + i_id_kontrahent;
but I have problems with casting the data and got errors.
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: CS0117: 'object' does not contain a definition for 'ToInt'
Source Error:
Line 301: SqlCommand Select = new SqlCommand("SELECT @@IDENTITY as i_id_kontrahent",NewDao);
Line 302: DataRowCollection newDr = Select.Execute().Tables[0].Rows;
Line 303: i_id_kontrahent = newDr[0]["i_id_kontrahent"].ToInt();
Line 304: RedirectUrl = "kontrahent_edit.aspx?i_id=" + i_id_kontrahent;
Line 305:
Can somebody advise how to tackle this problem
Thanks in advance
Norbert
|
 |
 |
ttflyer
Posts: 11
|
| Posted: 09/17/2007, 9:05 AM |
|
Had some problems with this as well. This worked for me although there is some danger that the value is not the one inserted since it is not possible to pull the value from the scope of the current request when using CodeCharge's built-in functions.
' After Insert Retrieve the Identity Value from SQL and store in Variable
Dim vloID AS Integer = Settings.connSIMSDataAccessObject.ExecuteScalar("SELECT IDENT_CURRENT('TableName')") 'works
'convert variable to URL Querystring parameter
Dim params As New LinkParameterCollection()
params.Add("rID",vloID)
'redirect to confirmation page
HttpContext.Current.Response.Redirect("page_cfm.aspx" + params.ToString("GET",""))
|
|
|
|
Norbi
Posts: 8
|
| Posted: 09/23/2007, 5:12 AM |
|
I contacted CCS to get some help, here is their answer ... might be useful for somebody 
Quote :
Norbert,
I contacted developers about your question and they reported that due to some features in .NET code generating patterns it is not possible to retrieve last inserted ID executing a query that returns @@identity or identity_scope().
But you can try a solution provided below. Please note that this solution requires modifications in CodeCharge.Data.<provider_name>.cs file and so this file is not overwritten while project/page generation. Please regenerate it when moving the project to new CCS version: open the CodeCharge.Data.<provider_name>.cs file in CCS code editor, select all code (Ctrl+A) and delete it. Then regenerate the project and after that add custom changes back.
Solution description:
1. open the CodeCharge.Data.OleDb.cs file if you`re using OLE DB Connection to SQL Server or CodeCharge.Data.ODBC.cs file if you`re using DSN connection;
2. find the protected override int ExecuteNonQueryImpl() function
3. modify it:
...
result=Convert.ToInt32(command.ExecuteNonQuery());
int lastID = Convert.ToInt32(ExecuteScalar("SELECT @@IDENTITY"));
System.Web.HttpContext.Current.Session[" lastId"] = lastID;
if(!flag)
...
4. create After Execute Insert or After Insert event on Record form and retrieve the session value there, e.g.
System.Web.HttpContext.Current.Response.Write(System.Web.HttpContext.Current.Session[" lastId"]);
Best regards
Norbert
|
|
|
|
Tuong Do
|
| Posted: 09/23/2007, 4:23 PM |
|
<Quote>
I contacted developers about your question and they reported that due to
some
features in .NET code generating patterns it is not possible to retrieve
last
inserted ID executing a query that returns @@identity or identity_scope().
</Quote>
I can do this in .NET with DB2, the key thing is you have to use the same
connection that was used to insert the record to retrieve the last Identity
ID
In the insert case, It is "Insert.Dao"
In the AfterExecuteInsert
<Code>
If IsNothing(E) Then
Dim LastInsertID As Integer
LastInsertID = CType((New IntegerField("",
Insert.Dao.ExecuteScalar("SELECT " & "VALUE(Identity_Val_Local(),0)" & "
FROM " & "SysIbm.SysDummy1"))).Value, Int64)
End If
</Code>
NOTE:
In the MS SQL case
you would use
@@IDENTITY rather than Identity_Val_Local()
"Norbi" <Norbi@forum.codecharge> wrote in message
news:846d2d7f5e7dbc@news.codecharge.com...
> Hi,
>
> Could you please tell me how to achieve the following really simple
> functionality in .NET
>
> 1. Insert the record to the database (via record form)
> 2. After record was inserted - redirect the form end continue
> 3. DB is MSSQL
>
>
> In PHP it would be really simple, in short something like this
>
> $db->query("select @@identity as identity");
> $db->next_record();
> $id = $db->f("identity");
> $Redirect = "page.php?param=" . $id;
>
>
> Based on some examples I tried to approach it in a different ways in .Net,
> eg.
>
> //Record kontrahent Event AfterInsert. Action Custom Code @62-2A29BDB7
> // -------------------------
>
> DataAccessObject NewDao = Settings.MSDataAccessObject;
> int i_id_kontrahent;
>
> SqlCommand Select = new SqlCommand("SELECT @@IDENTITY as
> i_id_kontrahent",NewDao);
> DataRowCollection newDr = Select.Execute().Tables[0].Rows;
> i_id_kontrahent = newDr[0]["i_id_kontrahent"].ToInt();
> RedirectUrl = "kontrahent_edit.aspx?i_id=" + i_id_kontrahent;
>
>
> but I have problems with casting the data and got errors.
>
>
> Description: An error occurred during the compilation of a resource
> required to
> service this request. Please review the following specific error details
> and
> modify your source code appropriately.
>
> Compiler Error Message: CS0117: 'object' does not contain a definition for
> 'ToInt'
>
> Source Error:
>
>
>
> Line 301: SqlCommand Select = new SqlCommand("SELECT @@IDENTITY as
> i_id_kontrahent",NewDao);
> Line 302: DataRowCollection newDr = Select.Execute().Tables[0].Rows;
> Line 303: i_id_kontrahent = newDr[0]["i_id_kontrahent"].ToInt();
> Line 304: RedirectUrl = "kontrahent_edit.aspx?i_id=" + i_id_kontrahent;
> Line 305:
>
>
>
>
> Can somebody advise how to tackle this problem
>
> Thanks in advance
>
> Norbert
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.yessoftware.com/
>
|
|
|
|
hiraldesai
Posts: 38
|
| Posted: 01/07/2008, 5:31 PM |
|
I have written a code like this.
If IsNothing(E) Then
Dim LastInsertID As Integer
LastInsertID = CType((New IntegerField("", Insert.Dao.ExecuteScalar("SELECT @@Identity FROM Individuals"))).Value, Int64)
Response.Write("<br> LastInsertID : " & LastInsertID.ToString())
End If
This seems to return 0 all the time. Am I doing something wrong?
_________________
-------
Hiral |
|
|
|
Tuong Do
|
| Posted: 01/10/2008, 2:34 PM |
|
Try this
Dim LastInsertID As Integer
LastInsertID = CType((New IntegerField("", Insert.Dao.ExecuteScalar("SELECT
@@Identity "))).Value, Int64)
That is omitting the from clause
"hiraldesai" <hiraldesai@forum.codecharge> wrote in message
news:84782d26cb4cf5@news.codecharge.com...
>I have written a code like this.
>
>
>
> If IsNothing(E) Then
>
> Dim LastInsertID As Integer
> LastInsertID = CType((New IntegerField("",
> Insert.Dao.ExecuteScalar("SELECT
> @@Identity FROM Individuals"))).Value, Int64)
>
> Response.Write("<br> LastInsertID : " & LastInsertID.ToString())
>
> End If
>
>
> This seems to return 0 all the time. Am I doing something wrong?
> _________________
> -------
> Hiral
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.yessoftware.com/
>
|
|
|
|
hiraldesai
Posts: 38
|
| Posted: 01/14/2008, 5:15 PM |
|
Hi Tuong,
Thanks. I tried it, it doesn't seem to work and returns 0. I am not an SQL Server expert, but the only problem I can see here is the trigger I have set on the table I am inserting the record to. Could that be a problem you think? If yes, is there a fix to that?
Also, for a short-term fix, I am using Max(ID_Field) from the table instead and it's working alright. That is what Yes Software has done in their multi-step registration example. I am not quite sure how reliable that is. 
LastInsertID = CType((New IntegerField("", Insert.Dao.ExecuteScalar("SELECT MAX(CRMID) FROM Individuals"))).Value, Int64)
Any comments/suggestions from anyone who has encountered this problem before are welcome.
Regards,
_________________
-------
Hiral |
|
|
|
Tuong Do
|
| Posted: 01/16/2008, 2:29 PM |
|
Hi Hiraldesai,
Yes If your trigger try to insert another record then it will effect the
identity, The Max ID won't be reliable as if two person try to insert at the
same time.
Method one:
Remove the trigger and run the SQL (trigger ) after you have obtain your
LastInsertID
Method two:
Use the max ID method together with a where clause
eg Name or generated timetamp
ie
Dim Namevariable = item.Name.value
LastInsertID = CType((New IntegerField("", Insert.Dao.ExecuteScalar("SELECT
MAX(CRMID) FROM Individuals WHERE Name='" & Namevariable &
" ))).Value, Int64)
"hiraldesai" <hiraldesai@forum.codecharge> wrote in message
news:8478c0921aa05c@news.codecharge.com...
> Hi Tuong,
>
> Thanks. I tried it, it doesn't seem to work and returns 0. I am not an SQL
> Server expert, but the only problem I can see here is the trigger I have
> set on
> the table I am inserting the record to. Could that be a problem you think?
> If
> yes, is there a fix to that?
>
> Also, for a short-term fix, I am using Max(ID_Field) from the table
> instead and
> it's working alright. That is what Yes Software has done in their
> multi-step
> registration example. I am not quite sure how reliable that is.
>
> LastInsertID = CType((New IntegerField("",
> Insert.Dao.ExecuteScalar("SELECT
> MAX(CRMID) FROM Individuals"))).Value, Int64)
>
> Any comments/suggestions from anyone who has encountered this problem
> before
> are welcome.
>
> Regards,
> _________________
> -------
> Hiral
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.yessoftware.com/
>
|
|
|
|
|
