Click here to Skip to main content
Search within:



Next

How do I update data that has its its primary key set to IsIdentity?
Sign Up to vote bad
good
See more: VB.NETT-SQL
In the following code the command executes but update doesn't take place. The main issue is the
primary which is IDNumber is set to IsIdentity in the SQL Server database. I'd be thankful if someone could help me out.
 
If rbnCore.Checked = True Then
                    subject = "Core"
                Else : subject = "Elective"
                End If
qry = "Update ProgramDetails.Subjects set SubjectCode=@SubjectCode,SubjectName=@SubjectName,SubjectType=@SubjectType,UserID=@UserID,Password=@Password where IDNumber='" & txtIDNumber.Text & "'"
                cmd = New SqlCommand(qry, cn)
                cmd.Parameters.Add(New SqlParameter("@SubjectCode", txtSubjectCode.Text))
                cmd.Parameters.Add(New SqlParameter("@SubjectName", txtSubName.Text))
                cmd.Parameters.Add(New SqlParameter("@SubjectType", subject))
                cmd.Parameters.Add(New SqlParameter("@UserID", txtUserName.Text))
                cmd.Parameters.Add(New SqlParameter("@Password", txtPassword.Text))
                cmd.ExecuteNonQuery()
                MessageBox.Show("Record successfully updated", "Updated", MessageBoxButtons.OK, MessageBoxIcon.Information)
Posted 7 Feb '13 - 11:32
Akaglo418
Edited 7 Feb '13 - 11:42
v3
Add a Solution

Comments
richcb - 7 Feb '13 - 17:36
Where you are adding your sqlParameters, what is "subject"?
Akaglo - 7 Feb '13 - 17:44
"subject" is a variable name that holds subject type e.g. Elective or Core
barneyman - 7 Feb '13 - 18:04
if idnumber is a number, why are you wrapping it in apostrophes? where IDNumber='" & txtIDNumber.Text & "'"
Zoltán Zörgő - 7 Feb '13 - 18:13
Since you don't want to update the identity field, that wont be the issue.
Mike Meinz - 7 Feb '13 - 20:01
Your IDENTITY column (IDNumber) should not be disclosed to the user. Your code indicates that you have the IDNUmber in a TextBox that the users sees and can change. That is not best practice for an IDENTITY column. The value of the IDENTITY column (IDNUmber) should be put into the SQLParameters like the other values. Put the execution of the SQL in a Try..Catch block and display a relevant error message (ex.message) if there is an error. You may need to be more specific when you build your SQLParameter objects. Following is some code from one of my programs where I build the SQLParameter objects and add those objects to the SQLCommand object. Note how I specify the data type and the parameter direction in addition to the parameter name and parameter value. I am using dates for my parameters in this example. You can use this as an idea on how to build your SQLParameter objects. The SqlDbType should match the data type in the database design. Note: There is no IDENTITY column in my example below. You should have an IDENTITY column parameter in your program. obCommand = New SqlCommand(strSQL, cn) Dim obparm As SqlParameter obparm = New SqlParameter obparm.ParameterName = "@PlayDate" obparm.SqlDbType = SqlDbType.SmallDateTime obparm.Direction = ParameterDirection.Input obparm.Value = Format$(dtCurrentDate, "yyyy-MM-dd") obCommand.Parameters.Add(obparm) obparm = New SqlParameter obparm.ParameterName = "@NextDate" obparm.SqlDbType = SqlDbType.SmallDateTime obparm.Direction = ParameterDirection.Input obparm.Value = Format$(DateAdd(DateInterval.Day, 1, dtCurrentDate), "yyyy-MM-dd") obCommand.Parameters.Add(obparm) obparm = Nothing
Akaglo - 8 Feb '13 - 3:17
Thank Sir, the problem has now solved, but I wish that you could show me how I can do away with & txtIDNumber.Text & by using a parameter. Even though the code now works fine what I've done is that I've set the Visible of txtIDNumber to False in the Property pane, to prevent the users from seeing the IsIdentity number. However, if I parametrize it to be like IDNumber=@IDNumber the following I get the message: Must declare the scalar variable "@IDNumber". How do therefore declare it, please?
Akaglo - 14 Feb '13 - 6:05
Please Sir, I have posted a question on Deleting a record from multiple tables. Kindly attend to it out for.

2 solutions

