how can i connect to sqlserver express manually when i use text boxed

hi guys,

i am very happy that i found this forum which discuss ASP.net.
i am new to ASP.net 2.0 and some need serious help. when i debug my application and try to insert data an error occur saying:

"Cannot open database "ecb" requested by the login. The login failed. Login failed for user 'HOME-USER\user'."

the code that i have used to submit data is as follow:

Imports System.Data.SqlClient
...
Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

Dim objConn As SqlConnection
Dim objCmd As SqlCommand

objconn =New SqlConnection("data source= localhost\sqlexpress; initial catalog =ecb; persist security info =true; trusted_connection=true")

objCmd = New SqlCommand("INSERT INTO staff (firstname, surname) VALUES (@.firstname, @.surname,)", objConn)

objCmd.Parameters.Add("@.FirstName", Data.SqlDbType.VarChar).Value = txtFirstName.Text
objCmd.Parameters.Add("@.Surname", Data.SqlDbType.VarChar).Value = txtSurname.Text

objConn.Open()
objCmd.ExecuteNonQuery()
objConn.Close()

i also used this data connection but didn't help:
objconn = New SqlConnection("Server=localhost; Database=ecb;user ID=' ';Password=' ' ") when i use this data

connection i get this message: Login failed for user ''. The user is not associated with a trusted SQL Server connection.

as far as i can remember i have used windows authentication when creating my database in sql server 200 express edition.

