UPDATE WHERE Clause with Parameters

[trims30]

The following code does not find record to be updated.

If however I change WHERE Recnum=@RecNum to WHERE Recnum=1 and remove parameters referencing @Recnum it will update record 1.

What am I doing wrong here?

StrSql = "UPDATE Equipment SET TimeStampCol =@TimeStampCol," & _
" Ident =@Ident, Descr =@Descr, Serial=@Serial, Cost=@Cost" & _
" WHERE Recnum =@RecNum"

oCmd = New OleDbCommand(StrSql, oConn)

oCmd.Parameters.Add(New OleDbParameter("@RecNum", OleDbType.Integer))
oCmd.Parameters.Add(New OleDbParameter("@TimeStampCol", OleDbType.Date))
oCmd.Parameters.Add(New OleDbParameter("@Ident", OleDbType.VarChar, 12))
oCmd.Parameters.Add(New OleDbParameter("@Descr", OleDbType.VarChar, 40))
oCmd.Parameters.Add(New OleDbParameter("@Serial", OleDbType.VarChar, 20))
oCmd.Parameters.Add(New OleDbParameter("@Cost", OleDbType.Double))

oCmd.Parameters("@RecNum").Value = intRecnum
oCmd.Parameters("@TimeStampCol").Value = Now
oCmd.Parameters("@Ident").Value = txtIdent.Text
oCmd.Parameters("@Descr").Value = txtDescr.Text
oCmd.Parameters("@Serial").Value = txtSerial.Text
oCmd.Parameters("@Cost").Value = txtCost.Text

oCmd.ExecuteNonQuery()

 

[r3plica]

first thing is first, get your program to display intRecnum so you can see if it holding a value.

oCmd.Parameters("@RecNum").Value = intRecnum
oCmd.Parameters("@TimeStampCol").Value = Now
oCmd.Parameters("@Ident").Value = txtIdent.Text
oCmd.Parameters("@Descr").Value = txtDescr.Text
oCmd.Parameters("@Serial").Value = txtSerial.Text
oCmd.Parameters("@Cost").Value = txtCost.Text

'oCmd.ExecuteNonQuery()
MsgBox(intRecnum)

 

[trims30]

Yes, it is displaying a valid number and that number is also in the database.

If intRecnum is 2 and if I were to use WHERE RecNum=2 it will work but it doesn't like the parameterization of @Recnum

Also, in my code example, how do I determine in code if update was successful or no record found?

Lee

 

[r3plica]

Use a stored procedure like so:

  CREATE PROC dbo.UpdateEquipment
  	@TimeStampCol DATE,
	@Ident VARCHAR(12), 
	@Descr VARCHAR(40), 
	@Serial VARCHAR(20), 
	@Cost FLOAT,
	@RecNum INT
  AS
  BEGIN
	UPDATE Equipment 
	SET 
		TimeStampCol = @TimeStampCol,
		Ident = @Ident, 
		Descr = @Descr, 
		Serial = @Serial, 
		Cost = @Cost
	WHERE Recnum =@RecNum
  END
  GO

Then use this class to update your records (fill in your own paremeters)

Imports App.Common
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Public Class EquipmentDataAccess
    Inherits BaseDataAccess

    Public Shared Function UpdateEquipment(ByVal pParameter As ParameterCollection) As Boolean
        Dim pCmd As New SqlCommand()
        pCmd.CommandType = CommandType.StoredProcedure
        pCmd.CommandText = "dbo.UpdateEquipment"
        pCmd.Parameters.Add("@RecNum", SqlDbType.Int).Value = pParameter.ID
        SetConnectionString = ConfigurationManager.ConnectionStrings("myConnection").ConnectionString
        Try
            ExecuteNonSelect(pCmd)
            Return True
        Catch ex As Exception
            'Throw New Exception(ex.ToString) 'Comment out this line if you want to see the error
            Return False
        End Try
    End Function
End Class

and the BaseDataAccess class is

Imports System.Data
Imports System.Data.SqlClient

