Summary of VB and related development issues

**************************************** **************************
Record problems and solutions to them:

The following problems exist:
1. Problems Encountered during programming or debugging
2. What you think
3. Estimated

Start record date: 17 Feb 2003

Modify date description
-------------------------------
01 change Apr 2004 to reverse order
**************************************** **************************

 

No :??
---------------
Problem type: VB
Date detected: 15 Sep 2006
Problem description: why is there a common phenomenon that the constant is not defined when running the code in the VB development environment, but it is actually defined in Enum, for unknown reasons?
Resolution Date:
Solution description:
Other notes:
------------------------------------------------------------------

No :??
---------------
Problem type: SQL
Date detected: 08 Sep 2005
Problem description: An SP can run properly in SQL analyzer separately, but fails in job schedule, but no error prompt is displayed, so no cause can be found.
Resolution Date:
Solution description:
Other notes:
------------------------------------------------------------------

No :??
---------------
Problem type: SQL & VB & Excel
Date detected: 21 Jul 2005
Problem description: convert data in Excel to SQL
Resolution Date:
Solution description:
Other notes:
------------------------------------------------------------------

No :??
---------------
Problem type: SQL
Found on: 18 Jul 2005
Problem description: in SQL, execute exec to execute sp, which is very slow, but directly execute the script in SP. Why?
Resolution Date:
Solution description:
Other notes:
------------------------------------------------------------------

No :??
---------------
Problem type: VB
Detected on: 13 Jul 2005
Problem description: When VSS is used in VB, sometimes the second screen in a form cannot be seen without checking out, sometimes it can be seen in the IDE environment, but not in the compiled EXE running state.
Resolution Date:
Solution description:
Other notes:
------------------------------------------------------------------

No :??
---------------
Problem type: VB
Detected on: 13 Jul 2005
Problem description: how to add the add in function in VB to facilitate internal use in VB
Resolution Date:
Solution description:
Other notes:
------------------------------------------------------------------

No :??
---------------
Problem type: VB
Detected on: 13 Jul 2005
Problem description: How to Create a VB wizard and how to use the Wizard to create a template to facilitate basic functions
Resolution Date:
Solution description:
Other notes:
------------------------------------------------------------------

No :??
---------------
Problem type: VB
Detected on: 13 Jul 2005
Problem description: how to add the left icon to the menu
Resolution Date:
Solution description:
Other notes:
------------------------------------------------------------------

Serial number: 39
---------------
Problem type: VB
Date detected: 23 Aug 2006
Problem description: Why are some subscripts unavailable when the control array is incorrect?
Resolution Date: 23 Aug 2006
Solution Description: one or more of the control arrays are added to a collection, but the collection is not cleared during form unload, an error occurs when the control array is not completely loaded again.
Other notes:
------------------------------------------------------------------

No.: 38
---------------
Problem type: VB
Date detected: 31 Jul 2006
Question: How can I directly set a folder in the dialog box without combining the drive/DIR control?
Resolution Date: 31 Jul 2006
Solution: 1. Reference Microsoft shell contaols and automation in reference.
2. Add the following code to a commond button:

Dim objshell as new shell32.shell
Dim ofd as shell32.folder2

Set ofd = objshell. browseforfolder (Me. hwnd, "Please choice a folder.", 16 + 64)
If not ofd is nothing then
Txtdestinationfolder. Text = IIF (right (OFD. Self. Path, 1) = "/", ofd. Self. Path, ofd. Self. Path &"/")
Else
Exit sub
End if

Other notes:
------------------------------------------------------------------

No.: 37
---------------
Problem type: SQL
Found on: 18 Jul 2005
Problem description: When select * into # A from OpenRowSet (...) is used, a prompt in serial number 29 is displayed, but the following prompt is displayed after option (robust plan) is used:
Warning: the query processor cocould not produce a query plan from the optimizer because the total length of all the columns in the group by or order by clause exceeds 8000 bytes. resubmit your query without the robust plan hint.
Resolution Date: 18 Jul 2005
Solution: add the option sub-Statement: Option (robust Plan) to the SQL statement based on the prompt to require the SQL statement to cancel the row size limit.
Other notes:
Example:
Select a. ID, object_name (A. ID) as objname, A. texttype
From syscomments
Inner join fmisdbvn2.dbo. syscomments B
On object_name (A. ID) = object_name (B. ID) and A. colid = B. colid
Where a. ctext <> B. ctext and A. id not in (select ID from sysobjects where type ='s ')
Option (robust plan)
------------------------------------------------------------------

