-1
[WebMethod]
        public bool AddStudent(Student student)
        {             bool UploadSuccess = false;
            cn.Open();
            int StudentID = 0;
            using (SqlCommand com = new SqlCommand("INSERT into tblStudent (StudentNumber, Name, Surname, DOB, Gender, EmailAddress, Address1, Address2, City, Postcode, Username, Password, Course) values ('" + student.StudentNumber + "' ,'" + student.Name + "' ,'" + student.Surname + "' ,'" + student.DOB + "', '" + student.Gender + "' ,'" + student.EmailAddress + "' ,'" + student.Address1 + "' ,'" + student.Address2 + "' ,'" + student.City + "' ,'" + student.Postcode + "' ,'" + student.Username + "' ,'" + student.Password + "' ,'" + student.Course + "')", cn))
            {
                int i = com.ExecuteNonQuery();
                StudentID = (int)com.ExecuteScalar();
                cn.Close();
                if (i != 0)

                    UploadSuccess = true;

                return UploadSuccess;
            }

I'm trying to insert data into fingerprint table which has four columns - ID (primary key) - StudentID (foreign key) linked to student table - description - Template

But the following error keeps coming up. I can't turn off the IDENTITY for ID as I want it to increment automatically. I also have a student table to store information. What I want to achieve is that after entering student details, I want to copy the studentID that was generated before onto the fingerprint table - StudentID column. the code I have for this is shown below.

private void btnSave_Click(object sender, EventArgs e)
        {
            fgrTemplate template = new fgrTemplate();
            template.StudentID = std.StudentID;
            template.Description = fngDes.Text;
            template.Template = m_StoredTemplate;
            if (upload.InsertTemplate(template))
            {
                MessageBox.Show("Student Successfully Added!");
            }
            else
            {
                MessageBox.Show("Student Not Successfully Added!");
            }

using (SqlCommand com = new SqlCommand("INSERT INTO tblFingerprint (StudentID, Description, Template) values ('" + template.StudentID + "' ,'" + template.Description + "' ,@Template)", cn))

this is what I have on my web service. However it gives me the error

John Saunders
  • 157,405
  • 24
  • 229
  • 388
user3144368
  • 1
  • 1
  • 3
  • 3
    Bobby Tables [strikes again](http://technet.microsoft.com/en-us/library/ms161953\(v=sql.105\).aspx). – Dustin Kingen Jan 31 '14 at 14:41
  • possible duplicate of [Cannot insert explicit value for identity column in table 'table' when IDENTITY\_INSERT is set to OFF](http://stackoverflow.com/questions/1334012/cannot-insert-explicit-value-for-identity-column-in-table-table-when-identity) – 48klocs Jan 31 '14 at 14:41
  • What does `upload.InsertTemplate` do? Why are you mixing string concatenation and paramaters? – D Stanley Jan 31 '14 at 14:42
  • Give the poor guy a break: http://xkcd.com/327/ – david.pfx Jan 31 '14 at 14:46
  • ASMX is a legacy technology, and should not be used for new development. WCF or ASP.NET Web API should be used for all new development of web service clients and servers. One hint: Microsoft has retired the [ASMX Forum](http://social.msdn.microsoft.com/Forums/en-US/asmxandxml/threads) on MSDN. – John Saunders Jan 31 '14 at 19:57

3 Answers3

1

Your first error:

You are trying to insert StudentID which seems like a IDENTITY type field (Auto-increment), you don't have to pass that in your INSERT statement, SQL server will generate one for your.

Your second problem is: Your query is not properly parameterized, you are using a combination of string concatenation and parameters. Your query and statement should be like:

using (SqlCommand com = new SqlCommand(@"INSERT INTO tblFingerprint (Description, Template) 
                                         values (@Description, @Template)", cn))
{
    com.Parameters.AddWithValue("@Description", template.Descriptio);
    com.Parameters.AddWithValue("@Template", template.Value); //what ever value is
    //....rest of your code

}

If you already have an StudentID and you want to update the existing record then use UPDATE statement.

If you want to manually insert the StudentID (overriding auto increment ID) then you have to use SET IDENTITY_INSERT

Habib
  • 205,061
  • 27
  • 376
  • 407
  • I have four column in my fingerprint table - ID (primary key), Student ID (foreign key) linked to the student table, description and template. after entering the student information i want to copy the studentID from tblstudent to tblfingeprint. – user3144368 Jan 31 '14 at 14:50
0

Remove or comment this line

//template.StudentID = std.StudentID;

Also remove StudentID from INSERT

"INSERT INTO tblFingerprint (Description,....
Iqbal
  • 1,156
  • 1
  • 15
  • 21
  • If I do this, how do I copy the student ID that links the fingerprint template to the student. – user3144368 Jan 31 '14 at 14:45
  • This seems to be identity field, it will be incremented automatically when ever you will insert new record to the table. If you want to insert your own value in this filed, them remove IDENTITY from the database structure then no need to change in your code it will work. – Iqbal Jan 31 '14 at 14:48
  • template.StudentID = std.StudentID is passing the information from the previous form to the current form. It loads the information and saves it to std so that I can save the studentID onto the tblFingeprint. I already have a primary key for tblFingerprint which is called ID. StudentID is a foreign key which is linked to tblStudent. I just want to copy that StudentID to link the template to their information – user3144368 Jan 31 '14 at 15:04
  • just double check in your database table tblFingerprint, make sure that identity is 'No'. If it is 'No' it should not raise error. If it is 'Yes' change it to 'No' and save table, then use your code it will work. – Iqbal Jan 31 '14 at 15:54
0

Your code can be attacked using an SQL INJECTION

In this case, try to use sql command parameters

Community
  • 1
  • 1
Lazaro Fernandes Lima Suleiman
  • 1,161
  • 10
  • 22