trouble updating ADODB in VB 6.0..

[LAbluesman]

ok, this in "interesting". I'm trying to do a simple update (ADODB Recordset) in VB6.0. whenever I call the update property, I get an error (see below). now, it's interesting because there are updates in other forms in the project that work just fine, and I'm following those working procedures as close as I can, but, it's causing an error here (lil' bastard). the code is below. please take a look at it and reply with any ideas or solutions you may have. thanks for your time and help.


'the code below is where the problem is occuring..
'where DE_brcs.rsde_cmm_test is the RECORDSET OBJECT being used.
'-----------------------------------------------------------------------------------------------------------
'In the function that executes the SQL..

DE_brcs.rsde_cmm_test.CursorType = adOpenKeyset 'These 2 lines are here to keep
DE_brcs.rsde_cmm_test.LockType = adLockOptimistic 'VB happy for updating purposes.
DE_brcs.rsde_cmm_test.Open (SQLstring)

'In the function that executes the ADODB update..

str_message = "You have selected the following updates: "
If txt_actlast.Text <> DE_brcs.rsde_cmm_test!actlast Then
str_message = str_message + vbCr + &quot;Last Name: &quot; + DE_brcs.rsde_cmm_test!actlast + _
&quot; >> &quot; & txt_actlast.Text
DE_brcs.rsde_cmm_test!actlast = txt_actlast.Text
End If

If MsgBox(str_message, vbOKCancel + vbQuestion, &quot;Updating fields&quot = vbOK Then
DE_brcs.rsde_cmm_test.Update <<--------------- Error occurs on this line.
Else
adodb_brcsClone.CancelUpdate
End If
'-------------------------------------------------------------------------------------------------------------
'Below is the error message that I get..

Run-time error '2147217913 (80040e07)'

[~some stuff about the ODBC driver~] Dynamic Parameter #53 type missmatch.

 

[vbkris]

can i have the sql?

Known is handfull, Unknown is worldfull

 

[LAbluesman]

Here's the SQL:

&quot;Select actlast, actfirst, actmid, actsoc,..<there are about 40 fields>..prtotph from actmem where actsoc = '<someSS#>' and actfirst = '<someFIRST>' and actlast = '<someLAST>'&quot;

For the actsoc, actfirst, actlast that come after the WHERE, the SQL must have at least one of these 3, or up to all three. It depends on which info in provided by the user (I'll throw in this code at the bottom of this post, so you can see what I mean, and use it if you have need to).

however, I'm skeptical that the SQL is causing the problem, since the correct info ~DOES~ come up, and I can view it and move to previous and next records. Again, the problem comes up when I try to UPDATE a record.


-----------------------------------------------------------
here's the code for creating the SQL that I promised above.

' ------ Creating the SQL connection string
  If txt_actsoc.Text <> &quot;&quot; Then
  ' ------ If ss# field not empty, create sql string w/ it.
    str_selectFind = str_select + &quot; where &quot; + str_actsoc + &quot;'&quot; + txt_actsoc.Text + &quot;'&quot;
  End If
  If txt_actfirst.Text <> &quot;&quot; Then  'If first name field not empty,...
    If txt_actsoc.Text <> &quot;&quot; Then
    ' ------ ..and there was something in ss#, add first name to the sql string.
      str_selectFind = str_selectFind + &quot; and &quot; + str_actfirst + &quot;'&quot; + txt_actfirst.Text + &quot;'&quot;
    Else
    ' ------ ..else, create sql string w/ first name only.
      str_selectFind = str_select + &quot; where &quot; + str_actfirst + &quot;'&quot; + txt_actfirst.Text + &quot;'&quot;
    End If
  End If
  If txt_actlast.Text <> &quot;&quot; Then   'If last name field not empty...
    If txt_actsoc.Text <> &quot;&quot; Or txt_actfirst.Text <> &quot;&quot; Then
    ' ------ ...and either ss# or first name field were not empty,
    '        add last name to the sql string.
      str_selectFind = str_selectFind + &quot; and &quot; + str_actlast + &quot;'&quot; + txt_actlast.Text + &quot;'&quot;
    Else
    ' ------ ..else create sql string w/ last name only.
      str_selectFind = str_select + &quot; where &quot; + str_actlast + &quot;'&quot; + txt_actlast.Text + &quot;'&quot;
    End If
  End If
' ------ END CREATION OF SQL CONNECTION STRING

 

[vbkris]

try
debug.print sql

run the queery in ur database, that should point out the error.

Known is handfull, Unknown is worldfull

 

[LAbluesman]

vbkris, could you be a lil' more specific in your suggestion. you say to use

&quot;debug.print sql&quot;[\code].  I am not familiar with this command, so, I'm not sure where (or around what code) this should be called.  my guess would be to call it right after I call [code]&quot;DE_brcs.rsde_cmm_test.Open (<SQL_string>)&quot;[\code],.. is this correct, or is it meant to be called somewhere else?

thanks!

 

[johnwm]

One of the common problems with creating SQL queries as a string is in the use of spaces and delimiters.

Debug.Print sql statement placed immediately after you have fully created the string will print it to the Immediate window. You can often see from there what the problem is, otherwise use copy& paste to run the query directly in your database.

If you are still having problems, you can always post the sql string straight from your code to the forum.


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[vbkris]

BINGO!!!

Known is handfull, Unknown is worldfull

 

[LAbluesman]

haven't heard from me in a while, huh..

well, the problem is still a problem, but it has been narrowed down. It seems to have nothing to do with the UPDATE statement itself.

I've found that comes from AMOUNT data types in the database (This is comparable to the DOUBLE datatype in VB6). This is a UNIFY Database, btw. What I've found is that if I so much as include these AMOUNT datatypes in the SQL, then I get the type mismatch error mentioned above. Again, all I'm doing with them at the moment is displaying them to the screen,.. I'm not updating their values at all. When I remove the AMOUNT datatypes, I can UPDATE, ADD, and DELETE with no worries.

In case you'd think seeing the code that displays the values is important, here it is:

  txt_actsoc.Text = adodb_brcsClone!actsoc
  txt_actmid.Text = IIf(IsNull(adodb_brcsClone!actmid), "~", adodb_brcsClone!actmid)

The above code works, it's just to give you an idea.

  txt_ytdwid.Text = IIf(IsNull(adodb_brcsClone!ytdwid), "~", adodb_brcsClone!ytdwid)

The above is example of code that displays the AMOUNT data types.

Again, thanks for your ideas/help in advance

 

[BobRodes]

Ok, here are some things to look at. My hypothesis is that the text box can't take the result of the amount field. First, try selecting a record with all null values in the amount type fields, and see if that works ok giving you the ~ thing. If it does, get a record that has something in an amount field, and put a breakpoint right before you try to plug its value into a text box. See if you can ? the value in the debug window. That should provide some clues. A possible fix is to use something like cDbl or cCur before assigning to a textbox. Also, this can't be a rare problem for people using Unity; see if there is doc on it in your Unity OLE DB provider doc.

Good luck,

Bob