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 change the column width?

I'm creating a C# program in Visual Studio. When I query the sql database, the results are put into a data grid view. However, the column widths are always too small when displayed. I have text that is not shown because the column width is not large enough. I tried going into Sql Server Management Studio Express and trying to modify the size property of the columns, but it won't let me change it (its grayed out). How can I fix this problem?Why would you want changing size on the back end server when you need changing size of your grid cells.
You can provide functionality to popup a full text when grid cell double-clicked.

If you'll change backend column size to accommodate your grid size (which is unheard of) you will just truncate existing data in a database and have part of data instead of full set of data.

To limit number of characters returned from a backend to a front end you can use Substring function.

For more help describe your intentions in more detailed and it would be a wise posting it on .NET forum.

Good Luck.

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 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