How do I update data that has its its primary key set to IsIdentity?

Question

0.00/5 (No votes)

See more:

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.

VB

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:32am

Akaglo

Updated 7-Feb-13 11:42am

v3

Add a Solution

Comments

Richard C Bishop 7-Feb-13 17:36pm

Where you are adding your sqlParameters, what is "subject"?

Akaglo 7-Feb-13 17:44pm

"subject" is a variable name that holds subject type e.g. Elective or Core

barneyman 7-Feb-13 18:04pm

if idnumber is a number, why are you wrapping it in apostrophes?

where IDNumber='" & txtIDNumber.Text & "'"

Zoltán Zörgő 7-Feb-13 18:13pm

Since you don't want to update the identity field, that wont be the issue.

Mike Meinz 7-Feb-13 20:01pm

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:17am

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:05am

Please Sir, I have posted a question on Deleting a record from multiple tables. Kindly attend to it out for.

2 solutions

Solution 1

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

Posted 7-Feb-13 13:12pm

Espen Harlinn

Comments

Akaglo 8-Feb-13 3:31am

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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Bernhard Hiller · Accepted Answer · 2013-02-07T22:04:00

Solution 2

Two changes:

VB

qry = "Update ProgramDetails.Subjects set SubjectCode=@SubjectCode,SubjectName=@SubjectName,SubjectType=@SubjectType,UserID=@UserID,Password=@Password where IDNumber=@IDNumber"

and then insert before cmd.ExecuteNonQuery()

VB

cmd.Parameters.Add(New SqlParameter("@IDNumber", txtIDNumber.Text))

Posted 7-Feb-13 22:04pm

Bernhard Hiller

Comments

Akaglo 8-Feb-13 5:06am

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)