CODEWITHSUNDEEP
postgresqlnpgsql
mjb
mjb

Reputation: 7969

How to delete a row in a Table in Npgsql?

I'm using this code to delete a row in a table, but it's not working. Do I make a mistake or miss out something?

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Npgsql;

namespace WindowsFormsApplication4
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button_RemoveBook_Click(object sender, EventArgs e)
        {
            NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;Port=5432;User Id=user;Password=1234;Database=library;");
            conn.Open();
            string sql = "DELETE FROM books WHERE BookID=1;";
            NpgsqlCommand command = new NpgsqlCommand(sql, conn);
            conn.Close();
        }
    }
}

The above task run, I notice that the database didn't make any changes. The bookID=1, which is the 1st row in the database still remains.

I have tried to use the INSERT command, it works. New data was inserted into the last row of the table. The below code running good.

private void button_addBook_Click(object sender, EventArgs e)
{
    NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;Port=5432;User Id=user;Password=1234;Database=library;");
    conn.Open();
    string addRow = string.Format("insert into books values(50,'Time','Frank','Science')");
    NpgsqlCommand command = new NpgsqlCommand(addRow, conn);
    conn.Close();

Any clue? Thanks.

Response to: astander

It shows this error message:
http://i901.photobucket.com/albums/ac218/pcser/error.jpg

Upvotes: 1

Views: 8002

Answers (2)

mjb
mjb

Reputation: 7969

astander and guys.
Thanks for you help.
After several try, I have found the solutions.

BookID is the name of a column, this symbol " " has to be added before and after the column name.

Therefore the correct way is:

    string sql = "DELETE FROM books WHERE \"BookID\"=1;";

This is wrong:

    string sql = "DELETE FROM books WHERE BookID=1;";


Thanks again for your help.

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166476

Have you tried using NpgsqlCommand.ExecuteNonQuery Method

Something like

private void button_RemoveBook_Click(object sender, EventArgs e) 
{ 
    NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;Port=5432;User Id=user;Password=1234;Database=library;"); 
    conn.Open(); 
    string sql = "DELETE FROM books WHERE BookID=1;"; 
    NpgsqlCommand command = new NpgsqlCommand(sql, conn); 
    command.ExecuteNonQuery(); //this line here??
    conn.Close(); 
}

Upvotes: 0

Related Questions