Update with Variables

[Rdurrance]

Hi all,
I am having a problem with updating my MSAccess database. Here is my code:

Dim FrstClr AsString = FrstClrTxt.Text
Dim SesID AsString = Session.SessionID

Dim queryString2 AsString = "UPDATE [SessionID] SET [Color1]=@Color1 WHERE ([SessionID].[SessionID] = @Session" & _
"ID)"
Dim dbCommand2 As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
dbCommand2.CommandText = queryString2
dbCommand2.Connection = OleDbConnection1

Dim dbParam_sessionID2 As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_sessionID2.ParameterName = "@SessionID"
dbParam_sessionID2.Value = SesID
dbParam_sessionID2.DbType = System.Data.DbType.[String]
dbCommand2.Parameters.Add(dbParam_sessionID2)

Dim dbParam_color1 As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_color1.ParameterName = "@Color1"
dbParam_color1.Value = FrstClr
dbParam_color1.DbType = System.Data.DbType.String
dbCommand2.Parameters.Add(dbParam_color1)

SesDA2.UpdateCommand = dbCommand2
SesDA2.UpdateCommand.CommandText = queryString2

SesDA2.Fill(SesDS2)

OleDbConnection1.Open()
SesDA2.UpdateCommand.ExecuteNonQuery()
OleDbConnection1.Close()

I tried to space it so you could see better.

SesID and FrstClr are variables. FrstClr relates to a Label that is linked to another database field. I thnk that my problem with the @Color1 parameter. If I change the query to:

UPDATE [SessionID] SET [Color1]='@Color1' WHERE ([SessionID].[SessionID] = @Session" & _
"ID)"

It will insert @Color1 into the database in the correct spot. So, I know that the query is working. I can use this same type of query with the variables and all to insert row all day long but when I try to update nothing happens.

I don't have to code it this way but I have been using this method to insert rows for a while. This is only one field in the database there are actually three fields that are updated on this particular page. I figured I would start with one and work my way up.

Any help would be appreciated.

Thank

Rob

 

[TechGnome]

You're adding your parameters in the wrong order. You should be creating and adding your Color1 parameter first, THEN add the SessionID parameter.

-tg

 

[Rdurrance]

I cannot believe, that is all. It worked. Holy Cow . This forum rocks!

Thanks again. I have been stuck on tihis for about three weeks now.