Update my ODBC Database

Databases
1

Hello, I have in my Computer SQL Express 2005. I use a ODBC Connection in Windows for see the data in Xojo.

When make a consult in the database all work fine. the connection is fine and I can update the listbox in XOJO.

Now I have a text Box and after I lost the Focus I make the connection but the system do not make any changes in my SQL Express (Using a ODBC Connection)

I try to use the SQLExecute(“BEGIN TRANSATION”) and the db.commit but still not work.

Best Regards

2

This is my Code and Works Fine

Dim SapDb as ODBCDatabase
SapDb=New ODBCDatabase

SqlStr=“Provider=SQLOLEDB;Driver={SQL Server};Server=192.168.0.31;Database=csc;Trusted_Connection=yes;”

SapDb.DataSource=SqlStr
SapDb.UserName=“sa”
SapDb.Password=“123”

3

My Connection is fine. The Problem I have is update my ODBC Record with the data of one TextBox or ListBox.

Do I need to use the ODBCPrepareStament…

Example

My TextBox Text = ‘HELLO’

My ODBC Table is TEMP
I want to change one Column of the Temp Table with the value of my textBox

Regards

4

SQLExecute(“BEGIN TRANSATION”)

…what do you expect that to do, exactly?
…are you checking for errors after issuing a statement?

In the one above, TRANSACTION is spelled incorrectly anyway.

I suspect you want to issue a statement which is more like

SQLExecute (“update TEMP set MYFIELD = '” + textbox1.text + “’”)

… but there is a lot more I could add to that , (which has already been covered many times in the sticky threads of the forums.)

5

this is my code

Dim sqlstr as String
Dim SapDb as ODBCDatabase
SapDb=New ODBCDatabase

SqlStr=“Provider=SQLOLEDB;Driver={SQL Server};Server=Compac-PC\SMP3;Database=elpolloloco;Trusted_Connection=yes;”

SapDb.DataSource=SqlStr
SapDb.UserName=“sa”
SapDb.Password=“suprsonic”
SapDb.SQLExecute(“BEGIN TRANSACTION”)
SapDb.SQLExecute (“update CorteDiario set AjusteLlevarChar = '” + PollosLlevar_txtbox.text + “’”)
SapDb.commit

6

I dont think you need the line SapDb.SQLExecute(“BEGIN TRANSACTION”)

What is the actual content of PollosLlevar_txtbox.text ?
If it contains a reserved character such as ’ or & you may have trouble.

Do you check the value of SapDb.error after you issue the statements?

7

Hello here is the conection with the ODBCDataBase .

the system do not show error and I see the value of my record set. But I can not update my SQL DataBase

Dim db As New ODBCDatabase
Dim sql As String
Dim rs As RecordSet

db.DataSource = “EplSmp”
db.UserName = “sa”
db.Password = “suprsonic”

If db.Connect Then
//proceed with database operations
db.SQLExecute(“BEGIN TRANSATION”)
sql = “SELECT * FROM CorteDiario”
rs = db.SQLSelect(sql)

if rs = Nil then
  MsgBox("NIL")
end If

rs.Edit
rs.field("AjusteLlevarChar").StringValue=trim(PollosLlevar_TxtBox.text)
rs.Update
db.SQLExecute ("UPDATE CorteDiario set AjusteLlevarChar = '" + PollosLlevar_TxtBox.text +"'")
db.Commit

Else
MsgBox("Error: " + db.ErrorMessage)
End If

8

If db.Connect Then
//proceed with database operations
db.SQLExecute(“BEGIN TRANSATION”)
sql = “SELECT * FROM CorteDiario”
rs = db.SQLSelect(sql)

if rs = Nil then
MsgBox(“NIL”)
end If

rs.Edit
rs.field(“AjusteLlevarChar”).StringValue=trim(PollosLlevar_TxtBox.text)
rs.Update
db.SQLExecute(“END TRANSATION”)

db.SQLExecute(“BEGIN TRANSATION”)
db.SQLExecute (“UPDATE CorteDiario set AjusteLlevarChar = '” + PollosLlevar_TxtBox.text +"’")
db.Commit
db.SQLExecute(“END TRANSATION”)
Else
MsgBox("Error: " + db.ErrorMessage)
End If

9

Thanks, I make the change and add db.SQLExecute(“END TRANSATION”) but still not working maybe is corrupt the database. But I can see the value of the database in one ListBox but still not update the SQLExpress 2005 with the value of my text box.

10

How do you know???
A SQL error does not cause an error in the Xojo code.
Instead, it sets the database error property.

So: Please check the error state of your database.
and TRANSATION is not a word

Try this:

db.SQLExecute ("UPDATE CorteDiario set AjusteLlevarChar = 'TEST') If db.Error Then MsgBox("DB Error: " + db.ErrorMessage) Else MsgBox("Should have updated") End If

11

Thanks this is the DB ERROR :

DB Error : [Microsoft][ODBC SQL Server Driver] Connection is busy
with results for another hstmt

12

Did you put that code after an rs.edit?

13

Yes Here is the code

Dim dbupdate As New ODBCDatabase
Dim sqlupdate As String
Dim rsupdate As RecordSet

dbupdate.DataSource = “EplSmp”
dbupdate.UserName = “sa”
dbupdate.Password = “suprsonic”

If dbupdate.Connect Then
//proceed with database operations
dbupdate.SQLExecute(“BEGIN TRANSATION”)
sqlupdate = “SELECT * FROM CorteDiario”
rsupdate = dbupdate.SQLSelect(sqlupdate)

if rsupdate = Nil then
  MsgBox("NILLL")
end If

rsupdate.Edit
rsupdate.field("AjusteLlevarChar").StringValue=trim(PollosLlevar_TxtBox.text)
rsupdate.Update
dbupdate.SQLExecute("END TRANSATION")

dbupdate.SQLExecute("BEGIN TRANSATION")
dbupdate.SQLExecute ("UPDATE CorteDiario set AjusteLlevarChar = 'TEST'")
dbupdate.Commit
dbupdate.SQLExecute("END TRANSATION")

If dbupdate.Error Then
  MsgBox("DB Error: " + dbupdate.ErrorMessage)
Else
  MsgBox("Should have updated")
End If

Else
MsgBox("Error: " + dbupdate.ErrorMessage)
End If

14

Do you think I need to use the SQLify() like the manual explain???

15

What is SQLify()?