SQL Statement - UPDATE .... WHERE ?

[Wesley008]

Oki, so I have this Update Statement.

UPDATE Classes SET [Class Name] = @ClassName, [Class Code] = @ClassCode, [Class Room] = @ClassRoom, [Grade] = @Grade, [Teacher] = @Teacher

Now, If I add WHERE ID = @ID to the END. And then add Parameters for it, like I did for the rest of the Columns. I get an Error.

                'oAdapter.UpdateCommand.Parameters.Add("@ID", OleDbType.Integer, 5, "ID")
                oAdapter.UpdateCommand.Parameters.AddWithValue("@ClassName", .txtClassName.Text)
                oAdapter.UpdateCommand.Parameters.AddWithValue("@ClassCode", .txtClassCode.Text)
                oAdapter.UpdateCommand.Parameters.AddWithValue("@ClassRoom", .txtClassRoomNum.Text)
                oAdapter.UpdateCommand.Parameters.AddWithValue("@Grade", .txtGrade.Text)
                oAdapter.UpdateCommand.Parameters.AddWithValue("@Teacher", .txtTeacher.Text)

So, why do I need the WHERE in the Update Statment, and how would I get it to work with this code:

Public Sub SaveMainDatabaseForEdit()
        oConnect = New OleDbConnection(sConnectionString & "Classes.mdb")
        SQL = "UPDATE Classes SET [Class Name] = @ClassName, [Class Code] = @ClassCode, [Class Room] = @ClassRoom, [Grade] = @Grade, [Teacher] = @Teacher"
        Using oCommand As New OleDbCommand(SQL, oConnect)
            With frmEditClasses
                Dim oAdapter As New OleDbDataAdapter
                oAdapter.UpdateCommand = oCommand
                oConnect.Open()
                'oAdapter.UpdateCommand.Parameters.Add("@ID", OleDbType.Integer, 5, "ID")
                oAdapter.UpdateCommand.Parameters.AddWithValue("@ClassName", .txtClassName.Text)
                oAdapter.UpdateCommand.Parameters.AddWithValue("@ClassCode", .txtClassCode.Text)
                oAdapter.UpdateCommand.Parameters.AddWithValue("@ClassRoom", .txtClassRoomNum.Text)
                oAdapter.UpdateCommand.Parameters.AddWithValue("@Grade", .txtGrade.Text)
                oAdapter.UpdateCommand.Parameters.AddWithValue("@Teacher", .txtTeacher.Text)
                Dim DR As DataRow
                DR = oDataSet.Tables(0).Rows(.icmbClassesIndex)
                DR.BeginEdit()
                DR("Class Name") = .txtClassName.Text
                DR("Class Code") = .txtClassCode.Text
                DR("Class Room") = .txtClassRoomNum.Text
                DR("Grade") = .txtGrade.Text
                DR("Teacher") = .txtTeacher.Text
                DR.EndEdit()
                oAdapter.Update(oDataSet)
                oDataSet.AcceptChanges()
            End With
        End Using
    End Sub

[Negative0]

Since you are using access, you need to add the parameters in the order in which they appear in the query. Access really doesn't support named parameters.

[Wesley008]

Ok, so now How do I fix this error:

"Parameter @ClassName has no default value."

with this code:

