Error when updating access record

[Talk2Tom11]

I am trying to update a record in an access database. and I am getting the following error...

 

 

argument 'Prompt' cannot be converted to type 'String'

 

 

My SQL statement is as the following:

 

SQL = "UPDATE Products SET TotalAmount = " & Trim(txtBoxTotal.Text) & ", Edited = '" & Trim(a) & "' WHERE ProductName = '" & cBoxCat.Text & "'"

 

the variable a = DataTime.Now.ToShortDateString

 

if someone knows what i am doing wrong please help.

[PlausiblyDamp]

Have you tried using a parameterised query rather than attempting to concatenate the strings?

[Talk2Tom11]

Have you tried using a parameterised query rather than attempting to concatenate the strings?

 

How would i go about that?

[amir100]

How would i go about that?

 

There are tons of reference out there about parameterized query. Here's one of those many: http://aspnet101.com/aspnet101/tutorials.aspx?id=1

 

Even MSDN should provide you with a quick start on using parameterized query.

 

Back to your problem. Can you give us more detail about your code? Btw what type of exception did you code produce?

 

Just trying to help. :D

Edited May 31, 2007 by amir100

[Talk2Tom11]

When I have this as my code it works fine.... but

 

 

    Function GetInsertSQL()

       Dim a As Integer
       txtBoxNew.Text = txtBoxTotal.Text - txtAmount.Text
       Dim SQL As String
       Dim Q As String = Chr(34)
       SQL = "UPDATE Products SET TotalAmount = " & Trim(txtBoxNew.Text) & " WHERE ProductName = '" + cboxCat.Text + "'"
       Return SQL
   End Function

 

but i want to add txtboxtotal and txtAmount.... when I do that.. it makes them strings and then gives me an error

[PlausiblyDamp]

Firstly make sure you put

Option Strict On

at the top of all your vb files - this will prevent a lot of run time errors slipping past the compiler and will highlight where the problem you are having occurs.

 

In regards to parameterising your quesry somethink lile

       cmd.CommandText = "UPDATE Products SET TotalAmount = ? WHERE ProductName = ?"
       cmd.Parameters.AddWithValue("TotalAmount", txtboxnew.text)
       cmd.Parameters.AddWithValue("ProductName", cboxCat.Text)

should get you started.

[MrPaul]

Arithmetic operations on strings

 

but i want to add txtboxtotal and txtAmount.... when I do that.. it makes them strings and then gives me an error

 

The textbox contents need to be converted to a numeric data type (such as Decimal) to perform the addition:

 

Dim newTotal As Decimal

newTotal = Decimal.Parse(txtBoxTotal.Text) + Decimal.Parse(txtAmount.Text)
txtBoxNew.Text = newTotal.ToString()

 

Of course it would be prudent to encapsulate this with code to handle the possible FormatException.

 

Good luck :)

[Mondeo]

Hi MrPaul,

 

Slightly off topic but what is the difference between

 

Decimal.Parse(TextBox1.Text)

 

and

 

CDec(TextBox1.Text)

 

Do they both give the same result, personally i've always used the latter, is there a difference?

 

Thanks

[MrPaul]

CDec vs Parse

 

The C* functions such as CDec, CBool and CInt are keywords in the Visual Basic language. C# does not have any comparable keywords beside the generic cast operator, as there are methods within the .Net framework itself, such as Parse, which can perform conversions. Therefore coming from a C# background I am not used to using Visual Basic-specific features such as this, as it appears to me they primarily exist to provide backwards compatibility with VB6 code.

 

Incidentally, MSDN actually recommends the C* keywords be used over the more general methods provided in the framework. In this case I might agree since they are keywords and not merely utility functions such as Trim. However, since the Parse method is part of the framework it can be applied to any .Net language, whereas CDec cannot. Using CDec almost certainly compiles to a call to Parse anyway.

 

As you point out, the results are usually the same, but they are not guaranteed to be, especially with the backwards compatibility functions within the Microsoft.VisualBasic namespace. I know the Val function in particular acts differently from similar methods in the framework.

 

As I've said elsewhere I recommend all Visual Basic programmers remove the reference to Microsoft.VisualBasic and use standard .Net methods wherever possible. Since CDec is a keyword this would still be useable.