Showing posts with label textbox. Show all posts
Showing posts with label textbox. Show all posts

Tuesday, March 27, 2012

@@identity

i have found loads of topics on this but have yet to find one that gives the answer i need.

I want to display the last insert id into a label/textbox after the INSERT function

how do i do this?

A question back at you -

Which database are you using?

|||

if the value you are using as an id is an idetity column and your database is sql server 2000 or 2005 then you can do something like this to get the last inserted value

strSQL = "";
strSQL += " Set Nocount on ";
strSQL += " Insert TableX(...) VALUES (...) ";
strSQL += " select IdentityInsert = IDENT_CURRENT( 'TableX' ) ";
strSQL += " set nocount off ";

hope this helps

|||

In sqlserver you can also do it with stored procedure:


CREATE procedure [dbo].[textbookop_insert])
as
begin
INSERT INTO [cosolu].[dbo].[cosolu_textbookop]
([textbookop_textbookid])
VALUES
(@.textbookop_textbookid)
return @.@.identity
end;
GO

You can insert the value using the following code:

 SqlConnection sqlconn = DB.CreateConn(); SqlCommand sqlcmd =new SqlCommand("textbookop_insert", sqlconn); sqlcmd.CommandType = CommandType.StoredProcedure; SqlParameter sqlpar = sqlcmd.Parameters.Add("ReturnValue", SqlDbType.Int); sqlpar.Direction = ParameterDirection.ReturnValue; sqlcmd.Parameters.AddWithValue("@.textbookop_textbookid", textbookop_textbookid); sqlconn.Open(); sqlcmd.ExecuteNonQuery();int camid = Convert.ToInt32(sqlcmd.Parameters["ReturnValue"].Value); sqlconn.Close();
Hope this helps.|||i am using an sqldatasource, dont know if that changes anything. I dont know how the syntax works when the connection is built into an object|||im using sql server 2005|||

mike7510uk:

i am using an sqldatasource, dont know if that changes anything. I dont know how the syntax works when the connection is built into an object

If the connection is built into the object, you have a TableAdapter that is connected to the table you are using. This TableAdapter contains methods that pull data from the table, like Fill() and GetData(). You can easily add another method to this TableAdapter to get the last value that was generated for the identity column of its table.

You can doubleclick on the .xsd file for the sqlDataSource, which opens the DataSet Designer. Go to the table you want to work with (lets call it myTable) and right click on any existing method in there. From this menu, choose Add Query.

This will give you a handy-dandy TableAdapter Query Configuration Wizard. In this wizard:

1. When it says "Choose a Query Type", choose "SELECT which returns a single value".

2. When it says "Specify a SQL SELECT statement, type in

SELECTIDENT_CURRENT ('myTable')FROM myTable

3. When it says "Choose a Function Name", give it a name like "GetLastIdentityValue".

Voila, you're done. So now, in your page, lets say you want to grab the ID of the last record entered and disply it in a label (Label1).

int theID = (int)myTableAdapter.GetLastIdentityValue();Label1.Text = theID.ToString();

Notice there is a cast in the first line, because the value returned by IDENT_CURRENT is of type nullable Decimal.

Hope that helps.

|||

After you do an insert you can return the key for the insert like:

INSERT INTO [dbo].[table] (name, phone)VALUES ("Denny", "555-1234")Return@.@.IDENTITY

Also be sure to read on the differences of SCOPE_IDENTITY() and @.@.IDENTITY - to be sure which one you should use to return the correct id.

|||so after i set Return @.@.IDENTITY how would that display it into a label / textbox?|||

Lee Dumond:

mike7510uk:

i am using an sqldatasource, dont know if that changes anything. I dont know how the syntax works when the connection is built into an object

If the connection is built into the object, you have a TableAdapter that is connected to the table you are using. This TableAdapter contains methods that pull data from the table, like Fill() and GetData(). You can easily add another method to this TableAdapter to get the last value that was generated for the identity column of its table.

You can doubleclick on the .xsd file for the sqlDataSource, which opens the DataSet Designer. Go to the table you want to work with (lets call it myTable) and right click on any existing method in there. From this menu, choose Add Query.

This will give you a handy-dandy TableAdapter Query Configuration Wizard. In this wizard:

