Getting the id generated by SQL Server for a new record

I am using ASP.NET 2.0 to add records to a database table in an SQL Server
database. The id field is automatically created when a record is added, and
I would like to be able to know what value was assigned to the id field for
use in my ASP.NET application. Is there a way to find out what value was
assigned to the record at the same time I create the record (in otherwords,
I do not want to use a SELECT statement)? Thanks.
Nathan Sokalski
njsokalski@.hotmail.com
http://www.nathansokalski.com/
On Nov 29, 10:42 pm, "Nathan Sokalski" <njsokal...@.hotmail.com> wrote:
> I am using ASP.NET 2.0 to add records to a database table in an SQL Server
> database. The id field is automatically created when a record is added, and
> I would like to be able to know what value was assigned to the id field for
> use in my ASP.NET application. Is there a way to find out what value was
> assigned to the record at the same time I create the record (in otherwords,
> I do not want to use a SELECT statement)? Thanks.
> --
> Nathan Sokalski
> njsokal...@.hotmail.comhttp://www.nathansokalski.com/
Look in the Books Online for the SCOPE_IDENTITY function. Lots of
good examples there.
|||On Nov 29, 9:42 pm, "Nathan Sokalski" <njsokal...@.hotmail.com> wrote:
> I am using ASP.NET 2.0 to add records to a database table in an SQL Server
> database. The id field is automatically created when a record is added, and
> I would like to be able to know what value was assigned to the id field for
> use in my ASP.NET application. Is there a way to find out what value was
> assigned to the record at the same time I create the record (in otherwords,
> I do not want to use a SELECT statement)? Thanks.
> --
> Nathan Sokalski
> njsokal...@.hotmail.comhttp://www.nathansokalski.com/
You would want to return the SCOPE_IDENTITY() value as an output
parameter from your DbCommand after you perform the insert.
|||On 30 Nov., 07:18, Dan Gartner <dgart...@.gmail.com> wrote:
> On Nov 29, 9:42 pm, "Nathan Sokalski" <njsokal...@.hotmail.com> wrote:
> You would want to return the SCOPE_IDENTITY() value as an output
You could use the OUTPUT feature of sql server 2005. But in this case
SCOPE_IDENTITY() and @.@.IDENTITY give back odd results while
IDENT_CURRENT() and inserted.id seem to give correct values.
inserted.id is the way the output feature is meant.
Example:
begin tran
create table tmp (id int identity, xyz varchar)
insert into tmp (xyz) output inserted.id values ('w')
insert into tmp (xyz) output @.@.identity values ('x')
insert into tmp (xyz) output ident_current('tmp') values ('y')
insert into tmp (xyz) output scope_identity() values ('z')
select * from tmp
drop table tmp
rollback
Results:
1
1
3
3
id xyz
-- --
1 w
2 x
3 y
4 z
(4 Zeile(n) betroffen)
|||Hi
You can use the output parameter of the stored procedure and return the
value that is being inserted. If you are using identity column then you can
use @.@.IDENTITY to return the last inserted indentity value.
--
Thanks,
Ibrahim
Software Consultant - Web Development, GB
"Nathan Sokalski" wrote:

> I am using ASP.NET 2.0 to add records to a database table in an SQL Server
> database. The id field is automatically created when a record is added, and
> I would like to be able to know what value was assigned to the id field for
> use in my ASP.NET application. Is there a way to find out what value was
> assigned to the record at the same time I create the record (in otherwords,
> I do not want to use a SELECT statement)? Thanks.
> --
> Nathan Sokalski
> njsokalski@.hotmail.com
> http://www.nathansokalski.com/
>
>
|||This method obviously requires using stored procs (which is almost always a
good idea for a bunch of reasons). If you are using ADO/ADONET you are in a
bit of a bind I think. IIRC you can't issue an insert statement and get a
select back out in a single Execute... type command.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Ibrahim Shameeque" <IbrahimShameeque@.discussions.microsoft.com> wrote in
message news:88277D10-9F15-42C3-BA0B-D18E4377E160@.microsoft.com...[vbcol=seagreen]
> Hi
> You can use the output parameter of the stored procedure and return the
> value that is being inserted. If you are using identity column then you
> can
> use @.@.IDENTITY to return the last inserted indentity value.
> --
> --
> Thanks,
> Ibrahim
> Software Consultant - Web Development, GB
>
> "Nathan Sokalski" wrote:
|||Kevin,
Sure you can, like this:
cmd.CommandText = "Insert Into Students (StudentName, Test1, Test2) Values
(@.StudentName, @.Test1, @.Test2); Select Scope_Identity()"
Then:
ID = cmd.ExecuteScalar
Kerry Moorman
"TheSQLGuru" wrote:

> This method obviously requires using stored procs (which is almost always a
> good idea for a bunch of reasons). If you are using ADO/ADONET you are in a
> bit of a bind I think. IIRC you can't issue an insert statement and get a
> select back out in a single Execute... type command.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Ibrahim Shameeque" <IbrahimShameeque@.discussions.microsoft.com> wrote in
> message news:88277D10-9F15-42C3-BA0B-D18E4377E160@.microsoft.com...
>
>
|||I seem to recall a client trying to do that recently (using ADO classic) and
it not working. Perhaps they missed the semicolon. I will recheck their
attempts and see if that does it.
One additional question since I am not an ADO guru. Does the Select
Scope_identity() not return a single-column single-row result set, which the
executescalar isn't expecting?
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Kerry Moorman" <KerryMoorman@.discussions.microsoft.com> wrote in message
news:D12AFECB-DC31-4CE3-A2A2-14F3AB3CD95D@.microsoft.com...[vbcol=seagreen]
> Kevin,
> Sure you can, like this:
> cmd.CommandText = "Insert Into Students (StudentName, Test1, Test2) Values
> (@.StudentName, @.Test1, @.Test2); Select Scope_Identity()"
> Then:
> ID = cmd.ExecuteScalar
> Kerry Moorman
>
> "TheSQLGuru" wrote:
|||Kevin,
ExecuteScalar returns the first column of the first row in the result set
returned by the query.
Kerry Moorman
"TheSQLGuru" wrote:

> One additional question since I am not an ADO guru. Does the Select
> Scope_identity() not return a single-column single-row result set, which the
> executescalar isn't expecting?
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
|||I just checked back with the developer that had the issue. He swears that
using VB6 and ADO classic your example fails. Were you using ADOc or
ADO.NET?
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Kerry Moorman" <KerryMoorman@.discussions.microsoft.com> wrote in message
news:6AD6FF28-692A-4DFA-9BCB-7D8E9A2302C1@.microsoft.com...
> Kevin,
> ExecuteScalar returns the first column of the first row in the result set
> returned by the query.
> Kerry Moorman
>
> "TheSQLGuru" wrote:
>

