Click here to Skip to main content
11,435,048 members (48,528 online)
Rate this: bad
Please Sign up or sign in to vote.
        Dim sString As String
        Dim sSql As String
        Dim sRS As New ADODB.Recordset
        Dim sConn As New ADODB.Connection
        Dim X As Integer
        Dim Y As Integer
        Me.lstEx.Text = ""
        sString = ""
        sSql = "Select TalentID From tbl_talent_databaseOrder by TalentID"
        sConn = CurrentProject.Connection
        sRS.Open(sSql, sConn, adOpenKeyset, adLockOptimistic)
        If Not sRS.EOF Then
            With sRS
                X = 0
                Do Until .EOF
                    Y = !TalentID
                    X = X + 1
                    If Y <> X Then 'chk to see if TalentID is sequential
                        sString = sString & X & " "  'if it is not, then record the non sequential number into the string
                        GoTo ChkSeq
                    End If
            End With
        End If
        Me.lstEx.Text = sString
        sRS = Nothing

How do i convert the above codes to compliment SqlConnection?
Posted 17-Dec-12 21:16pm
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

Here are more connection strings for you,
that show how to use connect to a datasource using OLEDB.
Conn String

And here is the MS-SQL compatible converted code equivalent of your above codeblock.
Function SomeKnockeUpCode() As String
	Dim intX As Integer = Nothing
	Dim intY As Integer = Nothing
	Dim strSting As String = Nothing
	Dim strSqlQUery As String = Nothing
	Dim cmdSqlCommand As Data.SqlClient.SqlCommand = Nothing
	Dim rdrDataReader As Data.SqlClient.SqlDataReader = Nothing
	'-> Process
	strSqlQUery = "Select TalentID From tbl_talent_database Order by TalentID"
	msSqlConnection = New Data.SqlClient.SqlConnection()
	'NOTE - You may need to CHECK your connection string!!! in the line below
	msSqlConnection.ConnectionString = CurrentProject.Connection
	cmdSqlCommand = New Data.SqlClient.SqlCommand(strSqlQUery, msSqlConnection)
	If cmdSqlCommand.Connection.State = Data.ConnectionState.Closed Then cmdSqlCommand.Connection.Open()
	rdrDataReader = cmdSqlCommand.ExecuteReader()
	If rdrDataReader.HasRows Then
		Do While rdrDataReader.Read()
			intX = 0
			intY = rdrDataReader.GetValue(rdrDataReader.GetOrdinal("TalentID"))
			Do While intX <> intY
				intX = intX + 1
				If intX <> intY Then
					strSting = strSting & intX & " "	'if it is not, then record the non sequential number into the string
					Exit Do
				End If
	End If
	If cmdSqlCommand.Connection.State = Data.ConnectionState.Open Then cmdSqlCommand.Connection.Close()
	'return string
	Return strSting
	'tidy up
	intX = Nothing
	intY = Nothing
	strSting = Nothing
	strSqlQUery = Nothing
	cmdSqlCommand = Nothing
	rdrDataReader = Nothing
End Function
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

I think you can manage it using OLEDB
Dont quote me on that as im not 110% sure.

Maybe this page will give you more info
Rickysay at 18-Dec-12 3:10am
I don't think so, as i'm using MSSQL as a backend.
Zaf Khan at 18-Dec-12 4:00am
Where is your connection string? that your using to connect to the data source?
Convert the code block above is not a problem.
but it doesn;'t neccessarily answer your question which is termed.....

How to convert ADODB connection to SQL.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Advertise | Privacy | Mobile
Web01 | 2.8.150428.2 | Last Updated 18 Dec 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100