1. When it says "Choose a Query Type", choose "SELECT which returns a single value".

2. When it says "Specify a SQL SELECT statement, type in

SELECTIDENT_CURRENT ('myTable')FROM myTable

3. When it says "Choose a Function Name", give it a name like "GetLastIdentityValue".

Voila, you're done. So now, in your page, lets say you want to grab the ID of the last record entered and disply it in a label (Label1).

int theID = (int)myTableAdapter.GetLastIdentityValue();Label1.Text = theID.ToString();

Notice there is a cast in the first line, because the value returned by IDENT_CURRENT is of type nullable Decimal.

Hope that helps.

where is this file? i dont have any xsd files in my folders!!

|||

Sorry, I thought you were using an ObjectDataSource. I should have read more closely. My bad. Embarrassed (With the OnjectDataSource, you can use a strongly-typed Dataset that contains TableAdapters.)

In the case of using an SqlDataSource, you'll can use one of the methods above to return the identity value with the insert command.

|||

Lee Dumond:

If the connection is built into the object, you have a TableAdapter that is connected to the table you are using. This TableAdapter contains methods that pull data from the table, like Fill() and GetData(). You can easily add another method to this TableAdapter to get the last value that was generated for the identity column of its table.

You can doubleclick on the .xsd file for the sqlDataSource, which opens the DataSet Designer. Go to the table you want to work with (lets call it myTable) and right click on any existing method in there. From this menu, choose Add Query.

This will give you a handy-dandy TableAdapter Query Configuration Wizard. In this wizard:

1. When it says "Choose a Query Type", choose "SELECT which returns a single value".

2. When it says "Specify a SQL SELECT statement, type in

SELECTIDENT_CURRENT ('myTable')FROM myTable

3. When it says "Choose a Function Name", give it a name like "GetLastIdentityValue".

Voila, you're done. So now, in your page, lets say you want to grab the ID of the last record entered and disply it in a label (Label1).

int theID = (int)myTableAdapter.GetLastIdentityValue();Label1.Text = theID.ToString();

Notice there is a cast in the first line, because the value returned by IDENT_CURRENT is of type nullable Decimal.

Question: doesn't this risk getting a later insert's identity value? How can I get the scope_identity value through the table adapter like this but from the same database call that actually makes the insert (less risky)? Regardless, thanks for a great solution using table adapters!

|||

Yes, is which why you should call itimmediately, right after you call the Update method. As you correctly point out, it surely doesn't make sense to use it in any other manner.

Here is an example where it could come in handy. Say, for instance, you have two tables: Orders and OrderItems. The OrderItems table is going to contain an OrderID column that is a foreign key back to the Orders table (OrderID would be a primary key column in Orders).

When you place OrderItems in your dataset, you have no idea what value to put in that foreign key OrderID column, because the current order hasn't been saved yet. You have a classic chicken-and-egg situation. What to do?

Easy... when you create the OrderItems, just put in any dummy value you want (it is going to be replaced with the correct value in a minute).

When you've added all the items you want, the current order is finished and you'll want to save it. To do that, do an Update on the OrdersTableAdapter, then immediately call the GetLastIdentityValue() method you've set up:

ordersTableAdapter.Update(myDBDataSet.Orders);int orderId = (int) ordersTableAdapter.GetLastIdentityValue();
This gives you the number to put in that foreign key column of the OrderItems table. So, now you loop through the dataset of the OrderItems, set each OrderID value of each accordingly, then update that:
foreach (myDBDataSet.OrderItemsRow rowin myDBDataSet.OrderItems){ row.OrderID = orderId;}orderItemsTableAdapter.Update(myDBDataSet.OrderItems);
Voila! All the foreign keys match, anf the integrity of the database is maintained.

|||

i did this in the end by using mysql and puttin it into the code behind.

All works good,

cheers for all help

|||

That is exactly the answer I needed... all the other projects here had been done in Deklarit, and after an update the DataRow held the new identity value... not so, apparently, with the xsd dataset in studio. This is exactly what I needed. Thanks so much!

Thomas

Sunday, March 11, 2012

.rdlc reports - add values through textbox name?

hi all

have a .rdlc report like an invoice.

