Newbie in VB

[victora]

I'm a programmer from a forgotten era. (you know, COBOL, RPG in AS400...) Started with VB 6.0 like 2 weeks ago and man, do i have tons of questions!
Anyway, here's my scenario: I'm trying to access an MS SQL 2000 using ADO. I use the property windows and was able to display records from my table: First,Previous, Next,Last...Eventually I want a button to do Add, Delete, Find, Cancel, Edit, Save

If somebody can give me a sample code to programatically connect to my SQL Server. Pls see below:

Server= myServername
Database=myDatabaseName
Table=myTableName

And what section or event those codes will be?

I have more questions in my mind but I will ask them as needed.

Thank you very much.

 

[fredericofonseca]

first of all it seems you are using ADODC controls instead of ADO code.
If this is the case then CHANGE it now.

regarding all your other questions if you search these forums (forum709 and forum222) you will find plenty of samples and FAQ's to help you.

Once you have done so and tryed some code please come back with your code and more specific problems and we will help you as we can.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[BobRodes]

Just to get you started: Begin with the connection object and the recordset object in ADO. Those two are enough to grab some data. Connections have to do with how you connect to the data source you're using, recordsets are cursors of varying types on subsets of the data source. One of the things that varies with every data source is the ConnectionString property of the Connection object. This is a string based parameter set that the connection object parses to access the other end. This faq faq222-3670 will give you some idea of the different types of recordsets and so on.

Since you're experienced in other areas, maybe a bit of a map will be helpful. OLE DB is a set of COM interfaces that manages data. OLE DB has consumers and providers, and therefore acts as a brokering mechanism, or level of indirection, between the source and the destination of data. ADO is an OLE DB consumer. Various providers exist, e. g. SQLOLEDB, which accesses SQL Server DBs.

Many older data access methodologies, for example ODBC, have been updated to work with OLE DB. There is, then, an OLE DB provider for ODBC, which is itself a level of indirection between consumers and providers. Therefore, solutions using ADO and ODBC have two such levels, and in this case, two's a crowd if it can be avoided. (An older dbms may only support ODBC and not have a direct OLE DB provider; in this case the OLE DB provider for ODBC needs to be used.) Now, if you are maintaining legacy DAO or RDO code, you're generally using ODBC (or JET, in the case of DAO) to access data. In this case, you may also run into DBMS's that no longer support ODBC except through an OLE DB provider, another example of the "two's a crowd" phenomenon. So, in general, use ADO when you can, and set your provider in your ConnectionString to an OLE DB provider appropriate to your DBMS.

It's the ConnectionString property of the Connection object that determines the provider, the database, and various other things. On the other hand, it's the Recordset object that determines the table name. In your research, then, concentrate on the aspects of the connection object that allow access to the data provider via OLE DB, and become familiar with the Recordset object. That will be what you need to return and view data sets.

Finally, as for insertions, updates and deletes: while the Recordset object has methods to alter data in the source, I generally don't use them. I prefer to use SQL Action queries (insert, delete, update). The ADODB Command object is there to handle SQL commands that do not return sets of data to the consumer context. After learning Connection and Recordset objects, I would take up the Command object. It's also necessary when working with Stored Procedures.

HTH

Bob

 

[shannanl]

Victoria, I use this code to access a SQL Server DB:

Dim conSave As New ADODB.Connection
Dim comSave As New ADODB.Command
Dim rstSave As New ADODB.Recordset

With conSave
.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog = " & strDBNAME & ";Data Source = " & strDBLOCATION & ""
.CursorLocation = adUseClient
.Open
End With

With comSave
.ActiveConnection = conSave
.CommandType = adCmdText
.CommandText = "SELECT FIELD FROM myTableName
ORDER BY THIS"
End With

Set rstSave = comSave.Execute

I set the database name and location into a string when I first load the program. Hope this helps.

Shannan

 

[victora]

Thanks to everybody! Answer from fedrico and bobrodes are very much appreciated. I have to copy/paste that one and save it. Very enlightening concept!
The reply from Shannanl is the one I'm looking for. I did just that with my own parms and run it. But I got this 'compile error: User-defined type not defined'

Heres my actual code:
Private Sub cmdConnect_Click()
Dim conSave As New ADODB.Connection
Dim comSave As New ADODB.Command
Dim rstSave As New ADODB.Recordset

With conSave
.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog = " & mc_main_exl & ";Data Source = " & CommuniCap - Exl & ""
.CursorLocation = adUseClient
.Open
End With

With comSave
.ActiveConnection = conSave
.CommandType = adCmdText
.CommandText = "Select * from XL_ClaimsProcessingLog "
End With

Set rstSave = comSave.Execute
End Sub


Another question:
Normally, where do you keep this codes? For testing, I created a command button just to check if its a clean compile/execution.

I had a connection established on my first attempt to access the table using the properties window. The ConnectionString is: Provider=MSDASQL.1;Persist Security Info=False;User ID=Administrator;Data Source=CommuniCap - Exl

Can I use this string in your code above?

Thank you

 

[fredericofonseca]

I'm glad you have appreciated my answer. It is nice to know we have helped someone.

In the meantime as it seems you are having some problems with your code may I suggest you look at this faq709-1526 as you will get a fully working example of accessing a DB with ADO. At the same time this will highlight one of the problems you are bound to find on your development.

Regarding using connection strings, may I suggest you search these forums for "connection" "strings" and you will find some links to sites with ALL type of connections to ALL type of databases.


And one thing you are probably missing is adding a reference to the required MDAC. You do this through your project->references menu option.

Use MDAC 2.8. If you don't have it then download it from

http://www.microsoft.com.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[BobRodes]

I would keep it in a procedure, and call it when needed. Make the procedure of as small a scope as possible. In other words, don't make it available to your entire program if the entire program doesn't use it.

As for the code itself:
1. Don't use the New keyword on the same line as the declaration. Break them up so:

Dim conSave as ADODB.Connection
Set conSave = New ADODB.Connection

(Do the same with your recordset object.)
This is more efficient, because VB doesn't actually instantiate the object when it's declared. Instead, every time you reference the object in your code (use a method or property), VB has to check and see if the object has been instantiated yet, and if not, instantiate it. That process of checking creates unnecessary overhead.

2. You don't have to use the command object in this situation. You use it either for action queries (insert, update, delete) or running stored procedures. (To run a stored procedure, take shannanl's syntax, change the commandtype to adStoredProc, and the commandtext to the name of the stored proc.) In this case,

Set rstSave = comSave.Execute

can be changed to

rstSave.Open "Select * from XL_ClaimsProcessingLog ", conSave

You can now remove all the code mentioning the comSave object. This is generally to be preferred as it is more efficient in terms of resources (one less object, no carrying of state).

One more thing: Shannan's code makes a client-side recordset. This is generally preferable, as it represents less overhead on the server. If you're interested, read up on disconnected recordsets, which allow a client-side cursor (i. e. recorset) to drop its connection once it has the data, allowing the connection to be recycled into the connection pool. When possible with respect to the functional requirements of the application (meaning, quite often), this represents the most efficient use of connections.

To answer your question: No, you can't. As you can see, the provider is different. MSDASQL is the OLE DB provider for the ADO Data Control. SQLOLEDB is the OLE DB provider for the ADO Object Library. (If you're using the above code, remove your Data Control--you don't need it.)

HTH

Bob