the funny thing is that when i use datagrid wizard or dataform the connection is fine ( i mean is automatically done for me but i don't know when i like to do it manually does not work).

can anyone help? thank you in advanced

Hi!

My guess is going to be that you have selected Mixed Authentication mode when you installed the server. Try specifying login details in your connection string. My guess is going to be 'ASPNET' for username and "" for password.

Something else:

What i could recommend you to do is to have a look at this link:http://www.asp.net/default.aspx?tabindex=4&tabid=3000. See Working with "Gridview and Formview"

If you are only updating Firstname and Surnames fields and other basic tasks, then you might rather want to make use of the visual tools in VWD to accomplish this.

|||

hi again:

thank you for your help. Anyway it sounds like is not working. i have tried many many solutions but all failed. however my question here can i uninstall SQL server express edition while keeping hold of the tables that i have done (is quite important)? and then reinstall the application just to make sure that the authentication level i will use is correct ( because i can't remember whether i have used sql server authentication or windows based authentication.!!!

do you thing is a good solution? if yes would you please tell me how to keep the tables and uninstall the application?

Many thanks saif44

|||

If you have created the website in your file system then its all fine. If you really want to then just make a back up of the folder.

You can go ahead and do a re-installation.

It really hard to say if its a good solution. I cant say. It's def worth a try

|||

Hello Saif,

This might help, I was having the same issue and this is one way to figure out your connection string.

Use your datagrid wizard and create a valid connection and make sure it works, then go back to your datagrid and go to configure data source.

This takes you to choose your data connection menu and the current connection that your datagid is using will be selected in the drop down list. Below the drop down list is a box with a + and the words connection string across from it. Click the + button and the connection string that was generated by the wizard will be shown. That should be your valid connection string that works.

A nicer way of doing it is to create the connection string in the web.config file and then reference the connection string name in your code.

for example in my web.config file I have this connection string:

<connectionStrings>

<removename="LocalSqlServer"/>

<addname="LocalSqlServer"connectionString="Server=[your server name];Database=[ur dbase name];User ID=;Password=;Trusted_Connection=False"/>

</connectionString>

Now I can reference the connection string programatical in my code as such:

Using connectionAsNew SqlConnection(ConfigurationManager.ConnectionStrings"LocalSqlServer").ConnectionString)

....[my code here]

end using

Of course this is just a short explanation, hopefully it gets you in the right direction.

The best resource I have found for asp.net is from 4guysfromRolla.com, this is their article on connection to a database:

http://aspnet.4guysfromrolla.com/articles/022206-1.aspx

thanks,

manny

|||Thank you very very much Manny. the problem is sloved. However, there is another problem with

objCmd.ExecuteNonQuery() it triggers a message saying "Invalid object name 'staff ' ". i don't know why this is happening?' Staff ' is the name of my table i am using and its syntax is correct.

please can you review this code for me and tell me what's wrong with it:

............

objconn =New SqlConnection(ConfigurationManager.ConnectionStrings("localsqlserver").ConnectionString)

objCmd =New SqlCommand("INSERT INTO staff(staffno, FirstName, Surname, DateRegistered) _

values (@.staffno, @.firstname, @.surname, @.dateregistered)", objconn

objCmd.Parameters.Add("@.StaffNo", Data.SqlDbType.Int).Value = txtStaffNo.Text

objCmd.Parameters.Add("@.FirstName", Data.SqlDbType.VarChar).Value = txtFirstName.Text

objCmd.Parameters.Add("@.Surname", Data.SqlDbType.VarChar).Value = txtSurname.Text

objCmd.Parameters.Add("@.DateRegistered", Data.SqlDbType.SmallDateTime).Value = txtRegDate.Text

objConn.Open()

objCmd.ExecuteNonQuery()

objConn.Close()

Response.Redirect("addstaff.aspx")

I have to say here that i am using an automatic insertion of the StaffNo when i first created the data definition table. do you think the code above is correct for the StaffNo? and what do you think it does cause the above error message to be trigged.

thank you very much for your help

how can I connect to .MDF through 2 applications?

Here is the big problem:

I am using ASP.NET 2.0 login control. this control connect to ASPNET.MDF database.I built another application using windows service, and this application also connects to ASPNET.MDF database.

problem is that the first application that connects to the database, locks the database, and so the other application cannot use the database untill the other application is closed.

I am going on circles about this, and just don't know what to do.

Please please please. love me do.

I don't think you can use the membership database for none Asp.net application because a console application is not Asp.net application, but if you could maybe you should create one for each application as the link below from a Microsoft security forum. The reason is by default every application uses the same application name to access the database so why not create one for each application so it is clean. Post again if you still have question. Hope this helps.

http://aspalliance.com/groups/microsoft_public_dotnet_framework_aspnet_security/ng-645637_Membership_Database_Security.aspx

sql

How can i connect a sql server database to visual basic 2005 express

Hi, i am new to sql server and visual basic, i need to connect my sql server database to a new application i've developed in visual basic 2005 express. Can any one tell me the steps to do this. Many Thanks.

Which SQL Server do you have? Is it on the same machine or a different machine?

Heres an MSDN article which uses SQL Server Express edition:http://msdn2.microsoft.com/en-us/library/ms345151.aspx

and heres a whole set of data-access tutorial videos:http://www.asp.net/learn/data-access/

|||

Hi, i am using sql server 2005 management studio. I have the sql server management studio installed in my computer, but i access the database from the server. How can i do the connection from the Visual Basic 2005 Express from my pc?

Thanks.

How can i configure sql server2000 with vb.net2003

Hi

I got an exe file of an application with program debug database.

Once i am running that program it is giving an error.

Can anyone suggest me how to configure SQL server2000 database with this application.

I have installed client tool of SQL server2000 on my system.

I am very new in MS platform.

................................................................................................................................

The error is like this

it is showing this error

System.Data.Sql.Client.qlException: SQL server does not exist or access denied.
at DataAccess.DataAccess.ExecuteInsertUpdateDeleteQuery(String prsConnString, String StoredProcName,sqlParameter[]parameterLost)

Thanks

I'm not exactly sure what it is you're trying to do, but if you want to set up replication using Enterprise Manager (are these the tools you're referring to?) then you should start by reading Replication topic in Books Online.sql

How can i configure SQL Server for clustring servers?

Hi
We are developing bussiness software for tourism company. Our application is
using SQL DB on MSSQL Server. We have a new customer and they have 500 or
more users.Around 300 users will be work on local network and 200 users will
be connect from remote side. My problem is how can i configure the sql
server's for best performance. We have just one DB for apllication. Am i
configure the sql servers for clustring.? I am wating for your ansvers. Thank
you"Batur KILIÇ" <Batur KILI@.discussions.microsoft.com> wrote in message
news:CB47B648-A5DE-4F9D-A94F-00E5F4DE8972@.microsoft.com...
> Hi
> We are developing bussiness software for tourism company. Our application
is
> using SQL DB on MSSQL Server. We have a new customer and they have 500 or
> more users.Around 300 users will be work on local network and 200 users
will
> be connect from remote side. My problem is how can i configure the sql
> server's for best performance. We have just one DB for apllication. Am i
> configure the sql servers for clustring.? I am wating for your ansvers.
Thank
> you
Clustering is a fail-over mechanism, not a scalability mechanism.
May be simpler to tune your application and buy a bigger server.|||Batur KILIÇ wrote:
> Hi
> We are developing bussiness software for tourism company. Our application is
> using SQL DB on MSSQL Server. We have a new customer and they have 500 or
> more users.Around 300 users will be work on local network and 200 users will
> be connect from remote side. My problem is how can i configure the sql
> server's for best performance. We have just one DB for apllication. Am i
> configure the sql servers for clustring.? I am wating for your ansvers. Thank
> you
Hi
Knowing that the customer has 500+ users doesn't tell a lot about the
load on the system...;-).
The first thing is to make sure that you design your application
properly and make the correct indexes etc.
I assume that you have some sort of idea on how the application performs
so based on that you should have an idea on how big a server you should
buy. Depending on the size, usage, design, complexity etc. of the
application, a dual proc server can be either way to small or really
overkill. Basically you should have enough memory and processor power
and a decent disk configuration. Then you can go on from that.
A decent disk configuration in my opinion will be to have enough disks
to create seperate arrays for OS files, Databasefile(s), logfile(s) and
backup. I'd then avoid RAID 5 for the logfiles and maybe for the
databasefiles (depending how much money you can spend) and then put
these on RAID 1 (or 10/01). Then buy 15 K disks rather than 10K and keep
in mind that the more spindles you have to read from the better (read)
performance you get.
With the above in mind, you'll be starting on a decent level. Then you
can start looking into specific areas if the performance isn't sufficient.
Regards
Steen Schlüter Persson
DBA

How can i configure SQL Server for clustring servers?

Hi
We are developing bussiness software for tourism company. Our application is
using SQL DB on MSSQL Server. We have a new customer and they have 500 or
more users.Around 300 users will be work on local network and 200 users will
be connect from remote side. My problem is how can i configure the sql
server's for best performance. We have just one DB for apllication. Am i
configure the sql servers for clustring.? I am wating for your ansvers. Than
k
you"Batur KILI" <Batur KILI@.discussions.microsoft.com> wrote in message
news:CB47B648-A5DE-4F9D-A94F-00E5F4DE8972@.microsoft.com...
> Hi
> We are developing bussiness software for tourism company. Our application
is
> using SQL DB on MSSQL Server. We have a new customer and they have 500 or
> more users.Around 300 users will be work on local network and 200 users
will
> be connect from remote side. My problem is how can i configure the sql
> server's for best performance. We have just one DB for apllication. Am i
> configure the sql servers for clustring.? I am wating for your ansvers.
Thank
> you
Clustering is a fail-over mechanism, not a scalability mechanism.
May be simpler to tune your application and buy a bigger server.|||Batur KILI wrote:
> Hi
> We are developing bussiness software for tourism company. Our application
is
> using SQL DB on MSSQL Server. We have a new customer and they have 500 or
> more users.Around 300 users will be work on local network and 200 users wi
ll
> be connect from remote side. My problem is how can i configure the sql
> server's for best performance. We have just one DB for apllication. Am i
> configure the sql servers for clustring.? I am wating for your ansvers. Th
ank
> you
Hi
Knowing that the customer has 500+ users doesn't tell a lot about the
load on the system...;-).
The first thing is to make sure that you design your application
properly and make the correct indexes etc.
I assume that you have some sort of idea on how the application performs
so based on that you should have an idea on how big a server you should
buy. Depending on the size, usage, design, complexity etc. of the
application, a dual proc server can be either way to small or really
overkill. Basically you should have enough memory and processor power
and a decent disk configuration. Then you can go on from that.
A decent disk configuration in my opinion will be to have enough disks
to create seperate arrays for OS files, Databasefile(s), logfile(s) and
backup. I'd then avoid RAID 5 for the logfiles and maybe for the
databasefiles (depending how much money you can spend) and then put
these on RAID 1 (or 10/01). Then buy 15 K disks rather than 10K and keep
in mind that the more spindles you have to read from the better (read)
performance you get.
With the above in mind, you'll be starting on a decent level. Then you
can start looking into specific areas if the performance isn't sufficient.
Regards
Steen Schlter Persson
DBA

How can I configure Report Services to show different set of Rendering Extension Per Repor

Hi There,
I'd like to configure the report services rendering extension on a per
report application basis. for example, if i have two report packages
named Package A and Package B, and I'd like to show only "PDF" as the
only export option for Package A, and "Excel" only for Package B. Is
there anyway to achieve this? changing the RSReportServer.config will
affect all report applications on the same server.
Regards,
KevinNo way to do this. Only way would be to have your own application instead of
using Report Manager.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<hodgelai@.yahoo.com> wrote in message
news:1124482992.992758.129540@.z14g2000cwz.googlegroups.com...
> Hi There,
> I'd like to configure the report services rendering extension on a per
> report application basis. for example, if i have two report packages
> named Package A and Package B, and I'd like to show only "PDF" as the
> only export option for Package A, and "Excel" only for Package B. Is
> there anyway to achieve this? changing the RSReportServer.config will
> affect all report applications on the same server.
>
> Regards,
> Kevin
>|||Thanks, Bruce!
Is that doable in SQL 2005?|||Thanks, Bruce!
Is that doable in SQL 2005?|||I don't think so but I don't know for sure.
One thing you could do is disable all export and then on your report have a
link at the top that exports to the format that you want.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<hodgelai@.yahoo.com> wrote in message
news:1124484347.411986.145310@.z14g2000cwz.googlegroups.com...
> Thanks, Bruce!
> Is that doable in SQL 2005?
>

How Can I Completely Delete a CE Table?

I have an application created with VS 2005 and VB 2005 using a Compact Edition Database. I created some tables that I no longer use or want. How can I permanently delete them from the database?

Connect to the database a run .ExecuteNonQuery:

Code Snippet

DROP TABLE tablename

You may have to run

Code Snippet

ALTER TABLE tablename DROP CONSTRAINT constraint_name

first, if you have foreign key constraints.

How can I check the notification-based cache invalidation with my SQL server 2005 really w

I have used the notification-based cache invalidation with my web application, but how can I check it really works? I mena if the backend data didn't change , the data will come from cache not from the database?

<asp:SqlDataSource ID="SqlDataSource_WebInfo" runat="server" ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>"
SelectCommand="SELECT simplified, traditional FROM temp" ProviderName="System.Data.SqlClient" EnableCaching="True" SqlCacheDependency="CommandNotification">

</asp:SqlDataSource>

Thanks a lot!

I believe, but not sure now, there is a callback function you can register and use, then it happens the function will be call with the ID you selected

how can I change SQL 2005 Licence key

we have a server installed SQL 2005 with Microsoft Action Pack Product Key,
because we had to install a application program before we got the Licence.
Now we got the Licence and Product Key for this server. Their are two
questions I am wondered.
One is I couldn't find the licence infromation in SQL menues. Where can I
find the Licence information in SQL?
The other is How can I swap the Licence Key in this situation.Do you really need to change SQL2005 license key?
I have never found any need to fiddle with the SQL2005 license key. It's
hard coded in the install media--well, I'm not sure whether that's always th
e
case, but it's always the case where I have dealt with SQL2005.
Linchi
"snailsnail" wrote:

> we have a server installed SQL 2005 with Microsoft Action Pack Product Key
,
> because we had to install a application program before we got the Licence.
> Now we got the Licence and Product Key for this server. Their are two
> questions I am wondered.
> One is I couldn't find the licence infromation in SQL menues. Where can I
> find the Licence information in SQL?
> The other is How can I swap the Licence Key in this situation.
>

how can I change SQL 2005 Licence key

we have a server installed SQL 2005 with Microsoft Action Pack Product Key,
because we had to install a application program before we got the Licence.
Now we got the Licence and Product Key for this server. Their are two
questions I am wondered.
One is I couldn't find the licence infromation in SQL menues. Where can I
find the Licence information in SQL?
The other is How can I swap the Licence Key in this situation.
Do you really need to change SQL2005 license key?
I have never found any need to fiddle with the SQL2005 license key. It's
hard coded in the install media--well, I'm not sure whether that's always the
case, but it's always the case where I have dealt with SQL2005.
Linchi
"snailsnail" wrote:

> we have a server installed SQL 2005 with Microsoft Action Pack Product Key,
> because we had to install a application program before we got the Licence.
> Now we got the Licence and Product Key for this server. Their are two
> questions I am wondered.
> One is I couldn't find the licence infromation in SQL menues. Where can I
> find the Licence information in SQL?
> The other is How can I swap the Licence Key in this situation.
>

how can I change SQL 2005 Licence key

we have a server installed SQL 2005 with Microsoft Action Pack Product Key,
because we had to install a application program before we got the Licence.
Now we got the Licence and Product Key for this server. Their are two
questions I am wondered.
One is I couldn't find the licence infromation in SQL menues. Where can I
find the Licence information in SQL?
The other is How can I swap the Licence Key in this situation.Do you really need to change SQL2005 license key?
I have never found any need to fiddle with the SQL2005 license key. It's
hard coded in the install media--well, I'm not sure whether that's always the
case, but it's always the case where I have dealt with SQL2005.
Linchi
"snailsnail" wrote:
> we have a server installed SQL 2005 with Microsoft Action Pack Product Key,
> because we had to install a application program before we got the Licence.
> Now we got the Licence and Product Key for this server. Their are two
> questions I am wondered.
> One is I couldn't find the licence infromation in SQL menues. Where can I
> find the Licence information in SQL?
> The other is How can I swap the Licence Key in this situation.
>sql

How can i change security mode of SQL Server Express 2005

Hello,
I have created one application in visual studio 2005 and also created setup project of that application. now i want to install SQL Server Express edition with my application. so i have checked SQL Server Express 2005 in Setup Project Properities(Prerequisites...). now i want to change SQL Secirty Mode during setup. and i don't know how can we do this?

Any idia?

Thread moved to the appropriate forum|||

hi,

SQLExpress setup boostrapper accept "parameters" to define the "final" settings of the installed instance at install time..

the parameter you are looking for is SECURITYMODE=SQL to enable standard SQL Server authenticated connections..

please have a look at http://msdn2.microsoft.com/en-us/library/ms144259.aspx for all supported parameters..

regards

|||

Thank for sujession, but still it is not work.
i have entered following line for change SQL Server Security Mode Windows Auth. To SQL Auth.

Start /wait setup.exe /qb INSTANCENAME=SQLEXPRESS SECURITYMODE=SQL SAPWD=pwd12345

i have also tried this :

Start /wait setup.exe /qb UPGRADE=Client_Components INSTANCENAME=SQLEXPRESS SECURITYMODE=SQL SAPWD=pwd12345

but still it is not work. i got the same error when i run my program "Login fail for user sa. The user is not associated with a trusted SQL Server connection.

|||

hi,

the exception is correctly related to not enabled SQL Server authenticated connections..

but the SECURITYMODE=SQL parameter is there for the case, and it "must" work as expected, as it really does since MSDE 2000 installer...

please pardon me, but are you sure you are later connecting to the correct SQLExpress instance?

regards

|||

Yes i connecting to correct SQLExpress instance.

but don't warry, it's solve now bcoz i directly change parameter in package.xml(Program Files\Microsoft Visual Studio 8\SDK\v2.0\BootStrapper\Packages\SqlExpress\en) file.

How can i change security mode of SQL Server Express 2005

Hello,
I have created one application in visual studio 2005 and also created setup project of that application. now i want to install SQL Server Express edition with my application. so i have checked SQL Server Express 2005 in Setup Project Properities(Prerequisites...). now i want to change SQL Secirty Mode during setup. and i don't know how can we do this?

Any idia?

Thread moved to the appropriate forum|||

hi,

SQLExpress setup boostrapper accept "parameters" to define the "final" settings of the installed instance at install time..

the parameter you are looking for is SECURITYMODE=SQL to enable standard SQL Server authenticated connections..

please have a look at http://msdn2.microsoft.com/en-us/library/ms144259.aspx for all supported parameters..

regards

|||

Thank for sujession, but still it is not work.
i have entered following line for change SQL Server Security Mode Windows Auth. To SQL Auth.

Start /wait setup.exe /qb INSTANCENAME=SQLEXPRESS SECURITYMODE=SQL SAPWD=pwd12345

i have also tried this :

Start /wait setup.exe /qb UPGRADE=Client_Components INSTANCENAME=SQLEXPRESS SECURITYMODE=SQL SAPWD=pwd12345

but still it is not work. i got the same error when i run my program "Login fail for user sa. The user is not associated with a trusted SQL Server connection.

|||

hi,

the exception is correctly related to not enabled SQL Server authenticated connections..

but the SECURITYMODE=SQL parameter is there for the case, and it "must" work as expected, as it really does since MSDE 2000 installer...

please pardon me, but are you sure you are later connecting to the correct SQLExpress instance?

regards

|||

Yes i connecting to correct SQLExpress instance.

but don't warry, it's solve now bcoz i directly change parameter in package.xml(Program Files\Microsoft Visual Studio 8\SDK\v2.0\BootStrapper\Packages\SqlExpress\en) file.

How can I bind a ReportViewer to a DataSourceControl?

I have created a System.Web.UI.DataSourceControl which interfaces with an external application and, with the help of a DataSourceView, generates an IEnumerable collection which can be used as a binding source. The primary use is to publish a table of data to a web page using a GridView which is bound to the DataSourceControl via it's DataSourceID property. All of this is working perfectly and now I want to be able to publish this same grid to a ReportViewer running in Local Mode. What is the proper way to do this? I know I need to design a report, but am unclear about how to make the connection between my DataSourceControl and the report. Here's what I have done so far, but am not sure if I am on the right path.

Starting with my existing, working page which has one DataSourceControl and one Gridview bound to the control.

1. Add a ReportViewer to the page.
2. Select "Design a new report" from the viewers smart-tag
3. Add a Table to the report
4. Add a DataSet to the project
5. Add a DataTable to the DataSet
6. Add 2 columns to the DataTable (assuming the case where my DataSourceControl will be returning 2 columns)
7. Now that I've added the DataSet and DataTable, it appears in the ReportViewer's "Website Data Sources" toolbox, so I drag "Column1" and Column2" out onto the table. This gives me 2 headers and the data cells look like "=Fields!Column1.Value" etc.
8. Only after I have added the columns can I go back to the ReportViewer's smart tag and select "Choose Data Sources". This brings up a dialog with a 2 column table with the headings "Report Data Source" and "Data Source Instance". The ReportDataSource is already set to "DataSet1_DataTable1" and the "DataSourceInstance is defaulted to "(None)" but has a drop-down with which I can either select the instance of my DataSourceControl which is on the page (DataSourceControl1) or else select "<New Data Source...> which brings up a Data Source Configuration Wizard. I choose to select my Data Source Control instance.
9. I select "Rebind Data Sources" on the Report Viewer's smart-tag. This adds a new "Object Data Source" to the page, but I don't see how this has any relation to my DataSourceControl. It's SelectMethod is set to "GetData" and its TypeName is set to "DataSet1TableAdapters." which doesn't make much sense to me.

What am I missing? Is there a simpler way to do this? Can I bind a ReportViewer to my DataSource control directly? or do I need to add methods to my DataSourceControl which return the tabular data as a DataTable inside of a DataSet?

Thanks for any help. This is way too confusing.no replys? is my question too complex? i would think this would not be too hard. any help would be greatly appreciated.

How can I be notified when record is updated

I want to build an windows application by using a visual C# to Notify the user that his data in the database had been changed ..such like "New Message In Your Mail Box Alert"..So I need to know if there is way that to let the SQL Server send a notify (just like Trigger) ..

osmansays,
Combine a trigger and a stored procedure to send a email on updates
--------------------------------
Procedure like :
Create Procedure sp_SMTPMail
@.SenderName varchar(100),
@.SenderAddress varchar(100),
@.RecipientName varchar(100),
@.RecipientAddress varchar(100),
@.Subject varchar(200),
@.Body varchar(8000),
@.MailServer varchar(100) = 'localhost'
AS

SET nocount on
declare @.oMail int
declare @.resultcode int
EXEC @.resultcode = sp_OACreate 'SMTPsvg.Mailer', @.oMail OUT
if @.resultcode = 0
BEGIN
EXEC @.resultcode = sp_OASetProperty @.oMail, 'RemoteHost', @.mailserver
EXEC @.resultcode = sp_OASetProperty @.oMail, 'FromName', @.SenderName
EXEC @.resultcode = sp_OASetProperty @.oMail, 'FromAddress', @.SenderAddress
EXEC @.resultcode = sp_OAMethod @.oMail, 'AddRecipient', NULL, @.RecipientName, @.RecipientAddress
EXEC @.resultcode = sp_OASetProperty @.oMail, 'Subject', @.Subject
EXEC @.resultcode = sp_OASetProperty @.oMail, 'BodyText', @.Body
EXEC @.resultcode = sp_OAMethod @.oMail, 'SendMail', NULL
EXEC sp_OADestroy @.oMail
END
SET nocount off
--------------------------------
Trigger like :
CREATE TRIGGER trgDataChanged on tblData
AFTER UPDATE
AS
BEGIN
exec sp_SMTPMail @.SenderName='me', @.SenderAddress='me@.somewhere.com', @.RecipientName = 'Someone', @.RecipientAddress = 'someone@.someplace.com', @.Subject='SQL Data Change', @.body='data in table tblData has been changed'
END
--------------------------------
If you also want to track the changes you can either translate the query to simple data or show the data that is changed but to view that you need to walk through the recordset with for instance a cursor.
Peter

How can i back-up my database in production environment.

I developed an asp.net application in visual web developer 2005 express edition and SQL sever 2005 express with Advanced services. The application has been deployed and iam wondering what tools are availabel to for backing up my data. Are there any tools i can use to back-up my database. Iam not talking of third party tools but tools a vailable in sql sever 2005 express with advanced services or visual web developer express.

OR can write a vb.net Sub procedure that i run and have my database backed up. If so where can i start or what other options may i explorer.

Hi Dear,

you can backup your datbase by usingMicrosoft SQL Server Management Studio and also by ur VB.NET application

i will try to explain you both ways.

1:By using Microsoft SQL Server Management Studio

Open Management Studio and Right Click On Your Database and go to the Tasks->Backup menu item, Backup Database Dialogbox will be open.

Click Add button in Destination Panel, and choose the Destination File. and simply press OK ( This is the simplest way, but you can customize your backup by choosing different options available on Backup Database Dialog)

2. By VB.NET Application

execute following TSQL statement in your SQL Server

USE

master

EXEC

sp_addumpdevice'disk','MyDataBaseBackup','c:\YourDatabaseBackupDirectory\MyDBBackup.dat'

' and then use following code in your application

' VB.NET Code

Dim conAsNew SqlConnection("ConnectionString")Dim comAsNew SqlCommand("BACKUP DATABASE YourDataBaseName TO MyDataBaseBackup")

com.Connection = con

com.Connection.Open()

com.ExecuteNonQuery()

com.Connection.Close()

Thanks

Regards,

Muhammad Akhtar Shiekh

|||

Oh thanks very much Akhtar,

I want to take on and understand thoroughly the second option(vb.net option) as its looks to be more flexible though a bit complicated. Looks like i have to start with stored procedures, isn't. Please would you point me to a tutorial from microsoft to get me started with the core basics of backingup a database using this approach.

Thanks very much.

|||Check out the BACKUP statement in Books On Line. You might want to schedule it as a job and include it in your weekly maintenance window (if you dont already have one, time to create one) rather than create a VB application for it.|||

The problem all starts with version of sql sever one is using. some of the documentation do not apply to the express version of sql sever so you find your self wasting valuable time reading an article that you later find out does not apply to the product version your running. Iam running sql sever 2005 express edition with advanced sevices in my case.

sql

How can I access data to SQL server 2005 using Windows application,ADO.net and .Net remoting.

How can I access data to SQL server 2005 using Windows form application,ADO.net and .Net remoting?

Can anybody help me? please...

This question is too vague. Do you have a specific issue or question?|||

Here is one place to start:

http://www.411asp.net/home/tutorial/howto/applicat

I suggest that you go to your local bookstore or library and look over the options. Or Google?

"create .NET application"

|||

Deepak Kapoor wrote:

This question is too vague. Do you have a specific issue or question?

Hi ! Deepak Kapoor

I am a C# beginner, I have to write some code using windows application,that's need to INSERT,UPDATE,SELECT and DELETE data to sql server using ADO.net and .net remoting...

1.The client application include 1 dataGridView and 4 buttons(Add,Delete,Edit and Load).

2.The server application include 1label and 1 button to start server application.

3.The remote object.

Here are my questions.

I already wrote for the Load button of the client application.But, I dont know how to program for the remaining buttons.And I want to access data directly into the dataGridView.If you know how to do,please post some explaination and give me some example code.

This is my remote object.

public class AuthorList : MarshalByRefObject

{

private static string connectionString = @."server = BMSTU\KTS ;database = kts;uid=sa;pwd=";

public DataTable GetAuthors()

{

string SQL = "SELECT * FROM Author";

// Create ADO.NET objects to execute the DB query.

SqlConnection conn = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand(SQL, conn);

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

// Execute the command.

try

{

conn.Open();

adapter.Fill(ds, "Author");

}

catch (Exception err)

{

Console.WriteLine(err.ToString());

}

finally

{

conn.Close();

}

// Return the first DataTable in the DataSet to the caller.

return ds.Tables["Author"];

}

// This method allows you to verify that the object is running remotely.

public string GetHostLocation()

{

return AppDomain.CurrentDomain.FriendlyName;

}

}

This is the Server application.

private void button1_Click(object sender, EventArgs e)

{

HttpChannel myChannel = new HttpChannel(8080);

ChannelServices.RegisterChannel(myChannel);

RemotingConfiguration.RegisterWellKnownServiceType(typeof(BookServer.AuthorList),

"AuthorList", WellKnownObjectMode.Singleton);

label1.Text = "Server Started";

button1.Enabled = false;

}

This is the Client application

private void btnLoad_Click(object sender, EventArgs e)

{

AuthorList atlst = (AuthorList)Activator.GetObject(typeof(AuthorList),

"http://localhost:8080/AuthorList", WellKnownObjectMode.Singleton);

dataGridView2.DataSource = null;

dataGridView2.DataSource = atlst.GetAuthors();

}

If you know how to program for the btnAdd,btnEdit and btnDelete of the Client application.And how to program in server and remote object.I am hoping anybody's help.

Best regards ,

thanks,

WhoAmI?

|||hmmm, can anyone help me?

How can db_owner restore db from backup and keep his permissions?

Hi there
I'm developing an application, and from time to time I take it to
production environment and keep it up to date with dev version. When I
tried to do this alone (istead of sysadmin) I got some error saying
that I didn't have enough permissions to do restore (despite I was a
db_owner).
Is it possible, that the reason for that is the fact that in dev
version which I was restoring there was no user which had db_owner
permissions? so, I was working on this database as some user with
db_owner permissions and restored it to the state in which there wasn't
any user mapped to my login in db anymore.
Is it possible? If so, then how can I restore db as a db_owner?
thanks a lot
HPFrom BOL:
"If the database being restored does not exist, the user must have CREATE
DATABASE permissions to be able to execute RESTORE. If the database exists,
RESTORE permissions default to members of the sysadmin and dbcreator fixed
server roles and the owner (dbo) of the database (for the FROM
DATABASE_SNAPSHOT option, the database always exists)."
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

How can db_owner restore db from backup and keep his permissions?

Hi there
I'm developing an application, and from time to time I take it to
production environment and keep it up to date with dev version. When I
tried to do this alone (istead of sysadmin) I got some error saying
that I didn't have enough permissions to do restore (despite I was a
db_owner).
Is it possible, that the reason for that is the fact that in dev
version which I was restoring there was no user which had db_owner
permissions? so, I was working on this database as some user with
db_owner permissions and restored it to the state in which there wasn't
any user mapped to my login in db anymore.
Is it possible? If so, then how can I restore db as a db_owner?
thanks a lot
HP
From BOL:
"If the database being restored does not exist, the user must have CREATE
DATABASE permissions to be able to execute RESTORE. If the database exists,
RESTORE permissions default to members of the sysadmin and dbcreator fixed
server roles and the owner (dbo) of the database (for the FROM
DATABASE_SNAPSHOT option, the database always exists)."
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .