VB 2008 如何使用文本框插入和更新数据库表行 [英] VB 2008 how to insert and update database table rows using textboxes

问题描述

我正在做我的系统的一部分，它正在更新和/或将数据插入到数据库中.我有一个带有组合框和 10 个文本框的表单.在这里，组合框是否填充了我数据库中诊断表中的疾病.

I'm doing a part of my system which is updating and/or inserting data into database. I have a form with a combobox and 10 textboxes. Here, the combobox if filled with illnesses from the diagnose table in my database.

诊断表的结构是:f_id，疾病，症状，所以一个疾病可以有很多症状.

Diagnose table's structure is: f_id, illness, symptoms, so an illness can have many symptoms.

如果从组合框中选择了一个项目，则症状将显示在文本框中.我已经开始工作了.我的问题是我应该让它能够插入或更新表中的行.我真的不知道该怎么做.到目前为止，这是我的代码:

If an item is selected from the combobox, the symptoms will be displayed on the textboxes. I already got this working. My problem is that I should make it able to insert or update the rows in the table. I really don't have any idea how to do this. So far, here's my code:

Call Connect()
        If Duplicate() = False Then

            STRSQL = "insert into diagnose values ('', @ill, @sym0), ('', @ill, @sym1), ('', @ill, @sym2), ('', @ill, @sym3), ('', @ill, @sym4), ('', @ill, @sym5), ('', @ill, @sym6), ('', @ill, @sym7), ('', @ill, @sym8), ('', @ill, @sym9)"
            Using myCmd = New MySqlCommand(STRSQL, myConn)
                myConn.Open()
                myCmd.Parameters.AddWithValue("ill", cmbRecord.Text)
                myCmd.Parameters.AddWithValue("sym0", symp0.Text)
                myCmd.Parameters.AddWithValue("sym1", symp1.Text)
                myCmd.Parameters.AddWithValue("sym2", symp2.Text)
                myCmd.Parameters.AddWithValue("sym3", symp3.Text)
                myCmd.Parameters.AddWithValue("sym4", symp4.Text)
                myCmd.Parameters.AddWithValue("sym5", symp5.Text)
                myCmd.Parameters.AddWithValue("sym6", symp6.Text)
                myCmd.Parameters.AddWithValue("sym7", symp7.Text)
                myCmd.Parameters.AddWithValue("sym8", symp8.Text)
                myCmd.Parameters.AddWithValue("sym9", symp9.Text)
                myCmd.ExecuteNonQuery()
            End Using
            MsgBox("Record Added")
            myConn.Close()
        Else
            STRSQL = "Update diagnose set first_aid = @ill, sname = @symp where first_aid = @ill"
            Using myCmd = New MySqlCommand(STRSQL, myConn)
                myConn.Open()
                myCmd.Parameters.AddWithValue("ill", cmbRecord.Text)
                myCmd.Parameters.AddWithValue("sym", symp0.Text)
                myCmd.Parameters.AddWithValue("sym", symp1.Text)
                myCmd.Parameters.AddWithValue("sym", symp2.Text)
                myCmd.Parameters.AddWithValue("sym", symp3.Text)
                myCmd.Parameters.AddWithValue("sym", symp4.Text)
                myCmd.Parameters.AddWithValue("sym", symp5.Text)
                myCmd.Parameters.AddWithValue("sym", symp6.Text)
                myCmd.Parameters.AddWithValue("sym", symp7.Text)
                myCmd.Parameters.AddWithValue("sym", symp8.Text)
                myCmd.Parameters.AddWithValue("sym", symp9.Text)
                myCmd.ExecuteNonQuery()
            End Using
            MsgBox("Record Updated")
            myConn.Close()
        End If

我已经有一个模块可以将我的项目连接到 mysql db (myConn).我不能做的是我不能更新诊断表中的任何行.我也无法添加行.诊断表如下所示(示例):

I already have a module that connect my project to mysql db (myConn). What I can't do is that I can't update any row in the diagnose table. I also can't add rows. The diagnose table look like this(example):

f_id | illness |  symptom
1   |  fever   |  fever
2   |  fever   |  hot temperature
3   |  fever   |  dizziness
4   |  fever   |  headache

所以在这种情况下，假设我在组合框中选择了发烧，然后它将在 4 个文本框中显示症状.如果用户进行了更改，Duplicate() 函数会检查组合框值是否已经有记录.如果为真，那么它将更新.假设用户添加了另一个症状，所以如果点击保存按钮，发烧会添加另一行包含添加的症状.

so in that case, say I chose fever in the combobox then it will display the symptoms on the 4 textboxes. If the user made changes, the Duplicate() function checks if the combobox value already have a record. If true then it will update. Say the user added another symptom, so if save button is clicked, fever will add another row with the added symptom.

如果为 false，则将添加一条新记录，这意味着将根据在文本框中输入的症状数量在表中添加新的一行或多行.所以，说'cold'要作为新记录添加，我输入了2个症状，这意味着我使用了表单中10个文本框中的2个，然后将在表格中添加2行.

If false, then a new record will be added, which means new row or rows will be added in the table dependeng on the number of symptoms inputted on the textboxes. So, say 'cold' is to be added as a new record and I entered 2 symptoms, this means that I used 2 of the 10 textboxes in the form, then 2 rows will be added on the table.

推荐答案

您正在尝试在同一个查询中插入多行，这是不可能的.您需要对插入的每一行进行单独的查询.循环遍历文本框数组(来自您上一个问题)以插入:

You are trying to Insert multiple rows in the same query, which is not possible. you need to have separate queries for each row inserted. Loop through the array of textboxes (from you previous question) to insert :

Dim arrayOfTextboxes() As TextBox = {symp0, ... put all textboxes here..., symp9}
myConn.Open()

If Duplicate() = False Then

    STRSQL = "insert into diagnose values (@ill, @sym)"

    For i = 0 To arrayOfTextboxes.Count - 1

       If String.IsNullOrEmpty(arrayOfTextboxes(i).Text) Then Continue For

       Using myCmd = New MySqlCommand(STRSQL, myConn)
            myCmd.Parameters.AddWithValue("@ill", cmbRecord.Text)
            myCmd.Parameters.AddWithValue("@sym", arrayOfTextboxes(i).Text)              
            myCmd.ExecuteNonQuery()
        End Using

    Next          

        MsgBox("Record Added")

Else

      // I don't get what you want to do in the update part yet.

End If

myConn.Close()

这篇关于VB 2008 如何使用文本框插入和更新数据库表行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！