Public Sub SaveMainDatabaseForEdit()
        oConnect = New OleDbConnection(sConnectionString & "Classes.mdb")
        SQL = "UPDATE Classes SET [Class Name] = @ClassName, [Class Code] = @ClassCode, [Class Room] = @ClassRoom, [Grade] = @Grade, [Teacher] = @Teacher WHERE ID = @ID"
        Using oCommand As New OleDbCommand(SQL, oConnect)
            With frmEditClasses
                Dim oAdapter As New OleDbDataAdapter
                oAdapter.UpdateCommand = oCommand
                oConnect.Open()
                oAdapter.UpdateCommand.Parameters.Add("@ID", OleDbType.Integer, 5, "[ID]")
                oAdapter.UpdateCommand.Parameters.Add("@ClassName", OleDbType.VarChar, 50, "[Class Name]")
                oAdapter.UpdateCommand.Parameters.Add("@ClassCode", OleDbType.VarChar, 20, "[Class Code]")
                oAdapter.UpdateCommand.Parameters.Add("@ClassRoom", OleDbType.VarChar, 20, "[Class Room]")
                oAdapter.UpdateCommand.Parameters.Add("@Grade", OleDbType.VarChar, 10, "[Grade]")
                oAdapter.UpdateCommand.Parameters.Add("@Teacher", OleDbType.VarChar, 50, "[Teacher]")
                Dim DR As DataRow
                DR = oDataSet.Tables(0).Rows(.icmbClassesIndex)
                DR.BeginEdit()
                DR("Class Name") = "AB"
                DR("Class Code") = .txtClassCode.Text
                DR("Class Room") = .txtClassRoomNum.Text
                DR("Grade") = .txtGrade.Text
                DR("Teacher") = .txtTeacher.Text
                DR.EndEdit()
                oAdapter.Update(oDataSet)
                oDataSet.AcceptChanges()
            End With
        End Using
    End Sub

[Negative0]

Move this line:

oAdapter.UpdateCommand.Parameters.Add("@ID", OleDbType.Integer, 5, "[ID]")

To the bottom of the parameters.add list.

[Wesley008]

Hmm... I did that, but it still coming up with the exact same error.

EDIT: Now, it comes up with ID as no default value.

[Hack]

So, why do I need the WHERE in the Update Statment

Because if you don't have a WHERE clause every single record in your database will be affected by your UPDATE. That is sometimes the desired end result, but typically only certain records should be updated at any one time.

I'm not sure what is going on with all of this Adapter stuff. Being the simpleton that I am, I usually go with something basic like this.

Imports System.Data.OleDb
 
Public Class Form1
 
    Private connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=sConnectionString & "Classes.mdb;"
    Private conn As OleDb.OleDbConnection
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        conn = New OleDbConnection(connstring)
        conn.Open()
    End Sub
 
 
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim SQL As String = String.Empty
 
SQL = "UPDATE Classes SET [Class Name] = @ClassName, "
SQL = SQL & "[Class Code] = @ClassCode, "
SQL = SQL & "[Class Room] = @ClassRoom, "
SQL = SQL & "[Grade] = @Grade, "
SQL = SQL & "[Teacher] = @Teacher "
SQL = SQL & "WHERE ID = @ID " 
 
Dim command As New OleDBCommand(SQL, conn)
 With command.Parameters
     .AddWithValue("@ClassName", txtClassName.Text)
     .AddWithValue("@ClassCode", txtClassCode.Text)
     .AddWithValue("@ClassRoom", txtClassRoomNum.Text)
     .AddWithValue("@Grade", txtGrade.Text)
     .AddWithValue("@Teacher", txtTeacher.Text)
     .AddWithValue("@ID",5)
 End With
 command.ExecuteNonQuery()
End Sub
End Class

Does that work for you?

[Wesley008]

oConnect = New OleDbConnection(sConnectionString & "Classes.mdb")
        SQL = "UPDATE Classes SET [Class Name] = @ClassName, [Class Code] = @ClassCode, [Class Room] = @ClassRoom, [Grade] = @Grade, [Teacher] = @Teacher WHERE ID = @ID"
        Dim oCommand As New OleDbCommand(SQL, oConnect)
        oConnect.Open()
        With frmEditClasses
            oCommand.Parameters.AddWithValue("@ClassName", .txtClassName.Text)
            oCommand.Parameters.AddWithValue("@ClassCode", .txtClassCode.Text)
            oCommand.Parameters.AddWithValue("@ClassRoom", .txtClassRoomNum.Text)
            oCommand.Parameters.AddWithValue("@Grade", .txtGrade.Text)
            oCommand.Parameters.AddWithValue("@Teacher", .txtTeacher.Text)
            oCommand.Parameters.AddWithValue("@ID", 5)
            oCommand.ExecuteNonQuery()
            MsgBox(oConnect.ConnectionString)
        End With

