更新查询不工作在vb.net

Question

我想在后面的代码中写入更新语句在vb.net.if ICCID存在于tbl_ICCID然后改变状态从0到1和Pic_Correct_ICCID.visible = true，如果不存在，显示“未找到”。 我写了这段代码，但没有工作，对于Tbl_ICCID中不存在的所有ICCID Pic_Correct_ICCID.visible = true。 请检查我的代码并解决我的问题。

in Cls_ICCID: 

Public Function Update_Status(ByVal ICCID_No As String, ByVal status As Integer) As String 
     Try 
      Dim cmd As SqlCommand 
      Dim sql As String 
      Dim sql2 As String 
      Dim myConnection As SqlConnection = New SqlConnection() 
      myConnection.ConnectionString = "Data Source=TEHRANI\TEHRANI;Initial Catalog=GSMProduction;Persist Security Info=True;User ID=sa;Password=1" 
      **sql = "UPDATE Tbl_ICCID SET Status='" & status & "' Where (ICCID = '" & ICCID_No & "')"** 
      myConnection.Open() 
      cmd = New SqlCommand(sql, myConnection) 
      cmd.ExecuteNonQuery() 
      cmd.Dispose() 
      myConnection.Close() 
      Update_Status = "" 
     Catch ex As SqlException 
      Update_Status = "Not found" 
     Catch ex As Exception 
      Update_Status = "Not connect to server" 
     End Try 
    End Function 

in Frm_Packing 



Private Sub Txt_ICCID_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Txt_ICCID.TextChanged 

     Pic_BP_Correct.Visible = False 
     Pic_BP_Wrong.Visible = False 

     Try 
      If Txt_ICCID.Text.Length = Txt_ICCID.MaxLength Then 
       lblError.Text = clsICCID.Update_Status(Txt_ICCID.Text.ToString(), 1) 
       lblError.ForeColor = Color.Red 
       stream = New System.IO.MemoryStream 
       pic_barcode = Nothing 
       cls.btnEncode(pic_barcode, Txt_ICCID.Text.Trim) 
       pic_barcode.Save(stream, System.Drawing.Imaging.ImageFormat.Png) 
       f = New IO.FileStream("C:\test55.png", IO.FileMode.Create, IO.FileAccess.ReadWrite) 
       b = stream.ToArray 
       f.Write(b, 0, b.Length) 
       f.Close() 
       Dim Val() = {stream.ToArray, Txt_ICCID.Text.Trim} 
       ds.Tables(0).Rows.Add(Val) 
       crp_report.SetDataSource(ds.Tables(0)) 
       frm_crp.CrystalReportViewer1.ReportSource = crp_report 
       If lblError.Text = "" Then 
        Pic_BP_Correct.Visible = True 
        GBDoubleCheck.Visible = True 
        Txt_LabelBarcode.Focus() 
       Else 
        Pic_BP_Wrong.Visible = True 
       End If 
      End If 
     Catch ex As Exception 
      Pic_BP_Wrong.Visible = True 
     End Try 
    End Sub 

Answer 1

很可能是由于将状态列值作为字符串而不是int发送。你应该删除那些单引号。此外，这是一个非常糟糕的练习来连接这样的查询。使用CommandBuilders类或Typed DataSets来保存自己以防SQL注入。