Getting started with SQL Server Mobile

I have recently upgraded to VS 2005, .NET 2.0, and SQL Server 2005 and am trying to upgrade some of my older Pocket PC 2003 apps. I am trying to educate myself but have become a bit confused about how to proceed and was hoping some of the knowledgable people in this group could point me in the right direction.

I have developed several data collection applications that used Pocket Access on the device and very easily sychronized with a custom MS Access database via Active Sync. I am slowly coming to the realization that using SQL Server Mobile will NEVER be that easy (which is a shame). My questions are......

1. If I want to upgrade to SQL Server Mobile, is there any way that I can continue to easily "synchronize" my data with a MS Access database? If so, any suggestion/links as to how to do this would be greatly appreciated!

2. If the above scenario is not possible, what is the best solution for me to create a distributable application that synchronizes into a local database on a computer that DOES NOT have SQL Server installed?

3.) Related to above, is creating a distributable SQL Server Express database the answer? Can I use the built in SQL Server synchronization tools via this scenario?

Thanks in advance!

Mike

Mike,

In terms of getting started with SQL Mobile and synchronizing data between SQL Mobile and SQL Server, a great place to start is this white paper:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5mobile.asp

There has been quite a bit of feedback regarding the disappearance of Pocket Access in WM5 and the ease with which it could be synchronized with a server-side Access database. Microsoft heard that and to their credit is working on something I can't say much about right now, but it will solidly address this in the next 3-4 months.

In terms of the "best" solution for synchronizing data, that depends on a long list of things. The obvious options are merge replication, remote data access, and web services. I gave an MSDN web cast in March 2005 on making this decision that includes all of the criteria and even a flow chart to help you land on the right choice. http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?culture=en-US&EventID=1032268682&CountryCode=US.

Creating a distributable SQL Express database is an option for x86-based clients, but not for Windows Mobile devices. SQL Express can indeed be a subscriber to a merge publication in a replication relationship.

Regards,

-Darren

Getting Started - what do I need?

While I have been programming .Net for four years and T-SQL for about 12, I am a newbie to the whole SQL CLR thing. I have VS.NET 2003 on my machine, and I installed the client tools for SQL 2005 which also installs VS 2005 for SQL. Yet when I open VS 2005, it only has Data Analysis projects, but no Database projects. If I go into VS 2003 and try to import some of the assemblies I have found by googling 'SQL CLR' - VS 2003 won't let me add them.

Exactly what do I need to install so that I can get started? Do I need an instance of SQL server running on my machine? (I currently do not have a server running, only the client tools are installed)

TIA,

--Yonah

Hi Yonah,

SQL Server 2005 Requirements:

Every SQL Server Edition (not sure about the new EVERYWHERE) including Express supports "CLR Integration" aka SQLCLR. Here is the sql product feature compare URL, http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx, feature is called "Common Language Runtime and .NET Integration".

Visual Studio 2005 Requirements (you dont have to use VS to create SQLCLR routines...but come on who uses vbc & csc w/Notedpad everyday? Which is actually why my SQLCLR book ASSUMES you will be using VS for SQLCLR work):

You need Visual Studio 2005 Professional, Tools for Office (VSTO), or Visual Studio Team System (VSTS) editions. See http://msdn.microsoft.com/vstudio/products/compare/default.aspx and look for "SQL Server 2005 Integration" feature.

Enjoy SQLCLR!

Derek

|||

You can check my blogs. i remeber i wrote article on this

Getting SSIS running on Web Server

I need to be able to run SSIS packages form an asp.net (win 2k3) web server. Wrox has a book out "Professional SQL Server 2005 Integration Services" where they call the dtsx package directly using the following vb.net snipette:

Imports Microsoft.SqlServer.Dts.DtsClient

Dim ssisConn As New DtsConnection
ssisConn.ConnectionString = String.Format("-f ""{0}""", strMyFilePath)
ssisConn.Open()

As you would expect this works great on a workstation that has BIDS installed on it but does not work on a web server where sql client tools are not installed. Without install sql tools on the server what needs to be done to get this functioning as coded? How about calling packages that are installed on the server?

If anyone knows of any sites or books that cover this in detail I would appreciate the info. I only seem to be able to find bits and pieces.

thanks in advance

You can't. To my knowledge, you'll need the SSIS client tools installed. The packages are executed using dtexec (or a variant, I suppose).|||Searching turned this up.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=172501&SiteID=1|||

I figured it wouldn't be possible to run packages from a web server w/out having ssis installed. I wanted to make sure that some combonation of dll's copied to the bin directory wouldn't get me what I needed. I opted to expose a web service on the sql server and consume the service from the web box. The following kb article describes how to do it via a job and a web service.

http://msdn2.microsoft.com/de-de/library/ms403355.aspx

Getting SSIS running on Web Server

I need to be able to run SSIS packages form an asp.net (win 2k3) web server. Wrox has a book out "Professional SQL Server 2005 Integration Services" where they call the dtsx package directly using the following vb.net snipette:

Imports Microsoft.SqlServer.Dts.DtsClient

Dim ssisConn As New DtsConnection
ssisConn.ConnectionString = String.Format("-f ""{0}""", strMyFilePath)
ssisConn.Open()

As you would expect this works great on a workstation that has BIDS installed on it but does not work on a web server where sql client tools are not installed. Without install sql tools on the server what needs to be done to get this functioning as coded? How about calling packages that are installed on the server?

If anyone knows of any sites or books that cover this in detail I would appreciate the info. I only seem to be able to find bits and pieces.

thanks in advance

You can't. To my knowledge, you'll need the SSIS client tools installed. The packages are executed using dtexec (or a variant, I suppose).|||Searching turned this up.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=172501&SiteID=1|||

I figured it wouldn't be possible to run packages from a web server w/out having ssis installed. I wanted to make sure that some combonation of dll's copied to the bin directory wouldn't get me what I needed. I opted to expose a web service on the sql server and consume the service from the web box. The following kb article describes how to do it via a job and a web service.

http://msdn2.microsoft.com/de-de/library/ms403355.aspx

Getting SQL Server NIC Mac Addresses in SP

