UPDATE command with @ variables

  • Thread starter Thread starter Gert
  • Start date Start date
G

Gert

Hi,

I am a newbie in ADO.NET. I use MYSQL in combination with ASP.NET VB. To
make it more perfect I like to do a SQL Update command with variables.
Please help. I found a lot of samples, most of them returning no errors at
all. But the record isn´t changed.

Please help

I need a `update customers set counter = @teller where id = @id

With VB.NET code. A lot of combinations are found but none of them really
changed the counter.

But I am not sure about how to the it. Please help.

Best regards,
Gert
 
Gert said:
Hi,

I am a newbie in ADO.NET. I use MYSQL in combination with ASP.NET VB. To
make it more perfect I like to do a SQL Update command with variables.
Click to expand...

Does "MySQL" support Stored Procedure (SP) ? If it does, build a simple SP
and execute that SP right on your MySQL server instance, if it works, then
it likely will work on VS.Net application.

John
 
Step 1. Build a SP on your MySQL server as below:
************
create procedure spTest
@f1 varchar(20)
,@f2 int
,@rc int out as

insert someTable select @f1,@f2
if(@@ERROR)return -1

select * from someTable where f1=@f1 and f2=@f2

set @rc=@@ROWCOUNT
return @rc
go
************

Step 2. Execute the above SP as below:

declare
@f1 varchar(20)
,@f2 int
,@rc int

set @f1='1st Record'
set @f2=2005
set @rc=0

exec spTest @f1,@f2,@rc out

select @rc as [ReturnCode]

go
*********

These work in MS/SQL engine. You will need to find out how to translate them
into MySQL.

John
 
Gert,

What .NET Data Provider are you using to talk to MySQL? The OLE DB
NET Data Provider w/ a MySQL OLE DB provider? The ODBC .NET Data Provider
w/ a MySQL ODBC driver? A .NET Data Provider that's specifically for MySQL?

I ask this question because the OLE DB and ODBC .NET Data Providers
support only parameter markers. So your query would look like: update
customers set counter = ? where id = ? If you're using a .NET Data
Provider that's specifically for MySQL, check the documentation on that
provider's parameter class. It should document how to construct a
parameterized query.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2005 Microsoft Corporation. All rights reserved.
 
Back
Top