更新查询无效 [英] Update query is not working

问题描述

大家好，



我正在使用更新命令来更新图书记录。我的假设如下：



我有一个名为SenderID的文本框，在每次加载时它都被禁用，ID将自动提供（文本框属性：启用为假=新发件人发送书籍输入和启用为真=同一发件人再次发送同一本书。如果是新发件人，我可以轻松更新图书库存。



但是，如果发件人（具有相同的Sender_ID和ISBN）已经在我的股票清单上，那么我只需要在Stock_In_Hand中查找发件人（具有ISBN，数量，LastModifiedDate，备注）记录并更新该特定记录添加数量字段。



如果员工启用文本框属性并提供SenderID然后失去焦点我应该从数据库中检索详细信息，以便员工不必再次键入，他们只需更改数量和备注字段并保存。我可以从Stock_In_Hand数据库中检索这些详细信息，但之后我需要使用新备注，修改日期和新旧数量的总和来更新同一个表。



这里我的更新查询无效。我是按照以下方式做到的。请让我知道我哪里出错了。在此先感谢。



我尝试过：

Hello everyone,

I am working on update command for updating the books record. My assumptions are as follows.

I have a text box called SenderID, On every load it it disabled and ID will be provided automatically( Textbox property: Enabled as false = New sender sending books Entry & Enabled as True = Same sender sending same book again). If new sender I can easily update the books stock.

However, if the sender (with same Sender_ID and ISBN) is already on my stock list then I just have to find the Sender in the Stock_In_Hand(which has ISBN, Quantity, LastModifiedDate, Remarks) Record and UPDATE that particular record Adding the Quantity field.

If the staff enables the textbox property and provides the SenderID then on lost focus I should retrieve details from the database so that staff doesn't have to type again, they just changes the Quantity and remarks field and saves. I am able to retrieve those details from Stock_In_Hand Database, but after that I need to update the same table with new remarks, date modified and Sum of old and new quantity.

Here my update query is not working. I have done that in following way. Please let me know where I have gone wrong. Thanks in advance.

What I have tried:

Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click

    If MsgBox("All entered details of send is correct.", vbYesNo, "Save Transaction") = vbYes Then
        Try
            con.Open()


            Dim str As String
            str = CreateTransID()

            If SenderIdentifier.Text <> "SameSender" Then
                Using cmd As New SqlClient.SqlCommand("INSERT INTO Sender_Details(Sender_ID, Sender_Address, Region_Name, Trans_ID, Rec_Token_No, Quantity, Remarks) VALUES(@d1, @d2, @d3, @d4, @d5, @d6, @d7)", con)
                    cmd.Parameters.Add(New SqlClient.SqlParameter("@d1", SqlDbType.NVarChar)).Value = txtSenderID.Text
                    cmd.Parameters.Add(New SqlClient.SqlParameter("@d2", SqlDbType.NVarChar)).Value = txtAddr.Text.Trim
                    cmd.Parameters.Add(New SqlClient.SqlParameter("@d3", SqlDbType.NVarChar)).Value = cbRegID.Text
                    cmd.Parameters.Add(New SqlClient.SqlParameter("@d4", SqlDbType.NVarChar)).Value = str
                    cmd.Parameters.Add(New SqlClient.SqlParameter("@d5", SqlDbType.NVarChar)).Value = txtSChallanNo.Text.Trim
                    cmd.Parameters.Add(New SqlClient.SqlParameter("@d6", SqlDbType.BigInt)).Value = txtSQuantity.Text.Trim
                    cmd.Parameters.Add(New SqlClient.SqlParameter("@d7", SqlDbType.NVarChar)).Value = txtSRemark.Text.Trim

                    Dim i As Integer

                    i = cmd.ExecuteNonQuery

                    If i > 0 Then

                        Using cmd1 As New SqlClient.SqlCommand("INSERT INTO Transaction_Details(Trans_ID, ISBN, SenderID, ReceiverID, Trans_Date, Quantity_Sent, challan, Remarks) VALUES(@d1, @d2, @d3, @d4, @d5, @d6, @d7, @d8)", con)
                            cmd1.Parameters.Add(New SqlClient.SqlParameter("@d1", SqlDbType.NVarChar)).Value = NewTransactionID.Text
                            cmd1.Parameters.Add(New SqlClient.SqlParameter("@d2", SqlDbType.NVarChar)).Value = cbISBN.Text
                            cmd1.Parameters.Add(New SqlClient.SqlParameter("@d3", SqlDbType.NVarChar)).Value = txtSenderID.Text
                            cmd1.Parameters.Add(New SqlClient.SqlParameter("@d4", SqlDbType.NVarChar)).Value = "HEADOFFICE0001"
                            cmd1.Parameters.Add(New SqlClient.SqlParameter("@d5", SqlDbType.Date)).Value = senderDatePicker.Text
                            cmd1.Parameters.Add(New SqlClient.SqlParameter("@d6", SqlDbType.Int)).Value = txtSQuantity.Text.Trim
                            cmd1.Parameters.Add(New SqlClient.SqlParameter("@d7", SqlDbType.NVarChar)).Value = txtSChallanNo.Text.Trim
                            cmd1.Parameters.Add(New SqlClient.SqlParameter("@d8", SqlDbType.NVarChar)).Value = txtSRemark.Text.Trim

                            Dim j As Integer

                            j = cmd1.ExecuteNonQuery

                            If j > 0 Then
                                Call btnClear_Click(sender, e)
                                NewTransactionID.Text = CreateTransID()
                                txtSenderID.Text = RandomString()

                                Me.DG1.DataSource = Nothing
                                Using cmd2 As New SqlClient.SqlCommand("SELECT * FROM Sender_Details", con)
                                    sda = New SqlDataAdapter(cmd2)

                                    dt = New DataTable ' THIS CODE IS IMPORTANT TO REFRESH THE TABLE WE HAVE TO CREATE NEW OBJ OF DATA TABLE ELSE IT WILL DUPLICATE THE DATA.

                                    sda.Fill(dt)
                                    Me.DG1.DataSource = dt
                                End Using
                                txtAddr.Focus()
                            End If
                        End Using
                    End If
                End Using

            ElseIf SenderIdentifier.Text = "SameSender" Then
                ' BELOW CODE WILL RETRIEVE THE QUANTITY AND CONVERT IT INTO INTEGER AFTER RETRIEVING, IT WILL BE ADDED UP WITH THE NEW QUANTITY SENT BY SENDER AND SAVED IT.


                Using sqlCmd As New SqlClient.SqlCommand("SELECT Quantity FROM Stock_In_Hand WHERE ISBN = @d1 ", con) '" & cbISBN.Text & "'", con)
                    sqlCmd.Parameters.Add("@d1", SqlDbType.NVarChar).Value = cbISBN.Text
                    Dim dr As SqlDataReader
                    dr = sqlCmd.ExecuteReader()
                    While dr.Read()
                        'TotalSum is a flag (textbox) that will store the sum of all the books from the record and newly added books quantity.
                        TotalSum.Text = txtSQuantity.Text + dr("Quantity")
                        con.Close()
                    End While
                End Using
                'con.Close()

                con.Open()
