The first time the data center charges granular Data Warehouse

Preface

The IDC room is also a long-running battle. I encountered a self-testing problem in the middle and was isolated by myself for a while. However, this just shows that my time management is poor, it seems that the direction of my future efforts has been found. When starting to knock on the data center, as said by Shi Ge, try to avoid the ready-made code used by SHI Jie, and think about the function implementation by yourself, this is also a great benefit, that is, you have a clear idea, not as dizzy as you think about students, at least you have mastered a lot of initiative. Of course, the bad thing is that the code you write is not technical. Although it is very simple, it is complicated, but it is all over, at least clearly where you can do better.

Technology Learning Database

• Additional database
  After watching the video, I was unwilling to use the database, so I built a database by referring to the previous database, which also found many problems. When a database is attached, a reminder is as follows:
  

  Error: 5120, that is, permission issue. Find the MDF file we want to attach, right-click the property-security, and check the full control in the file to attach the file:
  

  In this way, the database attaching problem can be solved.

• ID column
  When the ID column is used in the database, I checked it. Baidu explained this as follows: the ID column in SQL Server, also known as the ID column, is also called an auto-increment column. You can create and modify an ID column in the Enterprise Manager or using a Transact-SQL statement.
  The question is, how can we set it? It's actually very simple. Right-click the table you want to design and add the column you want to make an ID column in the ID column on the right!

• Modify Database
  Sometimes the content in the database is not allowed to be modified. I gave up when I hit the students. What the elder sister said is right. It is always necessary to pay back the mixed content. At this time, you may think that you can save some trouble, after that, you will have a big event. It's actually quite simple. In the database, click tool-Option-open as shown in. The check box is removed to modify the table.

Controls

• Msflexgrid
  For example, in the figure below, to delete the selected content, there are two parts to delete: first, delete the content in the database, and second, delete the content in the grid, the first row cannot be deleted when the content in the grid is deleted.

  
  The following is code analysis:

