ADO ( ایجاد پایگاه داده )

[آتوسا]

من تا به حال با پایگاه داده کار نکردم . با مراجعه به مراجعی که داشتم فقط ایجاد یه پایگاه داده به شیوه ADO و به کمک ADO Control و Data Grid و Data List و Data Combo رو یاد گرفتم . در این زمینه به دو سه اشکال برخوردم . یکی این که اگه بخوام اطلاعات از data base در یک text box لود بشه چی کار باید بکنم . تنها راهی که به دست آوردم اینه که اطلاعات اول به داخل یکی از اون کنترل هایی که ذکر کردم بره بعد به داخل text box بشه . ( textbox.data source رو مساوی adodc.readsource قرار دادم ولی هیچ اتفاقی نیافتاد ) خوب اگه من بخوام بدون واسطه این کار رو بکنم چی کار باید بکنم . دوم اینکه برای ارتباط با پایگاه داده حتما باید از کنترل ADO استفاده کنم ؟ ( البته ظاهرا روش دیگری هم یعنی Data Environment وجود داره که نمی دونم کدوم بهتره ) سوم هم اینکه می خوام در ابتدا read source کنترل ado خالی باشه ولی کامپایلر error میده . در ضمن وقتی در هنگام اجرای برنامه data source یکی از کنترل ها رو مقدار دهی می کنم باز هم با error مواجه می شوم ( منظورم کنترل های Data Grid و Data List و Data Combo است ) ممنون میشم جواب بدین :oops:

[zapata-es]

با سلام
برای اینکه بتونی یک مقدار خاص رو از یک دیتا درون تکست باکس بریزی کافیه که datasource تکست باکست رو به بانک اطلاعاتی خودت لینک بدی بعدش روی خاصیت datafield تکست باکست بری و از لیستی که باز میکنه(که همون فیلدهای جدولت هست)یکی رو انتخاب کنی.

دوم اینکه برای ارتباط با پایگاه داده حتما باید از کنترل ADO استفاده کنم ؟ ( البته ظاهرا روش دیگری هم یعنی Data Environment وجود داره که نمی دونم کدوم بهتره )
خب پیشنهاد میکروسافت استفاده از adoهست.و به نظر من که خیلی خوب و راحته.
در باره اون پیغام خطاها هم بیشتر توضیح بده .کد خودت و پیغام خطا رو بنویس شاید بتونیم کمک کنیم. :wink:
خوش باشی

[Alirezagoodarzi]

اگر مایل به استفاده از ADO هستیدکه ابزار خیلی خوبی هم هست بهتر است از فرم Unbound آن استفاده کنید.

در این حالت دست شما بازتر است و راحت تر برنامه تان کنترل میشود.
برای خوندن متون زبان اصلی تمابل دارید؟

[zapata-es]

علی جان من شدیدا تمایل دارم.لطف میکنی :flower:

[Alirezagoodarzi]

ببین دوست عزیز ADO تکنیکی برای اتصال به بانک اطلاعاتیست.
شما یک سری کنترل دارید که میتونه Bound بشه . به یک Datasource.
ولی مستقیم نمیتونه به یک db وصل بشه.یک واسطه میخواد.این واسطه منابع ADO است.

یکسری کنترلها مثل adodc واسطه میشن.یعنی براحتی با کمک ADO به بانک اطلاعاتی وصل میشن و از این میتونن Datasource کنترلهای دیگه مثل Textbox یا datagrid بشن.که راه ساده ولی دست وپا گیر استفاده از ADO است.

بجای این adodc شما میتونید از یک سری Object مربوط به ADO استفاده کنید و به DB وصل بشید و از متدها ، خواص و رویدادهای این اوبجکتها برای کار با db بهره بگیرید.

این اوبجکتها را در کتابخانه ActivX Data Object Library در قسمت Refrences باید اول در وی بی فعال کنید.

mainmunu/Project/Refrences/Microsoft Activex Data Object 2.x
Library

حالا در کد یک فورم دو Object از نوع Connection و Recordset که از اوبجکتهای ADO هستند ایجاد میکنیم.

Private Sub Form_Load()
dim cn As New ADODB.Connection
dim rs As New ADODB.Recordset
end sub

حالا یک شی بنام cn از نوع Connection داریم که به DB وصل میشه .این ارتباط را باید به cn بفهمانیم .برای این کار از یک ConnectionString که از خواص cn است استفاده میکنیم.