how can i add the values in my textboxes by the Name property of the Text Box.

like txtSubTotal.Text + txtTax.Text + txtShipping.Text. ?

all those fields are populated with calculations that run on the dataset and all have values. I just cant seem to write the proper syntax to add by control names, rather than field names.

well youshould use this syntax

ReportItems!txtSubTotal.Value + ReportItems!txtTax.Value + ReportItems!txtShipping.Value

NB: this wouldn't work whih agregiate functions unless used in a header or footer

Hope this help

|||

Yes I couldn't for the LIFE OF ME find any information on the

ReportItems! < identifier. AFter much searching i found it buried in a MSDN doc somewhere called "The 5 Elements of a Microsoft Report"

Anyways got it working fine now.

thanks,

mcm

|||

hi

Please tell me how to add Runtime value into Report.rdlc. I have two date(for start & end date) Controls on web page. I want to display those date on Report.rdlc using c#.

thanking you in advance

|||

here is a code fragment that could help you :

List<ReportParameter> par=new List<ReportParameter>();ReportParameter p=newReportParameter("saleid","43660");par.Add(p);this.reportViewer1.ServerReport.SetParameters(par);this.reportViewer1.RefreshReport();
Hope this helps

.NET regular expression to replace #$# special ASCII characters

Help pleaseâ?¦ - I have a report with textbox field called â'Bulletsâ' â' using
#$# special ASCII characters (pound-dollar-pound) as bullet delimiter from
legacy print publishing system.
Questions:
1. What is the correct .NET regular expression to replace #$# special ASCII
characters within textbox field to display Bullet textâ?¦?
2. Can textbox field display Bullet textâ?¦?
Please advise MSDN or Books Online references for .NET regular expression
Replace syntax.
Thank you.
MichaelHere is an example
Input Text: tester#$#bullet_1#$#bullet_2#$#bullet_3#$#bullet_4
Regular Expression: (ASCII char Octal) \043\044\043
Replace Expression: (ASCII char Octal) \015\011\052\040\040\040
Question: Is there a Code Customization example for parsing a textbox field
and replacing regular expression characters with HTML Bullets - like
<BR><FONT family=Wingding>something_bullet_symbol</FONT><TAB>
Please advise.
"Michael" wrote:
> Help pleaseâ?¦ - I have a report with textbox field called â'Bulletsâ' â' using
> #$# special ASCII characters (pound-dollar-pound) as bullet delimiter from
> legacy print publishing system.
> Questions:
> 1. What is the correct .NET regular expression to replace #$# special ASCII
> characters within textbox field to display Bullet textâ?¦?
> 2. Can textbox field display Bullet textâ?¦?
> Please advise MSDN or Books Online references for .NET regular expression
> Replace syntax.
> Thank you.
> Michael
>|||I tried this expression
=IIF(Fields!product_Bullets.Value=#$#, Fields!product_Bullets.Value=*)
Got the following error
The value expression for the textbox â'Product_Bulletsâ' contains an error:
[BC30201] Expression expected.
Please advise the correct expression syntax.
"Michael" wrote:
> Here is an example
> Input Text: tester#$#bullet_1#$#bullet_2#$#bullet_3#$#bullet_4
> Regular Expression: (ASCII char Octal) \043\044\043
> Replace Expression: (ASCII char Octal) \015\011\052\040\040\040
> Question: Is there a Code Customization example for parsing a textbox field
> and replacing regular expression characters with HTML Bullets - like
> <BR><FONT family=Wingding>something_bullet_symbol</FONT><TAB>
> Please advise.
> "Michael" wrote:
> > Help pleaseâ?¦ - I have a report with textbox field called â'Bulletsâ' â' using
> > #$# special ASCII characters (pound-dollar-pound) as bullet delimiter from
> > legacy print publishing system.
> >
> > Questions:
> >
> > 1. What is the correct .NET regular expression to replace #$# special ASCII
> > characters within textbox field to display Bullet textâ?¦?
> > 2. Can textbox field display Bullet textâ?¦?
> >
> > Please advise MSDN or Books Online references for .NET regular expression
> > Replace syntax.
> >
> > Thank you.
> > Michael
> >|||I tried this expression - OK
=IIF(Fields!product_Bullets.Value="chr(35) + chr(36) + chr(35)", "",chr(13)
+ chr(42) + chr(9))
"Michael" wrote:
> I tried this expression
> =IIF(Fields!product_Bullets.Value=#$#, Fields!product_Bullets.Value=*)
> Got the following error
> The value expression for the textbox â'Product_Bulletsâ' contains an error:
> [BC30201] Expression expected.
> Please advise the correct expression syntax.
>
> "Michael" wrote:
> > Here is an example
> >
> > Input Text: tester#$#bullet_1#$#bullet_2#$#bullet_3#$#bullet_4
> >
> > Regular Expression: (ASCII char Octal) \043\044\043
> >
> > Replace Expression: (ASCII char Octal) \015\011\052\040\040\040
> >
> > Question: Is there a Code Customization example for parsing a textbox field
> > and replacing regular expression characters with HTML Bullets - like
> > <BR><FONT family=Wingding>something_bullet_symbol</FONT><TAB>
> >
> > Please advise.
> >
> > "Michael" wrote:
> >
> > > Help pleaseâ?¦ - I have a report with textbox field called â'Bulletsâ' â' using
> > > #$# special ASCII characters (pound-dollar-pound) as bullet delimiter from
> > > legacy print publishing system.
> > >
> > > Questions:
> > >
> > > 1. What is the correct .NET regular expression to replace #$# special ASCII
> > > characters within textbox field to display Bullet textâ?¦?
> > > 2. Can textbox field display Bullet textâ?¦?
> > >
> > > Please advise MSDN or Books Online references for .NET regular expression
> > > Replace syntax.
> > >
> > > Thank you.
> > > Michael
> > >|||Please help with regular expression syntax.
I have tried both these two expressions, using ascii hex - but I am still
getting lost on the on whether or not Reporting Services will support the
Replace function including a ascii hex Carriage Return or an ascii hex
Horizontal Tab replace string...?
=Replace(Fields!product_Bullets.Value, "[chr(35) + chr(36) + chr(35)]",
"chr(13) + chr(42) + chr(9)", "1", "-1", "1")
=Replace(Fields!product_Bullets.Value, "([\x23][\x24][\x23])",
"([\xD][\x2A][\x9])", "1", "-1", "1")
Please advise any suggestions for correct regular expression syntax.
"Michael" wrote:
> I tried this expression - OK
> =IIF(Fields!product_Bullets.Value="chr(35) + chr(36) + chr(35)", "",chr(13)
> + chr(42) + chr(9))
> "Michael" wrote:
> > I tried this expression
> >
> > =IIF(Fields!product_Bullets.Value=#$#, Fields!product_Bullets.Value=*)
> >
> > Got the following error
> >
> > The value expression for the textbox â'Product_Bulletsâ' contains an error:
> > [BC30201] Expression expected.
> >
> > Please advise the correct expression syntax.
> >
> >
> > "Michael" wrote:
> >
> > > Here is an example
> > >
> > > Input Text: tester#$#bullet_1#$#bullet_2#$#bullet_3#$#bullet_4
> > >
> > > Regular Expression: (ASCII char Octal) \043\044\043
> > >
> > > Replace Expression: (ASCII char Octal) \015\011\052\040\040\040
> > >
> > > Question: Is there a Code Customization example for parsing a textbox field
> > > and replacing regular expression characters with HTML Bullets - like
> > > <BR><FONT family=Wingding>something_bullet_symbol</FONT><TAB>
> > >
> > > Please advise.
> > >
> > > "Michael" wrote:
> > >
> > > > Help pleaseâ?¦ - I have a report with textbox field called â'Bulletsâ' â' using
> > > > #$# special ASCII characters (pound-dollar-pound) as bullet delimiter from
> > > > legacy print publishing system.
> > > >
> > > > Questions:
> > > >
> > > > 1. What is the correct .NET regular expression to replace #$# special ASCII
> > > > characters within textbox field to display Bullet textâ?¦?
> > > > 2. Can textbox field display Bullet textâ?¦?
> > > >
> > > > Please advise MSDN or Books Online references for .NET regular expression
> > > > Replace syntax.
> > > >
> > > > Thank you.
> > > > Michael
> > > >