New to MSDE (but not SQL)

All,
looking to distribute MSDE with my ASP.NET app... looking for two pieces:
1) Is there a way to install MSDE with a DB already attached? I want to
install MSDE and then attach a DB, but attaching has to come after a
reboot(to start after services restart), and I would rather do it all at the
same time...
2) Good Freeware/Almost-freeware MSDE manager I can redistribute with my app
for the client
MTIA
wardeaux
> 2) Good Freeware/Almost-freeware MSDE manager I can redistribute with my
app
> for the client
http://www.aspfaq.com/2442
http://www.aspfaq.com/
(Reverse address to reply.)
|||1) Nope. You don't have to reboot the computer when you install MSDE though,
strictly speaking.
2) SQL Server Web Data Administrator:
http://www.microsoft.com/downloads/d...displaylang=en
Jacco Schalkwijk
SQL Server MVP
"Wardeaux" <wardeaux@.bellsouth.net> wrote in message
news:e10zJ72jEHA.3196@.TK2MSFTNGP10.phx.gbl...
> All,
> looking to distribute MSDE with my ASP.NET app... looking for two pieces:
> 1) Is there a way to install MSDE with a DB already attached? I want to
> install MSDE and then attach a DB, but attaching has to come after a
> reboot(to start after services restart), and I would rather do it all at
> the
> same time...
> 2) Good Freeware/Almost-freeware MSDE manager I can redistribute with my
> app
> for the client
> MTIA
> wardeaux
>
|||You don't necessarily need to reboot after installing. To start MSDE after
installing, just run "net start mssql$instancename" (if installing as a
named instance) or use scm.exe. You may want to start SQL Server Agent
also.
Distribute your database's .mdf and .ldf files and attach them using
osql.exe to execute the sp_attach_db stored procedure.
"Wardeaux" <wardeaux@.bellsouth.net> wrote in message
news:e10zJ72jEHA.3196@.TK2MSFTNGP10.phx.gbl...
> All,
> looking to distribute MSDE with my ASP.NET app... looking for two
pieces:
> 1) Is there a way to install MSDE with a DB already attached? I want to
> install MSDE and then attach a DB, but attaching has to come after a
> reboot(to start after services restart), and I would rather do it all at
the
> same time...
> 2) Good Freeware/Almost-freeware MSDE manager I can redistribute with my
app
> for the client
> MTIA
> wardeaux
>
sql

New to MS SQL Server, Need help with Windows Authentication

I am new to the whole ASP .NET scene, so my knowledge is very limited, and if I’m not clear enough please let me know.

I am reading a book published by APress about ASP .NET with C# and I am at the point where I get to begin working with data (fun!!). The problem is that I’m not able to connect to the SQL Server. I have Server 2003 and SQL Server 2005 running on a separate machine than the computer that I use for development – the server is named THESERVER, the SQL server is named THESQLSERVER, and the computer I use for development is my laptop named MYLAPTOP.

So here’s my connection string (I put this in my web.config XML file):

<add name="Pubs" connectionString="DataSource=THESERVER\THESQLSERVER;
 Initial Catalog=pubs;Integrated Securit=SSPI"/> 

Now here’s what my book reads: For Windows authentication to work, the currently logged-on Windows user must have the required authorization to access the SQL database. This is all it says about Windows authentication because the book assumes that I am running MS SQL Express off localhost.

Questions:

Does the book mean that I will have to add a user to my server for my laptop? If so, how do I log into this user using the connection string?What does it mean by “the current logged-on Windows user”? Is that referring to the user on my laptop, or a user on the server?

I’ve been reading around trying to find more information on exactly how Windows authentication works, but I keep coming up dry. I know that much of this is probably trivial to a lot of you, which is why I am asking because it isn’t to me.

Well, thanks in advance for any help that you can provide me.

<add name="Pubs" connectionString="DataSource=192.168.1.56;
 userid=sa; password=mypassword"/> 

In the connecting string you need to specify an ip address a user name and password if the SQL

runs in another computer, if you got integrated windows authentication is another story, but I would try with SQL authentication

|||

Thanks for the response.

