[2005] Unable to insert data to database using stored procedures

[bomayed]

After executing this code , I get no errors , but nothing is added to the database !

    

        Dim MyCmd As SqlCommand = New SqlCommand()

        MyCmd.CommandText = "insNewComplain"
        MyCmd.CommandType = Data.CommandType.StoredProcedure


        MyCmd.Parameters.Clear()
        MyCmd.Parameters.Add("@TypeID", SqlDbType.Int)
        MyCmd.Parameters.Add("@LocationID", SqlDbType.Int)
        MyCmd.Parameters.Add("@MobileNO", SqlDbType.Char)
        MyCmd.Parameters.Add("@CallerNO", SqlDbType.Char)
        MyCmd.Parameters.Add("@CallerName", SqlDbType.Char)
        MyCmd.Parameters.Add("@ComplainDetails", SqlDbType.Text)
        MyCmd.Parameters.Add("@ImportanceID", SqlDbType.Int)
        MyCmd.Parameters.Add("@DeathID", SqlDbType.Int)
        MyCmd.Parameters.Add("@StreetID", SqlDbType.Int)
        '-----------------------------------------------------------

        MyCmd.Parameters("@LocationID").Value = LocationsComboBox.SelectedValue
        MyCmd.Parameters("@TypeID").Value = ComplainTypeComboBox.SelectedValue
        MyCmd.Parameters("@MobileNO").Value = txtCallerMobile.Text
        MyCmd.Parameters("@CallerNO").Value = txtCallerNO.Text
        MyCmd.Parameters("@CallerName").Value = txtCallerName.Text
        MyCmd.Parameters("@ComplainDetails").Value = txtComplainDetails.Text
        MyCmd.Parameters("@ImportanceID").Value = ImportanceComboBox.SelectedValue
        MyCmd.Parameters("@DeathID").Value = 1 'HERE YOU HAVE TO STORE THE RADIO BUTTON OPTION 
        MyCmd.Parameters("@StreetID").Value = StreetsComboBox.SelectedValue


        MyConn = New SqlConnection


 
        MyCmd.Connection = MyConn


            MyConn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MYDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
            MyConn.Open()

                MyCmd.ExecuteNonQuery()

            Catch
                MessageBox.Show("Unable to Execute SQL " & Err.Description)
                Exit Sub
            End Try

        Catch
            Exit Sub
        End Try



        MessageBox.Show("INSERT WAS SUCCESSFUL!")


        MyConn.Close()


        MyCmd = Nothing

and here is my stored procedure:

ALTER PROCEDURE dbo.insNewComplain 
	
	@TypeID int ,
	@LocationID int,
	@MobileNO char(10),
	@CallerNO char(10),
	@CallerName varchar(50),
	@ComplainDetails text,
	@ImportanceID int,
	@DeathID int,
	@StreetID int
	
AS


	INSERT INTO Complains(TypeID, LocationID, MobileNO, CallerNO, CallerName, ComplainDate, ComplainDetails,
	ImportanceID, DeathID, StreetID)
	
	VALUES (@TypeID, @LocationID, @MobileNO, @CallerNO, @CallerName, getDate (),@ComplainDetails, 
	@ImportanceID, @DeathID, @StreetID)

[jmcilhinney]

What value does ExecuteNonQuery return?

[bomayed]

It doesn't return anything ?

I'm not sure , how do I check , must the ExecuteNonQuery return something ?

[techgnome]

ExecuteNonQuery returns the number of rows affected. So if it returns 1, one row was inserted. If it returns 0, no rows were inserted.

Now, I see from your connectionstring you are using user instance of SQL Server for the database.... How are you checking that the data isn't in the database? If you are checking the mdf that is being pointed to in the connection string, then it won't be there. It's only used as a template for the mdf database file that gets copied to your user folder. May want to look in there.

-tg

[jmcilhinney]

Of course it returns something. It's a function. All functions return something. What does it return?

[shakti5385]

Debug the code is the best way and see are you getting any error!
Also it's good that after making the stored procedure execute it manually first in the SQL SERVER also

Rather than .Add use the .AddWithValue in this way

MyCmd.Parameters.AddWithValue("@LocationID",LocationsComboBox.SelectedValue)
        MyCmd.Parameters.AddWithValue("@MobileNO", txtCallerMobile.Text)
'And so on...

[techgnome]

Personally I think the .Add is preferable over .AddWithValue
1) I don't have to specify a value. But then, I usually do a lot of looping, so adding parameters w/o values makes sense to me.
2) I can specify the datatype explicitly, rather than having it inferred.

-tg

[shakti5385]

Personally I think the .Add is preferable over .AddWithValue
1) I don't have to specify a value. But then, I usually do a lot of looping, so adding parameters w/o values makes sense to me.
2) I can specify the datatype explicitly, rather than having it inferred.

-tg

May Be

[techgnome]

AddWithValue is better where it's approrpate, otherwise Add is better.

don't take it out of context... it wasn't blindly stated that AddWithValue is always better.... but where it's APPROPRIATE. And as in the case of a loop, I don't think it is....

But you are entitled to your opinion...

-tg

[jmcilhinney]

AddWithValue should be used where you are executing a one-off command. You add the parameters and values once and then discard them. If you are going to use the command more than once and will/may need to change the parameter values then you should use Add.