Showing posts with label display. Show all posts
Showing posts with label display. 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

Thursday, February 9, 2012

(Select All) value

I would like to display "ALL" in a text box if the (Select All) option is
selected as a parameter.
I can display 1, 2, 5 to every single selected value in the textbox using
the JOIN function. But if the (Select All) parameter is selected, I would
rather display "All" in my textbox instead of 50+ parameters.
I have tried something of the following
=IIf(Parameters!param.Value = "Select All" , "All",
Join(Parameters!param.Value, " | "))
But this does not work.
Any suggestions out there?
Thanks
RobRob,
I have an idea, but I don't know how good it is...
If there is a way to count the number of values selected, you could
compare that with a count of the records in the dataset. However, since
I don't know if that is possible, here is an idea...
You are using a dataset to generate the "Available Values" for one
parameter. Use this same dataset as the default value for a new,
internal parameter. This parameter will not be used for anything other
than holding all of the values that could be selected.
Then, use an expression to compare the 2 JOIN results.
Iif(JOIN(Parameters!mainParm.Value,",") =JOIN(Parameters!internalParm.Value,","), "All",
JOIN(Parameters!mainParm.Value,",") )
Hope this helps!
-Josh
Rob wrote:
> I would like to display "ALL" in a text box if the (Select All) option is
> selected as a parameter.
> I can display 1, 2, 5 to every single selected value in the textbox using
> the JOIN function. But if the (Select All) parameter is selected, I would
> rather display "All" in my textbox instead of 50+ parameters.
> I have tried something of the following
> =IIf(Parameters!param.Value = "Select All" , "All",
> Join(Parameters!param.Value, " | "))
> But this does not work.
> Any suggestions out there?
> Thanks
> Rob|||Ok, I figured it out and I wanted to put my solution here for others in the
same boat.
Problem: In SSRS 2005 (non SP1) there is the Multi-value option for a
parameter. If you want to display the parameter list in a text box, you use
something like:
=Join(Parameters!param_mso.Label, ", ") //this goes in textbox
and (m.mso_num IN (@.param_mso)) //this goes in the query
This will display every select parameter in a textbox. But what happens if
you have 100+ parameters? Then you have an unmanageable list of parameters
being displayed on the report.
So, if the user selects (Select All) from the dropdown list, only display
"All" in the textbox.
Solution: You need to create a new dataset that only returns the row count
of the parameter list. So, for example: this is the SQL query that returns a
list of MSO objects that are associated with a NETWORK parameter
SELECT s.network_id, ms.mso_num, m.mso_name
FROM service s, mso_service ms, mso m
WHERE s.service_id = ms.service_id
and ms.mso_num = m.mso_num
and s.network_id = @.param_network
GROUP BY s.network_id, ms.mso_num, m.mso_name
ORDER BY ms.mso_num
I create a new DATASET just to get the count of the above query:
SELECT count(*)
FROM(
SELECT s.network_id, ms.mso_num, m.mso_name
FROM service s, mso_service ms, mso m
WHERE s.service_id = ms.service_id
and ms.mso_num = m.mso_num
and s.network_id = :param_network
GROUP BY s.network_id, ms.mso_num, m.mso_name
ORDER BY ms.mso_num)x
This way I know the max number of row items that the original query is going
to return. I then use the following formula in my textbox:
=IIf(Parameters!param_mso.Count = Parameters!param_count.Value, "ALL",
Join(Parameters!param_mso.Label, ", "))
So if the count of my MSO parameter equals the total count of the query,
then display "ALL", otherwise display all of the parameters selected.
I hope that this helps out.
Rob
"Josh" wrote:
> Rob,
> I have an idea, but I don't know how good it is...
> If there is a way to count the number of values selected, you could
> compare that with a count of the records in the dataset. However, since
> I don't know if that is possible, here is an idea...
> You are using a dataset to generate the "Available Values" for one
> parameter. Use this same dataset as the default value for a new,
> internal parameter. This parameter will not be used for anything other
> than holding all of the values that could be selected.
> Then, use an expression to compare the 2 JOIN results.
> Iif(JOIN(Parameters!mainParm.Value,",") => JOIN(Parameters!internalParm.Value,","), "All",
> JOIN(Parameters!mainParm.Value,",") )
> Hope this helps!
> -Josh
>
> Rob wrote:
> > I would like to display "ALL" in a text box if the (Select All) option is
> > selected as a parameter.
> >
> > I can display 1, 2, 5 to every single selected value in the textbox using
> > the JOIN function. But if the (Select All) parameter is selected, I would
> > rather display "All" in my textbox instead of 50+ parameters.
> >
> > I have tried something of the following
> >
> > =IIf(Parameters!param.Value = "Select All" , "All",
> > Join(Parameters!param.Value, " | "))
> >
> > But this does not work.
> >
> > Any suggestions out there?
> >
> > Thanks
> > Rob
>|||You can just use Count(Fieldname, "Dataset") to retrieve the number instead
of creating a seperate dataset.
"Rob" wrote:
> Ok, I figured it out and I wanted to put my solution here for others in the
> same boat.
> Problem: In SSRS 2005 (non SP1) there is the Multi-value option for a
> parameter. If you want to display the parameter list in a text box, you use
> something like:
> =Join(Parameters!param_mso.Label, ", ") //this goes in textbox
> and (m.mso_num IN (@.param_mso)) //this goes in the query
> This will display every select parameter in a textbox. But what happens if
> you have 100+ parameters? Then you have an unmanageable list of parameters
> being displayed on the report.
> So, if the user selects (Select All) from the dropdown list, only display
> "All" in the textbox.
> Solution: You need to create a new dataset that only returns the row count
> of the parameter list. So, for example: this is the SQL query that returns a
> list of MSO objects that are associated with a NETWORK parameter
> SELECT s.network_id, ms.mso_num, m.mso_name
> FROM service s, mso_service ms, mso m
> WHERE s.service_id = ms.service_id
> and ms.mso_num = m.mso_num
> and s.network_id = @.param_network
> GROUP BY s.network_id, ms.mso_num, m.mso_name
> ORDER BY ms.mso_num
> I create a new DATASET just to get the count of the above query:
> SELECT count(*)
> FROM(
> SELECT s.network_id, ms.mso_num, m.mso_name
> FROM service s, mso_service ms, mso m
> WHERE s.service_id = ms.service_id
> and ms.mso_num = m.mso_num
> and s.network_id = :param_network
> GROUP BY s.network_id, ms.mso_num, m.mso_name
> ORDER BY ms.mso_num)x
> This way I know the max number of row items that the original query is going
> to return. I then use the following formula in my textbox:
> =IIf(Parameters!param_mso.Count = Parameters!param_count.Value, "ALL",
> Join(Parameters!param_mso.Label, ", "))
> So if the count of my MSO parameter equals the total count of the query,
> then display "ALL", otherwise display all of the parameters selected.
> I hope that this helps out.
> Rob
>
>
> "Josh" wrote:
> >
> > Rob,
> >
> > I have an idea, but I don't know how good it is...
> >
> > If there is a way to count the number of values selected, you could
> > compare that with a count of the records in the dataset. However, since
> > I don't know if that is possible, here is an idea...
> >
> > You are using a dataset to generate the "Available Values" for one
> > parameter. Use this same dataset as the default value for a new,
> > internal parameter. This parameter will not be used for anything other
> > than holding all of the values that could be selected.
> >
> > Then, use an expression to compare the 2 JOIN results.
> > Iif(JOIN(Parameters!mainParm.Value,",") => > JOIN(Parameters!internalParm.Value,","), "All",
> > JOIN(Parameters!mainParm.Value,",") )
> >
> > Hope this helps!
> >
> > -Josh
> >
> >
> > Rob wrote:
> > > I would like to display "ALL" in a text box if the (Select All) option is
> > > selected as a parameter.
> > >
> > > I can display 1, 2, 5 to every single selected value in the textbox using
> > > the JOIN function. But if the (Select All) parameter is selected, I would
> > > rather display "All" in my textbox instead of 50+ parameters.
> > >
> > > I have tried something of the following
> > >
> > > =IIf(Parameters!param.Value = "Select All" , "All",
> > > Join(Parameters!param.Value, " | "))
> > >
> > > But this does not work.
> > >
> > > Any suggestions out there?
> > >
> > > Thanks
> > > Rob
> >
> >|||Won't let me edit my post. Something like this.
=IIF(Count(Fields!AcctCode.Value, "AcctCode") =Parameters!AcctCode.Count,"All",Join(Parameters!AcctCode.Label,","))
"Rob" wrote:
> I would like to display "ALL" in a text box if the (Select All) option is
> selected as a parameter.
> I can display 1, 2, 5 to every single selected value in the textbox using
> the JOIN function. But if the (Select All) parameter is selected, I would
> rather display "All" in my textbox instead of 50+ parameters.
> I have tried something of the following
> =IIf(Parameters!param.Value = "Select All" , "All",
> Join(Parameters!param.Value, " | "))
> But this does not work.
> Any suggestions out there?
> Thanks
> Rob