>>>>>>>> HERE! I AM NOT ABLE TO EXECUTE BELOW COMMAND 
                Using Mycommand As New SqlClient.SqlCommand("UPDATE Stock_In_Hand SET Quantity = @d1, StockModifiedDate =@d2, Remarks =@d3 WHERE ISBN =@d4", con)
                    Mycommand.Parameters.Add("@d1", SqlDbType.BigInt).Value =
                    Mycommand.Parameters.Add("@d2", SqlDbType.Date).Value = Date.Today
                    Mycommand.Parameters.Add("@d3", SqlDbType.NVarChar).Value = txtSRemark.Text.Trim
                    Mycommand.Parameters.Add("@d4", SqlDbType.NVarChar).Value = cbISBN.Text
                    Dim i As Integer
                    i = Mycommand.ExecuteNonQuery()
                    MsgBox(i)
                    If i > 0 Then
                        MsgBox("Stock in hand table updated")
                    End If
                End Using
            End If
            con.Close()
        Catch ex As Exception
            MsgBox("error")
        End Try
    End If
End Sub

推荐答案

尝试：

Try:

Mycommand.Parameters.AddWithValue("@d1", ???)  ' --- You need to provide a value here!
Mycommand.Parameters.AddWithValue("@d2", Date.Today)
Mycommand.Parameters.AddWithValue("@d3", txtSRemark.Text.Trim)
Mycommand.Parameters.AddWithValue("@d4", cbISBN.Text)

但我怀疑你的名字错了，d4应该是ID。我强烈建议你使用明智的参数名称，就像你应该为变量做的那样，并为你的控件做：

But I would suspect that you have the names wrong, and d4 should be the ID. I'd strongly suggest you use "sensible" names for parameters, just as you should sfor variables, and do for your controls:

Using Mycommand As New SqlClient.SqlCommand("UPDATE Stock_In_Hand SET Quantity = @QNTY, StockModifiedDate =@SMDT, Remarks =@REM WHERE ISBN =@ISBN", con)

并在AddWithValue调用中适当地更改参数名称 - 它使您的代码更可靠，更易于阅读。

And change the parameter names appropriately in the AddWithValue calls - it makes your code a lot more reliable and easier to read.

这篇关于更新查询无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！