SQL Server 2005 Connection

[techie_vb]

Hi,
I don't know whether I should post this query in Vb forum or Database forum.
Kindly reply for my query and am sorry to Cross post the query.

Iam using Vb Express Edition 2005.


I have tried connecting SQL Express 2005 with VB 2005.
Iam getting an exception like the following when I run the project.

***
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
***

I have tried to Connect to the Database through Buton Click Event.
I have created a Database (FirstDB.mdf) which have 5 records.
I have included "Microsoft ADO Data Objects (Multi Dimensional) 2.8 Library"in the New_Sql project.

I have chosen "SQL Authentication" here rather than "Windows Authentication".

My Code:

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

MsgBox("Trying to Connect SQLServer 2005")

Dim con As New ADODB.Connection
Dim res As New ADODB.Recordset


con.Open("Data Source=.\SQLEXPRESS;AttachDbFilename=FirstDB.mdf;Integrated Security=True;User Instance=True;UserName=newproject;password=tech")

MsgBox("Connected SQL Server 2005")

End Sub

End Class

Can anyone help me to fix the exception in this program?
Thanks in Advance!!!

[exterminator]

I am not a very good VB programmer but here are a few comments:

1. You should prefer using Dim and then seperately do a new rather than using Dim as new because VB doesn't actually do the allocation in the second case until it finds the first usage of the object. Hence it needs to do a check whenever you use it and that is slower. Doing both seperately is better since VB assumes that you have already done the new (allocation) and confides in you and if you haven't done so, it will shout about it!

2. Check for your connection string here - www.connectionstrings.com, if you used the right one. You have it wrong, it seems on the first look itself.

3. Refer to this link - Data Access using ADO (http://www.vbexplorer.com/VBExplorer/wrox/sample1061.asp) as well.

4. Refer to this link - "Multiple-step OLE DB operation generated errors" when opening ADO connection (http://support.microsoft.com/kb/269495/EN-US/). Before going ahead - there is section at the bottom to reproduce the behaviour. Do that and see if you get this same error. If yes, then do what it suggests else, I am not sure and neither would that link help.

Did you try fixing this error yourself? I put that exception message in the google search and got many results. You could have had found your answers quite easily. Let us know if (4) fixes your problem. Hope this helps. Regards

[exterminator]

And yes this is a VB question. It should not have had been put in the Database forum. Please ask a moderator (from this forum, Ejaz or I am not sure but VB forum mods could also do it) to merge your threads. Regards.

[Shuja Ali]

I would also suggest that if you are using VB.NET 2005 then you should be using ADO.NET instead of the classic ADO. Although I haven't used VB 2005, but I think you should be using System.Data.SQLClient. Use SQLConnection to connect to the SQL Server.