Call me blind, but I did exactly what you said, and it still didn't work. I didn't recive no error message, but nothing happens. My Database doesn't get changed.

EDIT: ID is a COUNTER in the SQL Statment, it will Increment every new Row Created( dunno if that makes a difference)

[Hack]

ID MUST match a value in your database or the query won't work.

If this is not what should happen, then ID must be removed from your WHERE clause and something else used to determine what record receives the update.

[Wesley008]

Wow, I feel n00b. Thanks so much, I just made ID the Combo Box Selected Index + 1 - so if I'm on the First Row, my Index would be 0 + 1 so it would edit the first Row.

[techgnome]

Umm... that's not a good idea... it will work as long as you NEVER delete any data....

-tg

[Wesley008]

Ok, so then how do I fix this issue?

[techgnome]

beats me... depends on how you are loading the data into the list box... but what I'd do is add the item to the list, then set the ItemData to the ID that corresponds with it... then use the value in the itemdata to set as the ID when updating.

-tg

[Wesley008]

Ok, so whats this ItemData? Can I get an Example?

[techgnome]

Oh... wait... crap... for some reason I was thinking this was VB 6... it's not... it's .NET... even better. three lines of code... more or less.

If you post the code you are using to get the data and populate the combo box, I'll see if I can tweak it to get what you need in a fairly easy and painless manner.

-tg

[Wesley008]

Public Sub SaveMainDatabaseForEdit()
        SQL = "SELECT [ID] FROM Classes"
        Dim oAdapter As New OleDbDataAdapter(SQL, oConnect)
        oAdapter.Fill(oDataSet)
        For Each DataRow As DataRow In oDataSet.Tables(0).Rows
            MsgBox(DataRow(5).ToString)
        Next
        oConnect = New OleDbConnection(sConnectionString & "Classes.mdb")
        SQL = "UPDATE Classes SET [Class Name] = @ClassName, [Class Code] = @ClassCode, [Class Room] = @ClassRoom, [Grade] = @Grade, [Teacher] = @Teacher WHERE ID = @ID"
        Using oCommand As New OleDbCommand(SQL, oConnect)
            With frmEditClasses
                oCommand.Parameters.AddWithValue("@ClassName", .txtClassName.Text)
                oCommand.Parameters.AddWithValue("@ClassCode", .txtClassCode.Text)
                oCommand.Parameters.AddWithValue("@ClassRoom", .txtClassRoomNum.Text)
                oCommand.Parameters.AddWithValue("@Grade", .txtGrade.Text)
                oCommand.Parameters.AddWithValue("@Teacher", .txtTeacher.Text)
                oCommand.Parameters.AddWithValue("@ID", .icmbClassesIndex + 1)
                oConnect.Open()
                oCommand.ExecuteNonQuery()
            End With
        End Using
    End Sub

That code will Edit & Save to the Row the ComboBox Selected Index is selected.

It's set to a variable and used here:

oCommand.Parameters.AddWithValue("@ID", .icmbClassesIndex + 1)

------

I've been trying this way:

Public Sub RefillIDColumn()
        oConnect = New OleDbConnection(sConnectionString & "Classes.mdb")
        SQL = "SELECT [ID] FROM Classes"
        Using oAdapter As New OleDbDataAdapter(SQL, oConnect)
            oConnect.Open()
            oAdapter.Fill(oDataSet)
            Dim i2 As Integer
            For Each i As DataRow In oDataSet.Tables(0).Rows
                SQL = "INSERT INTO CLASSES ([ID]) VALUES (@ID)"
                Using oCommand As New OleDbCommand(SQL, oConnect)
                    oCommand.Parameters.AddWithValue("@ID", i2)
                    oCommand.ExecuteNonQuery()
                End Using
                '                MsgBox(oDataSet.Tables(0).Rows(i2).Item(0).ToString)
                i2 += 1
            Next
        End Using
    End Sub

What that is doing is just completly rewriting the ID Column.

[Wesley008]

