UPDATE problem

D

David Fúnez

Hi;

My problem is updating 2 fields of a record, the code #1 works ok, but the
code #2 fails.

What i want is to UPDATE 5 values.

Thanks on advance

(A)
' Table Clientes Schema
cmd.CommandText = "CREATE TABLE Clientes (" & _
"Id_cliente nchar(5) NOT NULL PRIMARY KEY, " & _
"Nombre nchar(20), " & _
"Contacto nchar(20), " & _
"Direccion nvarchar(20), " & _
"Telefono nchar(8), " & _
"Activo bit)"

(B)
' Here i pass the textboxes values
Dim cId, cNom, cCont, cTel, cDir As String
Dim cAct As Boolean

cId = Me.txtIdCte.Text
cNom = Me.txtNombre.Text
cCont = Me.txtContacto.Text
cTel = Me.txtTelefono.Text
cDir = Me.txtDireccion.Text
cAct = Me.txtActivo.Text

#1
' This Update code works Ok with one field
cmd.CommandText = _
"UPDATE clientes SET nombre=" & cNom & " WHERE id_cliente=" & cId

#2
' This code doesn't work Updating 2 fields
cmd.CommandText = _
"UPDATE clientes SET nombre=" & cNom & _
", contacto=" & cCont & _
" WHERE id_cliente=" & cId
 
S

Sergey Bogdanov

The second code snippet does not work because you are trying to set
NCHAR field without quotes. Instead of modifing your SQL statement I
would recomend you to use SQL command with parameters [1]:

Dim c As New SqlCeCommand
c.CommandText = "UPDATE clientes SET nombre=?, contacto=? WHERE
id_cliente=?"
c.Parameters.Add( new SqlCeParameter ("@nombre", SqlDbType.NChar, 40,
"value"))
c.Parameters.Add( new SqlCeParameter ("@contacto", SqlDbType.Int, 40,
"value"))
....

One of benefits for this approach is that it prevents your code from SQL
injection attacks...

[1]
http://msdn.microsoft.com/library/d...lservercesqlcecommandclassparameterstopic.asp


Best regards,
Sergey Bogdanov
http://www.sergeybogdanov.com
 
D

David Fúnez

could you show me the right way, as you can see i'm new with SQL CE.

Thanks on advance
 
D

David Funez

Sergey;

Thanks a lot, but this code just display the next message [@nombre] and
doesn't update:

cmd.CommandText = _
"UPDATE clientes SET nombre=?, contacto=? WHERE id_cliente=?"

cmd.Parameters.Add(New SqlCeParameter("@nombre", SqlDbType.NChar, 30,
"value"))
cmd.Parameters.Add(New SqlCeParameter("@contacto", SqlDbType.NChar, 30,
"value"))
cmd.Parameters.Add(New SqlCeParameter("@id_cliente", SqlDbType.NChar, 5,
"value"))

cmd.Prepare()
cmd.ExecuteNonQuery(

messagebox.show("record updated") <=== This message is not displayed

?am i doing something wrong?

Thanks again.

--
David Funez
Tegucigalpa, Honduras


Sergey Bogdanov said:
The second code snippet does not work because you are trying to set NCHAR
field without quotes. Instead of modifing your SQL statement I would
recomend you to use SQL command with parameters [1]:

Dim c As New SqlCeCommand
c.CommandText = "UPDATE clientes SET nombre=?, contacto=? WHERE
id_cliente=?"
c.Parameters.Add( new SqlCeParameter ("@nombre", SqlDbType.NChar, 40,
"value"))
c.Parameters.Add( new SqlCeParameter ("@contacto", SqlDbType.Int, 40,
"value"))
...

One of benefits for this approach is that it prevents your code from SQL
injection attacks...

[1]
http://msdn.microsoft.com/library/d...lservercesqlcecommandclassparameterstopic.asp


Best regards,
Sergey Bogdanov
http://www.sergeybogdanov.com

Hi;

My problem is updating 2 fields of a record, the code #1 works ok, but
the code #2 fails.

What i want is to UPDATE 5 values.

Thanks on advance

(A)
' Table Clientes Schema
cmd.CommandText = "CREATE TABLE Clientes (" & _
"Id_cliente nchar(5) NOT NULL PRIMARY KEY, " & _
"Nombre nchar(20), " & _
"Contacto nchar(20), " & _
"Direccion nvarchar(20), " & _
"Telefono nchar(8), " & _
"Activo bit)"

(B)
' Here i pass the textboxes values
Dim cId, cNom, cCont, cTel, cDir As String
Dim cAct As Boolean

cId = Me.txtIdCte.Text
cNom = Me.txtNombre.Text
cCont = Me.txtContacto.Text
cTel = Me.txtTelefono.Text
cDir = Me.txtDireccion.Text
cAct = Me.txtActivo.Text

#1
' This Update code works Ok with one field
cmd.CommandText = _
"UPDATE clientes SET nombre=" & cNom & " WHERE id_cliente=" & cId

#2
' This code doesn't work Updating 2 fields
cmd.CommandText = _
"UPDATE clientes SET nombre=" & cNom & _
", contacto=" & cCont & _
" WHERE id_cliente=" & cId
Click to expand...
Click to expand...
 
D

David Funez

Sergey;

It already runs in Debug Mode when display a messagebox sayig [@nombre], it
doesn't display any exception.

Thanks

--
David Funez
Tegucigalpa, Honduras


Sergey Bogdanov said:
What exception do you have? Try to run it in debug mode and see what is
going on there.

Best regards,
Sergey Bogdanov
http://www.sergeybogdanov.com


David said:
Sergey;

Thanks a lot, but this code just display the next message [@nombre] and
doesn't update:

cmd.CommandText = _
"UPDATE clientes SET nombre=?, contacto=? WHERE id_cliente=?"

cmd.Parameters.Add(New SqlCeParameter("@nombre", SqlDbType.NChar, 30,
"value"))
cmd.Parameters.Add(New SqlCeParameter("@contacto", SqlDbType.NChar, 30,
"value"))
cmd.Parameters.Add(New SqlCeParameter("@id_cliente", SqlDbType.NChar, 5,
"value"))

cmd.Prepare()
cmd.ExecuteNonQuery(

messagebox.show("record updated") <=== This message is not displayed

?am i doing something wrong?

Thanks again.
Click to expand...
Click to expand...
 
S

Sergey Bogdanov

Um, you said in your previous post that MessageBox with "record updated"
was not shown. Thus I suggested you to run in debug (press F5 in VS) and
step through method to see why it was not shown maybe there is another
function that shows MesssageBox with "[@nombre]"...

Best regards,
Sergey Bogdanov
http://www.sergeybogdanov.com
 
D

David Funez

Sergey;

Problem solved:

With this code it works ok

cmd.Parameters.Add(New SqlCeParameter("@nombre", cNom))
cmd.Parameters.Add(New SqlCeParameter("@contacto", cCont))
cmd.Parameters.Add(New SqlCeParameter("@id_cliente", cId))

Thanks a lot.


--
David Funez
Tegucigalpa, Honduras


Sergey Bogdanov said:
Um, you said in your previous post that MessageBox with "record updated"
was not shown. Thus I suggested you to run in debug (press F5 in VS) and
step through method to see why it was not shown maybe there is another
function that shows MesssageBox with "[@nombre]"...

Best regards,
Sergey Bogdanov
http://www.sergeybogdanov.com


David said:
Sergey;

It already runs in Debug Mode when display a messagebox sayig [@nombre],
it doesn't display any exception.

Thanks
Click to expand...
Click to expand...
 
Top