保存VB6中的数值到SQL中

huangapple
go评论110阅读模式
英文:

Save VB6 values ​in SQL

问题

我有这个界面：

保存VB6中的数值到SQL中

当点击添加按钮时，会添加一个具有相同字段的UserControl：

保存VB6中的数值到SQL中

所以问题是：我如何保存主要的值以及已添加的值？也就是说，如果用户按下添加按钮，我如何保存这些值？

目前，这是整个界面的代码：

  1. Option Explicit
  2. Private oldPos As Integer
  4. Dim indice As Integer
  5. Dim indicee As Integer
  7. Dim cnn As Connection
  8. Dim rs As Recordset
  9. Dim sCon As String
  10. Dim sql As Command
  12. Private Sub btnAñadir_Click()
  13.    indice = indice + 1
  14.    indicee = indicee + 0
  16.    Picture1.Visible = True
  18.    Load uc1(indice)
  19.    Set uc1(indice).Container = Picture1 
  20.    uc1(indice).Visible = True
  21.    uc1(indice).Top = IIf(indice = 1, 0, uc1(indice - 1).Top + uc1(indice - 1).Height + 20)
  23.    ' 其余控件的加载和设置...
  25.    If indice = 3 Then
  26.    indice = 0
  27.    Me.btnAñadir.Enabled = False
  28.    End If
  29. End Sub
  31. Private Sub btnGuardar_Click()
  32.     Dim sql As String
  33.     sql = "INSERT INTO ejemplo(nombre) VALUES("
  34.     sql = sql & "'" & cmbAddTipo.Count & "',"
  35.     sql = sql & "'" & txtAddPrefijo.Text & "',"
  36.     sql = sql & "'" & txtAddNumero.Text & "',"
  37.     sql = sql & "'" & chkAddPrincipal.Value & "',"
  38.     sql = sql & "'" & chkAddActual.Value & "',"
  39.     sql = sql & "'" & cmbAddVinculo.Count & "',"
  40.     sql = sql & "'" & txtTimer.Text & "')"
  41.     cnn.Execute sql
  42. End Sub
  44. Private Sub Form_Load()
  45.    ' 初始化界面...
  47.    Call IniciarConexion
  48.    CargarTablaEnCombo "SELECT [tipo] FROM dbo.[tipo_Numero]", Me.cmbAddTipo(0), "tipo"
  49.    CargarTablaEnCombo "SELECT [tipoVinculo] FROM dbo.[tipo_Vinculo]", Me.cmbAddVinculo(0), "tipoVinculo"
  50. End Sub
  52. ' 其他子过程和函数...

注意：这只是你提供的代码的一部分，可能有其他部分的代码需要在保存值方面进行修改和补充。

英文:

I have this interface:

保存VB6中的数值到SQL中

When the add button is pressed, a UserControl with the same fields is added:

保存VB6中的数值到SQL中

So, the question is: How do I save the main values ​​and those that have been added? That is, if the user pressed the add button, how do I also save those values?