No.: 036
---------------
Problem type: VB & windows
Date detected: 04 May 2006
Problem description: scripting. FileSystemObject File System Processing
Resolution Date: 04 May 2006
Solution: The file system can be created in two ways:
1, set FS = Createobject ("scripting. FileSystemObject ")
2. Reference it first, and then use dim FSO as new FileSystemObject

Other notes:
FSO object model programming involves three main tasks:
Use the Createobject method or declare a variable as the FileSystemObject object type to create a FileSystemObject object.
Use appropriate methods for newly created objects.
Access the properties of this object.
The FSO object model is included in a type library called scripting, which is located in the scrrun. dll file. If this file has not been referenced, select the Microsoft scripting runtime item from the reference dialog box in the "properties" menu.

------------------------------------------------------------------

No.: 035
---------------
Problem type: VB & windows
Date detected: 21 Sep 2005
Problem description: Do I need to change the ODBC of each client on the front end after the SQL user and password change?
Resolution Date: 21 Sep 2005
Solution Description: 1. If a user has been created before, but the user password is different, you do not need to modify or create it again, because the user and password will be added to the vbprogram.
2. If it has not been created before, it can be created again, manually or automatically using a program.
Other notes:
------------------------------------------------------------------

No.: 034
---------------
Problem type: VB & windows
Date detected: 20 Sep 2005
Problem description: How to Use the VB program to change the registry and ODBC
Resolution Date: 21 Sep 2005
Solution Description: 1. You can add or modify the registry in the following ways, but you do not know how to delete the original items or how to handle them without prompting.
"
Regedit4

[HKEY_CURRENT_USER/software/Vb and VBA program settings/crystal FMIS system/Server setting]
"Sqluserid" = "fmisuser-test"
"Sqluserpwd" = "C13417E50A782794F394270F2E7020F5-test"

2. Use a program to establish ODBC. You can use DAO or the API: sqlconfigdatasource.

Other notes:
------------------------------------------------------------------

No.: 033
---------------
Problem type: VB & SQL
Discovery Date: 16 Aug 2005
Problem description: add and subtract data in VB, and the value stored in the database will be 0.01 different.
Resolution Date: 18 Aug 2005
Solution Description: cause 1: if a single or double variable and other types of variables are computed in mathematics, the result will become an inaccurate floating point number and be NN. nn9999... instead of NN. precise NN format;
Cause 2: When a variable or value with multiple decimal places is stored in the recordset, for example, if the field in the recordset is a fixed decimal point and smaller than the variable's decimal place, it will not be rounded down, instead, the CIDR Block value is used, so the difference is 0.01 or 0.001.
Other notes:
Example:
------------------------------------------------------------------

No.: 032
---------------
Problem type: SQL
Found on: 22 Jul 2005
Question: How can I add a script to a job? Why does runnable become false after being added?
Resolution Date: 25 Jul 2005
Solution Description: four steps should be taken: 1, sp_add_job; 2, sp_add_jobstep; 3, sp_add_jobschedule; 4, sp_add_jobserver.
Problem 2 occurs because 4th steps are not processed, but you can manually click OK in the job step modification screen to solve the problem.
Other notes:
Example:
------------------------------------------------------------------

No.: 031
---------------
Problem type: Windows
Detected on: 20 Jul 2005
Problem description: how to save the settings of the current user in the zenwork environment, so that the same environment settings will still be used after the next boot.
Resolution Date: 20 Jul 2005
Solution Description: 1. Configure the settings first, such as placement of shortcuts;
2. Control Panel/system/user profile;
3. Select profile name and record based on the current user, and then click Copy...
4. On the copy to screen, select the target directory: C:/Documents ents and settings/Default User.
5. OK.
Other notes:
Example:
------------------------------------------------------------------

