+ Post New Thread
Results 1 to 6 of 6
Coding Thread, C# and MSSQL not saving data to database in Coding and Web Development; Hi all, I have the following snippet of code, which does simple 'grab and stick in database' stuff: Code: SqlConnection ...
  1. #1

    localzuk's Avatar
    Join Date
    Dec 2006
    Location
    Minehead
    Posts
    17,663
    Thank Post
    516
    Thanked 2,446 Times in 1,894 Posts
    Blog Entries
    24
    Rep Power
    831

    C# and MSSQL not saving data to database

    Hi all,

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

    Code:
    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.

  2. #2

    localzuk's Avatar
    Join Date
    Dec 2006
    Location
    Minehead
    Posts
    17,663
    Thank Post
    516
    Thanked 2,446 Times in 1,894 Posts
    Blog Entries
    24
    Rep Power
    831
    Bump

  3. #3

    SYNACK's Avatar
    Join Date
    Oct 2007
    Posts
    11,097
    Thank Post
    857
    Thanked 2,680 Times in 2,273 Posts
    Blog Entries
    9
    Rep Power
    769
    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

    Code:
    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.
    Code:
    <code>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.

  4. Thanks to SYNACK from:

    localzuk (27th February 2009)

  5. #4

    localzuk's Avatar
    Join Date
    Dec 2006
    Location
    Minehead
    Posts
    17,663
    Thank Post
    516
    Thanked 2,446 Times in 1,894 Posts
    Blog Entries
    24
    Rep Power
    831
    Right, that returns the result that the record wasn't added.

    The SQL procedure is this:
    Code:
    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).

  6. #5

    SYNACK's Avatar
    Join Date
    Oct 2007
    Posts
    11,097
    Thank Post
    857
    Thanked 2,680 Times in 2,273 Posts
    Blog Entries
    9
    Rep Power
    769
    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.

  7. Thanks to SYNACK from:

    localzuk (27th February 2009)

  8. #6

    localzuk's Avatar
    Join Date
    Dec 2006
    Location
    Minehead
    Posts
    17,663
    Thank Post
    516
    Thanked 2,446 Times in 1,894 Posts
    Blog Entries
    24
    Rep Power
    831
    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!

SHARE:
+ Post New Thread

Similar Threads

  1. SIMS and MSSQL
    By interele in forum MIS Systems
    Replies: 2
    Last Post: 25th November 2008, 06:35 PM
  2. SIMS on MSSQL 2005
    By AlexB in forum MIS Systems
    Replies: 9
    Last Post: 7th November 2007, 09:20 AM
  3. MSSQL Trigger (i think) Question
    By k-strider in forum Coding
    Replies: 3
    Last Post: 28th November 2006, 12:15 PM
  4. MSSQL Trigger (i think) Question
    By k-strider in forum Coding
    Replies: 0
    Last Post: 27th November 2006, 11:45 AM
  5. Autocreate AD accounts from MSSQL
    By CyberNerd in forum Coding
    Replies: 9
    Last Post: 12th July 2006, 03:00 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •