How to connect to SQL?

[Guest 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()

[Guest 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 http://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 can't 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.