How can I get the MAC Addresses of all the NICS in a SQL Server box
either through code (VB .Net) or a Stored Procedure?
I know the winmgmts is something that can allow this but I am not
familiar with SP programming, and think that is the best way to go
since I don't want to worry about authentication to the SQL box if I
attempt it with code, a SP can just be called cleanly and retrieve the
info.
Anyone ever try this or have a sample? Thanks a ton!Assuming winmgmts is a set of com objects have a look at:-
sp_OA*
<gfricke@.gmail.com> wrote in message
news:1130160778.628613.27460@.g14g2000cwa.googlegroups.com...
> How can I get the MAC Addresses of all the NICS in a SQL Server box
> either through code (VB .Net) or a Stored Procedure?
> I know the winmgmts is something that can allow this but I am not
> familiar with SP programming, and think that is the best way to go
> since I don't want to worry about authentication to the SQL box if I
> attempt it with code, a SP can just be called cleanly and retrieve the
> info.
> Anyone ever try this or have a sample? Thanks a ton!
>|||Code generated with WMI Code Generator (VB.NET)
Imports System
Imports System.Management
Imports System.Windows.Forms
Namespace WMISample
Public Class MyWMIQuery
Public Overloads Shared Function Main() As Integer
Try
Dim searcher As New ManagementObjectSearcher( _
"root\CIMV2", _
"SELECT * FROM Win32_NetworkAdapter")
For Each queryObj As ManagementObject in searcher.Get()
Console.WriteLine("--")
Console.WriteLine("Win32_NetworkAdapter instance")
Console.WriteLine("--")
Console.WriteLine("MACAddress: {0}",
queryObj("MACAddress"))
Next
Catch err As ManagementException
MessageBox.Show("An error occurred while querying for WMI
data: " & err.Message)
End Try
End Function
End Class
End Namespace
Regards
Jacek
"gfricke@.gmail.com" wrote:

> How can I get the MAC Addresses of all the NICS in a SQL Server box
> either through code (VB .Net) or a Stored Procedure?
> I know the winmgmts is something that can allow this but I am not
> familiar with SP programming, and think that is the best way to go
> since I don't want to worry about authentication to the SQL box if I
> attempt it with code, a SP can just be called cleanly and retrieve the
> info.
> Anyone ever try this or have a sample? Thanks a ton!
>|||Thanks for the help with the VB side, now I am trying to replicate this
in a SP anyone have experience doing anything like this in a SP?|||Step 1. create vbs script (for example d:\a.vbs) with following code:
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2")
Set colItems = objWMIService.ExecQuery( _
"SELECT * FROM Win32_NetworkAdapterConfiguration",,48)
For Each objItem in colItems
if Len(Trim(objItem.MACAddress))>0 Then
Wscript.Echo "MACAddress: " & objItem.MACAddress
end if
Next
Step 2. create stored procedure (T-SQL) :
create procedure getMAC
AS
create table #O(output varchar(2000))
INSERT INTO #O
exec
master..xp_cmdshell 'cscript.exe /nologo d:\a.vbs'
select * from #O
Regards
Jacek
"gfricke@.gmail.com" wrote:

> Thanks for the help with the VB side, now I am trying to replicate this
> in a SP anyone have experience doing anything like this in a SP?
>|||Ok so far everything has been working great, however now I want to do
the same task using just the GetObject("winmgmts:\\" & strComputer &
"\root\CIMV2") concept, not the System.Management objects which are
part of the .Net framework. This is because I want to implement it in
FoxPro eventually (v7 or 8) which don't have the ability to use .Net
dlls.
1. So can anyone accomplish the task of getting the MAC Addresses from
a REMOTE box (assuming the machine has authentication to the remote
box) using this approach?
2. Also, what level of access does a user account need to get the MAC
Address info on the remote box, I don't want to give them full Admin
access if it can be avoided, any ideas?
Again thanks for everything, the vbs, and System.Management methods
worked great, now my boss wants these other approaches :)

Getting SQL Server deadlock error - how do I work around?

I have some ASP.NET C# code which executes a stored procedure in SQL Server via the SqlCommand and SqlConnection classes.

One of the stored procedures that gets executed is giving the error: "Transaction (Process ID 272) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction." This only happens occassionally.

Is there a way to get around this in my ASP.Net application? One thing I tried is ensuring that no 2 users entered the stored procedure concurrently:

