Using Stored Procedures of MSSQL

Databases
1

Hi, I’m new to Xojo (and programming in general) and I love this IDE, so I would like to know How I use stored procedures from MSSQL in xojo?

[code]Private Sub B8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles B8.Click

    Dim datos As New conexion

    datos.mant_clientes(Val(txti.Text), Val(txtd.Text), txtn.Text, txtr.Text, txtp.Text, txtm.Text, "i")
    MsgBox("Added correctly", MsgBoxStyle.Information, "INFORMACION")
    datos.llena_grid("select * from Clientes", DataGridView1)
End Sub[/code]

[code]Create PROCEDURE [dbo].[usp_mant_clientes]
@Id_cliente int,
@Id_factura int,
@Nombres varchar(40),
@Apellidos varchar(40),
@Direccion varchar(100),
@Telefono varchar(150),

@opc char(1)
as
begin
DECLARE @usuario_crea varchar(20), @fecha_crea datetime,@usuario_mod varchar(20),@fecha_mod datetime
Set @usuario_crea= SUSER_SNAME()
Set @fecha_crea= GETDATE()
Set @usuario_mod= SUSER_SNAME()
Set @fecha_mod= GETDATE()

if (@opc=‘i’)
begin
insert into Clientes values(@Id_cliente ,@Id_factura,@Nombres,@Apellidos,@Direccion,@Telefono, @usuario_crea,@fecha_crea,null,null)
end
if (@opc=‘a’)
begin
update Clientes set
Id_cliente=@Id_cliente,
Id_factura=@Id_factura,
Nombres=@Nombres,
Apellidos=@Apellidos,
Direccion=@Direccion,
Telefono=@Telefono,
usuario_mod=@usuario_mod,
fecha_mod=@fecha_mod
where Id_cliente=@Id_cliente
end

if(@opc=‘e’)
begin
delete from Clientes
where Id_cliente=@Id_cliente
end
end[/code]

[code]Public Sub mant_clientes(ByVal idcli As Integer, ByVal idfac As Integer, nombres As String, apellidos As String,
direccion As String, telefono As String, ByVal opc As String)

    conectar()
    Dim sql As String
    sql = "usp_mant_clientes '" & idcli & "', '" & idfac & "', '" & nombres & "','" & apellidos & "', '" & direccion & "', '" & telefono & "', '" & opc & "'"
    Dim cmd As New SqlCommand(sql, cn)
    cmd.ExecuteNonQuery()
    desconecta()
End Sub[/code]

I learned to do this way in my class, I don’t know if it’s good to use.
First code it’s to fill the database with a new row, it sends the values of the textfields, the code it’s from visual studio.
The second code it’s the procedure created in MSSQL, at the end if the variable it’s “i” it inserts e it eliminates(delete) and “a” updates.
The third code it’s about the sub “mant_clientes” to use with the first code.
I tried to use

dim sql as String sql= "usp_mant_alumno ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?" mDB.SQLExecute(sql)

In a method called add with parameters:

ByVal idfac As Integer  , idcli as int32, iddet as Int16,idemp as Int8, ByVal fecfac as date,obs as String, imp as Double, des as double, subt as double,iva as double, tot as double, formpa as string, opc as string

And this when I click the button add in my desktop app:

add(Val(txt1.Text),Val(txt2.Text),Val(txt3.Text),Val(txt4.Text),fecfac2,txt6.Text,CDbl(txt7.Text),CDbl(txt8.Text),Cdbl(txt9.Text),Cdbl(txt10.Text),Cdbl(txt11.Text),txt12.Text,"i")
The problem it’s that I can add with the method used in the example in xojo, I’m already connected to my database when I try to add those values, but they aren’t added, I don’t know why, maybe I’m not using stored procedures correctly? Or I can’t insert values in a table in this way even with a procedure?

2

may be miss a simple commit at the end of the transaction ?

3

Once you executed the stored procedure once in MSSQL, change CREATE PROCEDURE to ALTER PROCEDURE. I found that my calls would fail silently if I kept CREATE. There is probably a better way, but that works for me.

4

What is the error?

if mDB.error = true then
MsgBox(mDB.ErrorMessage)

end if

5

It was actually an error of syntax, the way I wanted to do was incorrect, specifically in this code:

dim sql as String sql= "usp_mant_alumno ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?" mDB.SQLExecute(sql)
It must be

sql= "usp_mant_alumo " +Str(idfac)+ " , " +Str(idcli)+  ...

And going on to the end, just converting everything to String and instead of “&” was “+”. Thanks for your help :D!