how to update a record using sql and adodc1 control 1

[obulldog27]

I am trying to update a spaceno record in my database called spaceno in table called info. The datatype is a number inputted in a textbox called spaceno1.text.
here is the code I am trying but cant get it to work
------------------------------------------------------

Adodc1.RecordSource = "SELECT info.spaceno FROM info where info.spaceno like '" & spaceno1.Text & "%';"
Adodc1.Refresh
With Adodc1
OriginalRecord = Adodc1.Recordset.Bookmark
.Recordset.MoveFirst
While Not .Recordset.EOF
.Recordset!spaceno = spaceno1.Text
.Recordset.Update
.Recordset.MoveNext
If .Recordset.EOF And .Recordset.BOF Then
.Recordset.Bookmark = OriginalRecord
Exit Sub
End If
Wend
End With
Adodc1.Refresh
End If

 

[Golom]

Couple of things to note:

* The LIKE operator is a text field operator. Presumably "spaceno" is being converted to a string to make the comparison. That means that (for example) if "spaceno1.text" is "1" then it will match any value of the field "spaceno" that starts with 1 (e.g. 1, 145, 191919, etc.) Is that what you want? The other possibility is that there won't be any matches because "spaceno" may be converted to a string using the "Str" function which usually puts a leading space before the number.

* A more economical way of doing this would be an UPDATE statement.

cn.Execute _
   "UPDATE info SET Spaceno = " & spaceno1.Text & " " & _
   "WHERE  Trim$(Str$(spaceno)) LIKE '" & spaceno1.Text & "%'"

Where "cn" is the ADO Connection.

 

[obulldog27]

spaceno1.text is actually a number. I want to find the exact number.

 

[BobRodes]

If you want to find an exact number, you just have to provide the exact number. What I'm gathering from your code is that you want to find a range of numbers, all of which begin with the numbers in your text box. Golom has pointed out what can go wrong with your approach, for text based sort of numbers is analogous to alpha sorts, therefore 191919 will come before 2, just as aiaiai comes before b.

Now, let's say you want the user to put in a 3 digit number, and want to get all 5 digit integers that begin with those 3 digits. Assuming your spaceno field is an integer of some sort, your WHERE clause would look like this:

"WHERE info.spaceno BETWEEN(CAST('" & spaceno1.Text & "00' AS int), CAST('" & spaceno1.text & "99' AS int))"

assuming I haven't messed up the syntax. Let's assume the present value of spaceno1.text is 189. Then your SQL code would look like this:

WHERE info.spaceno BETWEEN(CAST('18900' as int), CAST('18999' as int))

Note that between is an inclusive operator, corresponding to >= x and <= y, where x and y are the 2 arguments to the between function.

I'm not sure if this is what you are looking for. If it isn't, I'll still say it's been an interesting intellectual exercise. Also, I agree with golom about using the UPDATE functionality in SQL server rather than the Data control.

HTH

Bob