object synclock =new object() ;lock (synclock) {// execute SQL stored procedure ...}

This did not solve the problem, and I'm not even sure if that is the correct implementation to ensure sequential execution of the stored procedure.

I use a method which some frown upon but if you can handle dirty reads you will be fine. (sql 2000 SP4 or sql 2005) - use the no lock table hint on all your selects and joins in the procedure.

for example: select field1... from table1 with (NOLOCK) on joins its ... inner join table2 with (nolock) ...

So this will ask sql to read the data even if its in flux but if you don't care it will help with dead locks.

-c

Getting SQL or Database status on webpage.

Hi is it possible to have a page of a website display the status of a database or MS SQL with .Net code ?, if so what code would i use.

Any help would be appreciated.

Thank You

Do you want see in your webpage SQL Server status (running, paused, etc) ?|||

Idealy yes that would be great but it dosent matter particuarlly what kinda status, it would be great to get the server status as well as the database status but eather is fine.

Thank You

|||

This just an idea, you can try to simulate to connect and disconnect in the EM and trace the whole process. Using this trace information you could figure out if there is any information about the status in a table or if there is something related to it.

Because as far as I know, SQL server is data in tables and instructions in system procedures. whatever you do in SQL Server is data and instructions. So the status must also be somewhere in a system table and you can try to retrieve it.

I was involved in something similar to this and this kind of work around gives results ( time consuming though to understand SQL procedures to extract information you want)

Good Luck!

|||

Thanks for the idea but i need a quick solution, im really short on time and need a solution fast.

thanks anyway

|||Cant remember off the top of my head but check out some of the procs (and also extended procs) in the master DB. Perhaps you can find one that gives you the info you need..|||

Hi shadowmaster,

Actually, to get the status of a database, you can use SQL Server SMO(SQL Management Objects).

The Database.Status property will return the database status.

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.database.status.aspx

However, we cannot get the server status, because when a server is stopped, the service will not be available. In this case, we cannot even connect to that server. So we can only get the running status.

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!

sql

Getting SQL database structure from here to there

I'm sure there is a simple way to do this but I cannot think of it.
I have an ASP.NET application that I am publishing to a webhost. I have chosen to use FTP to copy my files (aspx, etc.). They work as far as links etc. Now, the next step is to create my SQL database on their server as they instruct, which I have done in name only. (Oh, if it makes a difference, I access the hosting service using Plesk control panel.) Is there any simple way to get my database structure from my PC to the host server? All responses welcome. Thanks.You might be able to ftp the actual raw mdf/ldf files up to the server, then attach these files to your db.
|||Thanks a ton. Thatwould be the perfect solution. Unfortunately, I don't see any kind of access to the attach command on their control panel. I will check again though. What about running scripts to create the tables, stored procedures, views etc.? Do you think that could work?|||Ok I think I misunderstood you...if you only need the schema (and notthe data itself) then you can script out the database an an .sql fileand then execute the resulting sql code on your hosting server and theschema will be created.
|||If you need to script the Data you can try my tool SQL Inserter. Its on GotDotnet
http://www.gotdotnet.com/workspaces/workspace.aspx?id=17ec0d2c-c29a-4eb3-83d5-b1c58bb32a78
Mathias|||

Thanks to everyone for responding. The script method seems to be the best one for me and is working so far.

Getting Rid of DBNull values in DB

As a part of migration to .net we are redesigning sql server 7 database and moving it to sql server 2005. There are a lot of fields with dbnull value in the existing db. It would be nice to populate them with some default value while transfering data to the new datbase on sql server 2005.

What is the best way to deal with the issue, are there any possible drawbacks and problems.

Your input will be greatly appreciated.

Getting rid of NULLs/having default values is generally a good thing. It removes a lot of overhead in your code to constantly check for NULLs. You can write UPDATE queries to update the NULLs to some default values. A sample update query could be:

UPDATE yourTable SET Price = 0 WHERE Price IS NULL

sql

Getting results from sqldatasource in codebehind

(New to ASP.net 2.0 and database connection)

I have created an sqldatasource on the aspx page, which works fine, but how do I get the results from it in the codebehind?

hmm..you should create the datasource in the code behind page. I mean that's what the page is for. Place all your asp.net code in it. If you use a code-behind page then aspx page is just for displaying the data.
|||

I can't seem to get it working. The connection works fine (I believe), but I can't seem to give the parameters a value

I have this code: (I know it's Insert instead of select, but that's what I'm working on now)

SqlDataSourceSupplierList.InsertCommand ="INSERT INTO dbo.ProdSup(SupID, ProdID) VALUES (@.SupID, @.ProdID)";

SqlDataSourceSupplierList.InsertParameters.Add("@.SupID", SqlDbType.Int) = 4;

SqlDataSourceSupplierList.InsertParameters.Add("@.ProdID","8");

SqlDataSourceSupplierList.Insert();

As you can see I have tried two ways to give them values, but none of them works. The fields in the DB are both integer, which I guess is why the second try doesn't work (it sends '8' in as a string)... The error I get here is that it "cannot insert the value NULL into column"

The line with @.SupID gives me this error:

CS1502: The best overloaded method match for 'System.Web.UI.WebControls.ParameterCollection.Add(string, string)' has some invalid arguments

What's wrong here?

Getting ReportViewer client parameters at runtime

I cannot seem to do the following:

1) I have a report that uses the ASP.NET 2.0 Report Viewer against a RS2005 report that has parameters. Some have default values. One is a text box. Others are drop-down lists.
2) I have ShowParameterPrompts = true and am presented the parameter area upon first visit to the page.
3) I fill in the parameters on the web browser page and click the View Report button
4) Now, on the server side, I want to examine the parameter values (in the Page_Load event). When I do a GetParameters on the ServerReport of the ReportViewer, I receive back a collection of the default parameters values, not the values that I just submitted.

How do I see the parameter values submitted by the client?

Other information I found while investigating this:

1) If I click on the View Report button again (and again), GetParameters always return the previous set of parameters, not the current values that the client submitted.
2) If I look at the Request.Forms collections I can see values for controls that have been generated by the ReportViewer control in the parameters area - where the values are different when the client submitts new parameters.
3) If I do a GetParameters in the pages Page_PreRender event, I see the same behavior.
4) I can do a walk of the control tree inside the report view control instance and see the various controls created for the parameters area. Those controls have the Request.Form values in them.
5) The Report Viewer, load, report refresh and prerender events don't seem to be much help either.

I could hack a solution using the control tree or the Request.Form values, but that's rather inelegant and relies on Micrsoft not changing the ReportViewer control's behavior.

Any explanations of what is happening here would be appreciated. Thanks,

ER Doll

I am having a similar problem. Have you found a solution, specifically to number 1 in the second section of your message?

CWaitz

|||

I am also trying to find how to get the parameters that the user has set when they hit the View Report button. But I think this is what happens when you do a GetParameters when you hit the View Report button on the ReportViewer control:

When you do a GetParameters() it gets the values of the parameters of the report that is already generated on the screen. So if you were to change the parameters and click View Report, in your Page_Load when you call GetParameters(), this is looking at the parameters before your new report is generated. So if you wanted the new parameters that the user has inputted, you would need to wait for the report to get generated then do a postback and use GetParameters.

If anyone knows how to get the parameter values the user has selected before the report is generated, please let us know.

|||

Examine your parameters in isPostBack block.