No.: 030
---------------
Problem type: SQL
Date detected: 15 Jul 2005
Problem description: The following error occurs sometimes when a row query or SP is executed:
[Microsoft] [odbc SQL Server Driver] [TCP/IP sockets] connectioncheckfordata (checkfordata ()).
Server: MSG 11, level 16, state 1, line 0
General network error. Check your network documentation.
Connection broken

Resolution Date:
Solution Description: 1. You can reconstruct the indexes of the key tables involved in this solution to solve this problem (in a few cases)
Other notes:
Example:
------------------------------------------------------------------

No.: 029
---------------
Problem type: SQL
Date detected: 12 Jul 2005
Problem description: when comparing large SQL records, especially when the row record content is too long, the following error occurs:
Cannot create a worktable row larger than allowable maximum. resubmit your query with the robust plan hint.
Resolution date: 12 Jul 2005
Solution: add the option sub-Statement: Option (robust Plan) to the SQL statement based on the prompt to require the SQL statement to cancel the row size limit.
Other notes:
Example:
Select a. ID, object_name (A. ID) as objname, A. texttype
From syscomments
Inner join fmisdbvn2.dbo. syscomments B
On object_name (A. ID) = object_name (B. ID) and A. colid = B. colid
Where a. ctext <> B. ctext and A. id not in (select ID from sysobjects where type ='s ')
Option (robust plan)
------------------------------------------------------------------

No.: 028
---------------
Problem type: VB
Date detected: 06 Jul 2005
Problem description: How to Use the pop-up menu calling program in the lower right corner of the System Tray
Resolution date: 12 Jul 2005
Solution: Use Form's popup menu as a reference. Several APIs need to be called before, including callwindowproca/setwindowlonga/shell_policyicona.
Other remarks: a separate module: modtray is available.
------------------------------------------------------------------

 

No.: 027
---------------
Problem type: SQL
Date detected: 02 Jul 2005
Problem description: the SQL server installed on the PC can be entered in local or PC name mode, but cannot be accessed by IP address, and cannot be connected by other PCs.
Resolution Date: 02 Jul 2005
Solution Description: In the general network configuration... in SQL Server properties (configure) of SQL enterprise manage, named piepes and TCP/IP must be included in enable protocols.
Other notes:
------------------------------------------------------------------

No.: 026
---------------
Problem type: SQL
Date detected: 28 Jun 2005
Problem description: an error occurs when all objects scripts are generated: Error 21305: [SQL-DMO] unexpected parameter value or other condition.
Resolution Date: 15 Jul 2005
Solution Description: Use the tracking program to locate the object that has an error, and then exclude its re-generation. You can also recreate the object and generate a script at a time.
Other notes:
------------------------------------------------------------------

No.: 025
---------------
Problem type: VB & SQL
Found on: 22 Jun 2005
Problem description: 1, changed from win2000 (Chinese internal code) + sql7.0 Chinese internal code version to win2000 (English version) + sql2k (English internal code ),
2. The database restores data from the SQL statement with the original Chinese internal code to sql2k, and it can still maintain the Chinese internal code lollation of the text field,
3. the DB and tables of the warehouse system still use Char/varchar, but not nchar/nvarchar;
With the above three conditions, the old system will be tested in this new DB. After the first test, it is found that although the select or view Chinese in the background can be properly displayed, the previous obtained is garbled.
Resolution Date: 22 Jun 2005
Solution Description: The cause is that when you establish a connection with SQL (because the correct INI file is not used), the specified server location for the first time is incorrect and fails, at the prompt of running the program, correct the server and select the database, and then perform system processing. At this time, garbled characters will occur, but some newly added records will be normal.
Solution: Specify the correct server and DB location in the INI file, and the Chinese will be displayed normally.
Other remarks: This may be because the system uses the data environment function. Therefore, when the server and DB are incorrectly specified for the first time, the system uses the default English internal code. if the system is normal for the first time, the system uses the internal code based on the actual situation in the database specified in the SQL statement.
------------------------------------------------------------------