'Locate the selected content: With msflexgrid1 if. rowsel>. row then startrow =. row endrow =. rowsel else startrow =. rowsel endrow =. row end if 'deletes the database content for I = startrow to endrow if MRC. recordcount <> 0 then MRC. movefirst while MRC. EOF = false if trim (MRC. fields (0) =. textmatrix (I, 0) then a (I) = I MRC. delete end if MRC. movenext Wend if next I 'can be used to delete the content in the grid table. One is to re-pass the content in the database to the grid, and the other is to use a loop, delete the selected content from the grid. The second method is used: If startrow = 0 then for I = 1 to endrow. removeitem A (I) Next I else for I = startrow to endrow. removeitem A (I) Next I end if

• Dtpicker Control
  If we want users to manually enter the date, it will be one thing. If the input date is incorrect, we will always remind you that it is not good. This is a lot of trouble. So now the date time control function is displayed. Here, we have to say that one attribute is the format attribute.

Attribute	Format
0-dtplongdate	Yyyy-mm-dd hh: mm: SS
1-dtp1_date	Yyyy-mm-dd
2-dtptime	Hh: mm: SS
3-dtpcustom	Depends on customeformat

The effect of running is as follows: does it feel a step closer to VB!

Of course, if this control is used, it is essential to determine the current date:

'Determine whether the selected date is greater than the current date. If it is greater than, set it to the current date. If dtp1.value> date then msgbox ", the selected date cannot be greater than the current date! "Dtp1.value = date exit sub end if

Export Excel

The core content of this part is a link between EXCEL and VB and a circular usage. The content in the grid table is added to every cell in the execel table from row to column. Of course, before using Microsoft Office XX Object Library, let's analyze the Code:

Public Function gridtoexcel (Flex as msflexgrid, g_commondialog as commondialog) on error goto errhandler dim xlapp as object 'define an Excel Object dim xlbook as object' define a workbook dim rows as integer, cols as integer dim irow as integer, hcol as integer, icol as integer dim new_col as Boolean dim new_column as Boolean g_commondialog.cancelerror = true 'on error goto errhandler' flag g_commondialog.flags = CDL Ofnhidereadonly 'sets the filter g_commondialog.filter = "all files (*. *) | *. * | Excel files "&_"(*. XLS) | *. XLS | batch files (*. BAT) | *. bat "'specify the default filter g_commondialog.filterindex = 2'. If no content exists in the" open "dialog box g_commondialog.showsave ', the exit process is if flex. rows <= 1 then msgbox "no data! ", Vbinformation, g_msgtitle exit function end if set xlapp = Createobject (" Excel. application ") 'create an Excel Set xlbook = xlapp. workbooks. add 'Add a workbook xlapp. visible = false with Flex rows =. rows Cols =. the content added under Cols irow = 0 icol = 1' is completed in two loops. It is worth noting that for the grid table, the first cell coordinate is () for excel, yes. 'External loop, for icol = 1 to cols' in each column, for irow = 1 to rows in each row, 'adds the content in the GRI table to excel xlapp. cells (irow, icol ). value =. textmatrix (irow-1, icol-1) Next irow next icol end with 'sets the Excel attributes with xlapp. rows (1 ). font. bold = true. cells. select. columns. autofit. cells (1, 1 ). select. application. visible = true end with 'select the saved path xlbook. saveas (g_commondialog.filename) xlapp. application. visible = true xla Pp. displayalerts = false xlapp. Quit set xlapp = nothing '"Return Control to Excel Set xlbook = nothing Flex. setfocus msgbox" data has been exported to excel. ", Vbinformation," success "exit function 'errhandler: 'The user pressed the" cancel "button 'if err. Number <> 32755 then' msgbox" and the data export failed! ", Vbcritical," warning "'end ifend Function

Other content

• Instance phone problems
  I have never been quite clear about the usage of dim first and set. Later I analyzed the usage of dim, which was previously stated. Set is like a usage. Let's talk about it in detail.

  Dim CNN as ADODB. Connection 'statement
  Set CNN = new ADODB. Connection 'instantiation
  Equivalent to dim CNN as new ADODB. Connection

• Function

• Instr Functions
  Instr ([start,] string1, string2 [, compare])
  Explanation: instr ([start,] string to be searched, string to be searched [, matching mode])
  The following example shows how to use it:

If instr ("insert, delete, update", ucase $ (stokens (0) then', then CNN. execute SQL 'execute the query statement SQL msgstring = ens (0) & "query successful" end if

• Replace Function
  Replace (str1, str2, [str3])
  Description: replace str3 with the str2 character in str1. Generally, str3 can be omitted, that is, it is a space.
  For example:
  Replace ("TT good guy", "TT") Return "good guy"
  I used this function to implement a line feed function. The running effect is as follows:
  

  The code is written in this way (only show part), which is simple:

STR = "recharge card number:" & trim (chargemrc. fields (2) & "," & "Last card balance:" str = Replace (STR, ",", CHR (13) & CHR (10 ))

• Split Function
  Variable = Split (original string variable, cut mark)
  This function is also useful in the module to divide the content in the database into arrays.

• Optimization
  There is a way to define strings to limit strings, and it feels quite helpful.

Dim TT as string 'allows you to add the character TT = "# $ % ^ &' % @ * at will @*! "If instr (1, TT, CHR (keyascii) <> 0 then keyascii = 0

The case statement is also quite simple. Here is an example of limiting data numbers:

Select Case str(KeyAscii)        Case str(48) To str(57)        Case str(8)Case Else        KeyAscii = 0        BeepEnd Select    

Ideological progress

• Flowchart

  At the very beginning, I was very lazy. I didn't want to draw a flowchart or repeat it all. The result was that I didn't figure out the relationship when I started typing, there is a feeling of being unable to move. It turns out that nothing can be done if you are too lazy. When you try again, you will first repeat the process and then repeat it. This is a good feeling, with clear thinking, it will be much less confused.

• Note

  I have been writing comments for three periods, and I really want to laugh. Stage 1: I don't understand. What do these comments mean? However, I just knocked on the comment. Stage 2: Aren't these comments all the same? Do I have to write it all? Stage 3: actively write comments and write comments first. What functions do I want to implement and what steps I want to learn, mark out what you want in the form of attention first, then fill in the code, and finally implement the function. It is easy to find this process. In conclusion, why do I need to write comments? It is convenient for you and others to achieve a win-win situation.

• Responsibility
  After the code is completed, I think everything is fine, right? No, I took my master and looked at my system. After I clicked it, I found a lot of problems. What I said was wrong. How can I become so good, I don't think about the problem from the user's point of view. I often focus on the convenience of writing code by myself. I don't want to add a time control at the beginning. I often restrict users and give users a request, in order to use my system, I think I may be a pig. We have been busy for a long time, and the result is that it is very convenient for others to use. For this purpose, do I always remember it to become a qualified programmer first.

• Fear

  I have to say, many times, we often ask others, can this be done? If someone tells you yes, you can do the same thing. If someone tells you that you don't know, you will be hesitant and frustrated on the way to completion. Others tell you that you can't, you give up. There are many people around you who need to be affirmed by others for what you do. Why bother yourself so much? If you want to make yourself good, let go of all your concerns, believe what you can do, whether or not others are competent.

• Standing on the shoulders of giants

  When I started the data center, I did not refer to other people's code, and reminded myself not to refer to the blogs of Shige and Shijie when I was not forced to do anything. But in the end, I learned a lot about them. There are many examples worth learning around us. We should be able to see them, even if we see the gap.

  I use the SELECT statement to call the database content from the beginning. I first find the content and then modify, delete, add, and update the content. However, I have never been impressed with what I saw in my previous database video. What I learned before, such as what I picked up from my sister's blog:

'This can be written when users are deleted. txtsql = "delect user_info where" Call executesql (txtsql, msgtext) has been defined in the module)

'Txtsql = "insert into user_info values ('" & txtuser. text & "','" & txtpwd. text & "','" & combouserlevel. text & "','" & txtname. text & "','" & Head & "')" Call executesql (txtsql, msgtext)

'Update a user's information. txtsql = "Update user_info set userid ='' where "Call executesql (txtsql, msgtext)

This is enough to reflect a problem. For normal video learning, we should take it seriously and make a summary. We should not be lazy when doing this. After all, we really don't have such a good brain, multi-Summary and multi-summary.

Another piece of content is also learned from Shijie. When we clear the text box, we can use a method to traverse the control:

'Declare a control dim TXT as Control' to traverse each control for each TXT in me. controls' if the control type is textbox if typeof txt is textbox then TXT. Text = "" End ifnext

This can not only implement textbox controls, but also implement other types. The advantage of doing so is that it is not prone to errors, but also very convenient. It is learned by others, when you are a cainiao.

Conclusion

If you want to learn more and make yourself more common sense, there will always be no harm. Maybe you can help yourself that day. Over time, you will be able to get through and look forward to your own growth, I believe that I can do well and give myself a good opportunity.

The first time the data center charges granular Data Warehouse