• (2 Pages)
  • +
  • 1
  • 2

Connecting to a SQL Server database using ADODB How to connect to a SQL server using Visual Basic 6.0 Rate Topic: **--- 3 Votes

#16 Guest_Vipin Tyagi*


Reputation:

Posted 30 April 2010 - 06:50 PM

Dim MyConnObj As ADODB.Connection
Dim myRecSet As ADODB.Recordset

Set MyConnObj As New ADODB.Connection
Set myRecSet As New ADODB.Recordset


OR

Dim MyConnObj As New ADODB.Connection
Dim myRecSet As New ADODB.Recordset

Both connection type have a little difference but both can be used to connect to Visual Basic to MS sql server 2000
Was This Post Helpful? 0

#17 Guest_Strider*


Reputation:

Posted 30 April 2010 - 08:21 PM

View Posthaidan_dan, on 02 June 2008 - 06:30 PM, said:

I've try to implement your code, but it give me an error on this line

Dim MyConnObj As New ADODB.Connection

it says User-defined type not defined, is that anything i must do i implement this code?could you help me to understand this porblem

Was This Post Helpful? 0

#18 Guest_Strider*


Reputation:

Posted 30 April 2010 - 08:24 PM

View PostStrider, on 1 May 2010 - 11:30 AM, said:

I've try to implement your code, but it give me an error on this line

Dim MyConnObj As New ADODB.Connection

it says User-defined type not defined, is that anything i must do i implement this code?could younot help me to understand this porblem


About your complaint, using Dim in ADODB Connection is not advisable, try using this code:

set [name of connection] = new ADODB.Connection

Hope it helps. Strider 002
Was This Post Helpful? 0

#19 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 179
  • View blog
  • Posts: 1,599
  • Joined: 17-April 07

Posted 18 May 2010 - 07:30 PM

View PostNoBrain, on 15 April 2010 - 04:57 AM, said:

so what you say is
Dim MyConnObj As ADODB.Connection
Dim myRecSet As ADODB.Recordset

Set MyConnObj As New ADODB.Connection
Set myRecSet As New ADODB.Recordset



instead of
Dim MyConnObj As New ADODB.Connection
Dim myRecSet As New ADODB.Recordset



why?



1)
Dim obj As MyClass
Set obj = New MyClass


2)
Dim obj As New MyClass


there is difference between this two statements
in the second declaration VB6 will treat the obj as an auto-instantiated variable.
later when you try to use it VB will check if it should be instantiated or not. This will cost you some extra CPU cycles, so the speed of the program might be slightly reduced.

This post has been edited by thava: 18 May 2010 - 07:32 PM

Was This Post Helpful? 2
  • +
  • -

#20 Guest_Ammu*


Reputation:

Posted 23 May 2010 - 02:41 AM

Thank you soo much. It helped me for a good start :)
Was This Post Helpful? 0

#21 knightski  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 04-July 10

Posted 04 July 2010 - 07:40 PM

I have a code to make a new table in ms access. My problem is how to connect to that table after. How to save on that table.
Any help is welcome. Thank You
Was This Post Helpful? 0
  • +
  • -

#22 Guest_cornerlock*


Reputation:

Posted 21 September 2010 - 11:57 AM

View PostCosineMkt, on 19 September 2008 - 11:50 AM, said:

I have the exact same problem. For the following command [Dim MyConnObj As New ADODB.Connection] it gives the error "User-Defined Type Not Defined" when I go to run the program. I looked at the worthless msdn website and it said it needed to be replaced to avoid conflict with ADO but never said what to replace it with.

you have no reference to ado object lib ,try adding one and run it again
Was This Post Helpful? 0

#23 skyv  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 11-October 10

Posted 11 October 2010 - 09:59 AM

Im getting an error saying : INVALID OBJECT NAME 'myTable' at the following code:

myRecSet.Open “select * from myTable”, MyConnObj, adOpenKeyset


I don't know why. Need some help pleazzzzzzz
Was This Post Helpful? 0
  • +
  • -

#24 skyv  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 11-October 10

Posted 12 October 2010 - 01:42 AM

View Postskyv, on 11 October 2010 - 08:59 AM, said:

Im getting an error saying : INVALID OBJECT NAME 'myTable' at the following code:

myRecSet.Open “select * from myTable”, MyConnObj, adOpenKeyset


I don't know why. Need some help pleazzzzzzz


------
Here is my VB code after creating successfully the database, the table and inserting records into the table with SQL:

Private Sub Command1_Click()
Dim MyConnObj As New ADODB.Connection 'ADODB Connection Object
Dim myRecSet As New ADODB.Recordset 'Recordset Object
Dim sqlStr As String ' String variable to store sql command

MyConnObj.Open _
"Provider = sqloledb;" & _
"Data Source=SKYLAPTOP;" & _
"Initial Catalog=TESTATV;" & _
"User ID=sa;" & _
"Password=sql2005;"

sqlStr = "select * from employee"
myRecSet.Open sqlStr, MyConnObj, adOpenKeyset

MsgBox "Total Number of records = " & myRecSet.RecordCount

Dim i As Integer 'variable to keep count i = 1
Print "#"; Tab; "ID"; Tab; "Name"; Tab; "Salary"
Print ""
While Not myRecSet.EOF ' Loop until endd fo file is reached
Print i; Tab; myRecSet(0); Tab; myRecSet(1); Tab; myRecSet(2)'0- 1st filed, 1- 2nd Field and so on...
myRecSet.MoveNext 'Moves the RecordSet pointer to the next position
i = i + 1
Wend
MyConnObj.Close

End Sub

***Now I'm getting an error saying: Invalid object name 'employee' at the underlined line above.....
Was This Post Helpful? 0
  • +
  • -

#25 emperorc  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 20-December 10

Posted 20 December 2010 - 07:01 AM

View Postbharath.karthikeyan, on 23 November 2009 - 03:03 AM, said:

Very Good post...A few optimistation for QTP's VB compliance...The code worked w/o a hitch....Thx a lot,Much appreciated! :)

By any chance...a code to execute an already existant stored procedure in SQL server would be really appreciated from your side.




Was This Post Helpful? 0
  • +
  • -

#26 JimSandberg  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 15-March 11

Posted 15 March 2011 - 04:33 PM

Hi,

This probably is a stupid question, but I don't understand which User ID and Password is being referred to! :S
When I try to run the code, the application stops for a good 15-20 seconds.
After that it gives me an error message saying:

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

I tried googling it and found alot of supposed fixes, nothing has worked yet! Though I'm not sure what the issue might be!
From what I understand, this method of connecting to an SQL does not require an ODBC, right?

Now, is there something crucial that I'm missing? Any settings in the SQL-database I should have another look at?

Help is greatly appreciated!

Thanks in advance
Regards,

Jim
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2