No.: 024
---------------
Problem type: Excel
Date detected: 17 Jun 2005
Problem description: when using special copy and paste in Excel to process a page with a specified format, it is found that the Row Height in the newly generated page is different from the row height in the original page, which is 15, new to 12.5
Resolution date: 17 Jun 2005
Solution: the special paste is still used, but the rows () method is used when the copy range is selected, that is, the row range is used for copy instead of the rang () method, this ensures that the information of related rows is consistent (such as rows with higher levels ).
Other notes:
------------------------------------------------------------------

No.: 023
---------------
Problem type: SQL
Date detected: 24 Mar 2004
Problem description: How to obtain and change the server's Character Set and sorting
Resolution Date: 24 Mar 2004
Solution Description: if you want to back up the database on one server and restore it to another server, the character set (character set) on the two servers must be consistent with the sorting method.
View: exec sp_helpsort or sp_server_info
Improvement: sql7 X:/MSSQL7/binn/rebuildm.exe, dedicated to rebuilding the master database
Other remarks: record previous handwritten records
------------------------------------------------------------------

No.: 022
---------------
Problem type: SQL
Date detected: 24 Mar 2004
Problem description: How to Handle binary Fields
Resolution Date: 24 Mar 2004
Solution: convert a constant value to a hexadecimal value string starting with "Ox. Convert the character to ASCII, then convert it to a hex string (hex function) in hexadecimal notation, and then string a string starting with "Ox.
Use a hexadecimal sequence as a constant in an SQL statement (Note: no single quotation marks are added)
Example: Update Table1 st Password = ox4e675... where id = 1
Other remarks: record previous handwritten records
------------------------------------------------------------------

No.: 021
---------------
Problem type: VB
Date detected: 24 Mar 2004
Problem description: how to deal with text and Image Fields
Resolution Date: 24 Mar 2004
Solution: For ado, the getchunk method and AppendChunk method of the Field object are generally available. However, if the field is greater than 5 MB, the memory may be insufficient and errors may occur.
Method of insurance: Use the writetext and updatetext statements provided by SQL to write less data at a time and continue writing. Disadvantages: network failures may result in incomplete data.
Recommended method: update with the AppendChunk method. If an error occurs, update with the writetext or updatetext statement.
Other remarks: record previous handwritten records
------------------------------------------------------------------

 

No.: 020
---------------
Problem type: SQL
Date detected: 24 Mar 2005
Problem description: During DTS, when the TXT file is transferred to the target table, the corresponding field cannot be found. Automatic check and query requires a new table with a corresponding name.
Resolution Date: 24 Mar 2005
Solution: In DTS, the table name is case sensitive, so the name of the target table must be confirmed strictly by case.
Other notes:
------------------------------------------------------------------

No.: 019
---------------
Problem type: VB
Date detected: 21 Mar 2005
Problem description: The field value in the combo box, which has a value before form. Show, but no more after show. Other controls are normal.
Resolution Date: 21 Mar 2005
Solution: The truedb combo control is often invalid when the from activate initial value is used. You need to reset it later.
Other notes: this problem has occurred many times and is related to combo control. The problem also lies before and after form load.
------------------------------------------------------------------

No.: 018
---------------
Problem type: SQL Server
Date detected: 11 Dec 2004
Problem description: The client can be directly connected using an IP address, but it cannot be successfully connected using TCP/IP in server alias.
Resolution Date: 11 Nov 2004
Solution: Set... (Anthony) on the server)
Other notes:
------------------------------------------------------------------

No.: 017
---------------
Problem type: SQL
Date detected: 25 Nov 2004
Problem description: sometimes there is a prompt in SQL: Intra-query parallelism caused your server command to deadlock
Solution Date: 26 Nov 2004
Solution Description: According to the prompt, the error should be caused by the association between multiple tables, especially the association between the same table and multiple tables, one solution is to simplify the processing of related scripts. Another solution is to set the option value.
Other notes:
------------------------------------------------------------------

No.: 016
---------------
Problem type: SQL
Date detected: 28 Jun 2004
Problem description: when processing the same table record, the same group by statement is used, but no order by clause is used,
Some can be sorted by group by on different dates, but some cannot.
Resolution Date:
Solution Description: 1. Use group by or order.
Other notes:
------------------------------------------------------------------