Currently, this is the entire interface code:

  1. Option Explicit
  2. Private oldPos As Integer
  3. Dim indice As Integer
  4. Dim indicee As Integer
  5. Dim cnn As Connection
  6. Dim rs As Recordset
  7. Dim sCon As String
  8. Dim sql As Command
  9. Private Sub btnAñadir_Click()
  10. indice = indice + 1
  11. indicee = indicee + 0
  12. Picture1.Visible = True
  13. Load uc1(indice)
  14. Set uc1(indice).Container = Picture1 
  15. uc1(indice).Visible = True
  16. uc1(indice).Top = IIf(indice = 1, 0, uc1(indice - 1).Top + uc1(indice - 1).Height + 20)
  17. Set lblTipo(indice).Container = uc1(indice)
  18. lblTipo(indice).Visible = True
  19. lblTipo(indice).Top = lblTipo(indice - 1).Top
  20. Load cmbAddTipo(indice)
  21. Set cmbAddTipo(indice).Container = uc1(indice)
  22. cmbAddTipo(indice).Visible = True
  23. cmbAddTipo(indice).Top = cmbAddTipo(indice - 1).Top
  24. CargarTablaEnCombo "SELECT [tipo] FROM dbo.[tipo_Numero]", Me.cmbAddTipo(indice), "tipo"
  25. Load lblAddPrefijo(indice)
  26. Set lblAddPrefijo(indice).Container = uc1(indice)
  27. lblAddPrefijo(indice).Visible = True
  28. lblAddPrefijo(indice).Top = lblAddPrefijo(indice - 1).Top
  29. Load txtAddPrefijo(indice)
  30. Set txtAddPrefijo(indice).Container = uc1(indice)
  31. txtAddPrefijo(indice).Visible = True
  32. txtAddPrefijo(indice).Top = txtAddPrefijo(indice - 1).Top
  33. Load lblAddNum(indice)
  34. Set lblAddNum(indice).Container = uc1(indice)
  35. lblAddNum(indice).Visible = True
  36. lblAddNum(indice).Top = lblAddNum(indice - 1).Top
  37. Load txtAddNumero(indice)
  38. Set txtAddNumero(indice).Container = uc1(indice)
  39. txtAddNumero(indice).Visible = True
  40. txtAddNumero(indice).Top = txtAddNumero(indice - 1).Top
  41. Load chkAddPrincipal(indice)
  42. Set chkAddPrincipal(indice).Container = uc1(indice)
  43. chkAddPrincipal(indice).Visible = True
  44. chkAddPrincipal(indice).Top = chkAddPrincipal(indice - 1).Top
  45. Load chkAddActual(indice)
  46. Set chkAddActual(indice).Container = uc1(indice)
  47. chkAddActual(indice).Visible = True
  48. chkAddActual(indice).Top = chkAddActual(indice - 1).Top
  49. Load lblAddVin(indice)
  50. Set lblAddVin(indice).Container = uc1(indice)
  51. lblAddVin(indice).Visible = True
  52. lblAddVin(indice).Top = lblAddVin(indice - 1).Top
  53. Load cmbAddVinculo(indice)
  54. Set cmbAddVinculo(indice).Container = uc1(indice)
  55. cmbAddVinculo(indice).Visible = True
  56. cmbAddVinculo(indice).Top = cmbAddVinculo(indice - 1).Top
  57. CargarTablaEnCombo "SELECT [tipoVinculo] FROM dbo.[tipo_Vinculo]", Me.cmbAddVinculo(1), "tipoVinculo"
  58. If indice = 3 Then
  59. indice = 0
  60. Me.btnAñadir.Enabled = False
  61. End If
  62. End Sub
  63. Private Sub btnGuardar_Click()
  64. Dim sql As String
  65. sql = "INSERT INTO ejemplo(nombre) VALUES("
  66. sql = sql & "'" & cmbAddTipo.Count & "',"
  67. sql = sql & "'" & txtAddPrefijo.Text & "',"
  68. sql = sql & "'" & txtAddNumero.Text & "',"
  69. sql = sql & "'" & chkAddPrincipal.Value & "',"
  70. sql = sql & "'" & chkAddActual.Value & "',"
  71. sql = sql & "'" & cmbAddVinculo.Count & "',"
  72. sql = sql & "'" & txtTimer.Text & "')"
  73. cnn.Execute sql
  74. End Sub
  75. Private Sub Form_Load()
  76. Dim theDate As Date
  77. theDate = Format(Now(), "short date")
  78. TextBox1.Text = theDate
  79. scrollAdd.Min = 0
  80. scrollAdd.Max = 3000
  81. scrollAdd.SmallChange = Screen.TwipsPerPixelX * 10
  82. scrollAdd.LargeChange = scrollAdd.SmallChange
  83. Call IniciarConexion
  84. CargarTablaEnCombo "SELECT [tipo] FROM dbo.[tipo_Numero]", Me.cmbAddTipo(0), "tipo"
  85. CargarTablaEnCombo "SELECT [tipoVinculo] FROM dbo.[tipo_Vinculo]", Me.cmbAddVinculo(0), "tipoVinculo"
  86. End Sub
  87. Sub CargarTablaEnCombo(ByVal sql As String, comboBox As Object, ByVal Campo As String)
  88. Set rs = New ADODB.Recordset
  89. rs.Open sql, cnn, adOpenDynamic, adLockOptimistic
  90. While Not rs.EOF
  91. If Not IsNull(rs.Fields(Campo)) Then
  92. comboBox.AddItem rs.Fields(Campo)
  93. End If
  94. rs.MoveNext
  95. Wend
  96. End Sub
  97. Function cerrarRecordset(R As Recordset)
  98. On Error Resume Next
  99. If Not R Is Nothing Then
  100. If R.State = adStateOpen Then
  101. R.Close
  102. Set R = Nothing
  103. Else
  104. Set R = Nothing
  105. End If
  106. End If
  107. Err.Clear
  108. On Error GoTo 0
  109. End Function
  110. Private Sub IniciarConexion()
  111. Set cnn = New ADODB.Connection
  112. With cnn
  113. .CursorLocation = adUseClient
  114. .Open "PROVIDER=MSDASQL;driver={SQL Server};server=server;uid=uid;pwd=password;database=database;"
  115. End With
  116. End Sub
  117. Private Sub Form_Unload(Cancel As Integer)
  118. On Local Error Resume Next
  119. cnn.Close
  120. Set cnn = Nothing
  121. End Sub
  122. Private Sub scrollAdd_Change()
  123. ScrollControls
  124. End Sub
  125. Private Sub scrollAdd_Scroll()
  126. ScrollControls
  127. End Sub
  128. Private Sub ScrollControls()
  129. Dim c As Control
  130. For Each c In Me.Controls
  131. If c.Container.Name = "Picture1" And Not TypeOf c Is VScrollBar Then
  132. c.Top = c.Top + (oldPos - scrollAdd.Value)
  133. End If
  134. Next
  135. oldPos = scrollAdd.Value
  136. End Sub