اجزاء این ConnectionString برای ارتباط با DB های مختلف مثل Access و SQL یا Paradox یا .... فرق میکنه ولی ساده است وراحت نگران نباشید.
حالا شما کد بالا را ایجاد کنید و در یک خط جدید جلوی cn نقطه بذارید و خواص متدها و رویدادهای اون رو مرور کنید من مهمون دارم باید برم بقیش رو هم ادامه میدم.

ببخشید در حد بسیار خلاصه برای باز شدن موضوع اینها رو نوشتم امیدوارم مفید باشه.
مقاله انگلیسی هم براتون جمع و جور میکنم و میذارم.

[آتوسا]

دوستان عزیز از پاسخ هاتون ممنونم . آقای گودرزی از بابت این نکته واقعا متشکرم . اتفاقا یک مشکل من این بود که ترجیحا نمی خواستم از کنترل ADO در برنامه ام استفاده کنم . در ضمن دوست عزیز در مورد اون متون اگه لطف کنین خیلی خوشحال میشم

[Alirezagoodarzi]

خوشحالم

How Do I Use the Connection Object in ADO?
Microsoft Corporation

April 1, 1998

What Is a Connection Object?
A Connection object represents a physical connection to a data store. To create a Connection object, you will supply the name of either an ODBC data store or an OLE DB provider. When you open the Connection object, you attempt to connect to the data store. The State property of the Connection object tells you whether you succeeded or failed. You can send SQL statements or run stored procedures by using the Execute method of the Connection object. If the command you send to the data store returns records, a Recordset object will be created automatically. You close the Connection object when you are through with it.

What Are the Connection Object's Methods and Properties?
The following table lists some of the more commonly used methods of the Connection object.

Method Description
Open Opens a connection to a data store.
Close Closes a connection and any dependent objects.
Execute Executes the specified query, SQL statement, stored procedure, or provider-specific text.
BeginTrans Begins a new transaction.
CommitTrans Saves any changes and ends the current transaction. It may also start a new transaction.
RollbackTrans Cancels any changes made during the current transaction and ends the transaction. It may also start a new transaction.


The following table lists some of the more commonly used properties of the Connection object.

Property Description
ConnectionString Contains the information used to establish a connection to a data store.
ConnectionTimeout Indicates how long to wait while establishing a connection before terminating the attempt and generating an error.
CommandTimeout Indicates how long to wait while executing a command before terminating the attempt and generating an error.
State Indicates whether a connection is currently open, closed, or connecting.
Provider Indicates the name of the provider used by the connection.
Version Indicates the ADO version number.
CursorLocation Sets or returns a value determining who provides cursor functionality.


How Do I Use the Connection Object to Connect to a Data Store?
To use a Connection object, simply specify a connection string, which identifies the data store you want to work with, and then call the Open method to connect.

The easiest way to open a connection is to pass the connection string information to the Open method. To determine whether the Connection object worked, you can use the State property of the Connection object. State returns adStateOpen if the Connection object is open and adStateClosed if it isn't. Here is an example of connecting to SQL Server by using an ODBC data store:

Sub ConnectionExample1()
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection

' Open a Connection using an ODBC DSN.
cnn.Open "Pubs", "sa", ""

' Find out if the attempt to connect worked.
If cnn.State = adStateOpen Then
MsgBox "Welcome to Pubs!"
Else
MsgBox "Sorry. No Pubs today."
End If

' Close the connection.
cnn.Close

End Sub

If you need to connect to only one data store, the procedure followed in the above code is the easiest way. Alternatively, you can create a Connection object and set the ConnectionString property before calling the Open method. This approach allows you to connect to one data store and then reuse the Connection object to connect to another data store.

Sub ConnectionExample2()
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection

' Open a connection using an ODBC DSN.
cnn.ConnectionString = "DSN=Pubs;UID=sa;PWD=;"
cnn.Open

' Find out if the attempt to connect worked.
If cnn.State = adStateOpen Then
MsgBox "Welcome to Pubs!"
Else
MsgBox "Sorry. No Pubs today."
End If

' Close the connection.
cnn.Close

End Sub

This method also gives you the opportunity to set other properties of the Connection object before connecting. For instance, you might want to set the connection time-out:

Sub ConnectionExample3()
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection

' Set properties of the Connection.
cnn.ConnectionString = "DSN=Pubs;UID=sa;PWD=;"
cnn.ConnectionTimeout = 30

' Open the connection.
cnn.Open

' Find out if the attempt to connect worked.
If cnn.State = adStateOpen Then
MsgBox "Welcome to Pubs!"
Else
MsgBox "Sorry. No Pubs today."
End If

