absolute newbie question - connecting to SQL server in ADO.net

  • Thread starter Thread starter Chris Strug
  • Start date Start date
C

Chris Strug

Hi,

I have previously worked with Access 2000 and SQL server 2000 and I am
comfortable using ADO in this way.

In a (probably vain) attempt to take things further I bought a copy of
VB.net standard edition with an eye to writing stand-alone applications with
SQL server 2000 in stead of using Access using (I assume) ADO.net

Having installed VB.net I've created a simple windows application and in the
code to the initial form load I have tried to create a simple connection to
an SQL server.

And I'm stuck.

This is the code that I am attempting to use in the load event of the
default form.


Dim conn As ADODB.Connection
conn = New ADODB.Connection

conn.Open("Provider=MSDataShape.1;Persist Security Info=False;Data
Source=TLC01;Integrated Security=SSPI;Initial Catalog=ConControl2;Data
Provider=SQLOLEDB.1")

conn.Open()

In running this I get the error:

"An unhandled exception of type
'System.Runtime.InteropServices.COMException' occurred in
WindowsApplication2.exe

Additional information: Operation is not allowed when the object is open."

I have referenced "Microsoft ActiveX Data Objects 2.7 Library". Other than
that I'm not really sure what to do.

I have three questions:

1. Am I (trying to) using ADO or ADO.net in this example?

2. How do I get the above connection working? I assume that I'm missing some
vital aspect of the connection but I don't know what it is. As I said, my
experience of ADO prior to this was in Access 2000.

3. Have I wasted my money buying VB.net standard because it can't connect to
an SQL server 2000 server?

My apologies if these questions are somewhat banal but in my defence we all
have to start somewhere. :)

Any advice, links or otherwise are gratefully recieved..

Kind regards

Chris.
 
Hi Chris,

1. ADO
2. Use Open only once as before.
You are opening twice the same connection.
3. No, you can connect very well with sql server.
(System.Data, System.Data.SqlClient namespace for sql server).

I suggest you to read .net help files, topic on ado.net would be a good
starting point.
 
Miha Markic said:
Hi Chris,

1. ADO
2. Use Open only once as before.
You are opening twice the same connection.
3. No, you can connect very well with sql server.
(System.Data, System.Data.SqlClient namespace for sql server).

I suggest you to read .net help files, topic on ado.net would be a good
starting point.
Click to expand...

Miha,

Many thanks for your reply.

1. Ahh, so, ADO.net is "referenced" through import system.data,
system.data.sqlclient statements?
2. Of course. Sorry, should have read that properly before I posted it. I
have managed to open a recordset and move about in it and so on. However
I've found that I have use a command object as the source property for the
recordset as opposed to a simple select statement in a string. Interesting.
3. Thank God for that!

Yes I do need to read the documentation, I just find it somewhat confusing
until I manage to get a basic understanding of the concepts. However, thank
you for your reply, your advice is appreciated.

Chris.
 
Chris Strug said:
Miha,

Many thanks for your reply.

1. Ahh, so, ADO.net is "referenced" through import system.data,
system.data.sqlclient statements?
Click to expand...

Yes, the main part of ado.net resides in System.Data.dll assembly which
contains System.Data(and .Common) namespaces.
..SqlClient is Sql server specific part (there are also OracleClient, OleDb
and Odbc namespaces).
3. Thank God for that!
:-)


Yes I do need to read the documentation, I just find it somewhat confusing
until I manage to get a basic understanding of the concepts. However, thank
you for your reply, your advice is appreciated.
Click to expand...

Yes, you might also search a bit on internet - you'll found a ton of
information on ado.net.
Good starting point might be MSDN magazine.
http://msdn.microsoft.com/msdnmag/default.aspx
Check also Ryan's www.knowdotnet.com.
 
Miha,

I've been hunting about and found www.ondotnet.com which has book extracts
from some O'Reilly texts that seems very to be a good source. Following on
from this I've managed to connect to my server and retreieve data - I just
need to figure out how to bind it to controls (such as the datagrid) and run
stored procedures and I'll be flying!

Just using vanilla ADO still seems a bit quirky though, no doubt I'll get
the hang of it soon enough. :0

However, thanks again for your time.

Once last question, could you recommend any books on VB.net / ADO.net? I've
realised that they're too different from VB6/ADO for me to muddle
through....

Thanks again.

Chris.
 
My book is written with the ADO "classic" developer in mind. See
www.betav.com for details.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Chris Strug said:
Hi,

I have previously worked with Access 2000 and SQL server 2000 and I am
comfortable using ADO in this way.

In a (probably vain) attempt to take things further I bought a copy of
VB.net standard edition with an eye to writing stand-alone applications with
SQL server 2000 in stead of using Access using (I assume) ADO.net

Having installed VB.net I've created a simple windows application and in the
code to the initial form load I have tried to create a simple connection to
an SQL server.

And I'm stuck.

This is the code that I am attempting to use in the load event of the
default form.


Dim conn As ADODB.Connection
conn = New ADODB.Connection

conn.Open("Provider=MSDataShape.1;Persist Security Info=False;Data
Source=TLC01;Integrated Security=SSPI;Initial Catalog=ConControl2;Data
Provider=SQLOLEDB.1")

conn.Open()

In running this I get the error:

"An unhandled exception of type
'System.Runtime.InteropServices.COMException' occurred in
WindowsApplication2.exe

Additional information: Operation is not allowed when the object is open."

I have referenced "Microsoft ActiveX Data Objects 2.7 Library". Other than
that I'm not really sure what to do.

I have three questions:

1. Am I (trying to) using ADO or ADO.net in this example?

2. How do I get the above connection working? I assume that I'm missing some
vital aspect of the connection but I don't know what it is. As I said, my
experience of ADO prior to this was in Access 2000.

3. Have I wasted my money buying VB.net standard because it can't connect to
an SQL server 2000 server?

My apologies if these questions are somewhat banal but in my defence we all
have to start somewhere. :)

Any advice, links or otherwise are gratefully recieved..

Kind regards

Chris.
Click to expand...

Hello Chris,

You should try our data controls for use with vb.net and sqlserver.
You can create data access forms in minutes, and without writing much
code at all. All of the connection and control binding is done for
you. Our website is www.sqlcafe.net. You can also bind your database
columns to our data grid with no work at all. Please sign up for our
beta list and visit our discussion forum.

Thanks,

(e-mail address removed)
 
Back
Top