Two changes:
qry = "Update ProgramDetails.Subjects set SubjectCode=@SubjectCode,SubjectName=@SubjectName,SubjectType=@SubjectType,UserID=@UserID,Password=@Password where IDNumber=@IDNumber"
and then insert before cmd.ExecuteNonQuery()
cmd.Parameters.Add(New SqlParameter("@IDNumber", txtIDNumber.Text))
  Permalink  
Posted 7 Feb '13 - 22:04
Bernhard Hiller27.8K
Comments
Akaglo - 8 Feb '13 - 5:06
Sir, kindly review the following code for me. The update command executes but no update takes place. If Not bError Then sbQry.Append("Update StaffDetails.Staff set StaffID=@StaffID,EmployeeName=@EmployeeName,SSN=@SSN,Day=@Day,Month=@Month,Year=@Year,Sex=@Sex,Qualifications=@Qualifications,Rank=@Rank,Responsibility=@Responsibility,ApDay=@ApDay,ApMonth=@ApMonth,ApYear=@ApYear,RegNumber=@RegNumber,Phone=@Phone,Email=@Email,Status=@Status") If img IsNot Nothing Then sbQry.Append(",Photo=@Photo") End If sbQry.Append(" where Number='").Append(txtNo.Text).Append("'") cmd = New SqlCommand(sbQry.ToString, cn) cmd.Parameters.Add(New SqlParameter("@StaffID", txtNo.Text)) cmd.Parameters.Add(New SqlParameter("@StaffID", txtStaffID.Text)) cmd.Parameters.Add(New SqlParameter("@EmployeeName", txtName.Text)) cmd.Parameters.Add(New SqlParameter("@SSN", txtSsNumber.Text)) cmd.Parameters.Add(New SqlParameter("@Day", cboDay.Text)) cmd.Parameters.Add(New SqlParameter("@Month", cboMonth.Text)) cmd.Parameters.Add(New SqlParameter("@Year", txtYear.Text)) cmd.Parameters.Add(New SqlParameter("@Sex", Sex)) cmd.Parameters.Add(New SqlParameter("@Qualifications", txtQualifications.Text)) cmd.Parameters.Add(New SqlParameter("@Rank", txtRank.Text)) cmd.Parameters.Add(New SqlParameter("@Responsibility", txtRespons.Text)) cmd.Parameters.Add(New SqlParameter("@ApDay", cboApDay.Text)) cmd.Parameters.Add(New SqlParameter("@ApMonth", cboApMonth.Text)) cmd.Parameters.Add(New SqlParameter("@ApYear", txtApYear.Text)) cmd.Parameters.Add(New SqlParameter("@RegNumber", txtRegdNum.Text)) cmd.Parameters.Add(New SqlParameter("@Phone", txtPhone.Text)) cmd.Parameters.Add(New SqlParameter("@Email", txtEmail.Text)) cmd.Parameters.Add(New SqlParameter("@Status", cboStatus.Text)) If img IsNot Nothing Then cmd.Parameters.Add(New SqlParameter("@Photo", img)) End If cmd.ExecuteNonQuery() lblSave_Update.Text = "Record successfully updated" End If Showgrid() Catch ex As Exception MessageBox.Show(ex.Message)
You're doing a fine job using parameters, so why this:
IDNumber='" & txtIDNumber.Text & "'"
Btw: You are comparing IDNumber, which I assume is a number, with a text string ...
 
Best regards
Espen Harlinn
  Permalink  
Posted 7 Feb '13 - 13:12
Espen Harlinn229K
Comments
Akaglo - 8 Feb '13 - 3:31
Please if I parametrize it to be like IDNumber=@IDNumber the following I get the message: Must declare the scalar variable "@IDNumber". How do therefore declare it, please.
Add a Solution

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

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 OriginalGriff 216
1 Sergey Alexandrovich Kryukov 169
2 Tadit Dash 154
3 Richard MacCutchan 145
4 Santhosh G_ 115
0 Sergey Alexandrovich Kryukov 10,338
1 OriginalGriff 7,965
2 CPallini 4,201
3 Rohan Leuva 3,522
4 Maciej Los 3,159


Advertise | Privacy | Mobile
Web01 | 2.6.130523.1 | Last Updated 8 Feb 2013
Copyright © CodeProject, 1999-2013
All Rights Reserved. Terms of Use
Layout: fixed | fluid