Can't update a gridview with custom SQL statement

Question

User-828040185 posted

I have a database with this tables...

CREATE TABLE Seminar
(
Id INT PRIMARY KEY IDENTITY (1,1) NOT NULL,
Naziv NVARCHAR(1000) NOT NULL,
Opis NVARCHAR(1000) NOT NULL
)

CREATE TABLE Grad
(
Id INT PRIMARY KEY IDENTITY (1,1) NOT NULL,
Grad NVARCHAR(50) NOT NULL
)

CREATE TABLE Zaposlenik
(
IdZaposlenika INT PRIMARY KEY IDENTITY (1,1) NOT NULL,
Ime NVARCHAR(50) NOT NULL,
Prezime NVARCHAR(50) NOT NULL,
KorisnickoIme NVARCHAR(50) NOT NULL,
email NVARCHAR(50) NOT NULL,
lozinka NVARCHAR(50) NOT NULL
)

CREATE TABLE Termin
(
Id INT PRIMARY KEY IDENTITY (1,1) NOT NULL,
SeminarID INT FOREIGN KEY (SeminarID) REFERENCES Seminar(Id) NOT NULL,
Datum NVARCHAR(50) NOT NULL,
GradId INT FOREIGN KEY (GradId) REFERENCES Grad(Id) NOT NULL,
BrPolaznika INT DEFAULT 0 NOT NULL,
Popunjen BIT DEFAULT 0 NOT NULL
)

CREATE TABLE Predbiljezba
(
Id INT PRIMARY KEY IDENTITY (1,1) NOT NULL,
Ime NVARCHAR(50) NOT NULL,
Prezime NVARCHAR(50) NOT NULL,
Email NVARCHAR(50) NOT NULL,
Telefon NVARCHAR(50) NOT NULL,
Napomena NVARCHAR(500),
SeminarID INT FOREIGN KEY (SeminarID) REFERENCES Seminar(Id) NOT NULL,
TerminID INT FOREIGN KEY (TerminID) REFERENCES Termin(Id) NOT NULL,
PotvrdaP BIT DEFAULT 0 NOT NULL,
StatusP NVARCHAR(50),
CONSTRAINT chk_status CHECK (StatusP IN ('Prihvaćena', 'Odbijena')) 
)

Then I created this JOIN SELECT statement

SELECT        p.Id, p.Ime, p.Prezime, p.Email, p.Telefon, p.Napomena, s.Naziv, t.Datum, g.Grad, p.PotvrdaP, p.StatusP
FROM          Predbiljezba as p INNER JOIN
              Seminar as s ON s.Id = p.SeminarId INNER JOIN
	      Termin as t ON t.Id = p.TerminId INNER JOIN
	      Grad as g ON g.Id = t.GradId
WHERE p.PotvrdaP = @PotvrdaP

And this works fine gridview shows exactly what I want. Gridview automatically created UPDATE, DELETE and INSERT statements. When I press gridview update button and change specific value there is an error after I press save. Error says that I can't save a NULL value for one of the columns that is not even shown in my gridview. It is from one of the tables included but it isn't selected for showing in gridview. How can I change that if I can ?

Thank you.

Answer 1

User36583972 posted

Hi Goran_C#,

When I press gridview update button and change specific value there is an error after I press save

After you automatically generate INSERT, UPDATE, and DELETE statements based on the selected table and columns. If you have an error says that can't save a NULL value for one of the columns that is not even shown in my gridview, you can remove the data field in UpdateCommand.

You can refer the following tutorials and make a test on your side.

Inserting, Updating, and Deleting Data with the SqlDataSource:

http://www.asp.net/web-forms/overview/data-access/accessing-the-database-directly-from-an-aspnet-page/inserting-updating-and-deleting-data-with-the-sqldatasource-vb

You can also create INSERT, UPDATE, and DELETE statements by yourself.

Insert, Update, Delete In GridView Using ASP.Net C#:

http://www.c-sharpcorner.com/UploadFile/0c1bb2/insert-update-delete-in-gridview-using-single-stored-proce/

Best Regards,

Yohann Lu

Answer 2

User-828040185 posted

Thank you very much, you heped me a lot.