Public Class BaseDataAccess
    Private Shared mstrConnectionString As String
    Private Shared mConn As SqlConnection

    Public Shared WriteOnly Property SetConnectionString() As String
        Set(ByVal value As String)
            mstrConnectionString = value
        End Set
    End Property

    Shared Function FillDataSet(ByVal pSQLCmd As SqlCommand) As DataSet
        Dim ds As New DataSet
        Dim da As New SqlDataAdapter
        If mstrConnectionString <> "" Then
            OpenConnection()
            pSQLCmd.Connection = mConn
            da.SelectCommand = pSQLCmd
            da.Fill(ds)
            da.Dispose()
            CloseConnection()
        Else
            Throw New ApplicationException("Connection String has not been set")
        End If
        Return ds
    End Function

    Shared Sub ExecuteNonSelect(ByVal pSQLCmd As SqlCommand)
        If mstrConnectionString <> "" Then
            OpenConnection()
            pSQLCmd.Connection = mConn
            pSQLCmd.ExecuteNonQuery()
            CloseConnection()
        Else
            Throw New ApplicationException("Connection String has not been set")
        End If
    End Sub

    Shared Function ExecuteScalar(ByVal pSQLCmd As SqlCommand)
        Dim sReturn

        If mstrConnectionString <> "" Then
            OpenConnection()
            pSQLCmd.Connection = mConn
            sReturn = pSQLCmd.ExecuteScalar()
            CloseConnection()
        Else
            Throw New ApplicationException("Connection String has not been set")
        End If

        Return sReturn
    End Function

    Private Shared Sub OpenConnection()
        mConn = New SqlConnection
        mConn.ConnectionString = mstrConnectionString
        mConn.Open()
    End Sub

    Private Shared Sub CloseConnection()
        If mConn.State = ConnectionState.Open Then
            mConn.Close()
            mConn.Dispose()
        End If
    End Sub

End Class

at least then you will get a "False" if your record doesnt insert.

 

[trims30]

Wow... Seems like an awful lot of work just to fix a WHERE clause.

Don't get me wrong, I do appreciate all comment but I'm trying to lean how to use basic Parameters right now so I'd rather stick with what I've got and what I understand right now before going to Stored Procedures.

I can successfully use "WHERE Recnum = " & cstr(intRecnum) and eliminate the @RecNum parameter but I'd like to make it work with Parameter.

Could the problem be related to the fact that RecNum in the database is a AutoNumber Primary Key and is "Read Only" field and you can't put a value into that field?


If that's the case then can we not use parameter in Where Clause to find by RecNum (Record ID)?

 

[JohnH]

Access database doesn't support named parameters, so the order in which you add them to the Parameters collection is crucial, they must match exactly the order of parameters in the query.
(regarding readonly, you are not setting this field)

 

[trims30]

John:
Ok -Yes, I'm using Access 2007 Database and since you said parameters must match those in the query and my query is...

StrSql = "UPDATE Equipment SET TimeStampCol =@TimeStampCol," & _
" Ident =@Ident, Descr =@Descr, Serial=@Serial, Cost=@Cost" & _
" WHERE Recnum =@RecNum"

The Where Recnum=@Recnum refers to Recnum field that IS NOT in the SET part of the SQL - Is that the source of my problem?

Lee

 

[Tom]

To re-word what JM was saying. Access ignores whatever you name the parameters and only goes by the order that you created it (like an index). So you have to add them to your command object in the exact same order that they are used in your Select/Update statement.

Since @RecNum is the last parameter in your update statement, it also must be last in the order that you are adding it to the command object.

 

[trims30]

Tom:
Thank you very much. That solves the problem.

Lastly, if doing an UPDATE how do I determine if the WHERE clause fails?

For Example if I'm updating record 44 (WHERE RECNUM=44) and record 44 does not exist - how do I trap that? can't seem to figure that out and Try/Catch doesn't see the error.

Lee

 

[Tom]

intRecordsUpdated = cmd.ExecuteNonQuery

 

[Tom]

Tom:
Thank you very much. That solves the problem.

JM gave the answer, I just clarified the same thing. Click on the rep icon above his post to give him the credit.

 

[trims30]

Tom:
Thank you -

Lee