' Close the connection.
cnn.Close

End Sub

This syntax for the ConnectionString property assumes that the data store has already been created by using the ODBC Administrator (or in code). It is becoming increasingly popular to not have to rely on existing ODBC data stores. This eases the setup burden. The next example shows an alternative method for connecting to SQL Server, relying merely on the existence of the ODBC driver itself:

Sub ConnectionExample4()
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection

' Open a connection by referencing the ODBC driver.
cnn.ConnectionString = "driver={SQL Server};" & _
"server=rgreennt;uid=sa;pwd=;database=pubs"
cnn.Open

' Find out if the attempt to connect worked.
If cnn.State = adStateOpen Then
MsgBox "Welcome to Pubs!"
Else
MsgBox "Sorry. No Pubs today."
End If

' Close the connection.
cnn.Close

End Sub

Today there are a wide variety of ODBC drivers you can use with ADO to talk to data. In the future, there will be more OLE DB providers available to connect to data stores. The Microsoft® OLE DB Provider for ODBC is currently the default provider for ADO. You can use a different provider by setting the Provider property of the Connection object.

Sub ConnectionExample5()
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection

'Set the provider property to the OLE DB Provider for ODBC.
cnn.Provider = "MSDASQL"

' Open a connection using an ODBC DSN.
cnn.ConnectionString = "driver={SQL Server};" & _
"server=rgreennt;uid=sa;pwd=;database=pubs"
cnn.Open

' Find out if the attempt to connect worked.
If cnn.State = adStateOpen Then
MsgBox "Welcome to Pubs!"
Else
MsgBox "Sorry. No Pubs today."
End If

' Close the connection.
cnn.Close

End Sub

In the code above, setting the Provider property is not necessary because the OLE DB Provider for ODBC is the default provider for ADO. However, this shows you how you would change the provider when you want to use other OLE DB providers.

