Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET T-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 12:32pm
Akaglo419
Edited 7-Feb-13 12:42pm
v3
Comments
richcb at 7-Feb-13 17:36pm
   
Where you are adding your sqlParameters, what is "subject"?
Akaglo at 7-Feb-13 17:44pm
   
"subject" is a variable name that holds subject type e.g. Elective or Core
barneyman at 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ő at 7-Feb-13 18:13pm
   
Since you don't want to update the identity field, that wont be the issue.
Mike Meinz at 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 at 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 at 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.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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  
Comments
Akaglo at 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)
Rate this: bad
good
Please Sign up or sign in to vote.

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
  Permalink  
Comments
Akaglo at 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.

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

  Print Answers RSS
0 OriginalGriff 299
1 Maciej Los 295
2 Sergey Alexandrovich Kryukov 205
3 Aajmot Sk 202
4 Sinisa Hajnal 166
0 OriginalGriff 7,760
1 Sergey Alexandrovich Kryukov 7,072
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,785


Advertise | Privacy | Mobile
Web04 | 2.8.1411023.1 | Last Updated 8 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100