No.: 015
---------------
Problem type: VB-Tips
Date detected: 05 Jun 2004
Problem description: When operating on the grid, you sometimes need to add a field or change the column order, which leads to many problems.
Resolution Date: 05 Jun 2004
Solution Description: 1. A good solution is to use a method not related to the order and quantity. It is best to use the datafield attribute of the Grid Object;
Other notes:
------------------------------------------------------------------

No.: 014
---------------
Problem type: VB
Date detected: 24 May 2004
Problem description: (some code may be copied) When you modify the code, you will find that the attributes and methods cannot pop up, or you cannot locate stored procedures or functions.
Resolution Date: 24 May 2004
Solution Description: cause 1, which may be due to an error at the beginning of this section or an undeclared variable or object;
2. It may be because an object used in the code needs to be declared for reference in the project, but it is not referenced yet, so the system does not recognize it.
Other notes:
------------------------------------------------------------------

No.: 013
---------------
Problem type: VB
Found on: 01 Apr 2004
Problem description: when running a program, some PCs are normal, and some have "path/File Access Error"
Resolution Date: 10 Apr 2004
Solution: Sometimes, when checking a file, if the file property is hidden, you must specify to check the hidden file before it can be found. In this way, you can perform the next operation.
Other notes:
------------------------------------------------------------------

No.: 012
---------------
Problem type: VB
Found on: 01 Apr 2004
Problem description: when running a program (processing MDB tables), some PCs are normal, while others are "data provider does not exist or cannot be found ."
Resolution Date: 26 Apr 2004/03 Apr 2004
Solution Description: 1. The main reason should be jet3.51, which should be replaced by jet4.0;
2. You can also try if the version of the ADO provider is inconsistent or the version is too low on the problematic computer.
Other remarks: Sometimes the related Ole components cannot be referenced normally due to an office installation problem.
------------------------------------------------------------------

No.: 011
---------------
Problem type: VB
Found on: 18 Mar 2004
Problem description: when running a program, the "select ODBC" dialog box appears, but the program should actually run normally.
Resolution Date: 18 Mar 2004
Solution Description: The reason is that the ODBC parameter (or other parameters) introduced during the execution of the EXE contains lower-case letters. It is best to change all the characters to upper-case letters to make them normal!
Other remarks: (Records of the items found and resolved in the previous year)
------------------------------------------------------------------

No.: 010
---------------
Problem type: VB
Date of discovery: 08 Mar 2004
Problem description: the icon of a vbp tool bar is abnormal after the system is reinstalled.
Resolution Date :??
Solution description:
Other notes:
------------------------------------------------------------------

No.: 009
---------------
Problem category: VFP & SQL
Date of discovery: 05 Mar 2004
Problem description: NULL date and related
Resolution Date: 05 Mar 2004
Solution Description: If a null date value can be null in VFP, the SQL statement to be transferred may change to '31 dec 1899 ', which may cause some problems, update after conversion or check during conversion.
Other notes:
------------------------------------------------------------------

No.: 008
---------------
Problem type: VFP
Found on: 27 Feb 2004
Problem description: When a date field is used as a condition for filtering, it is often unable to be performed due to different formats.
Solution Date: 27 Feb 2004
Solution: Use the ctodd () function to convert the date string, for example, where adate = ctodd ('2017/04 ')
Other notes:
------------------------------------------------------------------

No.: 007
---------------
Problem type: VB
Found on: 27 Feb 2004
Problem description: Sometimes the combox control cannot be automatically located.
Solution Date: 27 Feb 2004
Solution: 1. Check whether automatic positioning is enabled for this control;
2. Check whether the bound field value is a character. If it is not a character (such as a number), you may need to convert the value
Data type;
3. It may also be because the data source is reset when the control gets the focus.
Other notes:
------------------------------------------------------------------

