Click here to Skip to main content
13,148,107 members (72,349 online)
Rate this:
 
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
Updated 6-Aug-16 17:03pm
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  
Comments
   
I HAVE A QUESTION ABOUT SQLCONNECTION
IS THERE CODE FOR SQLCONNECTION USING UDL FILE
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 18-Dec-12 3:10am
   
I don't think so, as i'm using MSSQL as a backend.
Zaf Khan 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.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

i'v a question about sqlconnection
is there code for sqlconnection using udl file or this file can't link with sqlconnection??
  Permalink  
Comments
Richard MacCutchan 7-Aug-16 2:29am
   
This is not a solution, and this question is almost 4 years old.

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web04 | 2.8.170924.1 | Last Updated 6 Aug 2016
Copyright © CodeProject, 1999-2017
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