Visual Basic.NET help with Database Programming (ADO.NET)

[B&W]

Basically i am trying to update a specific row in the datagrid of my Students table. But as soon as i run the query (displayed below), all of the rows in the datagrid gets updated instead of just 1 row.

Private Sub btnupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnupdate.Click
        Dim conn As OleDbConnection = New OleDbConnection
        conn.ConnectionString = strConn
        conn.Open()

        Dim cmd As OleDbCommand = New OleDbCommand
        cmd.CommandText = "qyUpdateStudents"
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = conn

        cmd.Parameters.AddWithValue("@Reference", txtRef2.Text)
        cmd.Parameters.AddWithValue("@FName", txtfname2.Text)
        cmd.Parameters.AddWithValue("@LName", txtlname2.Text)
        cmd.Parameters.AddWithValue("@Address", txtaddress2.Text)
        cmd.Parameters.AddWithValue("@PostCode", txtpostcode2.Text)
        cmd.Parameters.AddWithValue("@County", txtcounty2.Text)
        cmd.Parameters.AddWithValue("@Tel", txttel2.Text)
        cmd.Parameters.AddWithValue("@Mobile", txtmobile2.Text)
        cmd.Parameters.AddWithValue("@CourseType", txtcoursetype2.Text)
        cmd.Parameters.AddWithValue("@CourseTitle", txtcoursetitle2.Text)
        cmd.Parameters.AddWithValue("@Duration", txtduration2.Text)
        cmd.Parameters.AddWithValue("@YearofStudy", txtyearofstudy2.Text)
        cmd.Parameters.AddWithValue("@Fees", txtfees2.Text)

        Dim incompleted As Integer = cmd.ExecuteNonQuery
        If incompleted > -1 Then
            MessageBox.Show("The Student Details have been Updated Successfully", "Saving.....", MessageBoxButtons.OK, MessageBoxIcon.Information)

        Else
            MessageBox.Show("Error Updating Data.....Please Try Again", "Error....", MessageBoxButtons.RetryCancel, MessageBoxIcon.Error)
        End If

        conn.Close()
        conn.Dispose()
        cmd.Dispose()
    End Sub

i am using the updatequery in microsoft access. i made a select query which select the table information that i wopuld like to edit called getStudents and put this in the update query.

Here is the sql view of my update query:

PARAMETERS Reference Long, FName Text ( 255 ), LName Text ( 255 ), Address Text ( 255 ), PostCode Text ( 255 ), County Text ( 255 ), Tel Text ( 255 ), Mobile Text ( 255 ), CourseType Text ( 255 ), CourseTitle Text ( 255 ), Duration Short, YearofStudy Short, Fees Currency;
UPDATE qyGetStudents SET qyGetStudents.Reference = [Reference], qyGetStudents.FName = [FName], qyGetStudents.LName = [LName], qyGetStudents.Address = [Address], qyGetStudents.PostCode = [PostCode], qyGetStudents.County = [County], qyGetStudents.Tel = [Tel], qyGetStudents.Mobile = [Mobile], qyGetStudents.CourseType = [CourseType], qyGetStudents.CourseTitle = [CourseTitle], qyGetStudents.Duration = [Duration], qyGetStudents.YearofStudy = [YearofStudy], qyGetStudents.Fees = [Fees];

I have been trying to get it working for days but to no avail, any help would be greatly appreciated.

 

[B&W]

you need a WHERE clause in your UPDATE statement. If you don't specify which record to update, it will update ALL records!!!!

So, ideally you would pass through the ID for the student into the stored procedure as a parameter, which then passes it through into the WHERE clause of the UPDATE statement.

where shall I stick in the "WHERE" clause in the sql output I showed in my OP.

sorry but I dont completely understand what you trying to say.

 

[B&W]

ok this is basic SQL:

UPDATE <tablename>
SET [<field>=<value>],
WHERE [condition]

if you don't specify a WHERE clause, so saying there is no condition for the update, ALL records will be updated.

Can you please giv me an example from the query i have provided you in the OP.

 

[B&W]

ok what field holds the unique identifier for a student? Assuming it's reference, then your query should look like this:

UPDATE qyGetStudents
SET qyGetStudents.FName = [FName], qyGetStudents.LName = [LName], qyGetStudents.Address = [Address], qyGetStudents.PostCode = [PostCode], qyGetStudents.County = [County], qyGetStudents.Tel = [Tel], qyGetStudents.Mobile = [Mobile], qyGetStudents.CourseType = [CourseType], qyGetStudents.CourseTitle = [CourseTitle], qyGetStudents.Duration = [Duration], qyGetStudents.YearofStudy = [YearofStudy], qyGetStudents.Fees = [Fees]
WHERE qyGetStudents.Reference = [Reference]

note that you don't update the actual Reference, just all the other fields.

first of all, i would like to thank you for your time and actually trying to help me but i still cant get get the update to work. In stead of updating all of the records, this time it updates nothing whatsoever.

Just a quick question> Does the reference number needs to be set as the primary key?

 

[B&W]

ok what's the primary key of your students table?

you will need to specify what record to update in the WHERE bit of your UPDATE statement, usually if it's 1 record only, by specifying a value for the primary key.

i have no primary key in the tblstudents. simply because if i have the ref as the primary key then one of my other forms called frmView which browses the student individually, i used the bindingsource technique instead of entering the code myself.

 

[B&W]

i have no primary key in the tblstudents. simply because if i have the ref as the primary key then one of my other forms called frmView which browses the student individually, i used the bindingsource technique instead of entering the code myself.

its ok i have just made the primary key on my tblStudents for the Refernce Number. Now what shall i do next?

 

[B&W]

ok now that you have a proper unique identifier, you need to change your update statement in your stored procedure and add the WHERE clause as i have shown earlier.

i have copied and pasted your query into my update query and still nothing gets updated at all.

 

[B&W]

This is what I would do:

1. debug and see what values are being passed into the cmd. parameters, make sure the reference is valid and correct

2. try manually executing the stored procedure to see if it updates anything

no my friend, still nothing...

 

[B&W]

ok try execute this in SQL directly:

UPDATE qyGetStudents
SET FName = 'testing123'
WHERE Reference = x

fill in a valid reference number for x.

does this work?

yes, works fine.

 

[B&W]

ok. can you show me the exact text in the stored procedure?

UPDATE qyGetStudents SET FName = 'testing123'
WHERE Reference=725;

you on about that right?

 

[B&W]

yes I believe its how they work in Access. In the vb code that's what it means by commandtype.storedprocedure.