An exception of type 'System.Data.SqlClient.Sq

问题:

An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code

Additional information: Incorrect syntax near the keyword 'WHERE'.

I dont know where is the wrong in the syntax !!

using( var command1 = new SqlCommand("INSERT INTO Employee(EmpPhone,Password,OfficeNo,Floor, Building) VALUES (@EmpPhone,@Password,@OfficeNo,@Floor, @Building) WHERE EmpID ='" + id.Text + "'", con))
{
      command1.Parameters.AddWithValue("@EmpPhone", Convert.ToInt32(phone.Text));
      command1.Parameters.AddWithValue("@Password", password.Text);
      command1.Parameters.AddWithValue("@OfficeNo", officeNo.Text);
      command1.Parameters.AddWithValue("@Floor", Convert.ToInt32(floor.Text));
      command1.Parameters.AddWithValue("@Building", Convert.ToInt32(building.Text));

     command1.ExecuteNonQuery();
}

回答1:

Problem is still unidentified. What actually you want to achieve, a new row or update an existing row.

In case of insert:

using( var command1 = new SqlCommand("INSERT INTO Employee(EmpPhone,Password,OfficeNo,Floor, Building) VALUES (@EmpPhone,@Password,@OfficeNo,@Floor, @Building)", con))
                {
                command1.Parameters.AddWithValue("@EmpPhone", Convert.ToInt32(phone.Text));
                command1.Parameters.AddWithValue("@Password", password.Text);
                command1.Parameters.AddWithValue("@OfficeNo", officeNo.Text);
                command1.Parameters.AddWithValue("@Floor", Convert.ToInt32(floor.Text));
                command1.Parameters.AddWithValue("@Building", Convert.ToInt32(building.Text));


  int i=         command1.ExecuteNonQuery();
}

Update:

using( var command1 = new SqlCommand("Update Employee Set EmpPhone=@EmpPhone,Password=@Password,OfficeNo=@OfficeNo,Floor=@Floor, Building = @Building Where EmployeeId =@Id", con))
 {
   command1.Parameters.AddWithValue("@EmpPhone", Convert.ToInt32(phone.Text));
   command1.Parameters.AddWithValue("@Password", password.Text);
   command1.Parameters.AddWithValue("@OfficeNo", officeNo.Text);
   command1.Parameters.AddWithValue("@Floor", Convert.ToInt32(floor.Text));
   command1.Parameters.AddWithValue("@Building", Convert.ToInt32(building.Text));
 command1.Parameters.AddWithValue("@Id", Convert.ToInt32(Id.Text));


  int i= command1.ExecuteNonQuery();
}

回答2:

As unlimit said, you can't use WHERE clause in INSERT statement. Here it's syntax;

INSERT 
{
        [ TOP ( expression ) [ PERCENT ] ] 
        [ INTO ] 
        { <object> | rowset_function_limited 
          [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
        }
    {
        [ ( column_list ) ] 
        [ <OUTPUT Clause> ]
        { VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n     ] 
        | derived_table 
        | execute_statement
        | <dml_table_source>
        | DEFAULT VALUES 
        }
    }
}

I feel like you try to update existing row. If it is, you should use UPDATE (Transact-SQL) statement like;

using( var command1 = new SqlCommand("Update Employee Set EmpPhone=@EmpPhone,Password=@Password,OfficeNo=@OfficeNo,Floor=@Floor, Building = @Building Where EmployeeId =@Id", con))
 {
   command1.Parameters.AddWithValue("@EmpPhone", Convert.ToInt32(phone.Text));
   command1.Parameters.AddWithValue("@Password", password.Text);
   command1.Parameters.AddWithValue("@OfficeNo", officeNo.Text);
   command1.Parameters.AddWithValue("@Floor", Convert.ToInt32(floor.Text));
   command1.Parameters.AddWithValue("@Building", Convert.ToInt32(building.Text));
   command1.Parameters.AddWithValue("@Id", Id.Text);

   command1.ExecuteNonQuery();
}

回答3:

Yes "Insert" command can only insert a new row into your table and you cannot modify/update your table using insert command. Use "Update" command to update your row that is already present in your table

回答4:

I also have similar problems in visual studio 2013*

    Private Sub ButtonProses_Click(sender As Object, e As EventArgs) Handles ButtonProses.Click
                Dim index As Integer
                If TextIDSupp.Text = "" Then
                    MsgBox("Data Suplier tidak boleh kosong!")
                    TextIDSupp.Focus()
                ElseIf DataGridView1.Item(0, index).Value = "" Then
                    MsgBox("Transaksi pembelian masih kosong")
                    TextKodeBR.Focus()
                Else
                    'simpan tabel pembelian
                    Koneksi()
                    CMD = New SqlCommand("INSERT INTO tbl_pembelian (no_pembelian,tanggal_pembelian,id_supplier,total_barang,total_harga) VALUES (@no_pembelian, @tanggal_pembelian, @id_supplier, @total_barang, @total_harga)", CONN)
                    With CMD
                        .Parameters.AddWithValue("@no_pembelian", TextNoTrans.Text)
                        .Parameters.AddWithValue("@tanggal_pembelian", TextTglTrans.Text)
                        .Parameters.AddWithValue("@id_supplier", TextIDSupp.Text)
                        .Parameters.AddWithValue("@total_barang", TextTotalBR.Text)
                        .Parameters.AddWithValue("@total_harga", TextTotalHgBR.Text)
                        .ExecuteNonQuery()
                    End With
                    CONN.Close()

                    For i As Integer = 0 To DataGridView1.RowCount - 2

                        'simpan tabel pembelian detail
                        Koneksi()
                        CMD = New SqlCommand("INSERT INTO tbl_pembelian_detail (no_pemebelian, kode_barang, Qty, harga_barang, sub_total_harga) VALUES (@no_pemebelian, @kode_barang, @Qty, @harga_barang, @sub_total_harga)", CONN)
                        With CMD
                            .Parameters.AddWithValue("@no_pemebelian", TextNoTrans.Text)
                            .Parameters.AddWithValue("@kode_barang", DataGridView1.Item(0, i).Value)
                            .Parameters.AddWithValue("@Qty", DataGridView1.Item(2, i).Value)
                            .Parameters.AddWithValue("@harga_barang", DataGridView1.Item(4, i).Value)
                            .Parameters.AddWithValue("@sub_total_harga", DataGridView1.Item(5, i).Value)
                            .ExecuteNonQuery()
                        End With
                        CONN.Close()

                        'update tabel barang 
                        Koneksi()
                        CMD = New SqlCommand("SELECT stock FROM tbl_barang WHERE kode_barang ='" + DataGridView1.Item(0, i).Value + "'", CONN)
                        DRead = CMD.ExecuteReader
                        DRead.Read()
                        If DRead.HasRows Then
                            Dim jumlah As String
                            jumlah = DRead(0) + DataGridView1.Item(2, i).Value

                            Koneksi()
                            CMD = New SqlCommand("UPDATE tbl_barang SET stock ='" + jumlah + "' WHERE kode_barang='" + DataGridView1.Item(0, i).Value + "'", CONN)
                            CMD.ExecuteNonQuery()
                            CONN.Close()
                        End If
                        CONN.Close()

                    Next

                    MsgBox("Data berhasil disimpan")
                    call_all()
                End If
            End Sub