Delete commad is not working VB.Net

Question

1.00/5 (1 vote)

See more:

hey I have have created a store procedure in SQL for deleting a data from my data base the store procedure is working fine when I am using it in SQL but when I call same store procedure in my VB.Net project here I am getting msg data deleted but when checking physically data still remains in data base . Can any one help.
The Store procedure is

SQL

create proc  sptblTrg_detail_Delete
@Course_Id int
as
begin
delete tblTrg_Detail where Trg_Ind_ID= @Course_Id
End

And VB.Net code

VB

Private Sub BtnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnDelete.Click
       Gp1.Enabled = True
       If TxtPIS.Text = "" Then
           MsgBox("Please Enter PIS No and Correct Batch NO")
           TxtPIS.Focus()
           Exit Sub
       Else
           Dim Mycon As New SqlConnection(con)
           Mycon.Open()
           Dim da As SqlDataAdapter
           da = New SqlDataAdapter("sptblTrg_detail_Delete", Mycon)
           da.SelectCommand.CommandType = CommandType.StoredProcedure
           da.SelectCommand.Parameters.AddWithValue("@Course_Id", TxtCourseID.Text)
           MsgBox("Detail Deleted Successfully")
           clearall()
           Dgvtrainees.DataSource = Nothing
       End If
   End Sub

Posted 17-Jun-14 17:56pm

Ram A Singh

Updated 17-Jun-14 18:29pm

v2

Add a Solution

Comments

Thava Rajan 18-Jun-14 2:16am

there are lot of problems here use the Normal command

3 solutions

Solution 1

Hi Ram,

You have not written statement to execute stored procedure...

Posted 17-Jun-14 18:07pm

Chaitanya Pai

Solution 2

try this.. :)
1) You have not erote from in stored procedure.

SQL

create proc  sptblTrg_detail_Delete
@Course_Id int
as
begin
delete from tblTrg_Detail where Trg_Ind_ID= @Course_Id
End

2) There is no need to use Sqldataadapter for delete records in database you can use ExecuteNonQuery inseated it

Posted 17-Jun-14 18:48pm

Nirav Prabtani

Updated 17-Jun-14 19:15pm

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Thava Rajan · Accepted Answer · 2014-06-17T20:24:00

This is what you need i think

VB

Private Sub BtnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnDelete.Click
    If TxtPIS.Text = "" Then
        MsgBox("Please Enter PIS No and Correct Batch NO")
        TxtPIS.Focus()
        Exit Sub
    Else
        Dim con As String, RecordsAffected As Integer
        Try
            Using Mycon As New SqlConnection(con)
                Mycon.Open()
                Using cmd As New SqlCommand("sptblTrg_detail_Delete", Mycon)
                    cmd.CommandType = CommandType.StoredProcedure
                    cmd.Parameters.AddWithValue("@Course_Id", TxtCourseID.Text)
                    RecordsAffected = cmd.ExecuteNonQuery()
                End Using
            End Using
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            If RecordsAffected > 0 Then
                MessageBox.Show("Detail Deleted Successfully")
                clearall()
                Dgvtrainees.DataSource = Nothing
            Else
                MessageBox.Show("Problem in Deleting")
            End If
        End Try
    End If
End Sub