Click here to Skip to main content
Rate this: bad
good
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
                .MoveFirst()
                Do Until .EOF
                    Y = !TalentID
ChkSeq:
                    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
                    .MoveNext()
                Loop
            End With
        End If
        Me.lstEx.Text = sString
        sRS = Nothing
 
How do i convert the above codes to compliment SqlConnection?
Posted 17-Dec-12 20:16pm
Rate this: bad
good
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
				Else
					Exit Do
				End If
			Loop
		Loop
	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
  Permalink  
Rate this: bad
good
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
  Permalink  
Comments
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
Web03 | 2.8.140926.1 | Last Updated 18 Dec 2012
Copyright © CodeProject, 1999-2014
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