I know that I can do itthis way - using SQL authentication instead - but I was hoping thatthey may be some way of doing it through the Integrated WindowsAuthentication method. The only reason why I want to do it thisway is because of the claim that it is much more secure this way. If anyone can say otherwise and prove that it isn't that much moresecure, that is relatively the same, please tell me :D. That isthe only reason that is holding me back from using th SQLauthentication approach.

Thanks again for the quick response, I really appreciate it.

|||Anyone else with suggestions/solutions?|||Actually setting it to InProc, or ServerState is as secure as SQL Sessin State|||Setting which, MS SQL Server? How do I do that?

new to .net MSDE (database) question ?

Hi, I am new to .net, I installed .net sdk and framework.
now I downloaded MSDE and installed it, I got this directory : C:\MSDERelA
but when I double click in the setup file nothing happened, please help me on what should I do .
thanksMay be missing parameters.

Open the ReadmeMSDE2000A.htm and read under the '3.4.4 MSDE 2000 Release A Installation Examples'.

new Reporting Services

I just read an article online about SQL Server Reporting Services. Any
sense of whether this functionality can be embeded in an ASP.NET web
application? The article made it sound like you'd have to go to a separate
web page in order to view the reports ...
http://www.sqlmag.com/articles/index.cfm?articleid=40529
Thanks in advance.
MarkYes you can. Have a look at the developer samples (if you are on B2). They
include a ReportViewer Sample ASP.NET Server Control for integrating
Reporting Services reports in a Web application. RS is basically a web
service so you can do pretty much what you want with it. Flexibility and
extensibility are key features
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Mark" <mfield@.idonotlikespam.cce.umn.edu> wrote in message
news:uOCOw7rsDHA.2408@.tk2msftngp13.phx.gbl...
I just read an article online about SQL Server Reporting Services. Any
sense of whether this functionality can be embeded in an ASP.NET web
application? The article made it sound like you'd have to go to a separate
web page in order to view the reports ...
http://www.sqlmag.com/articles/index.cfm?articleid=40529
Thanks in advance.
Mark

new problem - RESTORE

I thought I have successfully restored a backup from another instance of
MSDE, however in my .NET program I am getting an error message about the
login failed. So what I did is create a login id using sp_grantlogin. I
assume that this allows my Windows login to be authenticated. Just in case,
I also added a new login account using sp_addlogin. Those both worked.
Now when I do...EXEC sp_grantaccess to the database, I get an error:
Database cannot be opened. It is in the middle of a restore.
When I was restoring, I did the RESTORE FILELISTONLY to get the names of the
files in the backup. I then did RESTORE DATABASE using WITH and MOVE which
announced it was successful, and the two files were placed into the
directory. I thought this was it, what did I miss?
Thanks,
Brad
Make sure you use WITH RECOVERY (the default) othewise the database will be
left in a state where it is expecting to continue the restore process using
additional backup sets. Also if you are moving databases from one server to
another you will need to synchronize the users using sp_change_users_login
(see the BOL for this one).
Jim
"AllcompPC" <allcomppc@.sbcglobal.net> wrote in message
news:%23LZHQ0HEFHA.624@.TK2MSFTNGP09.phx.gbl...
>I thought I have successfully restored a backup from another instance of
> MSDE, however in my .NET program I am getting an error message about the
> login failed. So what I did is create a login id using sp_grantlogin. I
> assume that this allows my Windows login to be authenticated. Just in
> case,
> I also added a new login account using sp_addlogin. Those both worked.
> Now when I do...EXEC sp_grantaccess to the database, I get an error:
> Database cannot be opened. It is in the middle of a restore.
> When I was restoring, I did the RESTORE FILELISTONLY to get the names of
> the
> files in the backup. I then did RESTORE DATABASE using WITH and MOVE
> which
> announced it was successful, and the two files were placed into the
> directory. I thought this was it, what did I miss?
> Thanks,
> Brad
>
|||Thank you Jim. I got it and it seems to be working fine.
Brad
"Jim Young" <thorium48@.hotmail.com> wrote in message
news:eY58HwIEFHA.228@.tk2msftngp13.phx.gbl...
> Make sure you use WITH RECOVERY (the default) othewise the database will
be
> left in a state where it is expecting to continue the restore process
using
> additional backup sets. Also if you are moving databases from one server
to[vbcol=seagreen]
> another you will need to synchronize the users using sp_change_users_login
> (see the BOL for this one).
> Jim
> "AllcompPC" <allcomppc@.sbcglobal.net> wrote in message
> news:%23LZHQ0HEFHA.624@.TK2MSFTNGP09.phx.gbl...
I
>

