How do I connect to SQL

In VB6 I use to use a module modDataAccess to set a ConnectionString
Public Const sDataConn = "Provider=MSDASQL;Persist Security Info=False;uid=sa;pwd=;Initial;Catalog=falDB;Data Source=falDB;Connect Timeout=600"
Public Conn As ADODB.Connection
Public RS As ADODB.Recordset

And used it in:              
Dim Conn As New ADODB.Connection
Conn.Open sDataConn
Conn.Execute "UPDATE tblAnalysis SET dtmBeginDate = ('" & FixDate(DTPickerBeginDate.Value) & "')"
Conn.Execute "DELETE FROM rptAgeAnalysis"
Conn.Execute "INSERT INTO aTable (intTemp, nvcTemp) VALUES (1,1)"
.
.
Conn.Close

And when I want a value for a variable:
                Dim SystemDate as Date
                Set RS = New ADODB.Recordset
                RS.Open "Select dtmSystemDate FROM tblSystemDate WHERE   ……", sDataConn
                If Not RS.EOF Then SystemDate = RS("dtmSystemDate")
                Set RS = Nothing

what can I use in VB.net that are simular to above ?
mariostruwigAsked:
Who is Participating?
 
DhaestCommented:
What datatype is dtmSystemDate  ?
In example below, I assume it's a string
Dim SystemDate As String
Dim sql As String = _
     "Select dtmSystemDate FROM tblSystemDate WHERE   ……"

    Using conn As New SqlConnection(connString)
        Dim cmd As New SqlCommand(sql, conn)
        Try
            conn.Open()
            SystemDate = cmd.ExecuteScalar().ToString()
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
    End Using

Open in new window

0
 
DhaestCommented:
An example can be found here:
SQL In VB.NET
http://www.daniweb.com/code/snippet216920.html#
0
 
DhaestCommented:
To retrive just one value from the database, use the executescalar-function
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx
0
 
Om PrakashCommented:
Please check the example
Imports System.Data.SqlClient

Dim sConnection As String = "-- connection string -- "

Dim objCommand As New SqlCommand
objCommand.CommandText = "Select dtmSystemDate FROM tblSystemDate WHERE . . . . "
objCommand.Connection = New SqlConnection(sConnection)
objCommand.Connection.Open()

Dim objDataReader As SqlDataReader = objCommand.ExecuteReader()

If objDataReader.HasRows Then
	Do While objDataReader.Read()
		MessageBox.Show("Field: " & Convert.ToString(objDataReader(0)))
	Loop
Else
Console.WriteLine("No data found")
End If
objDataReader.Close()
objCommand.Dispose()

Open in new window

0
 
mariostruwigAuthor Commented:
how do I get the value of "dtmSystemDate" into the variable say "SystemDate"
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.