We help IT Professionals succeed at work.

ADODB.Recordset is not compatible with OledbConnection

jsctechy
jsctechy asked
on
675 Views
Last Modified: 2013-11-05
hi,
 afew days ago one of the experts here have point out to me this but i need to use ADODB to insert records in my database.
Now my quesion here is, if ADODB.Recordset is not compatible with OledbConnection whats compatible with ADODB that willmake this work?

what type of connection do i need then?
Thanks
Comment
Watch Question

jkaiosIT Director

Commented:
What type of database are you trying to connect to? SQL, Oracle, Access, dBase, FoxPro, etc.
jsctechyInfrastructure Team Lead

Author

Commented:
MS SQL 2005
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>Now my quesion here is, if ADODB.Recordset is not compatible with OledbConnection whats compatible with ADODB that willmake this work?
ADODB.Connection is compatible.

now, you REALLY should leave the ADODB stuff behind, and use the ado.net features properly, this will be benefical for performance, stability and security of your applications/queries.
jsctechyInfrastructure Team Lead

Author

Commented:
angeIIII,
i do understand that ADODB is really powerfull in terms of performance and alll the stuff you have mention but why in executing this easy 20 lines of code it give me errors if i'm not doing anything out of extraordinary?

why when writing this line (rs.Fields("BUY_SELL") = "W") gives me "Property 'Item' is ReadOnly'
how do i turn it to be able to write?

here is the entire code:

 Dim oSQLConn As SqlClient.SqlConnection = New SqlClient.SqlConnection()
       

        oSQLConn.ConnectionString = _
    "Network Library=DBMSSOCN;" & _
    "Data Source=XX.X.7.222,1433;" & _
    "Initial Catalog=DatabaseName;" & _
    "User ID=ITDEV;" & _
    "Password=XXXXXXXX"
        oSQLConn.Open()

        MsgBox("you were able to stablish connection to the database")

        Dim strSQL As String

        strSQL = "SELECT * FROM TRDE"
        Dim rs As ADODB.Recordset
        rs = New ADODB.Recordset
        rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
        rs.Open(strSQL, oSQLConn)

        rs.Delete()
        rs.MoveNext()

        rs.AddNew()
        rs.Fields("BUY_SELL") = "W"
        rs.Fields("TRADE_DATE") = Now()
        rs.Update()

        rs.UpdateBatch()
        rs.Close()

        oSQLConn.Close()

    End Sub

Since i have all this trouble with ADO.NET i went out and got his book: http://www.microsoft.com/MSPress/books/5354.aspx but it seems like that not even help.

i dont want to go to OLEDB stuff because i need to rewrite lots of stuff and for my need right now ADODB is really need it.
Thanks
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:

        rs.Fields("BUY_SELL").Value = "W"
        rs.Fields("TRADE_DATE").Value = Now()
jsctechyInfrastructure Team Lead

Author

Commented:
Jesus why this is happenign to me =/

ok that fix the fields error but i'm getting this error:
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another

the line is this:
 rs.Open(strSQL, oSQLConn)
I tried:
rs.Open(strSQL, oSQLConn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
but still does not work!!!
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
oSQLConn is SqlConnection, not ADODB.Connection?...
jsctechyInfrastructure Team Lead

Author

Commented:
ok so what would be ADODB.Connection like to my code?
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
jsctechyInfrastructure Team Lead

Author

Commented:
i fixed like this:
Dim strConn As ADODB.Connection
        strConn = New ADODB.Connection
        strConn.ConnectionString = "driver={SQL Server};" & _
      "server=ITDEV;uid=ITDEV;pwd=XXXXXXXX;database=DatabaseName"
        strConn.Open()

BUT
i got an error on the second rs.Fields("TRADE_DATE").Value = now() Multi-step operation generated erros. Check each status value
jsctechyInfrastructure Team Lead

Author

Commented:
i guess the error was simple to track down:
it seems like the type Now () can not be insert it into a nvarchar(10)  =)
i guess the datatype has to be change on the database  or instead of inserting the now() insert 12/12/2007 and that will work

I have try this and it works.

Angellll,
Thanks for all you help and your  patience.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.