保存VB6中的数值到SQL中

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

Save VB6 values ​in SQL

问题

我有这个界面:

保存VB6中的数值到SQL中

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

保存VB6中的数值到SQL中

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

目前,这是整个界面的代码:

Option Explicit
Private oldPos As Integer

Dim indice As Integer
Dim indicee As Integer

Dim cnn As Connection
Dim rs As Recordset
Dim sCon As String
Dim sql As Command

Private Sub btnAñadir_Click()
   indice = indice + 1
   indicee = indicee + 0

   Picture1.Visible = True

   Load uc1(indice)
   Set uc1(indice).Container = Picture1 
   uc1(indice).Visible = True
   uc1(indice).Top = IIf(indice = 1, 0, uc1(indice - 1).Top + uc1(indice - 1).Height + 20)

   ' 其余控件的加载和设置...

   If indice = 3 Then
   indice = 0
   Me.btnAñadir.Enabled = False
   End If
End Sub

Private Sub btnGuardar_Click()
    Dim sql As String
    sql = "INSERT INTO ejemplo(nombre) VALUES("
    sql = sql & "'" & cmbAddTipo.Count & "',"
    sql = sql & "'" & txtAddPrefijo.Text & "',"
    sql = sql & "'" & txtAddNumero.Text & "',"
    sql = sql & "'" & chkAddPrincipal.Value & "',"
    sql = sql & "'" & chkAddActual.Value & "',"
    sql = sql & "'" & cmbAddVinculo.Count & "',"
    sql = sql & "'" & txtTimer.Text & "')"
    cnn.Execute sql
End Sub

Private Sub Form_Load()
   ' 初始化界面...

   Call IniciarConexion
   CargarTablaEnCombo "SELECT [tipo] FROM dbo.[tipo_Numero]", Me.cmbAddTipo(0), "tipo"
   CargarTablaEnCombo "SELECT [tipoVinculo] FROM dbo.[tipo_Vinculo]", Me.cmbAddVinculo(0), "tipoVinculo"
End Sub

' 其他子过程和函数...

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

英文:

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:

Option Explicit
Private oldPos As Integer
Dim indice As Integer
Dim indicee As Integer
Dim cnn As Connection
Dim rs As Recordset
Dim sCon As String
Dim sql As Command
Private Sub btnAñadir_Click()
indice = indice + 1
indicee = indicee + 0
Picture1.Visible = True
Load uc1(indice)
Set uc1(indice).Container = Picture1 
uc1(indice).Visible = True
uc1(indice).Top = IIf(indice = 1, 0, uc1(indice - 1).Top + uc1(indice - 1).Height + 20)
Set lblTipo(indice).Container = uc1(indice)
lblTipo(indice).Visible = True
lblTipo(indice).Top = lblTipo(indice - 1).Top
Load cmbAddTipo(indice)
Set cmbAddTipo(indice).Container = uc1(indice)
cmbAddTipo(indice).Visible = True
cmbAddTipo(indice).Top = cmbAddTipo(indice - 1).Top
CargarTablaEnCombo "SELECT [tipo] FROM dbo.[tipo_Numero]", Me.cmbAddTipo(indice), "tipo"
Load lblAddPrefijo(indice)
Set lblAddPrefijo(indice).Container = uc1(indice)
lblAddPrefijo(indice).Visible = True
lblAddPrefijo(indice).Top = lblAddPrefijo(indice - 1).Top
Load txtAddPrefijo(indice)
Set txtAddPrefijo(indice).Container = uc1(indice)
txtAddPrefijo(indice).Visible = True
txtAddPrefijo(indice).Top = txtAddPrefijo(indice - 1).Top
Load lblAddNum(indice)
Set lblAddNum(indice).Container = uc1(indice)
lblAddNum(indice).Visible = True
lblAddNum(indice).Top = lblAddNum(indice - 1).Top
Load txtAddNumero(indice)
Set txtAddNumero(indice).Container = uc1(indice)
txtAddNumero(indice).Visible = True
txtAddNumero(indice).Top = txtAddNumero(indice - 1).Top
Load chkAddPrincipal(indice)
Set chkAddPrincipal(indice).Container = uc1(indice)
chkAddPrincipal(indice).Visible = True
chkAddPrincipal(indice).Top = chkAddPrincipal(indice - 1).Top
Load chkAddActual(indice)
Set chkAddActual(indice).Container = uc1(indice)
chkAddActual(indice).Visible = True
chkAddActual(indice).Top = chkAddActual(indice - 1).Top
Load lblAddVin(indice)
Set lblAddVin(indice).Container = uc1(indice)
lblAddVin(indice).Visible = True
lblAddVin(indice).Top = lblAddVin(indice - 1).Top
Load cmbAddVinculo(indice)
Set cmbAddVinculo(indice).Container = uc1(indice)
cmbAddVinculo(indice).Visible = True
cmbAddVinculo(indice).Top = cmbAddVinculo(indice - 1).Top
CargarTablaEnCombo "SELECT [tipoVinculo] FROM dbo.[tipo_Vinculo]", Me.cmbAddVinculo(1), "tipoVinculo"
If indice = 3 Then
indice = 0
Me.btnAñadir.Enabled = False
End If
End Sub
Private Sub btnGuardar_Click()
Dim sql As String
sql = "INSERT INTO ejemplo(nombre) VALUES("
sql = sql & "'" & cmbAddTipo.Count & "',"
sql = sql & "'" & txtAddPrefijo.Text & "',"
sql = sql & "'" & txtAddNumero.Text & "',"
sql = sql & "'" & chkAddPrincipal.Value & "',"
sql = sql & "'" & chkAddActual.Value & "',"
sql = sql & "'" & cmbAddVinculo.Count & "',"
sql = sql & "'" & txtTimer.Text & "')"
cnn.Execute sql
End Sub
Private Sub Form_Load()
Dim theDate As Date
theDate = Format(Now(), "short date")
TextBox1.Text = theDate
scrollAdd.Min = 0
scrollAdd.Max = 3000
scrollAdd.SmallChange = Screen.TwipsPerPixelX * 10
scrollAdd.LargeChange = scrollAdd.SmallChange
Call IniciarConexion
CargarTablaEnCombo "SELECT [tipo] FROM dbo.[tipo_Numero]", Me.cmbAddTipo(0), "tipo"
CargarTablaEnCombo "SELECT [tipoVinculo] FROM dbo.[tipo_Vinculo]", Me.cmbAddVinculo(0), "tipoVinculo"
End Sub
Sub CargarTablaEnCombo(ByVal sql As String, comboBox As Object, ByVal Campo As String)
Set rs = New ADODB.Recordset
rs.Open sql, cnn, adOpenDynamic, adLockOptimistic
While Not rs.EOF
If Not IsNull(rs.Fields(Campo)) Then
comboBox.AddItem rs.Fields(Campo)
End If
rs.MoveNext
Wend
End Sub
Function cerrarRecordset(R As Recordset)
On Error Resume Next
If Not R Is Nothing Then
If R.State = adStateOpen Then
R.Close
Set R = Nothing
Else
Set R = Nothing
End If
End If
Err.Clear
On Error GoTo 0
End Function
Private Sub IniciarConexion()
Set cnn = New ADODB.Connection
With cnn
.CursorLocation = adUseClient
.Open "PROVIDER=MSDASQL;driver={SQL Server};server=server;uid=uid;pwd=password;database=database;"
End With
End Sub
Private Sub Form_Unload(Cancel As Integer)
On Local Error Resume Next
cnn.Close
Set cnn = Nothing
End Sub
Private Sub scrollAdd_Change()
ScrollControls
End Sub
Private Sub scrollAdd_Scroll()
ScrollControls
End Sub
Private Sub ScrollControls()
Dim c As Control
For Each c In Me.Controls
If c.Container.Name = "Picture1" And Not TypeOf c Is VScrollBar Then
c.Top = c.Top + (oldPos - scrollAdd.Value)
End If
Next
oldPos = scrollAdd.Value
End Sub

答案1

得分: 1

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

Private Sub btnGuardar_Click()
   Dim i As Integer
   Dim CM As ADODB.Command

   For i = 0 To NumberOfUserControls - 1
      Set CM = New ADODB.Command
      Set CM.ActiveConnection = cnn
      CM.CommandType = adCmdText
      CM.CommandText = "INSERT INTO ejemplo (nombre) VALUES (?)"
      CM.Parameters.Append CM.CreateParameter("@nombre", adInteger, , , uc1(i).Nombre)
      CM.Execute , , adExecuteNoRecords
   Next
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:

Private Sub btnGuardar_Click()
Dim i As Integer
Dim CM As ADODB.Command
For i = 0 To NumberOfUserControls - 1
Set CM = New ADODB.Command
Set CM.ActiveConnection = cnn
CM.CommandType = adCmdText
CM.CommandText = "INSERT INTO ejemplo (nombre) VALUES (?)"
CM.Parameters.Append CM.CreateParameter("@nombre", adInteger, , , uc1(i).Nombre)
CM.Execute , , adExecuteNoRecords
Next
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
匿名

发表评论

匿名网友

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

确定