MyCommand.CommandText =
"UPDATE PATIENTS SET LastName='" & tboxLastName.Text & "',FirstName='" & tboxFirstName.Text & "' WHERE PatientID=
'" & Val(tboxPatientID.Text) & "'"
the application throws in another exception saying "No value given
for one or more required parameter" and the debugger was displaying
WHERE PatientID = '1", instead of '1'
so I think its in the WHERE statement, I had tried different combination of quotes but to no avail....
Help on Update String
Hot Topic
- Controls Flickering when drawed on the form
- A complex coding for me
- Controls Flickering when drawed on the form
- how can i use a combobox with checkbox????
- how to update rows in an mdb data source??
- DAta Sources Toolbox functionality reversed
- How to duplicate...
- IDE pauses after every keystroke. -
- Problem extracting URL from Favorites showdailog, used to work
- What happened to the Installer class template
Help on Update String
JaggedEdge
pdx_benjamin
AVD
You're not specifying any quotes at all. What does Val do It's completely superfluous here. If the ID is a number ( and it should be ), then quotes are always an error anyhow.
Armand du Plessis
For SQL @FirstParameter is an identifier that this is actually a parameter and not a field from the database.
So you would define a parameter with this name. As for the type - this would be defined when adding the parameter, So you are specifying a specific type to the value. This is one of the reasons why use of parameters is safer than just creating a SQL string for SQL Injection Attacks.
For Access I think the Identifier for a parameter is slightly different. I dont use access much these days.
squimmy
Instaed of inserting the text directly into the sql string you should use stored procedures or parametized queries...
The correct procedure for your update should be:
MyCommand.CommandText =
"UPDATE PATIENTS SET LastName= @FirstParameter, FirstName=@SecondParameter WHERE PatientID= @ThirdParameter"
Dim p1 as oledbparameter
Dim p2 as oledbparameter
Dim p3 as oledbparameter
p1.value =tboxLastName.Text
p2.value =tboxFirstName.Text
p3.value =Cint(tboxPatientID.Text)
MyCommand.Parameters.Add(p1)
MyCommand.Parameters.Add(p2)
MyCommand.Parameters.Add(p3)
Dim NumberOfRecordsEffected as integer = MyCommand.ExecuteNonQuery
Metal_Fly
What if you take the contents of the string and put them in SQL Server itself Or are you using Access
Is it the same exception Post the entire string as it appears, show it with a msgbox or something.
GlennDek
Good point - I was just looking for quotes, based on the details he gave :-)
Ahmed Kuradi
I had able to solve my update problem using the above code, but the thing is there always a syntax error everytime I inputed a single quote(') character say Jay's...I know this is due to my update string for as it treated the data with ' as an sql string...I like the idea of DMan1 of using stored procedure and I am using an access database.I just want to ask what are the @FirstParameter,@SecondParameter and @ThirdParameter and what should be their values ...thanks....
Ted Warring