After updating, value in db dissapears (intermittent) [using SQL sever]

[k3]

Hi,

I followed the example from Sceppa's book (chap.10). I have a function which
creates a SqlCommand object using a SQL statement with parameterized values
to update a row.

'**************************************
Function CreateUpdateCmd() As SqlCommand
dim strSQL As String
strSQL = "UPDATE TestTable SET Amount = @Amount, OrderType = @OrderType
WHERE IDNum = @IDNum"

dim cmd As New SqlCommand(strSQL, cn) ' cn created elsewhere
dim pc as SqlParameterCollection = cmd.Parameters

pc.Add("@Amount", SqlDbType.Int)
pc.Add("@OrderType", SqlDbType.VarChar) ' Loaded into a combo box
pc.Add("@IDNum", SqlDbType.Int)

return cmd
End Function
'**************************************

I then have a function which uses the created command and initializes the
parameters with the row's values.

pc("@Amount").Value = row("Amount")
pc("@OrderType").Value = row("OrderType")
pc("@IDNum").Value = row("IDNum")

The ExecuteNonQuery() always affects one row (which is good).... but
sometimes the varchar value completely dissapears from the DB's column in
that row, it goes to NULL. This is very intermittent and I couldn't recreate
the sequence which causes the problem. I have many other fiels, but it's
only that particular combo box's field that does it.

Has anyone had this problem before?

Thanks!

 

[Miha Markic]

Hi k3,

You should check what data you assign to parameter - you are probably
passing an empty string to that parameter.

 

[k3]

Yes I do check the string, it's always a non-empty string passed in. And the
string gets store to the DB because I can see it using Enterprise Manager in
SQL Server 2000. But sometimes, that value dissapears when I run my program
even though I do not perform an update.


"Miha Markic" <miha at rthand com> wrote in message
| Hi k3,
|
| You should check what data you assign to parameter - you are probably
| passing an empty string to that parameter.
|
| --
| Miha Markic - RightHand .NET consulting & development
| miha at rthand com
| www.rthand.com
|
| | > Hi,
| >
| > I followed the example from Sceppa's book (chap.10). I have a function
| which
| > creates a SqlCommand object using a SQL statement with parameterized
| values
| > to update a row.
| >
| > '**************************************
| > Function CreateUpdateCmd() As SqlCommand
| > dim strSQL As String
| > strSQL = "UPDATE TestTable SET Amount = @Amount, OrderType =
| @OrderType
| > WHERE IDNum = @IDNum"
| >
| > dim cmd As New SqlCommand(strSQL, cn) ' cn created elsewhere
| > dim pc as SqlParameterCollection = cmd.Parameters
| >
| > pc.Add("@Amount", SqlDbType.Int)
| > pc.Add("@OrderType", SqlDbType.VarChar) ' Loaded into a combo box
| > pc.Add("@IDNum", SqlDbType.Int)
| >
| > return cmd
| > End Function
| > '**************************************
| >
| > I then have a function which uses the created command and initializes
the
| > parameters with the row's values.
| >
| > pc("@Amount").Value = row("Amount")
| > pc("@OrderType").Value = row("OrderType")
| > pc("@IDNum").Value = row("IDNum")
| >
| > The ExecuteNonQuery() always affects one row (which is good).... but
| > sometimes the varchar value completely dissapears from the DB's column
in
| > that row, it goes to NULL. This is very intermittent and I couldn't
| recreate
| > the sequence which causes the problem. I have many other fiels, but it's
| > only that particular combo box's field that does it.
| >
| > Has anyone had this problem before?
| >
| > Thanks!
| >
| >
| >
| >
|
|