New to vb.net

[shannanl]

I have been programming in VB6 for quite a while but I am new to VB.Net and having a heck of a time transitioning to ADO.NET. Can someone tell me what is wrong with the code below? I took this out of a book but I can't get it to work:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'--CONNECTION
Dim oConnection As New SqlClient.SqlConnection("Data Source = DBSERVER1; Initial Catalog=Test_Cafeteria")

'--COMMAND
Dim oCommand As New SqlClient.SqlCommand("Select * FROM [Charge_accounts_T]", oConnection)

'-- DATARADER
Dim oDataReader As SqlClient.SqlDataReader = oCommand.ExecuteReader

'-- ITERATE THROUGH RESULT
While oDataReader.Read()
Debug.WriteLine(oDataReader.GetSqlValue(0))
End While

End Sub

Here is the error I am getting >

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: ExecuteReader requires an open and available Connection. The connection's current state is Closed.

Thanks in advance.

Shannan

 

[Dimandja]

You are not connected to yopur data source.

1. Do you have a server called DBSERVER1?

2. If the database and your application are on your PC, try specifying "Data Source=localhost".

__________________________________________
Try forum1391 for lively discussions

 

[shannanl]

Yes, i have a server called dbserver1. The application and database are on the same server. I tried localhost instead of dbserver1 and I still get the same error.

Thanks,

Shannan

 

[Skittle]

You need to open the connection.
Try adding just before the executeReader command:-

OConnection.Open






Dazed and confused

 

[shannanl]

I tried this and it gives me a system error.


'-- DATARADER
oConnection.Open
Dim oDataReader As SqlClient.SqlDataReader = Command.ExecuteReader

Thanks,

Shannan

 

[Skittle]

Hmm. Strange.

Could be that you need a ';' at the end of your connection string:- '....Initial Catalog=Test_Cafeteria;'
All my examples have a ';' at the end.

If that does not do it, try browsing/adding the server to the server explorer in vb.net visio studio. Once you have it defined in the server explorer you can drag and drop the connection on to your form as SQLConnection1 and then you can use it in place of OConnection. You won't have to Dim the connection in your code.



Dazed and confused

 

[shannanl]

Maybe I am going about this backwards. Here is what I use in VB6 to connect to a database. I am trying to figure out the .NET equivelant.

Dim conAccount As New ADODB.Connection
Dim comAccount As New ADODB.Command
Dim rstAccount As New ADODB.Recordset

With conAccount
.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Fast_order_data;Data Source=dbserver1"
.CursorLocation = adUseClient
.Open
End With

With comAccount
.ActiveConnection = conAccount
.CommandType = adCmdText
.CommandText = "SQL Statement"
End With

Set rstAccount = comAccount.Execute

Any suggestions?

Thanks,

Shannan

 

[RiverGuy]

Try this site:

http://www.connectionstrings.com/

 

[Skittle]

I think you are pretty close with the example you first posted. I think its all about the connection.

Your code doesn't like it according to your error message so try creating the connection a different way. If it were me, I'd open the Server Explorer window in Vb.Net Studio, right click on servers and select add server.

Once that server is defined successfully, you know you have a valid connection object that you can drag from the server explorer on to your form/project. Using that instead of OConnection I think will work (ie drop the DIM that defines OConnection and replace all other OConnection occurences in your code with SQLConnection1). Otherwise your code looks alright although I'm no expert.


Dazed and confused

 

[Skittle]

Just thought of something else as I was half way through the car park.

You have not specified a username and password. That might be the problem. I've just remembered I got the same error message when I first started using VB.net and it took me a while to figure out it needed user authority.

Try 'username=XXXX;password=XXXXX;'

Or 'Trusted Connection=True;'

on your connection string.

Still thing server explorer will get you there though.
Good luck.

Dazed and confused