protected void Page_Load(object sender, EventArgs e){if(isPostBack) {//EXAMINE }}

|||

bullpit:

Examine your parameters in isPostBack block.

protected void Page_Load(object sender, EventArgs e){if(isPostBack) {//EXAMINE }}

I've been doing this but it doesn't work. If it were a DateTime parameter then it would work because when you change the date in ReportViewer it does a Postback which updates the ReportViewer. Is it possible to always do a Postback whenever a value for a parameter is changed? Even for String parameters that appear as textboxes? This would solve my problem.

|||

What about using TextChanged and SelectedIndex changed event. Make a function where you load your Reportviewer. Call that function everytime there is text changed or selected index changed.

|||I don't have access to those events since the controls are rendered automatically in the ReportViewer depending on what type of parameter it is. I'm trying to see if there is a property in the actual parameter creation when creating the report. There has to be because one of my reports that has a dropdown list did a postback whenever the selection changed. But another report didn't. I have to find out how this happened...|||

The values of the parameters are stored in the view state of the parameter controls. Unfortunately, as you mentioned, you don't have direct access to those controls at run time. However, you do have access to them through the Controls collection of the ReportViewer, if you know what to look for. The following code will extract the currently selected parameter values. NOTE: this code uses reflection and is dependent on the internal implementation of the parameter controls. This is necessary since the ReportViewer does not expose the values of these controls in any other way.

Public Function GetCurrentParameters(ByVal viewerAs Microsoft.Reporting.WebForms.ReportViewer)As ReportParameter()Dim paramsAreaAs Control = FindParametersArea(viewer)Dim paramsAs New List(Of ReportParameter)()FindParameters(paramsArea, params)Return params.ToArray()End FunctionPrivate Function FindParametersArea(ByVal viewerAs Microsoft.Reporting.WebForms.ReportViewer)As ControlFor Each childAs ControlIn viewer.ControlsIf child.GetType().Name ="ParametersArea"ThenReturn childEnd IfNextReturn NothingEnd FunctionPrivate _ParameterControlTypeAs Type = System.Reflection.Assembly.GetAssembly(GetType(Microsoft.Reporting.WebForms.ReportViewer)).GetType("Microsoft.Reporting.WebForms.ParameterControl")Private Sub FindParameters(ByVal parentAs Control,ByVal paramsAs List(Of ReportParameter))Dim paramAs ReportParameterDim paramInfoAs ReportParameterInfoDim paramValuesAs String()For Each childAs ControlIn parent.ControlsIf _ParameterControlType.IsAssignableFrom(child.GetType())ThenparamInfo =CType(GetPropertyValue(child,"ReportParameter"), ReportParameterInfo)If paramInfoIs Nothing Then ContinueForparamValues =CType(GetPropertyValue(child,"CurrentValue"),String())If Not paramValuesIs Nothing AndAlso paramValues.Length > 0Thenparam =New ReportParameter()param.Name = paramInfo.Nameparam.Values.AddRange(paramValues)params.Add(param)End IfEnd IfFindParameters(child, params)NextEnd SubPublic Function GetPropertyValue(ByVal targetAs Object,ByVal propertyNameAs String)As ObjectReturn target.GetType().GetProperty(propertyName, BindingFlags.IgnoreCaseOr BindingFlags.InstanceOr BindingFlags.NonPublicOr BindingFlags.Public).GetValue(target,Nothing)End Function

|||

autofed,

Your code saved my ass. It worked like a charm. Thank you.

Getting Report page Count

Hi All
We have an ASP.NET application that lists all the reports hosted in our SQL
RS by using RS webservices. When user selects on a particular report from the
list the corresponding parameters get shown again using web service calls.
After the selection of parameters, the user gets to view the report in the
report viewer control. We have also designed a custom tool bar complete with
export options and with prev/next functionalities. In order to get the
pagecount we are calling the render method and get the streamid count. We are
passing the device info for Image i.e.
<DeviceInfo><OutputFormat>EMF</OutputFormat></DeviceInfo>. The streamId count
however is very random and does not match the actual total page count of the
report.
Can anyone please help or guide here...
Thanks !
PRI just wanted to add that the report gets shown in the reportviewer by URL.
The render method is used to purely generate the pagecount. Shouldn't there
be an easier way...
"PR" wrote:
> Hi All
> We have an ASP.NET application that lists all the reports hosted in our SQL
> RS by using RS webservices. When user selects on a particular report from the
> list the corresponding parameters get shown again using web service calls.
> After the selection of parameters, the user gets to view the report in the
> report viewer control. We have also designed a custom tool bar complete with
> export options and with prev/next functionalities. In order to get the
> pagecount we are calling the render method and get the streamid count. We are
> passing the device info for Image i.e.
> <DeviceInfo><OutputFormat>EMF</OutputFormat></DeviceInfo>. The streamId count
> however is very random and does not match the actual total page count of the
> report.
> Can anyone please help or guide here...
> Thanks !
> PR

Getting proc name from within .Net code

Hi,

I'm currently in the midst of writing my first sqlclr sproc.

In our normal T-SQL sprocs we use the following:

OBJECT_NAME(@.@.PROCID)

to get the name of the sproc. We later use the result of this as a parameter to a custom message when we use RAISERROR.

Question is, is there a way of doing the same from within a SQLCLR sproc? i.e. Can someone give me a bit of code that will return the name of the current sproc? (Obviously this is not the same as the name of the .Net class that implements my sproc).

Thanks in advance for any help that you can provide.

Regards

I've had word from Umachander at MSFT who says that this isn't possible but that they are looking at it for a future version.

-Jamie

Getting past SQL Network Interface error 26 - Error Locating Server/Instance Specified

First-time poster (and very new to ASP.NET, so please be patient) - I am using VS 2005. I have an ASP.Net 2.0 app that is using Forms Authentication and a Login server control with AspNetSqlMembershipProvider. I have an instance of ASPNETDB.MDF in the App_Data directory on my local machine and all works well (or at least as expected). I used the Copy Web Site function to port the app (including the ASPNETDB.MDF files) to my production platform, where I encounter the error mentioned in the subject line. I have found posts (both here and other forums) that mention enabling TCP/IP using Surface Area Configuartion Utility, SQL Server Browser are exempt from firewall, and a few other things. My production machine does not have SQL SVR 2005 installed nor SQL SVR Express.

Do I have to install SQL Server Express onto my production platform in order to resolve this, or is there another means (something in SQL Server 2005 Management Studio on my local machine) that will enable me to do this on the production machine?

Also, I have been told that it is possible to use a database on another machine as the data store for authentication. Does anyone know how to do that? This would make the first question moot.

Thanks in advance!

Then you cannot run your application because SQL Server is RDBMS (relational database management systems). When you install SQL Server then follow the steps in this thread. Hope this helps.

http://forums.asp.net/thread/1403472.aspx

|||

Thanks for the response, but I worked out my optimum solution on my own (the "Aslo" part of my original post. Using the aspnet_regsql.exe, I have my authentication database on a full SQL Server 2005 box elsewhere in my enterprise, and I worked out the web.config setting to make it work.

I understand that SQL Server is the underlying DBMS. That point was not in question. The issue was, (now moot) was it truly needed on the same box, and if not, how to make it work.

Getting meta-data for Linked Servers

My customer has a .NET application that reads meta data from SQL Server, Oracle, DB2, and several propritary databases. Because each DBMS stores the meta data using various techniques, they have written custom code for each DBMS. They are working on a generic ODBC/OLEDB suppport, but in the interim I was trying to use SQL Server to link to an Access database. The Access linked server works fine for queries in Query Analyzer, but I would like to be able to programatically read the metadata for an Access DB (tables, columns, types, etc) via the linked server. SQL Server's usual mechanism for storing meta-data in the Master database aparently is not used for Linked Servers.

Does SQL Server expose Linked Server meta data?

How would one retrieve this meta data if it is exposed?

Thanks,

Ben

Hi Ben,

No SQL Server does not store metadata about linked servers (excluding connection information stored in sys.servers/sysservers). You would need to interrogate the actual linked server for this. So, if it were MSAccess, you would need to refer to the system tables (such as MSysObjects) etc.

Cheers,

Rob

|||

I was afraid that was the answer.

Thanks

getting list of SQL Instances / Databases on network

I am using C#.NET and I am writing an application where I need to display to
the user in a comboBox all the SQL Server instances that can be detected and
dis. I have seen many applications like Enterprise Manager that can detect
them all. Once the user selects the instance, I would also like to get a lis
t
of all the databases stored into another combo.
How do I do this in C#?
Thanks for the help in advance.
David
Message posted via http://www.webservertalk.comhi
probably this can hekp you:
http://chanduas.blogspot.com/2005/0...-databases.html
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"David C via webservertalk.com" wrote:

> I am using C#.NET and I am writing an application where I need to display
to
> the user in a comboBox all the SQL Server instances that can be detected a
nd
> dis. I have seen many applications like Enterprise Manager that can detect
> them all. Once the user selects the instance, I would also like to get a l
ist
> of all the databases stored into another combo.
> How do I do this in C#?
> Thanks for the help in advance.
> David
>
> --
> Message posted via http://www.webservertalk.com
>|||This just lists the databases on a *known* single instance, which is also
treated here in more depth:
http://www.aspfaq.com/2456
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:3EAACA02-A627-4CE6-8A6E-E9CCFF6208D2@.microsoft.com...
> hi
> probably this can hekp you:
> http://chanduas.blogspot.com/2005/0...-databases.html
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "David C via webservertalk.com" wrote:
>|||If you are familiar with SQL-DMO, you can use ListAvailableServers()
You can also use the SQLPing utility; one version has C# source code
included.
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=26
In addition, Gert has produced some tools:
http://www.sqldev.net/misc/EnumSQLSvr.htm
http://www.sqldev.net/misc/ListSQLSvr.htm
http://www.sqldev.net/misc/OleDbEnum.htm
"David C via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:52A41F2E6B25A@.webservertalk.com...
>I am using C#.NET and I am writing an application where I need to display
>to
> the user in a comboBox all the SQL Server instances that can be detected
> and
> dis. I have seen many applications like Enterprise Manager that can detect
> them all. Once the user selects the instance, I would also like to get a
> list
> of all the databases stored into another combo.
> How do I do this in C#?
> Thanks for the help in advance.
> David
>
> --
> Message posted via http://www.webservertalk.com|||Thank Chandra but that will only help me once I get the instance. I also nee
d
to know how to query all the instances that exist on the network. Any help
would be great from someone.
Thanks,
David
Chandra wrote:
>hi
>probably this can hekp you:
>http://chanduas.blogspot.com/2005/0...-databases.html
>
>[quoted text clipped - 7 lines]
Message posted via http://www.webservertalk.com|||Thank Chandra but that will only help me once I get the instance. I also nee
d
to know how to query all the instances that exist on the network. Any help
would be great from someone.
Thanks,
David
Chandra wrote:
>hi
>probably this can hekp you:
>http://chanduas.blogspot.com/2005/0...-databases.html
>
>[quoted text clipped - 7 lines]
Message posted via http://www.webservertalk.com|||Here's a very quick way to do it in C#
Create a new console application, and add this reference:
Project | Add Reference | COM | Microsoft SQLDMO Object Library
using System;
using System.Collections.Generic;
using System.Text;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
SQLDMO.Application sqlDmoApplication = new SQLDMO.Application();
SQLDMO.NameList serverList;
serverList = sqlDmoApplication.ListAvailableSQLServers();
foreach(string serverName in serverList)
{
Console.WriteLine(serverName);
}
}
}
}
That's it... now, as others might mention, this isn't 100% accurate, because
some servers in your network may be "hidden," and the service also has to be
started to be detected this way.
"David C via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:52A41F2E6B25A@.webservertalk.com...
>I am using C#.NET and I am writing an application where I need to display
>to
> the user in a comboBox all the SQL Server instances that can be detected
> and
> dis. I have seen many applications like Enterprise Manager that can detect
> them all. Once the user selects the instance, I would also like to get a
> list
> of all the databases stored into another combo.
> How do I do this in C#?
> Thanks for the help in advance.
> David
>
> --
> Message posted via http://www.webservertalk.com|||Aaron,
If I could give you 1000 points for giving the perfect answer I would.
This was an outstanding piece of code that worked perfectly. We have an SQL
expert here and he did not think of this. So props to you.
Thanks,
David
Aaron Bertrand [SQL Server MVP] wrote:
>Here's a very quick way to do it in C#
>Create a new console application, and add this reference:
>Project | Add Reference | COM | Microsoft SQLDMO Object Library
>using System;
>using System.Collections.Generic;
>using System.Text;
>namespace ConsoleApplication1
>{
> class Program
> {
> static void Main(string[] args)
> {
> SQLDMO.Application sqlDmoApplication = new SQLDMO.Application()
;
> SQLDMO.NameList serverList;
> serverList = sqlDmoApplication.ListAvailableSQLServers();
> foreach(string serverName in serverList)
> {
> Console.WriteLine(serverName);
> }
> }
> }
>}
>That's it... now, as others might mention, this isn't 100% accurate, becaus
e
>some servers in your network may be "hidden," and the service also has to b
e
>started to be detected this way.
>
>[quoted text clipped - 10 lines]
Message posted via http://www.webservertalk.com|||The best way to do it is using SQLBrowseConnect function from ODBC (no
SQLDMO dependency). Check the sample here:
http://www.codeproject.com/cs/database/LocatingSql.asp
Note: SQLBrowseConnect does not work if LAN is not available (cable
unplugged, etc.) while local instances are still accessible :-))
cheers,
</wqw>|||I too am looking for this same type of information. I read through the
response's and none gave me any information I could use. I've already tried
the one David said returned the information he needed. Here's what I am
looking for.
I've got SQL Server 2005 Express installed twice with 2 instances. The
first is the default SQLExpress and the second I named. We'll say
TestExpress. I know if I go into the registry under SQL Server it list both
of these instance names and I can probably retrieve this information from
there. But is there anyway to retrieve it via SQLDMO or any of the .NET SQL
references.
This code Aaron listed and David said it worked for him
SQLDMO.Application sqlDmoApplication = new SQLDMO.Application();
SQLDMO.NameList serverList;
serverList = sqlDmoApplication.ListAvailableSQLServers();
foreach(string serverName in serverList)
{
Console.WriteLine(serverName);
}
This only retrieved 2 items
{local}
the other was my computer name
Any help would be appreciated.
Joe
"David C via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:52A41F2E6B25A@.webservertalk.com...
>I am using C#.NET and I am writing an application where I need to display
>to
> the user in a comboBox all the SQL Server instances that can be detected
> and
> dis. I have seen many applications like Enterprise Manager that can detect
> them all. Once the user selects the instance, I would also like to get a
> list
> of all the databases stored into another combo.
> How do I do this in C#?
> Thanks for the help in advance.
> David
>
> --
> Message posted via http://www.webservertalk.com

