I. Generating the connection string of AdoDb.Connection (Connectionstring) ADO is the mainstream of the currently accessing the database, but its Connectionstring often has a large string and is accessed different databases (such as Access and SQL Server), or access methods (via ODBC and OLE DB) Different, the settings of its specific parameters are very different, which adds a certain difficulty to the connection string. Here, you will introduce two ways to generate connectionstring so that when there is no ready-made data, you will write the needs of the connected string. Method 1, the connection string of the AdodC control generates a connection string. Use VB to create a standard EXE project, first reference the ADODC part (select menu [Project] / [Parts ...], select Microsoft Ado Data Control in the pop-up part window, add it to Form1, the default name is adodc1 Select AdodC1, click Right-click, click "AddC Properties", select "Use Connection Strings", click Generate Buttons, next to the Wizard step by step to enter each related connection information, "OK" Later, in the text box below "Use the Connection String", it is what we need. Method 2, call the ADO connection window to get the connection string. Use VB to create a new standard EXE project, reference Microsoft Ole DB Service Component 1.0 Type libary and Microsoft ActiveX Data Objects 2.x Library (specifically manipulated with Adodc, just by menu [engineering] / [reference ...] Add a TextBox to Form1, double-click Form1, enter the Code editing area, clear all the code generated by VB, then enter the following code:
Option expedition
Private Sub Form_Load () Dim dlTemp As MSDASC.DataLinks Dim cnTemp As ADODB.connection Set dlTemp = New MSDASC.DataLinks Set cnTemp = New ADODB.connection dlTemp.PromptEdit cnTemp Text1.Text = cnTemp.ConnectionString
SET DLTEMP = Nothing set cntemp = Nothing end SUB
When running, a wizard that generates the connection string similar to Adodc will pop up, enter each related data, and then get the connection string we need after Form1 after "OK".
Second, the Chinese Character Code The author I used to add or modify the data to SQL Server 6.5, I found that the Chinese characters found in the query were garbled. I originally thought that the client's environment had problems, and later opened the server-side database, found there in the Chinese characters They are all garbled, and the force of Jiu Niu Erhu is only found: In the process of appending and modification, ADO automatically translates characters (autotranslate), and it has become garbled after translation (Microsoft has opened a "small Small "jokes), the solution is to do not let it translate, the specific operation is as follows: If the ADO is connected directly to the database through the OLE DB, add a string in Connection's Connectionstring: AutoTranslate = false; if the ADO is connected to the ODBC The database is configured when the ODBC is configured, and the "execution character data conversion" is removed. Third, in SQL, the string VB containing single quotes is written in the SQL, and the string data is caused by single quotes, such as: select * from mytable where id = 'firstid', if the firstid is first'ID, That is, a single quotem is more, and the above-described way of writing will result in an error, and the solution is to replace each single quotem in the string, the following function starts QL completes the function, and uses single quotes to handle the characters Cause: Private Function Strtosql (Byval Strign As String) AS String Strtosql = "'" Replace (Strvalue, "'", "'" "'" End Function When writing SQL, there is a string data, Regardless of whether there is a single quotes, you can use this: strValue = "first'id" strsql = "select * from myTable where id =" strtosql (Strvalue)
Fourth, only the previous N records of the query result This is an older, the person who has a little experience will think of this feature in SQL to use the "SELECT TOP" statement, such as accessing the Access database: SELECT TOP 50 * From MyTable SQL Server 7.0 and SQL Server 2000 can be like this, but in SQL Server 6.5, it does not support "SELECT TOP". The author uses a compromised approach to limit the "set rowcount" of SQL Server 6.5. Number of records, for example: MyConnection.execute "SET ROWCOUNT 50" ... 'Execute Query MyConnection.execute "Set RowCount 0" The last line expressed cancellation data limit, this sentence must not be less, because the number of records The survival period of MyConnection is valid, so other queries will also be restricted by this limit, and only 50 records are returned, and the author has suffered.