No.: 006
---------------
Problem type: VB
Discovery Date: 19 Nov 2003
Problem description: For a predefined command object, if some input parameters have their default values, the parameter variables in Win98 cannot be assigned a value, but an error occurs in Win2k. "... which was not supplied."
Resolution date: 19 Mar 2003
Solution: if there is a variable value with a default value but no value is assigned, assign the same value to it. You can set the value to the original default value, such as null.
Other notes:
------------------------------------------------------------------

No.: 005
---------------
Problem type: VB
Found on: 14 Apr 2003
Question: How to speed up a database processing program? (Including checking and adding or updating a large number of tables)
Resolution Date: 15 Apr 2003
Solution description:
Improvements can be made in two aspects: software/hardware
1. Improvements in software, including processing methods and improved algorithms;
A. VB
1, to reduce the number of interactions between the client and the server to improve efficiency and speed;
For example, the insert/. Update method for each record is changed to RST. updatebatch.
2. Use a predefined command instead of the select or open RST or cn.exe C method each time;
3. Distribute the one-time wait time among other time points to avoid taking too much time or making the user feel that the waiting time is too long;
B. SQL
1. Using indexes to increase the query and update speed, and modifying the datatype of a field may also affect the speed;
2. Avoid storing a large amount of data in a table;
For items that must be retained and frequently used, you can use other methods to divide them into multiple tables, such as a set of books (for example, store data separately by month or a period of time ), or other Splitting Methods
For extraordinary data, you can delete, compress, and back up expired data (which may be one month or several months ago) based on its general timeliness, keep only necessary and minimum data records
3. Operations on large data tables can be performed in different forms. In the process of saving data from VB to the result table, add an intermediate result table to increase the speed of disk storage, later, the intermediate table data is automatically or manually added to the final result table at one time at a specified or scheduled time to reduce the time consumed for each result table update or new record addition.

2. upgrade and improve the hardware, such as adding memory and CPU.
A. Improve the corresponding configuration standards, such as memory, CPU, and HD Performance (such as speed)
B. Place the corresponding data file of the database on different HD images of the same machine to prompt the data processing speed.

Other notes:
------------------------------------------------------------------

No.: 004
---------------
Problem type: VB
Date detected: 17 Mar 2003
Problem description: In the column of the grid control, you cannot directly enter Chinese characters (or when you press the button for the first time), but after adding a space (or deleting it, you can enter Chinese characters normally.
Resolution Date: 28 mar 2003
Solution Description: At first, you cannot use the code to "add a space, and then delete it". Then, you can lose the effect of Chinese characters. Then, you can find that you use a direct value (assign a space, then assign a vehicle or space) the method is useless, and the sendKey function is used to simulate the direct keyborder input effect. Therefore, when the focus is set to the corresponding column, the sendKey function can be used to simulate the input space.
Other notes:
------------------------------------------------------------------

No.: 003
---------------
Problem type: VB
Date detected: 17 Mar 2003
Problem description: there is an additional control in the grid control, and the carriage return to the grid control should be in the attachment control, but in fact the additional control cannot get the focus in the Code way.
Resolution Date: 28 mar 2003
Solution: doevents process. Add the doevents process to the control code to process multithreading and increase the latency.
Other notes:
------------------------------------------------------------------

No.: 002
---------------
Problem type: VB
Date detected: 17 Mar 2003
Problem description: assign a surface value to the control of another form in one from statement, but it does not show the assigned value but is empty.
Resolution Date: 18 Mar 2003
Solution Description: this problem is mainly because the Assignment time is incorrect and should not be performed in form_load, but in the form_active event.
Other notes:
------------------------------------------------------------------

Serial number: 001
---------------
Problem type: VB
Found on: 17 Feb 2003
Problem description: an error occurs and the program is closed when a program is run.
Resolution Date: Apr 2003 & 29 May 2003
Solution Description: 1. One possible reason is that too much text, especially Chinese, is added to the comment. If so, you only need to remove too much text and then compile it;
2; another case is that a reference item is lost in the reference, but it is displayed in the reference list: missing, re-build the correct reference or discard this reference. You will remove the selected flag and re-compile it.
Other notes: One major reason is that the system cannot automatically prompt such problems during full compilation. Another reason may be caused by richwin.
------------------------------------------------------------------