How Do I Use the Connection Object to Execute a Command?
The Execute method is used to send a command (an SQL statement or some other text) to the data store. If the SQL statement returns rows, a Recordset object is created. (The Execute method always returns a Recordset object, but it is a closed Recordset if the command doesn't return results.)

Sub ConnectionExample6()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cnn = New ADODB.Connection

' Open a connection by referencing the ODBC driver.
cnn.ConnectionString = "driver={SQL Server};" & _
"server=rgreennt;uid=sa;pwd=;database=pubs"
cnn.Open

' Create a Recordset by executing an SQL statement.
Set rs = cnn.Execute("Select * From authors")

' Show the first author.
MsgBox rs("au_fname") & " " & rs("au_lname")

' Close the connection.
rs.Close

End Sub

Remember that the returned Recordset object from connection.execute is always a read-only, forward-only cursor. If you need a Recordset object with more functionality, you should first create a Recordset object with the desired property settings and then use the Recordset object's Open method to execute the query and return the desired cursor type.

In the following example, the command passed to the data source is a Delete statement. Because no rows are returned, you do not need to explicitly use a Recordset object. How many rows were deleted? You can use the recordsAffected parameter to find out.

Sub ConnectionExample7()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cnn = New ADODB.Connection

' Open a connection by referencing the ODBC driver.
cnn.ConnectionString = "driver={SQL Server};" & _
"server=rgreennt;uid=sa;pwd=;database=pubs"
cnn.Open

' Send a Delete statement to the database.
cnn.Execute ("Delete From authors Where au_id = '011-01-0111'")

' Find out how many rows were affected by the Delete.
Set rs = cnn.Execute("Select @@rowcount")
' Display the first field in the recordset.
MsgBox rs(0) & " rows deleted"

' Close the connection.
rs.Close

End Sub

In the next example, the command passed to the data store specifies the name of a stored procedure to run. Because rows are returned, you do need to use a Recordset object.

Sub ConnectionExample8()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cnn = New ADODB.Connection

' Open a connection by referencing the ODBC driver.
cnn.ConnectionString = "driver={SQL Server};" & _
"server=rgreennt;uid=sa;pwd=;database=pubs"
cnn.Open

' Create a recordset by running a stored procedure.
Set rs = cnn.Execute("Exec byroyalty 50")

' Loop through the recordset and show the author's ID.
Do While Not rs.EOF
MsgBox rs("au_id")
rs.MoveNext
Loop

' Close the connection.
rs.Close

End Sub

[Alirezagoodarzi]

ADO is short for Microsoft ActiveX Data Objects. It is the technology that is used to communicate with databases (and many other types of data sources). This tutorial will attempt to teach you the basics of ADO, and give you enough knowledge to do 90% of the tasks you will ever need to use ADO for. In most cases with ADO there are several different ways to do a specific task, this tutorial will only demonstrate one way to do things, although in my opinion the way demonstrated here is the most clear, straightforward, and flexible way to do things, and the method I personally use all the time.

To use ADO in your project you must go to the Project Menu within VB and choose the References... option. This will bring up the references dialog box. Find the Microsoft ActiveX Data Objects 2.x Library. There are various version from 2.0 to the latest which I believe is 2.6 or 2.7. As long as its 2.0+ then you will be fine. If for some reason it doesn't appear as an option in this dialog you can install it by downloading MDAC (Microsoft Data Access Components) from http://www.microsoft.com/data/download.htm.


Now the first thing you need to do is create an ADODB.Connection object and use it to create an open connection to your database. To do this you create a variable of type ADODB.Connection, and create a new instance of it. You then have to specify a ConnectionString, which is a string containing all the information that ADO needs in order to find and access your database. It will include things such as what type of database it is and what database driver it needs to use (for example MS Access, SQL Server, Oracle, DB2, Informix, etc), it will also include the location of the database (for access this will be the location of the mdb file, for SQL Server this may be a server name or IP address), it may also include a user name and/or password that is needed to access the database, along with potentially lots of other information that the specific database you are connecting to requires. To see some templates you can use to construct your connection string from you can visit www.connectionstrings.com , they maintain a list of various template connection strings to access most of the popular databases. For this example I will be accessing an Access database, and the connection string will look like this: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyAccessDB.mdb;" . If you compare this to the template from www.connectionstrings.com you will notice I have left out the User ID and password sections, this is because I have not setup a user id/password on my particular access database. Once you have figured out what connection string you need to use, you set the .ConnectionString property of your ADODB.Connection object. You then call the .Open method of your Connection object to tell ADO to open the connection to your database. So far the code should look as follows:

Dim MyConn As ADODB.Connection

Set MyConn = New ADODB.Connection
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyAccessDB.mdb;"
MyConn.Open


Now we need to actually retrieve some data from the database. To do this you should have a basic understanding of SQL (Structured Query Language) as this is what I use to communicate with the database (it is possible to use ADO without using SQL at all, but I highly recommend using SQL statements as they give you better control and understanding of the communications taking place between ADO and your database). If you have never had to deal with SQL there is a good tutorial at www.w3schools.com/sql (and I plan on writing my own SQL tutorial in future). Learning and using simple SQL statements should be very easy for anybody, but SQL is also capable of doing VERY complex and powerful operations. So it is one of those things that is easy to learn, but difficult to master. For this tutorial I will only be using very basic SQL statements, and you can probably figure out what they are doing even if you have never seen SQL before in your life.

So to retrieve data from a database you have to use a SQL SELECT statement. Once you have figured out what the appropriate SQL statement should look like that will return the data you want, you send it to the database using the .Execute method of your Connection object. For this example the SQL statement I will be using will be "SELECT CustomerName, CustomerAddress, CustomerPhoneNumber FROM Customers". This will retrieve the Name, Address and Phone Number of all records (customers) in the table called Customers.

When executing SQL statments that return data (such as a SELECT) statement, the .Execute method will return an ADODB.Recordset object which will contain the data you requested. So the first thing you need to do is Dim a variable of type ADODB.Recordset, but you do NOT need to instantiate a new instance (using the New keyword) as the .Execute method will create it for you. Once you've done that, you use the Set keyword to set your recordset object equal to the result from the .Execute method. So far our code should look like this:

Dim MyConn As ADODB.Connection
Dim MyRecSet As ADODB.Recordset

Set MyConn = New ADODB.Connection
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyAccessDB.mdb;"
MyConn.Open

Set MyRecSet = MyConn.Execute("SELECT CustomerName, CustomerAddress, CustomerPhoneNumber FROM Customers")

MyConn.Close


After you have retrieved your data you should probably close the connection to the database (if you aren't planning on using it again in the near future). That is what the MyConn.Close line is doing there. As you can see, this code will create a connection to the database, execute a SQL statement to retrieve some data, and stuff the data into the MyRecSet object. It then closes the connection to the database.

Now we want to display the retrieved data on the form. I am just going to use a simple listbox to do this. I have placed a listbox on my form and called it lstData, I will put this data into it with 1 list item per customer, and I will display the data in the format: Name - Address - PhoneNumber. In order to do this we will have to loop through each record in the RecordSet object, and call .AddItem on the listbox. To do this we have to know a little bit about how the recordset works. The Recordset object is like a big list of data records. There is a current record that the recordset is "at" and it provides methods to allow you to move to the next record, the previous record, the first or the last record. It also provides methods to allow you to retrieve data from the current record. One important property is the .EOF property of the Recordset, this returns true if the current record is not valid, and false if it is valid. You can use this to check if there is any data in the recordset initially, if there was no data returned from your .Execute() call, then the Recordsets .EOF property will initially return True. Once we know we are on a current record we retrieve data from the Recordset by accessing the Fields collection. The Fields collection is used the same way as a standard VB collection, and it contains a member for each field (column) in the current record (row). You can access a particular member by using the .Item() property, and giving either a number representing the index of the field, or a string representing the name of the field. In my example the valid names would be "CustomerName", "CustomerAddress", and "CustomerPhoneNumber", or we could alternativly use the numeric indexes of 0, 1, and 2 respectively (although using the numeric indexes is generally considered a bad practice). This will get us an object representing the specified field of the current record, we can then use the .Value property to get the actual data out of it (if this seems long-winded there are coding shortcuts you can take which I will demonstrate in a minute). You will have to do this for every field you are interested in, and then when you have retrieved all the data you want from this record, you can move the current record using .MoveNext, .MovePrevious, .MoveFirst, or .MoveLast methods of the Recordset object. Once you have moved the current record don't forget to check the .EOF property again to make sure you have not gone past the end (or the beginning if you are moving backwards, which can be checked using .BOF) of the available data. So the code to retrieve our data and place it into a listbox will look like this:

Dim MyConn As ADODB.Connection
Dim MyRecSet As ADODB.Recordset
Dim strName As String
Dim strAddress As String
Dim strPhone As String

Set MyConn = New ADODB.Connection
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyAccessDB.mdb;"
MyConn.Open

Set MyRecSet = MyConn.Execute("SELECT CustomerName, CustomerAddress, CustomerPhoneNumber FROM Customers")

Do Until MyRecSet.EOF
strName = MyRecSet.Fields.Item("CustomerName").Value
strAddress = MyRecSet.Fields.Item("CustomerAddress").Value
strPhone = MyRecSet.Fields.Item("CustomerPhoneNumber").Value

lstData.AddItem strName & " - " & strAddress & " - " & strPhone

MyRecSet.MoveNext
Loop

MyConn.Close


Now the line strName = MyRecSet.Fields.Item("CustomerName").Value seems very long-winded and there are many ways that you can shorten that syntax. The following lines all do the exact same thing and are just coding shortcuts, you can use whichever one you prefer:

strName = MyRecSet.Fields.Item("CustomerName").Value
strName = MyRecSet.Fields.Item("CustomerName")
strName = MyRecSet.Fields("CustomerName")
strName = MyRecSet("CustomerName")

I would like to take a moment to mention another available syntax. The following is a valid syntax, and will do the exact same thing as the 4 lines above, however it is an obsolete syntax that is a left over from long ago. I would recommend not using it, and instead using one of the options from above.

strName = MyRecSet!CustomerName

You can also use similar code to update the data in your database. All you need to do is determine what the appropriate SQL statement would be. It will most likely be an UPDATE or an INSERT statement, and you pass it to the .Execute method of your connection object. ADO will send the SQL statement to the database, and the database will do the appropriate actions as specified by your SQL statement. You usually will not be expecting data to be returned from an UPDATE or INSERT SQL statement, so you will not need to store the result from the .Execute statement. This is a simple example of adding a new customer:

Dim MyConn As ADODB.Connection

Set MyConn = New ADODB.Connection
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyAccessDB.mdb;"
MyConn.Open

MyConn.Execute("INSERT INTO Customers(CustomerName, CustomerAddress, CustomerPhoneNumber) VALUES('Dylan Smith', '123 Fake Street', '(204) 888-8888')")

MyConn.Close


You do the exact same thing for updating existing records, except you replace the SQL statement passed to .Execute with the appropriate UPDATE SQL statement.

This hopefully has taught you the basic concepts you need in order to use ADO to interact with databases. ADO will allow you to interact with any database which has an ODBC or OLE DB compatible driver (which is pretty much every current database), and that driver is installed on the current PC. If you wish to do more complex database interaction, all you need to do is pass in more complex SQL statements to the .Execute method. ADO will also allow you to do things such as execute stored procedures on the database server (if your target database supports them), and retrieve metadata describing the structure of the database.