New Install VS 2005 Pro & SQL Server 2005 Dev Problem

VS looks OK and MSSQLSERVER and SQLEXPRESS etc. show in 'Services/Local' as started/automatic.

I am trying to do Management Studio Tutorial. For the change from SEM.

The problem is in opening SQL Server Management Studio there is a dialog

'Connect to Server' showing Type = Database engine, Authentication = Windows Authentication, Server name = blank.

When I enter Server name = MSSQLSERVER and press Connect I get an error message 'Cannot connect to MSSQLSERVER… Error 53.

How do I get to see the tutorial and recognise the SQL?

XP.Pro on a big fast machine for Web development all on the one machine

Some experience of SQL Server on W2K and W2K3 with VS. Notice the SQL Server does not show in the task bar as before.

To answer my own question in case it helps others.

This looks like Catch22 at first sight.

In 'Connect to Server' dialog Click 'Options'.

See a change to two tabs, 'Local' and 'Connection Properties'.

Click 'Server Name' and browse for more. Click.

Shows Browse for Servers and my two instances, with names different to what shows in Services. Is this for security?

Select, click Connect and the SSMC shows with 7 items, hurray.

The annoying thing is there is nothing to indicate how to do this on the dialog.

The Help topic of the dialog name does not help, as usual.

Polite comment. I notice there are few queries similar to my question in this forum. Does this mean I am unusual or people have a better place to go for such.

I will certainly be a frequent user of the forum for Web applications in VS/VB.

|||Thank you for sharing your solution. it may help others in the future.

Derekfoxes wrote:

Polite comment. I notice there are few queries similar to my question in this forum. Does this mean I am unusual or people have a better place to go for such.

There are a lot of people here who are very conversant with SQL Server. But installation and setup for SQL Server 2005 is relatively new and we're not yet used to all of the common pitfalls and their solutions. TheSQL Server newsgroups are probably the best place for that level of question as more of the SQL Server MVPs tend to hang out there, or perhaps theSQL Server Forums at the MSDN Forums.|||

Thanks for the reply and the two sites.

Trying to install Adventure Works now.

Tiny point, the forum ack and notifications are arriving duplicated.

|||

Derekfoxes wrote:

Tiny point, the forum ack and notifications are arriving duplicated.

Thanks for the alert; we areaware of the problem and the source code update should be forthcoming.

New Install - Log Says Eval Expired, But It's Not an Eval

I have installed the standard edition of SQL2005 w Reporting Services on my XP Pro desktop. SQL2005 works fine, but SSRS will not. In the log, I see the message that the evaluation copy has expired--but the install is not an eval copy. I have tried uninstalling SQL Server a couple of times, but no change. Anyone know why this is happening?

What is the errors you are getting with trying to work with RS?

The message you see in the log file does not apply if you are installing any version except the Evaluation version.

|||Another log displays the message "object not set to instance of an object".

New Id

I am using Microsoft Visual Studios 2005 Pro creating a web form in asp.net with a vb.net code page. I am trying to get the id of a newly inserted record. I am using the sqldatasource insert command to insert the record. Does anybody know how to return the new id. during the insert? Thanks, Any help is appreciated

there are basically 2 ways of doing that.

1. Create ID before you do insert using something like SELECT Max(id) +1 FROM ... Use this approach if you ID field is not set as a identity column

2. If your id field is an identity field then you can write a stored procedure that inserts the record and then you do something like

SET @.ID = scope_identity()

Where @.ID is your output parameter or you can return it as a return value.

|||

Here is a modified sample from Quickstarts tutorial with inline SQL for insert (ContactID is the Identity field of the table):

ProtectedSub SqlDataSource1_Inserted(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)

Response.Write("Record Inserted: " + Server.HtmlEncode(e.Command.Parameters("@.ContactID").Value.ToString()) +"<br/>")

EndSub

<asp:GridViewAutoGenerateColumns="False"DataKeyNames="ContactID"DataSourceID="SqlDataSource1"

ID="GridView1"runat="server">

<Columns>

<asp:CommandFieldShowDeleteButton="True"/>

<asp:BoundFieldDataField="ContactID"HeaderText="ContactID"InsertVisible="False"

ReadOnly="True"SortExpression="ContactID"/>

<asp:BoundFieldDataField="ContactName"HeaderText="ContactName"SortExpression="ContactName"/>

</Columns>

</asp:GridView>

<asp:SqlDataSourceConnectionString="<%$ ConnectionStrings:MSDN_forumConnectionString %>"ID="SqlDataSource1"

