How to convert adoDB connection to SqlConnection?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

VB

        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

Rickysay

Updated 6-Aug-16 17:03pm

Add a Solution

3 solutions

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

Posted 17-Dec-12 21:05pm

Zaf Khan

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.

Solution 3

i'v a question about sqlconnection
is there code for sqlconnection using udl file or this file can't link with sqlconnection??

Posted 6-Aug-16 17:03pm

‫بسام الهاملي‬‎

Comments

Richard MacCutchan 7-Aug-16 2:29am

This is not a solution, and this question is almost 4 years old.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Zaf Khan · Accepted Answer · 2012-12-17T22:41:00

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.

VB

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