C# and MSSQL not saving data to database

[localzuk]

Hi all,

 

I have the following snippet of code, which does simple 'grab and stick in database' stuff:

 

SqlConnection conn = new SqlConnection(Properties.Settings.Default.TonyTillDBC);
               conn.Open();
                   string ins = "INSERT INTO People (FirstName, LastName, Balance, TokenID, Photo, TypeID)	VALUES (@FirstName, @LastName, @Balance, @TokenID, @Photo, @PupilType)";
                   SqlCommand sql = new SqlCommand(ins, conn);
                   sql.CommandType = CommandType.Text;
                   
                   SqlParameter[] paramarray = new SqlParameter[6];
                   paramarray[0] = new SqlParameter();
                   paramarray[0].SqlDbType = SqlDbType.VarChar;
                   paramarray[0].ParameterName = "FirstName";
                   paramarray[0].Value = this.tbxFirstName.Text;
                   paramarray[1] = new SqlParameter();
                   paramarray[1].SqlDbType = SqlDbType.VarChar;
                   paramarray[1].ParameterName = "LastName";
                   paramarray[1].Value = this.tbxLastName.Text;
                   paramarray[2] = new SqlParameter();
                   paramarray[2].SqlDbType = SqlDbType.Money;
                   paramarray[2].ParameterName = "Balance";
                   paramarray[2].Value = this.tbxStartingBalance.Text;
                   paramarray[3] = new SqlParameter();
                   paramarray[3].SqlDbType = SqlDbType.VarChar;
                   paramarray[3].ParameterName = "TokenID";
                   paramarray[3].Value = this.tbxTokenID.Text;
                   paramarray[4] = new SqlParameter();
                   paramarray[4].SqlDbType = SqlDbType.Image;
                   paramarray[4].ParameterName = "Photo";
                   byte[] picval = fnc.cvtImgToByteArray(this.pbxPhoto.Image);
                   paramarray[4].Value = picval;
                   paramarray[5] = new SqlParameter();
                   paramarray[5].SqlDbType = SqlDbType.Int;
                   paramarray[5].ParameterName = "PupilType";
                   paramarray[5].Value = 2;
                   sql.Parameters.AddRange(paramarray);
                   sql.ExecuteNonQuery();
                   MessageBox.Show("Person added to the database.");
                   conn.Close();

 

The problem I've got is that it worked fine, once. It inserted one line of data into my People table.

 

But now, when it is called, it runs, states 'Person added to the database' in it's messagebox and doesn't put anything in the People table. I have the snippet above running in a try catch but no exceptions are being thrown.

 

I'm at a loss. The connection string is right, as another part of the app works fine, and as I said, it worked that first time.

 

Any ideas? It has got me perplexed.

[localzuk]

Bump

[SYNACK]

Have you tried checking the output of the ExecuteNonQuery to see if there are any messages from the SQL driver?

 

The last chunk of this code implements it:

CodeProject: Insert and retrieve data through stored procedure. Free source code and programming help

 

conn.Open(); int rows = command.ExecuteNonQuery();
conn.Close();

 

First, we retrieve the username and password information from the user. This information may be entered onto a form, through a message dialog or through some other method. The point is, the user specifies the username and password and the applicaton inserts the data into the database. Also notice that we called the ExecuteNonQuery() method of the Connection object. We call this method to indicate that the stored procedure does not return results for a query but rather an integer indicating how many rows were affected by the executed statement. ExecuteNonQuery() is used for DML statements such as INSERT, UPDATE and DELETE. Note that we can test the value of rows to check if the stored procedure inserted the data successfully.

if (rows == 1) {
MessageBox.Show("Create new user SUCCESS!");
}
else {
MessageBox.Show("Create new user FAILED!"); } 

 

We check the value of rows to see if it is equal to one. Since our stored procedure only did one insert operation and if it is successful, the ExecuteNonQuery() method should return 1 to indicate the one row that was inserted. For other SQL statements, especially UPDATE and DELETE statements that affect more than one row, the stored procedure will return the number of rows affected by the statement.

[localzuk]

Right, that returns the result that the record wasn't added.

 

The SQL procedure is this:

PROCEDURE dbo.InsertNonPupil (

@FirstName varchar(100),
@LastName varchar(100),
@Photo image,
@Balance money,
@TokenID varchar(20),
@PupilType integer)
AS	
INSERT INTO People (FirstName, LastName, Balance, TokenID, Photo, TypeID)
VALUES (@FirstName, 
		@LastName, 
		@Balance, 
		@TokenID, 
		@Photo, 
		@PupilType)

RETURN

 

This is a simplified, cut down version of my earlier procedure which did a check check first (ie. UPDATE, then if the rowcount==0 then run insert).

[SYNACK]

Have you tried running the statment directly in the a managment studio type thing for mySQL. I would try running it directly in SQL Managment Studio first if it was MSSQL to make sure that the statments and permissions inside the DB were all valid.

[localzuk]

Right, that's got it. I had an index on one of the fields that could have a null value which required values to be unique at the same time. So the second 'null' that came along and voila, it's a dupe.

 

Cheers! I'll remember to check the procedures first in future!