New Oracle Data Provider for .NET (ODP .NET) 10.2.0.2.20 failure.

Oracle recently released Oracle Data Provider for .NET (ODP .NET) 10.2.0.2.20. It happened on August 1, 2006. Oracle claims that its software is compliant with Microsoft ADO.NET 2.0 and “…more flexible, faster, and more stable….”. On top of it Oracle introduced many new features “… not available from other .NET drivers…”.

http://www.oracle.com/technology/tech/windows/odpnet/index.html

I decided to give it a shot and try in SSIS.

I installed ODP.NET on my machine and it works just fine in PL/SQL Developer

Next, I opened Data Source Wizard in BIDS for SSIS project.

I see Oracle Data Provider for .NET option is enlisted under .NET Providers node on a tree. But when I select it, I am getting an error dialog:

Failed to find or load the registered .Net Framework Data Provider.

So what could be done in such scenario?

Regards,

Yitzhak

The message seems to say the provider just cannot be found. These seems a bit extreme, but you could find out what has been registered, and try and see if the assembly is then available.

Managed providers are registered in C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG\machine.config. There will be a section that looks a bit like this -

<system.data>
<DbProviderFactories>
<add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc" type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="OleDb Data Provider" invariant="System.Data.OleDb" description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="OracleClient Data Provider" invariant="System.Data.OracleClient" description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="SQL Server CE Data Provider" invariant="Microsoft.SqlServerCe.Client" description=".NET Framework Data Provider for Microsoft SQL Server 2005 Mobile Edition" type="Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
</DbProviderFactories>
</system.data>

This shows that I have 5 managed providers, and it also gives the strong name for each. They should then be available in the GAC. A simple application that loads an assembly by it's string name could be use dto check that it really is available.

This seems somewhat excessive, and there should be simpler explanation, but the documentation didn't make too much sense to me.

|||

I checked machine.config file on my machine.
Here is what it has:

<system.data>
<DbProviderFactories>
<add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc"
type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<add name="OleDb Data Provider" invariant="System.Data.OleDb"
description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<add name="OracleClient Data Provider" invariant="System.Data.OracleClient"
description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<add name="SqlClient Data Provider" invariant="System.Data.SqlClient"
description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<add name="Oracle Data Provider for .NET" invariant="Oracle.DataAccess.Client"
description="Oracle Data Provider for .NET" type="Oracle.DataAccess.Client.OracleClientFactory, Oracle.DataAccess, Version=2.102.2.20, Culture=neutral, PublicKeyToken=89b483f429c47342" />
</DbProviderFactories>
</system.data>

I also checked c:\winnt\assembly directory (GAC)
Oracle.DataAccess version 2.102.2.20 is there.

Regards,
Yitzhak

|||

There is some new development on the subject.

Oracle Data Provider for .NET (ODP .NET) 10.2.0.2.20 comes with the sample projects for Visual Studio 2005. I decided to try one of them - DSPopulate. It issues a single SQL statement and populates a data grid for Win Form app. Long story short, the sample application complies and works. References node in VS2005 shows a reference to Oracle Data provider.

I reopened my SSIS project and went through the same steps to create a new Data Source. I picked Oracle Data Provider for .NET. And here is some new behavior. Connection tab shows the following message:

Connection page is not available. Please switch to use ‘All’ properties tab page to set the properties of connection string.

So I switched to All tab and was able to set needed parameters and successfully test the connection.

I tested Oracle ODP.NET based connection with the Data Reader Source task. It works.

Regards,

Yitzhak

|||

Hi,

We also installed ODP.Net in my system however after the installation our usual oracle providers didnt worked. Started showing error when we used other oracle providers(Oracle Provider, Microsoft oracle provider).

Whether we can use ODP.NET as destination? If so, how can we use it?

Please help me in this regarding

Thanks

New Oracle Data Provider for .NET (ODP .NET) 10.2.0.2.20 failure.