Getting last value and increasing it by one

Hello,
My name is Tim and I am new here so i would just like to start of with saying hi to everyone!

the problem i have is i am using asp.net and SQL and I want to get the last value of the called vidid and increase it by one.

I have found this code
SELECT MAX(vidid) + 1
FROM dbo.Videos

I am using dreamweaver 8 and I use the test button and it submits it and comes up with the value i want.

I then press ok and go to bindings to get the value and there are no binding so i wrote the code in my self and it does not display anything not even a error msg.

I have also tryed this code

SELECT vidid + 1
FROM dbo.Videos
ORDER BY vidid DESC

This does not work aswell but if i remove the + 1 then i see that last value.

Any ideas ?? like alternate code

Thanks

Tim

Quote:

Originally Posted by Renar

Hello,
My name is Tim and I am new here so i would just like to start of with saying hi to everyone!

the problem i have is i am using asp.net and SQL and I want to get the last value of the called vidid and increase it by one.

I have found this code
SELECT MAX(vidid) + 1
FROM dbo.Videos

I am using dreamweaver 8 and I use the test button and it submits it and comes up with the value i want.

I then press ok and go to bindings to get the value and there are no binding so i wrote the code in my self and it does not display anything not even a error msg.

I have also tryed this code

SELECT vidid + 1
FROM dbo.Videos
ORDER BY vidid DESC