NVM, That method wont work, I still need something in the UPDATE .... WHERE area.

[techgnome]

I was looking for the code where you LOAD the combo box.

-tg

[Wesley008]

Public Function FillComboBox(ByVal winForm As Form, ByVal cmbName As ComboBox) As Boolean
        oConnect = New OleDbConnection(sConnectionString & "Classes.mdb")
        SQL = "SELECT [Class Name], [Class Code] FROM Classes"
        Using oCommand As New OleDbCommand(SQL, oConnect)
            oConnect.Open()
            oReader = oCommand.ExecuteReader
            With winForm
                cmbName.Items.Clear()
                While oReader.Read
                    cmbName.Items.Add("[" & oReader(1) & "] " & oReader(0))
                End While
            End With
        End Using
    End Function

I made a Function because I call it twice in 2 different forms.

[techgnome]

There's no reason to make it a function, you aren't returning anything. Also, there's no reason to pass in the form... just the combo box. You're using the With but then not taking advantage of it... see if this works for you:

	Public Sub FillComboBox(ByVal cmbName As ComboBox)
		Dim dtData As New DataTable

		oConnect = New OleDbConnection(sConnectionString & "Classes.mdb")
		Sql = "SELECT [Class Name], [Class Code] FROM Classes"
		Using oCommand As New OleDbCommand(Sql, oConnect)
			oConnect.Open()
			oreader = oCommand.ExecuteReader

			dtData.Load(oreader)
			cmbName.DataSource = dtData
			cmbName.DisplayMember = "fieldname to display"
			cmbName.ValueMember = "fieldnname to use as value"
		End Using
	End Sub

-tg

[Wesley008]

How is that going help me Edit a Row if I start Deleting Rows how am I suppose to reference back to the Row? Since the ID Field is going to be Different.

[techgnome]

The IDs come from the database, right?
So if I have this in the table:
ID ... Name
1 ..... Wesley008
2 ..... TechGnome

And I load it into the combo box like I showed... when you select your name, the selectedvalue property of the combo box will be 1. You can then use that ID to load your info, edit it, and update the database.

If you then add a new user, say si_th_geek, then you'll end up with this:

ID ... Name
1 ..... Wesley008
2 ..... TechGnome
3 ..... si_the_geek

If you were then to delete me, you get this:

ID ... Name
1 ..... Wesley008
3 ..... si_the_geek

And now, let's add Hack and mendhack:

ID ... Name
1 ..... Wesley008
3 ..... si_the_geek
4 ..... hack
5 ..... mendhack

See? Even though 2 was removed, as long as you use the ID returned by the DB, you'll always be able to get back to the row. (until it too is deleted from the database, but then after that, it wouldn't be available to load back into the combo box anyways.)

-tg

[Wesley008]

Can you please explain this:

cmbName.DisplayMember = "[Class Name]"
cmbName.ValueMember = "[ID]"


a bit more? Like what do I put their? I've never used that method before.

[techgnome]

I did show where to put them... look at the FillCombo method I posted... it's in there.

-tg

[Wesley008]

Ok, so I got that, but when I'm setting up my parameters to Save the Edited Verison, Do I reference back to the cmbName.ValueMember? Like this?

Public Sub SaveMainDatabaseForEdit()
        oConnect = New OleDbConnection(sConnectionString & "Classes.mdb")
        SQL = "UPDATE Classes SET [Class Name] = @ClassName, [Class Code] = @ClassCode, [Class Room] = @ClassRoom, [Grade] = @Grade, [Teacher] = @Teacher WHERE ID = @ID"
        Using oCommand As New OleDbCommand(SQL, oConnect)
            With frmEditClasses
                oCommand.Parameters.AddWithValue("@ClassName", .txtClassName.Text)
                oCommand.Parameters.AddWithValue("@ClassCode", .txtClassCode.Text)
                oCommand.Parameters.AddWithValue("@ClassRoom", .txtClassRoomNum.Text)
                oCommand.Parameters.AddWithValue("@Grade", .txtGrade.Text)
                oCommand.Parameters.AddWithValue("@Teacher", .txtTeacher.Text)
                oCommand.Parameters.AddWithValue("@ID", .cmbClasses.ValueMember)
                oConnect.Open()
                oCommand.ExecuteNonQuery()
            End With
        End Using
    End Sub

