Update Query with parameters any rule

[TonySaliba]

I have a simple Access Database with five fields, nameset,cat,name,path and desc. The first three fields are text 30 and the last two text 255. There are no indexes or unique values, this is a very small table holding around 20 records.

I am trying to update using OLEDBCommand with parameters.

I just discovered that when I add parameters these must be added in the same sequence as they are defined in the SQL statement. I did not encounter any rule in any book that states that this is a prerequisite. I would like to know whether I am missing anything. I was under the impression that specifying the name of the parameter is enough. If I change the order of adding parameters the program will not work, I think I am missing something somewhere.

One other thing is what is the best oledbtype to use in this case.

A simplified version of the program is as follows

Dim SQLupdate As String = "Update Table1 set Cat = @NewCat, Name = @NewName, Path=@NewPath,Desc=@NewDesc where " _
& "(( Nameset=@NameSet) and (Cat=@cat) and (Name=@Name) and (Path=@Path))"


Dim cmdUpdate As New OleDb.OleDbCommand(SQLupdate, oleCfgConn)




With cmdUpdate

.Parameters.Add("@NewCat", OleDb.OleDbType.VarChar, 30)
.Parameters.Add("@NewName", OleDb.OleDbType.VarChar, 30)
.Parameters.Add("@Newpath", OleDb.OleDbType.VarChar, 255)
.Parameters.Add("@NewDesc", OleDb.OleDbType.VarChar, 255)

.Parameters.Add("@NameSet", OleDb.OleDbType.VarChar, 30)
.Parameters.Add("@Cat", OleDb.OleDbType.VarChar, 30)
.Parameters.Add("@name", OleDb.OleDbType.VarChar, 30)
.Parameters.Add("@path", OleDb.OleDbType.VarChar, 255)

.Parameters.Item("@Nameset").Value = "Standard"
.Parameters.Item("@Cat").Value = cmbCategory.SelectedItem
.Parameters.Item("@name").Value = txtName.Text
.Parameters.Item("@path").Value = txtPath.Text

.Parameters.Item("@NewName").Value = "my new Name"
.Parameters.Item("@NewCat").Value = "my new cat"
.Parameters.Item("@Newpath").Value = "my new path"
.Parameters.Item("@NewDesc").Value =" my new description"

End With

[Lunatic3]

If you are using OleDbCommand then you should use ? in your sql atatment as the placeholder of the parameter and as OleDbCommand doesn't support named parameters the order of parameters is the order you add them to the parameters collection. If you use SqlDbCommand then you can use named parametes prefixed with @ and the order is not important. What you have done is a mix up of those two

[TonySaliba]

Thanks. I thought I was mixing something. Unfortunatly I never encountred this issue. All examples use named parameters.

Thanks