runat="server"SelectCommand="select ContactID, ContactName from Contacts"

InsertCommand="INSERT INTO Contacts (ContactName) VALUES (@.contactName);SELECT @.contactID = Scope_Identity()"

OnInserted="SqlDataSource1_Inserted"DeleteCommand="DELETE FROM Contacts WHERE ContactID = @.ContactID">

<InsertParameters>

<asp:ParameterName="contactName"Type="String"/>

<asp:ParameterDirection="Output"Name="contactID"Type="Int32"/>

</InsertParameters>

<DeleteParameters>

<asp:ParameterName="ContactID"/>

</DeleteParameters>

</asp:SqlDataSource>

<br/>

<asp:DetailsViewAutoGenerateRows="False"DataKeyNames="ContactID"DataSourceID="SqlDataSource1"

DefaultMode="Insert"HeaderText="Insert New Contact"Height="50px"ID="DetailsView1"

runat="server"Width="125px">

<Fields>

<asp:BoundFieldDataField="ContactID"HeaderText="ContactID"InsertVisible="False"

ReadOnly="True"SortExpression="ContactID"/>

<asp:BoundFieldDataField="ContactName"HeaderText="ContactName"SortExpression="ContactName"/>

<asp:CommandFieldShowInsertButton="True"/>

</Fields>

</asp:DetailsView>

|||

orka777:

there are basically 2 ways of doing that.

1. Create ID before you do insert using something like SELECT Max(id) +1 FROM ... Use this approach if you ID field is not set as a identity column

2. If your id field is an identity field then you can write a stored procedure that inserts the record and then you do something like

SET @.ID = scope_identity()

Where @.ID is your output parameter or you can return it as a return value.

For Number 1, is correct but might no be that good even it will do the job for you.

For Number 2, the code should be like:

1SET @.MyIdentity =@.@.IDENTITY

Where @.MyIdentity in an output parameter of type int.

Here I prefere to not return the identity of the inserted record as an output parameter, I perefere to return it using the Return statment in the stored procedure., like:

1CREATE PROCEDURE MySP23AS45SET NOCOUNT ON67declare MyIdentityint-- as a normal variable in the stored procedure body89Insert Into MyTable (col1, col2)values ('A','B')10SET @.MyIdentity =ISNULL(@.@.IDENTITY,-1)-- if no record is inserted then you will get -1111213Return @.MyIdentity1415SET NOCOUNT OFF16

Good luck.|||

I am trying to do this through the sqldatasource1.insert statement that I have built using the querybuilder. When I try to put in the @.ID as an output or as a return value It says I need to declare the scalar variable. I have already added in the parameters area and it is set to output with the control being the textbox that I want the Id in. But when I add the line "SET @.NewID = scope_identity()" or "SET @.NewID = @.@.identity" then I get an error on both @.NewId and @.SN which is another scalar variable which normally works fine and works fine again after I pull that line out. But I'm not doing it as a stored procedure. Could that be the problem? I don't know about stored procedures. How do I create them?

|||

Carehalf:

I don't know about stored procedures. How do I create them?

Just check my last post, it is a complete running fine example.

Anyway.. here is a very simple example:

1CREATE PROCEDURE MyStoredProcedureName2@.MyInputParameterint,-- input parameter of type integer3@.MyOutputParameterint OUTPUT-- output parameter of type integer45AS67-- write you statments here8-- and here9-- and here as well :)1011-- how to set a value for ourput parameter12SET @.MyOutputParameter = 100-- just for example1314-- how to increment the input parameter by 1 (for example)15SET @.MyInputParameter = @.MyInputParameter + 1161718/* At the end you the @.MyOutputParameter output parameter value19 will be 100 (as per the logic in this simple example20 Did you see how easy it is to created a stored procedure? */21

Good luck.

|||

I understand the stored procedure from looking at your last statement and this one, but I am not sure where to create it

|||

Carehalf:

I understand the stored procedure from looking at your last statement and this one, but I am not sure where to create it

Create you stored procedures in your application/project database.

Example:

USE MyDatabase
GO

Then the CREATE PROCEDURE statment as in my last two posts.

Good luck.

|||

Carehalf:

Create a table in your database and copy the code sample I posted (change the connection string in the code) and check the syntax to work for you if you don't want to create the Stored Procedure.

Here is the table definition:

CREATETABLE [dbo].[Contacts](

[ContactID] [int]IDENTITY(1,1)NOTNULL,

[ContactName] [nvarchar](50)

)