Oracle recently released Oracle Data Provider for .NET (ODP .NET) 10.2.0.2.20. It happened on August 1, 2006. Oracle claims that its software is compliant with Microsoft ADO.NET 2.0 and “…more flexible, faster, and more stable….”. On top of it Oracle introduced many new features “… not available from other .NET drivers…”.

http://www.oracle.com/technology/tech/windows/odpnet/index.html

I decided to give it a shot and try in SSIS.

I installed ODP.NET on my machine and it works just fine in PL/SQL Developer

Next, I opened Data Source Wizard in BIDS for SSIS project.

I see Oracle Data Provider for .NET option is enlisted under .NET Providers node on a tree. But when I select it, I am getting an error dialog:

Failed to find or load the registered .Net Framework Data Provider.

So what could be done in such scenario?

Regards,

Yitzhak

The message seems to say the provider just cannot be found. These seems a bit extreme, but you could find out what has been registered, and try and see if the assembly is then available.

Managed providers are registered in C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG\machine.config. There will be a section that looks a bit like this -

<system.data>
<DbProviderFactories>
<add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc" type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="OleDb Data Provider" invariant="System.Data.OleDb" description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="OracleClient Data Provider" invariant="System.Data.OracleClient" description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="SQL Server CE Data Provider" invariant="Microsoft.SqlServerCe.Client" description=".NET Framework Data Provider for Microsoft SQL Server 2005 Mobile Edition" type="Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
</DbProviderFactories>
</system.data>

This shows that I have 5 managed providers, and it also gives the strong name for each. They should then be available in the GAC. A simple application that loads an assembly by it's string name could be use dto check that it really is available.

This seems somewhat excessive, and there should be simpler explanation, but the documentation didn't make too much sense to me.

|||

I checked machine.config file on my machine.
Here is what it has:

<system.data>
<DbProviderFactories>
<add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc"
type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<add name="OleDb Data Provider" invariant="System.Data.OleDb"
description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<add name="OracleClient Data Provider" invariant="System.Data.OracleClient"
description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<add name="SqlClient Data Provider" invariant="System.Data.SqlClient"
description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<add name="Oracle Data Provider for .NET" invariant="Oracle.DataAccess.Client"
description="Oracle Data Provider for .NET" type="Oracle.DataAccess.Client.OracleClientFactory, Oracle.DataAccess, Version=2.102.2.20, Culture=neutral, PublicKeyToken=89b483f429c47342" />
</DbProviderFactories>
</system.data>

I also checked c:\winnt\assembly directory (GAC)
Oracle.DataAccess version 2.102.2.20 is there.

Regards,
Yitzhak

|||

There is some new development on the subject.

Oracle Data Provider for .NET (ODP .NET) 10.2.0.2.20 comes with the sample projects for Visual Studio 2005. I decided to try one of them - DSPopulate. It issues a single SQL statement and populates a data grid for Win Form app. Long story short, the sample application complies and works. References node in VS2005 shows a reference to Oracle Data provider.

I reopened my SSIS project and went through the same steps to create a new Data Source. I picked Oracle Data Provider for .NET. And here is some new behavior. Connection tab shows the following message:

Connection page is not available. Please switch to use ‘All’ properties tab page to set the properties of connection string.

So I switched to All tab and was able to set needed parameters and successfully test the connection.

I tested Oracle ODP.NET based connection with the Data Reader Source task. It works.

Regards,

Yitzhak

New in MSDE, Please help!

Hi,

I am newbie in ASP.NET and MSDE arena. I understand that MSDE has no GUI and I need to have MSDE tools to work with it. I have already installed ASP.NET 1.1 and Microsoft.NET SDK 1.1 and MSDE in my Windown Professional 2000 PC and I have run and tested for .aspx and its working perfectly. But I want to use MSDE to create some database driven web applications.

Could you please help me how I can do that? BTW, I installed Microsoft SQL Web Data Administrator and its giving me SQLWebAdminStart.exe Application error and asking for debugging while opening the program. I am stranded what to do now?

I really appreciate your help.

regards,
Peter-Toronto
(Linux, PHP, MySQL)Hello Peter again,

I want to ask you which tools are you using to build your ASP.net application. If you'r using WebMAtrix project, then you can easly create your own database by going to Data Tab(located on the right section of the webmatrix page). Then go to "Aadd database connection". from there you can choose whether you want to create MSDE or Access databases. finally, you can go through the next steps to create your database. It is easy steps.