This does not work aswell but if i remove the + 1 then i see that last value.

Any ideas ?? like alternate code

Thanks

Tim

Hi Tim,

I'm not familiar with dreamweaver but your original SQL query should work. If it returns a value in query analyser then make sure you're binding it correctly and also check your database connection string in .net.|||

Quote:

Originally Posted by DonlonP

Hi Tim,

I'm not familiar with dreamweaver but your original SQL query should work. If it returns a value in query analyser then make sure you're binding it correctly and also check your database connection string in .net.

hello thanks for responding...
i used a sqlcommand instead and its working now but thanks anyway.|||hi
if you use this value for inserting new record in a multi user application your solution may not work.check it.

Getting Identity/Serial of Row Just Inserted?

This isn't so much purely a SQL Server question as a question on ASP.NET VB technique. In particular, I have a situation where I am either inserting a NEW row for a "profile table" (name, email, etc.) or Updating an existing one. In both cases, I need to create a new row in a related table which has the identity/serial column of the parent table as the primary key for the data to be inserted into this subsidiary table (for which there may be many rows inserted, all tying back to the parent).

At the time I do the update, of course, I have the identity/serial of the "parent" so it's easy to update/insert. However, if the profile is NEW, I need to capture the identity/serial which was inserted so as to use it for the child table insert. (I remember a call to an obscure function which was -- essentially -- "give me the identity/serial of that which was just INSERTed" but I am unable to locate equivalent functionality. (I have searched various online help files for "Insert serial", "Insert identity" and the like with no results.

Hints? Mahalos in advance ... :) KevInKauai

You can use the SCOPE_IDENTITY() function to retrieve the ID of the row just inserted. Check out books on line to read up some info on the function.

|||

@.@.IDENTITY

Returns the last-inserted identity value.

Syntax

@.@.IDENTITY

Return Types

numeric

Remarks

After an INSERT, SELECT INTO, or bulk copy statement completes, @.@.IDENTITY contains the last identity value generated by the statement. If the statement did not affect any tables with identity columns, @.@.IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @.@.IDENTITY returns the last identity value generated. If the statement fires one or more triggers that perform inserts that generate identity values, calling @.@.IDENTITY immediately after the statement returns the last identity value generated by the triggers. The @.@.IDENTITYvalue does not revert to a previous setting if the INSERT or SELECT INTO statementor bulk copy fails, or if the transaction is rolled back.

@.@.IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions in that they return the last value inserted into the IDENTITY column of a table.

@.@.IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @.@.IDENTITY is not limited to a specific scope.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope. For more information, seeIDENT_CURRENT.

Examples

This example inserts a row into a table with an identity column and uses @.@.IDENTITY to display the identity value used in the new row.

INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @.@.IDENTITY AS 'Identity'

|||

Perhaps I wasn't clear. I'm doing this in an ASP.NET application (..aspx) -- NOT in Transact-SQL -- where neither IDENTITY_CURRENT nor any of those other TRANSACT-SQL constants seems to be available.

KevInKauai

|||

KevInKauai:

NOT in Transact-SQL

Then perhaps you should convert it to a stored proc and youre life will be easier

|||

KevInKauai:

Perhaps I wasn't clear. I'm doing this in an ASP.NET application (..aspx) -- NOT in Transact-SQL -- where neither IDENTITY_CURRENT nor any of those other TRANSACT-SQL constants seems to be available.

KevInKauai

But it is still what you need to do. How exactly you implement it depends on how you're doing the code, but SCOPE_IDENTITY is what you need. (Mehedi: @.@.IDENTITY is generally not recommended, since concurrent operations can return the wrong value. SCOPE_IDENTITY() virtually always returns what you actually need.)

Are you running dynamic SQL in your ASP.NET application? Then you can tack on a call to SCOPE_IDENTITY() to the end of the query. Something like this:

string sql = "INSERT <row into primary table>; SELECT SCOPE_IDENTITY()"

Then execute the code and read the return value from the statement.

As David commented, this is all much easier and cleaner if you use a stored procedure, because then the new identity can either be returned as a single row, single column record set, or as the return value from the procedure.

In essence, you need to cause SQL Server to send you the value, which requires SCOPE_IDENTITY. But there are various ways to get it to do that.

Make sense?

Don

|||

Hi, Don - -

I prefer not to deal with Stored Procedures in general as they tend (to me) to be cumbersome and require extra steps rather than the "on-the-fly" development that I am presently dealing with.

That said, apprenting the "SCOPE_IDENTITY" to the INSERT seemed to not get an error, but where does the result come back?