[techgnome]

Almost...
AddWithValue("@ID", .cmbClasses.ValueMember)

Should be:
AddWithValue("@ID", .cmbClasses.SelectedValue)

-tg

[Wesley008]

Thanks!

It kinda works, I'm still running into a big issue.

When I add say 2 Items, and I edit one of them, It's editing all of them.

You have the code used for Saving the Edited Verison above.

And heres how I retrive it to fill into text boxes just in case you need it.

Public Sub RetriveMainDatabaseForEdit()
        oConnect = New OleDbConnection(sConnectionString & "Classes.mdb")
        SQL = "SELECT [Class Name], [Class Code], [Class Room], [Grade], [Teacher] FROM Classes"
        Using oAdapter As New OleDbDataAdapter(SQL, oConnect)
            oConnect.Open()
            oAdapter.Fill(oDataSet)
            With frmEditClasses
                .txtClassName.Text = oDataSet.Tables(0).Rows(.icmbClassesIndex).Item("Class Name")
                .txtClassCode.Text = oDataSet.Tables(0).Rows(.icmbClassesIndex).Item("Class Code")
                .txtClassRoomNum.Text = oDataSet.Tables(0).Rows(.icmbClassesIndex).Item("Class Room")
                .txtGrade.Text = oDataSet.Tables(0).Rows(.icmbClassesIndex).Item("Grade")
                .txtTeacher.Text = oDataSet.Tables(0).Rows(.cmbClasses.SelectedIndex).Item("Teacher")
            End With
        End Using
    End Sub

BTW. Thank you very much this has been great. And also, your website, make a request for a song, doesn't work (atleast in FF)


EDIT:
Am I going have to change all the DisplayValue and STuff for the Text Boxes like I did with the Combo Box? or is it something different. Its not the display issue I'm having, its just that. anything I edit get's changed for all the data.

[techgnome]

Looks like the sql is off... it's pulling all of the items in the database, then you're only displaying the first item in the table.

Might want to add a where clause similar to the one in your update. That way, you pull in the one you actually selected. Also your use of a dataset is using a hammer to kill a fly... it's overkill and there's no real reason to do so. If you use a datatable instead, then you can le .... OH.... wait ... hold on.... I see what you've done. Ahh.... OK ... yeah.. there's a better way.

Try this:

      Public Sub RetriveMainDatabaseForEdit()
              oConnect = New OleDbConnection(sConnectionString & "Classes.mdb")
              SQL = "SELECT [Class Name], [Class Code], [Class Room], [Grade], [Teacher] FROM Classes WHERE ID = " & frmEditClasses.cmbClasses.SelectedValue
              'You might get some flak for using a concatenated string for the SQL (I'm usually the one to give the flak) but for simplicity, roll with it

              Using oAdapter As New OleDbDataAdapter(SQL, oConnect)
                  oConnect.Open()
                  oAdapter.Fill(oDataTable) ' Use a datatable here... it's smaller, faster and easier for your needs
                  dim oDataRow As DataRow = oDataTable.Rows(0) 'Get the first and only row
                  With frmEditClasses
                      .txtClassName.Text = oDataRow("Class Name").Tostring
                      .txtClassCode.Text = oDataRow("Class Code").Tostring
                      .txtClassRoomNum.Text = oDataRow("Class Room").Tostring
                      .txtGrade.Text = oDataRow("Grade").Tostring
                      .txtTeacher.Text = oDataRow("Teacher").Tostring
                  End With
              End Using
          End Sub

I'm not able to test it, but that should be close to what you need. Basically, rather than loading the entire table, only load the row you need.

-tg

edit - thanks, but it does work... but you have to register first... which I don't suggest jsut yet as it's still a work in progress and I plan to flush the users table eventually... right now it's full of people who have tried to spam the place.