Member Avatar for matejkralik

Hello I use this code as a stored procedure to delete row of the table:

ALTER PROCEDURE dbo.DeleteRowMaterialTyp
    (
    @CisloParam varchar(MAX) /* hodnota v tabulke, ktora nam identifikuje riadok, ktory sa ma zmazat */
    )   
AS
    Delete From MaterialTyp Where Cislo = @CisloParam
    RETURN

And this is code where I call this procedure:

EtiketyLINQDataContext EtiketyData = new EtiketyLINQDataContext();     
            string CurrentRowCellParam = LINQdataGridView.CurrentRow.Cells[0].Value.ToString();
            EtiketyData.DeleteRowMaterialTyp(CurrentRowCellParam);

It's quite strange to me, because when I execute this DeleteRowMaterialTyp stored procedure, it deletes, also when I use deletion in running application, deletion works again, i can see it on datagridview, but when I come back to the sql environment and manually make selection of the datas, I can see, that nothing has been deleted. Where is the problem? Shall I do something additional between sql database and linq class dbml?

  • 2 Contributors
  • 6 Replies
  • 126 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by matejkralik

Dani AI

Generated

Short summary: the UI shows the row removed but the row still appears when you inspect the database in SQL Server. That almost always means the application is talking to a different database/file/instance than the one you are opening in SSMS. ’s suggestion to add logging is good — it proves whether the stored procedure actually ran and which database it hit. later said the CodeProject approach fixed it; the steps below explain the typical causes and concrete checks so others can verify and fix this quickly.

Check which database the app really uses. Print the DataContext connection info from the running app and compare that to the server and database shown in SSMS:

System.Diagnostics.Debug.WriteLine(EtiketyData.Connection.ConnectionString);

or, for SQL-specific info:

var sqlConn = (System.Data.SqlClient.SqlConnection)EtiketyData.Connection;
System.Diagnostics.Debug.WriteLine(sqlConn.DataSource + " / " + sqlConn.Database);

Common culprits and fixes

  • Visual Studio project contains an .mdf that is copied to the bin folder at runtime (Copy to Output Directory). The app modifies the copy, not your original file. Check the file property and the |DataDirectory| behavior (see the DataDirectory doc).
  • The connection string used by the app points to a different server/instance than the one you opened in SSMS. Use the DataSource/Database info above to open the exact same server in SSMS.
  • The stored proc may run under a transaction that later rolls back or exceptions in the app are being swallowed. Wrap the call in try/catch and log exceptions.

Quick ways to confirm activity

  • Add a tiny logging table and have the stored procedure insert a row at start (proves the call and parameter). Example:
CREATE TABLE DebugLog (Id INT IDENTITY PRIMARY KEY, Msg NVARCHAR(200), Created DATETIME DEFAULT GETDATE());
-- inside stored procedure:
INSERT INTO DebugLog(Msg) VALUES('DeleteRowMaterialTyp called, param=' + @CisloParam);
  • Use SQL Server Profiler / Extended Events to watch the stored-proc call live.

Relevant Microsoft references: the |DataDirectory| substitution and the Visual Studio copy-to-output behavior are common causes — see the Microsoft guidance on connection strings and the output-file behavior and use Profiler to watch calls.

Recommended Answers

All 6 Replies

Member Avatar for LizR

2 things come to mind.

1. Is it actually calling the DeleteRowMaterialTyp ? eg, if you changed it to log to a new table like a syslog table to say "I was here with param: xxx" . does it do it?
2. is the value of CurrentRowCellparam what you think it is?

Member Avatar for matejkralik

aha, ok, I think that problem maybe more, that it cannot touch MaterialTyp table...hmm, but anyway, this touches the storedprocedure DeleteRowMaterialTable, which is called with this line: EtiketyLINQDataContext EtiketyData = new EtiketyLINQDataContext(); so where is the solution?

because I already used messagebox to see currentrow.cell value that I need...

Member Avatar for LizR

As I suggested, make a new table and adjust the stored procedure to append to a form of log file to say "Hi I was here" so you know it at least called it and that it received the parameter etc.

Member Avatar for matejkralik

Could please describe me step by step what to do with this log file?

Member Avatar for LizR

um, well the idea is to prove that the call is successfully running so the point is to use an additional table as a debug state to say

I got to this point with this value
I got to that point with this value
I didnt do that
I did this

so you can tell from the table where abouts your code failed.

Member Avatar for matejkralik

After all I found the solution here :

I do not know how to execute delete, insert, update using datacontext...I really don't know, so I just joined the database...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.