1 SQL =String.Format("INSERT INTO [Parent] ([parentData]) " & _2 "VALUES ('{0}'); SELECT SCOPE_IDENTITY() ", _3 txtRowData.Text)45 SqlDataSource1.InsertCommand = SQL67Try8 SqlDataSource1.Insert()

The row got inserted (verified that), but now how do I get that identity? (Sorry to be such a blank here. This serial stuff was always obscure and I guess we can blame Chris Date for not including it more formally in the SQL definitiong.)

tia ... :) KevInKauai

Getting Identity back

in my VB.NET program, when I do the following, how can I get the value of
the primary key back
which is an Identity column?
Thanks,
T
connPO.Open()
Dim strSQL As String
strSQL = "INSERT INTO Orders " & _
"(JobID, Description, Notes, Status)" & _
"VALUES (@.JobID, @.Description, @.Notes, @.Status)"
Dim mycommand As New SqlCommand(strSQL, connPO)
mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
Try
rowsAffected = mycommand.ExecuteNonQuery()
If rowsAffected = 0 Then
Return "Rows Updated were Zero - Update was not effective"
End If
Return ""
Catch db As SqlException
If db.Number <> 2627 Then '2627 means dup add
Return db.Number & " " & db.Message
End If
Catch ex As System.Exception
Return ex.Message
Finally
connPO.Close()
End Try
(I notice you also posted in the dotnet forum as I answered there also)
Tina,
Right before you Return "" enter the following code
'now get the identity back
strSQL = "Select @.@.IDENTITY as 'Identity'"
Dim GetIDCommand As New SqlCommand(strSQL, connPO)
Dim myReturn as integer = GetIDCommand.ExecuteScalar
Regards,
Gary Blakely
Dean Blakely & Associates
www.deanblakely.com
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:ejJHsp65FHA.3312@.TK2MSFTNGP15.phx.gbl...
> in my VB.NET program, when I do the following, how can I get the value of
> the primary key back
> which is an Identity column?
> Thanks,
> T
> connPO.Open()
> Dim strSQL As String
> strSQL = "INSERT INTO Orders " & _
> "(JobID, Description, Notes, Status)" & _
> "VALUES (@.JobID, @.Description, @.Notes, @.Status)"
> Dim mycommand As New SqlCommand(strSQL, connPO)
> mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
> mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
> mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
> mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
> Try
> rowsAffected = mycommand.ExecuteNonQuery()
> If rowsAffected = 0 Then
> Return "Rows Updated were Zero - Update was not effective"
> End If
> Return ""
> Catch db As SqlException
> If db.Number <> 2627 Then '2627 means dup add
> Return db.Number & " " & db.Message
> End If
> Catch ex As System.Exception
> Return ex.Message
> Finally
> connPO.Close()
> End Try
>
|||"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:ejJHsp65FHA.3312@.TK2MSFTNGP15.phx.gbl...
> in my VB.NET program, when I do the following, how can I get the value of
> the primary key back
> which is an Identity column?
> Thanks,
> T
> connPO.Open()
> Dim strSQL As String
> strSQL = "INSERT INTO Orders " & _
> "(JobID, Description, Notes, Status)" & _
> "VALUES (@.JobID, @.Description, @.Notes, @.Status)"
> Dim mycommand As New SqlCommand(strSQL, connPO)
> mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
> mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
> mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
> mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
> Try
> rowsAffected = mycommand.ExecuteNonQuery()
> If rowsAffected = 0 Then
> Return "Rows Updated were Zero - Update was not effective"
> End If
> Return ""
> Catch db As SqlException
> If db.Number <> 2627 Then '2627 means dup add
> Return db.Number & " " & db.Message
> End If
> Catch ex As System.Exception
> Return ex.Message
> Finally
> connPO.Close()
> End Try
>
You need to call SCOPE_IDENTITY in the same batch that does the INSERT:
strSQL = "INSERT INTO Orders "
...
strSQL = strSQL + "; SELECT SCOPE_IDENTITY()"
The result set is the IDENTITY value. There are a couple of points to note.
Firstly, SCOPE_IDENTITY is SQL Server 2000 / 2005 only. In SQL Server 7
you'll have to use @.@.IDENTITY. Using @.@.IDENTITY means that the return value
will reflect any INSERT done in a trigger if one exists on your table.
That's generally not what is wanted, so in the case of SQL Server 7 on a
table with a trigger you'll have to use a different technique: Issue a
SELECT with a WHERE clause based on alternate key values from among those
you inserted. IDENTITY should never be the only key of a table so it should
always be possible to retrieve the value without using either SCOPE_IDENTITY
or @.@.IDENTITY. For single row inserts however, SCOPE_IDENTITY is more
efficient.
Secondly, the above is bad advice :-). Any INSERT should be done with a
parameterized stored proc rather than dynamically in client code unless you
have an exceptional reason to do otherwise. Since your code is parameterized
anyway I don't know why you wouldn't use a proc here.
I notice you posted independently to at least two other groups.
Multi-posting is very inconsiderate and devalues the newsgroup experience
for everyone. If you really must hit several different groups with your
question then it's better to cross-post - i.e. the same message copied to
multiple groups so that there is just a single thread to continue the
discussion in. Don't cross-post excessively, but 1 or 2 well-chosen groups
in a cross-post is generally acceptable whereas pretty everyone hates
multi-posting.
Hope this helps.
David Portas
SQL Server MVP
|||> I notice you posted independently to at least two other groups.
Sorry. I think you posted twice to the SAME group actually. It was still a
multi-post to this one though.
David Portas
SQL Server MVP
|||To top it off, I gave Tina the same answer as you YESTERDAY in the adonet
group. Go figure.
Greg
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:T_qdnTqwkN2VouveRVnytw@.giganews.com...
> Sorry. I think you posted twice to the SAME group actually. It was still a
> multi-post to this one though.
> --
> David Portas
> SQL Server MVP
> --
>
|||David,
I posted to one other group - the adonet group. The groups behave
differently so I do that to try to get best answers. The SQL Server group
always replys and very quickly but they are not always ADO oriented - I
ususally get T_SQL type answers. The adonet group doesn't answer so fast or
so well but they are ado oriented.
I didn't know that was against the rules. Sorry.
T
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:T_qdnTqwkN2VouveRVnytw@.giganews.com...
> Sorry. I think you posted twice to the SAME group actually. It was still a
> multi-post to this one though.
> --
> David Portas
> SQL Server MVP
> --
>