May 14, 2011 at 2:14 am
Dim rst As New ADODB.Recordset
Dim con_sql As New ADODB.Connection
Dim fld As ADODB.Field
con_sql.Open("File Name=E:\sql.udl;UID="";PWD=""")
rst.Open("delete from titleauthor", con_sql, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockPessimistic)
rst.Open("insert into titleauthor(au_id,title_id,au_ord,royaltyper)values('1-1','po',1,1)", con_sql, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockPessimistic)
rst.Open("select * from titleauthor", con_sql, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockPessimistic)
While Not rst.EOF
For Each fld In rst.Fields
ListBox1.Items.Add(fld.Value & ",")
Next
rst.MoveNext()
End While
rst.Close()
con_sql.Close()
May 14, 2011 at 6:12 am
It would have been nice had you posted the error message returned when you executed your code, now it is my guess that your connection string is invalid.
I have successfully used as a connection string:
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Software Manager Database;Data Source=MCC
Catalog= the name of the database. which in this case is Software Manager Database
Data Source = the name of the server
If you would like to know a "neater" method to write a subroutine to open any SQL 2000 database using VB 6, post again and ask for same and I will try to assist you further.
May 16, 2011 at 2:29 am
Hi,Ron.
Thanks.
I want to know a "neater" method to write a subroutine
to open any SQL 2000 database using VB 6 .
I have overlooked something about foreign key.
But what vb6 is better that vb 2005 ?
May 16, 2011 at 7:09 am
I have also used this module in VB5 and simply moved it into VB6 to keep up to date.
What I would suggest to you is to just execute this bit of code in DEBUG mode. Build a simple form with say one command buttion and when you click on that button have it execute this code. Pay attention to your form as you do so. I have also used the Microsoft OLE DB Service Component 1.0 Type Library in VB.NET applications and it still performs in the same manner.
Private Sub CmdConnection_Click()
'
'In project references you MUST select
' Microsoft OLE DB Service Component 1.0 Type Library
' This in turn loads oledb32.dll into your project
' then
' Dim objMSDAC As MSDASC.DataLinks
'
' In project reference you MUST select
' Microsof ActiveX Data Objects 2.8 Library
' This in turn loads Msado15.dll into your project
'
' Note if invoking the approle then pay attention to:
'
' http://qa.sqlservercentral.com/Forums/Topic533262-146-'5.aspx#bm568983
'
' 'For SQLOLEDB provider
' strConnect = "Provider=SQLOLEDB;server=SQL7Web;OLE DB Services = -2;
' uid=AppUser;pwd=AppUser;initial catalog=northwind"
'
' For MSDASQL provider
'strConnect = "DSN=SQLNWind;UID=Test;PWD=Test; OLE DB Services= -2"
'
Dim I As Integer
Dim J As Integer
Dim Errx As Long
On Error GoTo E_Han -- my simple error handing code - trivial
Set objMSDAC = New MSDASC.DataLinks
'
' The following will display a form which is contained in the
' Msado15.dll Simply follow the instructions on this form
'
Cnn_String = objMSDAC.PromptNew
Cnn.ConnectionString = Cnn_String
'
' Extract database name
'
I = InStr(1, Cnn_String, "Initial Catalog")
Dbnamr = Mid(Cnn_String, I + 16, Len(Cnn_String) - (I + 15))
'
' Extract Catalog Name
'
J = InStr(1, Dbnamr, ";")
Catalog_Name = Left(Dbnamr, J - 1)
If Cnn.State = adStateOpen Then
Cnn.Close
End If
Cnn.Open
I have used the above code to access SQL Server starting with version 7.0, up to and including 2005.
Hope this assists you in accomplishing what you need to do.
May 16, 2011 at 7:39 am
Thank Ron for comprehensive reply.
This should help not only me to understand the next steps
that someone needs to be done.
Sergey
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply