# Detect Error from Prepared Statement

**URL:** <https://forum.xojo.com/t/detect-error-from-prepared-statement/34985>\
**Category:** Databases\
**Created:** [December 15, 2016, 8:33am UTC](https://forum.xojo.com/t/detect-error-from-prepared-statement/34985 "2016-12-15T08:33:48Z")\
**Posts on this page:** 16\
**Page:** 1

<div class="post-metadata">

**Author:** ![LAM\_YUK\_HONG](https://forum.xojo.com/user_avatar/forum.xojo.com/lam_yuk_hong/32/529_2.png) [@LAM\_YUK\_HONG](https://forum.xojo.com/u/LAM_YUK_HONG)\
**Post date:** [December 15, 2016, 8:33am UTC](https://forum.xojo.com/t/detect-error-from-prepared-statement/34985/1 "2016-12-15T08:33:48Z")

</div>

Hi All,

Here is my code, self is a connected database , Loc\_Sql is a sql statement passed into the module

```auto
  Dim rs as RecordSet
  Dim ps as MSSQLServerPreparedStatement
  
  ps = self.Prepare(Loc_Sql)
  If self.Error Then
    msgbox(loc_sql)
    MsgBox(self.ErrorMessage)
  End If

   ps.BindType(0, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
   ps.bind(0, "Test")

  rs = ps.sqlselect
  If self.Error Then
    msgbox(loc_sql)
    MsgBox(self.ErrorMessage)
  End If
```

I intentionally passed in an invalid sql statement, however no error is raised. Maybe something wrong with my syntax?

---

<div class="post-metadata">

**Author:** ![Markus\_Winter](https://forum.xojo.com/user_avatar/forum.xojo.com/markus_winter/32/144_2.png) [@Markus\_Winter](https://forum.xojo.com/u/Markus_Winter)\
**Post date:** [December 15, 2016, 10:35am UTC](https://forum.xojo.com/t/detect-error-from-prepared-statement/34985/2 "2016-12-15T10:35:23Z")

</div>

Self is a protected keyword and is the parent window. Try me instead?

---

<div class="post-metadata">

**Author:** ![Greg\_O\_Lone](https://forum.xojo.com/user_avatar/forum.xojo.com/greg_o_lone/32/49_2.png) [@Greg\_O\_Lone](https://forum.xojo.com/u/Greg_O_Lone)\
**Post date:** [December 15, 2016, 11:07am UTC](https://forum.xojo.com/t/detect-error-from-prepared-statement/34985/3 "2016-12-15T11:07:25Z")

</div>

> [@303557:@Markus Winter](#):
>
> Self is a protected keyword and is the parent window. Try me instead?

Agreed. Don’t use Self for your own property names. It’s magic in all sorts of ways in a desktop app and even more so in a web app.

---

<div class="post-metadata">

**Author:** ![LAM\_YUK\_HONG](https://forum.xojo.com/user_avatar/forum.xojo.com/lam_yuk_hong/32/529_2.png) [@LAM\_YUK\_HONG](https://forum.xojo.com/u/LAM_YUK_HONG)\
**Post date:** [December 15, 2016, 11:17am UTC](https://forum.xojo.com/t/detect-error-from-prepared-statement/34985/4 "2016-12-15T11:17:48Z")

</div>

Thanks. But I tried to use the instance name to replace self, still no error raised. ☹

---

<div class="post-metadata">

**Author:** ![Greg\_O\_Lone](https://forum.xojo.com/user_avatar/forum.xojo.com/greg_o_lone/32/49_2.png) [@Greg\_O\_Lone](https://forum.xojo.com/u/Greg_O_Lone)\
**Post date:** [December 15, 2016, 11:22am UTC](https://forum.xojo.com/t/detect-error-from-prepared-statement/34985/5 "2016-12-15T11:22:07Z")

</div>

I would still check the ErrorMessage property. Just in case it’s some sort of warning that doesn’t warrant setting Error to True.

---

<div class="post-metadata">

**Author:** ![LAM\_YUK\_HONG](https://forum.xojo.com/user_avatar/forum.xojo.com/lam_yuk_hong/32/529_2.png) [@LAM\_YUK\_HONG](https://forum.xojo.com/u/LAM_YUK_HONG)\
**Post date:** [December 15, 2016, 11:24am UTC](https://forum.xojo.com/t/detect-error-from-prepared-statement/34985/6 "2016-12-15T11:24:27Z")

</div>

Thanks Greg. My testing statement is a totally invalid statement, I think the db should capture this error.

---

<div class="post-metadata">

**Author:** ![Greg\_O\_Lone](https://forum.xojo.com/user_avatar/forum.xojo.com/greg_o_lone/32/49_2.png) [@Greg\_O\_Lone](https://forum.xojo.com/u/Greg_O_Lone)\
**Post date:** [December 15, 2016, 11:26am UTC](https://forum.xojo.com/t/detect-error-from-prepared-statement/34985/7 "2016-12-15T11:26:41Z")

</div>

Have you stepped through this code in the debugger to see if it makes it all the way through? I ask because the prepare statement throws exceptions and does not set error codes.

---

<div class="post-metadata">

**Author:** ![Greg\_O\_Lone](https://forum.xojo.com/user_avatar/forum.xojo.com/greg_o_lone/32/49_2.png) [@Greg\_O\_Lone](https://forum.xojo.com/u/Greg_O_Lone)\
**Post date:** [December 15, 2016, 11:30am UTC](https://forum.xojo.com/t/detect-error-from-prepared-statement/34985/8 "2016-12-15T11:30:16Z")

</div>

In the above sample, could you show the sql you are passing?

---

<div class="post-metadata">

**Author:** ![LAM\_YUK\_HONG](https://forum.xojo.com/user_avatar/forum.xojo.com/lam_yuk_hong/32/529_2.png) [@LAM\_YUK\_HONG](https://forum.xojo.com/u/LAM_YUK_HONG)\
**Post date:** [December 15, 2016, 1:48pm UTC](https://forum.xojo.com/t/detect-error-from-prepared-statement/34985/9 "2016-12-15T13:48:34Z")

</div>

The SQL statement is simple: SELECT GUID, Status FROM SystemUser WHERE UserCode=?

and then I pass this SQL together with an 2 dimensional array representing values and data types to the database class. In the above example, putting the “Test” string is just for simplicity, actually I am reading the passed array for binding. Then I return a recordset back

If the record exists, I can read the value in the calling window by rs.Field(“GUID”).integervalue. That means, the code works. Just if I try to change the statement to like: SELECT GUIDs, Stats, FROM SystemUser WHERE UserCode=?, no error is posted

---

<div class="post-metadata">

**Author:** ![Wayne\_Golding](https://forum.xojo.com/user_avatar/forum.xojo.com/wayne_golding/32/165_2.png) [@Wayne\_Golding](https://forum.xojo.com/u/Wayne_Golding)\
**Post date:** [December 15, 2016, 6:45pm UTC](https://forum.xojo.com/t/detect-error-from-prepared-statement/34985/10 "2016-12-15T18:45:30Z")

</div>

I have never seen an error creating a prepared statement on any database engine in Xojo, it is not until you sqlexecute or sqlselect that you’ll get your error.

---

<div class="post-metadata">

**Author:** ![LAM\_YUK\_HONG](https://forum.xojo.com/user_avatar/forum.xojo.com/lam_yuk_hong/32/529_2.png) [@LAM\_YUK\_HONG](https://forum.xojo.com/u/LAM_YUK_HONG)\
**Post date:** [December 15, 2016, 11:12pm UTC](https://forum.xojo.com/t/detect-error-from-prepared-statement/34985/11 "2016-12-15T23:12:09Z")

</div>

Yes I did:

```auto
rs = ps.sqlselect
  If self.Error Then
    msgbox(loc_sql)
    MsgBox(self.ErrorMessage)
  End If
```

Just because I didn’t get error from here, so I try in somewhere else

---

<div class="post-metadata">

**Author:** ![Maximilian\_Tyrtania](https://forum.xojo.com/user_avatar/forum.xojo.com/maximilian_tyrtania/32/294_2.png) [@Maximilian\_Tyrtania](https://forum.xojo.com/u/Maximilian_Tyrtania)\
**Post date:** [December 16, 2016, 9:46am UTC](https://forum.xojo.com/t/detect-error-from-prepared-statement/34985/12 "2016-12-16T09:46:06Z")

</div>

> [@](#):
>
> But I tried to use the instance name to replace self, still no error raised.

What instance name? Just try “me” there.

---

<div class="post-metadata">

**Author:** ![Markus\_Winter](https://forum.xojo.com/user_avatar/forum.xojo.com/markus_winter/32/144_2.png) [@Markus\_Winter](https://forum.xojo.com/u/Markus_Winter)\
**Post date:** [December 16, 2016, 10:40am UTC](https://forum.xojo.com/t/detect-error-from-prepared-statement/34985/13 "2016-12-16T10:40:56Z")

</div>

The very first answer told you not to use Self. As long as you insist on using self there is no helping you. Stop wasting everyones time.

---

<div class="post-metadata">

**Author:** ![LAM\_YUK\_HONG](https://forum.xojo.com/user_avatar/forum.xojo.com/lam_yuk_hong/32/529_2.png) [@LAM\_YUK\_HONG](https://forum.xojo.com/u/LAM_YUK_HONG)\
**Post date:** [December 17, 2016, 10:39am UTC](https://forum.xojo.com/t/detect-error-from-prepared-statement/34985/14 "2016-12-17T10:39:46Z")

</div>

Sorry guys, I was out of work today… I didn’t meant to insist to use self, just I don’t have time to test yet… Will try your suggestion to use me instead, and will post here again the result. Thanks all.

---

<div class="post-metadata">

**Author:** ![LAM\_YUK\_HONG](https://forum.xojo.com/user_avatar/forum.xojo.com/lam_yuk_hong/32/529_2.png) [@LAM\_YUK\_HONG](https://forum.xojo.com/u/LAM_YUK_HONG)\
**Post date:** [December 19, 2016, 2:37am UTC](https://forum.xojo.com/t/detect-error-from-prepared-statement/34985/15 "2016-12-19T02:37:56Z")

</div>

Okay, I have tested to replace self by me, still no error is posted, here is my complete code (it is under a class myDatabase which is a subclass of MSSQLServerDatabase):

```auto
DatabaseSelect(Loc_Sql as string, BindValues(,) as string) as RecordSet

  dim i as integer
  Dim rs as RecordSet
  Dim ps as MSSQLServerPreparedStatement
  
  ps = me.Prepare(Loc_Sql)
  If me.Error Then
    msgbox("t1: " + loc_sql)
    MsgBox(me.ErrorMessage)
  End If
  for i = 0 to UBound(BindValues)
    Select Case BindValues(i,1)
    Case "String"
      ps.BindType(i, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
    Case "Integer"
      ps.BindType(i, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_INT)
    End Select
    ps.bind(i, BindValues(i,0))
  next
  rs = ps.sqlselect
  If me.Error Then
    msgbox(loc_sql)
    MsgBox(me.ErrorMessage)
  End If
  
  return rs
```

---

<div class="post-metadata">

**Author:** ![system](https://forum.xojo.com/uploads/default/original/1X/455b4dcc0e61630e9f81940004ddffa49c432a46.png) [@system](https://forum.xojo.com/u/system)\
**Post date:** [October 29, 2020, 6:34pm UTC](https://forum.xojo.com/t/detect-error-from-prepared-statement/34985/16 "2020-10-29T18:34:58Z")

</div>