答案1

得分: 1

您的应用程序允许创建任意数量的UserControls。在某个时刻，您将需要保存所有这些数据。一种方法是遍历这些UserControl并构建插入语句。如评论中所述，最好对输入进行清理并使用参数。类似于以下方式：

  1. Private Sub btnGuardar_Click()
  2.    Dim i As Integer
  3.    Dim CM As ADODB.Command
  5.    For i = 0 To NumberOfUserControls - 1
  6.       Set CM = New ADODB.Command
  7.       Set CM.ActiveConnection = cnn
  8.       CM.CommandType = adCmdText
  9.       CM.CommandText = "INSERT INTO ejemplo (nombre) VALUES (?)"
  10.       CM.Parameters.Append CM.CreateParameter("@nombre", adInteger, , , uc1(i).Nombre)
  11.       CM.Execute , , adExecuteNoRecords
  12.    Next
  13. End Sub

为简单起见，此代码假定您知道UserControl的数量。它还建立在我在这个答案中提出的思路之上（链接：https://stackoverflow.com/q/59410212/5162073）。您需要清理数据并根据需要添加额外的参数。

英文:

Your app allows the creation of any number of UserControls. At some point, you will need to save all this data. One approach is to to loop through those UserControl's and build an Insert statement. As mentioned in the comments, it is wise to sanitize the input and to use parameters. Something like this:

  1. Private Sub btnGuardar_Click()
  2. Dim i As Integer
  3. Dim CM As ADODB.Command
  4. For i = 0 To NumberOfUserControls - 1
  5. Set CM = New ADODB.Command
  6. Set CM.ActiveConnection = cnn
  7. CM.CommandType = adCmdText
  8. CM.CommandText = "INSERT INTO ejemplo (nombre) VALUES (?)"
  9. CM.Parameters.Append CM.CreateParameter("@nombre", adInteger, , , uc1(i).Nombre)
  10. CM.Execute , , adExecuteNoRecords
  11. Next
  12. End Sub

For simplicity, this code assumes you know the number of UserControl's. It also builds upon the ideas I presented in this answer. You will need to sanitize the data and add additional Parameters as needed.

huangapple
  • 本文由 发表于 2020年1月6日 20:11:26
  • 转载请务必保留本文链接：https://go.coder-hub.com/59611886.html
  • save
  • sql
  • sql-server
  • vb6
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定