How to connect to SQL?

[Peter]

I looked thru the forums and couldnt find any answere to this.. or maybe im to tired to see it. Anyway, my question is how i make a connection to an SQL-server.

In earlier VB6 application i used this:

Public DataConn As New ADODB.Connection
Dim rsTemp As Recordset

DataConn.Provider = "SQLOLEDB;Data Source=SQLSERVERNAME;Initial Catalog=MYDB;User ID=sa;Password=thepassword"
DataConn.Open
Set rsTemp = DataConn.Execute("SELECT * FROM TABLE")

Do While Not rsTemp.EOF
	Debug.Print rsTemp(1)
	rsTemp.MoveNext
Loop

DataConn.Close
Set rsTemp = Nothing

How do i do this in VB.NET?

Thanks!

Regards,
Peter

 

[Derek Stone]

Dim oConnection As New SqlConnection()
Dim oCommand As New SqlCommand()

oConnection.ConnectionString = "<INSERT YOUR CONNECTION STRING HERE>"
oConnection.Open()

oCommand.Connection = oConnection
oCommand.CommandText = "<INSERT YOUR SQL STATEMENT HERE>"

Dim oDataReader As SqlDataReader = oCommand.ExecuteReader()

Dim i As Int32
While oDataReader.Read()
    i = oDataReader.GetInt32(oDataReader.GetOrdinal("<INSERT FIELD NAME HERE>"))
End While

oDataReader.Close()
oConnection.Close()

 

[Peter]

Do i need any special reference for this?
I get:
"Type SqlConnection is not defined"
"Type SqlCommand is not defined"
"Type SqlDataReader is not defined"
..when compiling.

And the connectionstring doesnt work.
I use:

oConnection.ConnectionString = "SQLOLEDB;Data Source=PC3053\PC3053;Initial Catalog=SD;User ID=sa;Password=Uh3//#hhfd78HIU"

Any suggestions?

/ Peter

 

[Derek Stone]

You need to import System.Data.SqlClient.

 

[dejota6]

Ok, I can follow all that stuff, but here is my problem. How do you import System.Data.SqlClient. I found a whole bunch of connection strings on www.able-consulting.com but they had the same thing. any help would be great.

 

[Volte]

At the top of your code, above where the class or whatever is defined,
type

Imports System.Data.SqlClient

For example, in a Form, it might look like this:

Imports System.Data.SqlClient

Public Class Form1
    Inherits System.Windows.Forms.Form
    ... more code

 

[dejota6]

Well, I thought i followed all that stuff.

here is my new question

I have a program that was connected to an Access Database. That database is now an sql database. i dont want to rewrite the entire code, i just want to change my connection to an sql database. here is what i have thus far.

Public m_cnADOConnection As New ADODB.Connection()

            m_cnADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=H:\BHM\Official.mdb")

and then to open a recordset, i used the following

Dim rstAddress As New ADODB.Recordset()
rstAddress.Open("tblAddress", m_cnADOConnection, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)

the problem i ran into with the code given above by volte and derek is that i cant make the connection global to allow all of my forms to have access to it. I am a 6.0 programmer, so i might not even be using .net to its full potential. If anyone could help me out, that would be great.