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 startec with Integration Services

I am having trouble starting an Integrated Services Project.

I have just installed Visual Studio 2005 in order to use ASP 2.0 for web development, but my new web hosting package includes SQL Server 2005, rather than SQL 2000, which I have been using. I need DTS to upload a database to the production server, and I think I now need an Integration Services Project.

I have installed the Developer version of SQL Server 2005, and the install program says that Integration Servives are installed. However, when I open the SQL Server Business Intelligence Studio, I seem to get Visual Studio without Intregration Services. When I open a new project, there is no template for an Integration Services project. Can anyone help?

The Integration Services project template should be there, look for it under Business Intelligence Projects folder.|||

Many thanks. I have managed to create a new project now

David

sql

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 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 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 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 records and output parameter from ASP

I have a need for a stored procedure to return a recordset AND an output parameter that contains the count of records in the recordset.

I can get either but not both. (ie. if there is a select then there is no output parameter)

The stored procedure is:

ALTER PROCEDURE test
(
@.Msgs nvarchar(150) OUTPUT
)

As
declare @.selCount int
SELECT folderLocation, externalDocsID from externalDocs

set @.selCount = @.@.ROWCOUNT
IF @.@.ROWCOUNT = 0
BEGIN
SET @.Msgs = 'No folders meet this selection criteria'
RETURN
END
else
BEGIN
SET @.Msgs = @.selCount
END
return

-------------------------

the asp code is

Dim cnnStoredProc ' Connection object
Dim cmdStoredProc ' Command object
Dim rstStoredProc ' Recordset object
Dim folderText
Set cnnStoredProc = Server.CreateObject("ADODB.Connection")
cnnStoredProc.Open db
' get the correct records for a page
Set cmdStoredProc = Server.CreateObject("ADODB.Command") ' Create Command object we'll use to execute the SP
cmdStoredProc.ActiveConnection = cnnStoredProc ' Set our Command to use our existing connection
cmdStoredProc.CommandText = "test" ' Set the SP's name and tell the Command object
cmdStoredProc.CommandType = adCmdStoredProc
cmdStoredProc.Parameters.Refresh

' ---- SET PARAMETERS --GET A PAGE WORTH OF RECORD---------------------
set prop=ADODB.Parameter
cmdStoredProc.Parameters("@.functionCode").Value = "L" 'functionCode
cmdStoredProc.Parameters("@.customerID").Value = customerId 'CustomerID
if searchCriteria <> "" then
cmdStoredProc.Parameters("@.searchcriteria").Value = searchCriteria 'search string
end if
if showAssigned = "Y" then
cmdStoredProc.Parameters("@.excludeDefined").Value = "Y" '
end if
set rstStoredProc = cmdStoredProc.Execute
'

response.write("msgs=" & cmdStoredProc("@.Msgs")) ' <<<<<<< THIS DOESN'T WORK------------------------------

I can enumerate through the recordset but the output parameter is blank.

Any Ideas

Thanks

Quote:

Originally Posted by Mike Lester

I have a need for a stored procedure to return a recordset AND an output parameter that contains the count of records in the recordset.

I can get either but not both. (ie. if there is a select then there is no output parameter)

The stored procedure is:

ALTER PROCEDURE test
(
@.Msgs nvarchar(150) OUTPUT
)

As
declare @.selCount int
SELECT folderLocation, externalDocsID from externalDocs

set @.selCount = @.@.ROWCOUNT
IF @.@.ROWCOUNT = 0
BEGIN
SET @.Msgs = 'No folders meet this selection criteria'
RETURN
END
else
BEGIN
SET @.Msgs = @.selCount
END
return

-------------------------

the asp code is

Dim cnnStoredProc ' Connection object
Dim cmdStoredProc ' Command object
Dim rstStoredProc ' Recordset object
Dim folderText
Set cnnStoredProc = Server.CreateObject("ADODB.Connection")
cnnStoredProc.Open db
' get the correct records for a page
Set cmdStoredProc = Server.CreateObject("ADODB.Command") ' Create Command object we'll use to execute the SP
cmdStoredProc.ActiveConnection = cnnStoredProc ' Set our Command to use our existing connection
cmdStoredProc.CommandText = "test" ' Set the SP's name and tell the Command object
cmdStoredProc.CommandType = adCmdStoredProc
cmdStoredProc.Parameters.Refresh

' ---- SET PARAMETERS --GET A PAGE WORTH OF RECORD---------------------
set prop=ADODB.Parameter
cmdStoredProc.Parameters("@.functionCode").Value = "L" 'functionCode
cmdStoredProc.Parameters("@.customerID").Value = customerId 'CustomerID
if searchCriteria <> "" then
cmdStoredProc.Parameters("@.searchcriteria").Value = searchCriteria 'search string
end if
if showAssigned = "Y" then
cmdStoredProc.Parameters("@.excludeDefined").Value = "Y" '
end if
set rstStoredProc = cmdStoredProc.Execute
'

response.write("msgs=" & cmdStoredProc("@.Msgs")) ' <<<<<<< THIS DOESN'T WORK------------------------------

I can enumerate through the recordset but the output parameter is blank.

Any Ideas

Thanks

i believe RecordSet have a property called RecordCount...

example:
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("northwind.mdb"))

set rs=Server.CreateObject("ADODB.recordset")
sql="SELECT * FROM Customers"
rs.Open sql,conn

if rs.Supports(adApproxPosition)=true then
i=rs.RecordCount
response.write("The number of records is: " & i)
end if

rs.Close
conn.Close
%>

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 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 error context from executed package

Background:

I am executing a package programmatically from ASP.NET using C#. The package is being loaded from SQL Server at runtime and then it is executed from a custom class written in C#.

Problem:

When a task fails, I'd like information on why it failed. I read about implemeting the IDTSEvents interface as a way to have my package call back to code. This works fine (I have code in the OnTaskFailed event handler), but I'm not sure how to find out why a task failed. Since my task is loading a flat file to SQL Server, failure will likely come in one of two flavors: either the file format will be wrong or the data will violate a database constraint. In etiher case, I'd like that feedback. I am looking that the TaskHost object that gets passed to my event handler and I haven't figured out how to access this information. Am I going about this the right way? If yes, how do I access the error information I am looking for?

Try the OnError event handler. There are variables scoped to that eventhandler that contain the error code and error description.

-Jamie

|||

OK. That works fine. I can now get the description of the errror. When I get an error, I'd like to stop executing the package and surface the error to the through the UI (which happens to be a web page). There are a couple of things I noticed with this call back to the OnError event handler:

The event handler is triggered multiple times even though I return false, which is supposed to cancel the task. What I'd like is to abort the execution of the package (not just cancel the task). I tried to throw a custom exception within the handler, but the exception doesn't propogate from the OnError handler to my calling code (the code that executes the package) and it doesn't stop the handler from being called multiple times.

Any ideas?