I hope it is clear now.

new in .Net but learning SqlConnection, SqlCommand and SqlDataReader How in code_behind

Hi.

I want to now a little about CodeBehind how to use it with SqlConnection, SqlCommand and SqlDataReader.

How i use:
Web.Config
<connectionStrings>
<add name="ConnectionStringTest" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\DSNbase.mdb;Persist Security Info=True"providerName="System.Data.OleDb"/>
</connectionStrings>

Default.aspx
<asp:RepeaterID="test"runat="server"DataSourceID="SqlDataSourcetest">
...
..
<asp:SqlDataSourceID="SqlDataSourcetest"runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionStringTest %>"
ProviderName="<%$ ConnectionStrings:ConnectionStringTest.ProviderName %>"
SelectCommand="SELECT [ID], [Name], [Sex], [Born], [Color], [Dog], [Images] FROM [Dogs] WHERE ([Dog] = 'YES') ORDER BY [ID]">
</asp:SqlDataSource>

How can i take my asp:SqlDataSource and put it in my CodeBehind, so it use the ConnectionString from my Web.Config and so i can use my Repeater on my default.aspx site.. !?
Maybe if u got a great link that show "How to do" maybe om MSDN/MSDN2.

You can use a datareader. Heres an example i put together using a stored procedure:

#region StoredProcedure GetDogs
// CREATE PROCEDURE GetDogs()
// AS
// SELECT ID, Name, Sex, Born, Color, Dog, Images FROM Dogs WHERE Dog='YES' ORDER BY ID
// RETURN
#endregion

//This will get the connectionstring from the web.config
SqlConnection objConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionStringText"].ConnectionString);
SqlCommand objCmd = new SqlCommand("GetDogs", objConn);
objCmd.CommandType = CommandType.StoredProcedure;

objConn.Open();
SqlDataReader objRdr = objCmd.ExecuteReader(CommandBehavior.CloseConnection);

if (objRdr.HasRows)
{
testRepeater.DataSource = objRdr;
testRepeater.DataBind();
}
else { // do alternative if no data is present. }

objRdr.Close();
objConn.Close();

|||

Hi.

How will this look like if im using VB.

|||

This should work, but im not 100% sure, Im doing this by hand and not in VS, but its striaght forward and you should be able to see how to change it:

//This will get the connectionstring from the web.config
Dim objConn As SqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionStringText"].ConnectionString)
Dim objCmd As SqlCommand = new SqlCommand("GetDogs", objConn)
objCmd.CommandType = CommandType.StoredProcedure

objConn.Open();
Dim objRdr As SqlDataReader = objCmd.ExecuteReader(CommandBehavior.CloseConnection)

testRepeater.DataSource = objRdr
testRepeater.DataBind()

objRdr.Close()
objConn.Close()

Hope this helps.

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)

)

new database, set userid and password

I am creating a new application and just created a new database

Application: VB.net 2005/ ASP.net

Database: Sql Server 2005 with four tables

I need to set the userid and password on the database. How do I do that?

I want to be able to create a SQL connection object in my code and I have something like the following:

<CODE>

Dim objconAsNew SqlConnection("server=serverName;uid=;pwd=;database=SomeDatabase")

</CODE>

but the "uid" and the "pwd" are not set on my database. what is an easy way to do this? thanks

This site has complete connection strings in all pop db systems:http://www.connectionstrings.com/

Both Windows Auth and SqlServer Auth has examples in the site.

|||

I recommend that you use windows integrated authentication

Simply make sure that the user identify of the application pool in which your application lives has login rights to your database. Then you don't have to store the password in your config file or your code.

Check this outhttp://weblogs.asp.net/achang/archive/2004/04/15/113866.aspx

Matt

|||

Hi

Yes Matt you are right.

By windows authentication we can achieve this.

First use windows authentication, create user, give him neccessary rights, and mapped this user with your newly created database.

and now in your webconfig provide this username and password to connect this database.

I hope this will helpful u...

|||

Preferably you do not put the username and password in the web.config

If we are using windows integrated authentication, then you want to give permission to the windows account that your application runs under on the database. Then you do not have to worry about password in the config file.