Hi to all

Please could some one help ?

I have the 425b2 dbisam odbc std version using Vb6

I can retrieve data from the database but have a problem updating or inserting a new record

---------------------------------------------------------------------------------


Private RS As Recordset
Private DB As Database
Private WS As Workspace


Public Sub UpdateNote(Symbol As String)

Caption = "User notes - " & Symbol

Set WS = CreateWorkspace("", "", "", dbUseODBC)
Set DB = WS.OpenDatabase("Database", False, True, "ODBC;dsn=Smart
Charts;TablePassword1=Pass;")
Set RS = DB.OpenRecordset("SELECT * FROM UserNotes WHERE sName = '" & Symbol & "'",
dbOpenDynaset, dbExecDirect, dbOptimisticValue)

'This works perfect and I add the data to a text box
If RS.RecordCount <> 0 Then
  txtNotes.Text = RS("sNote")
End If

'This below does nada nothing at all

With RS
  .Edit
  .Fields(0) = "New"
  .Fields(1) = "Record"
  .Fields(2) = "BLA BLA BLA"
  .Update
End With
 
End Sub


Ta

The RS RecordSet is a "SELECT" statement.
To change the data in the table you will need to use either an "INSERT" or and "UPDATE" command.

As this code is in Visual Basic, something that I know nothing about I cannot give you an example.
If you search the internet you should find plenty of examples of how to change data in VB using ODBC drivers

Chris Holland


Deneys wrote:
> Hi to all
>
> Please could some one help ?
>
> I have the 425b2 dbisam odbc std version using Vb6
>
> I can retrieve data from the database but have a problem updating or inserting a new record
>
> ---------------------------------------------------------------------------------
>
>
> Private RS As Recordset
> Private DB As Database
> Private WS As Workspace
>
>
> Public Sub UpdateNote(Symbol As String)
>
>  Caption = "User notes - " & Symbol
>  
>  Set WS = CreateWorkspace("", "", "", dbUseODBC)
>  Set DB = WS.OpenDatabase("Database", False, True, "ODBC;dsn=Smart
> Charts;TablePassword1=Pass;")
>  Set RS = DB.OpenRecordset("SELECT * FROM UserNotes WHERE sName = '" & Symbol & "'",
> dbOpenDynaset, dbExecDirect, dbOptimisticValue)
>
>  'This works perfect and I add the data to a text box
>  If RS.RecordCount <> 0 Then
>    txtNotes.Text = RS("sNote")
>  End If
>  
>  'This below does nada nothing at all
>
>  With RS
>    .Edit
>    .Fields(0) = "New"
>    .Fields(1) = "Record"
>    .Fields(2) = "BLA BLA BLA"
>    .Update
>  End With
>   
> End Sub
>
>
> Ta
>

Hi Chris Holland

Thanks for your input I can now insert a new record and update that record as well, using the
INSERT TO and UPDATE SQL statements this all works fine But.....


I'm trying to update a memo field and if the input has a new line break for example it
is a users notes then I get an error





--------------------------------------------------------------------------

Chris Holland <mail@chrisholland.me.uk> wrote:

The RS RecordSet is a "SELECT" statement.
To change the data in the table you will need to use either an "INSERT" or and "UPDATE"
command.

As this code is in Visual Basic, something that I know nothing about I cannot give you an
example.
If you search the internet you should find plenty of examples of how to change data in VB
using ODBC drivers

Chris Holland


Deneys wrote:
> Hi to all
>
> Please could some one help ?
>
> I have the 425b2 dbisam odbc std version using Vb6
>
> I can retrieve data from the database but have a problem updating or inserting a new record
>
> ---------------------------------------------------------------------------------
>
>
> Private RS As Recordset
> Private DB As Database
> Private WS As Workspace
>
>
> Public Sub UpdateNote(Symbol As String)
>
>  Caption = "User notes - " & Symbol
>  
>  Set WS = CreateWorkspace("", "", "", dbUseODBC)
>  Set DB = WS.OpenDatabase("Database", False, True, "ODBC;dsn=Smart
> Charts;TablePassword1=Pass;")
>  Set RS = DB.OpenRecordset("SELECT * FROM UserNotes WHERE sName = '" & Symbol & "'",
> dbOpenDynaset, dbExecDirect, dbOptimisticValue)
>
>  'This works perfect and I add the data to a text box
>  If RS.RecordCount <> 0 Then
>    txtNotes.Text = RS("sNote")
>  End If
>  
>  'This below does nada nothing at all
>
>  With RS
>    .Edit
>    .Fields(0) = "New"
>    .Fields(1) = "Record"
>    .Fields(2) = "BLA BLA BLA"
>    .Update
>  End With
>   
> End Sub
>
>
> Ta
>

Hi Deneys,

I would recommend that you do the UPDATE and INSERT using parameters:

Here is an example in C#

MyCommand.SQL = "INSERT INTO MyTable (Title,Group,Description) VALUES (?,?,?)";

MyCommand.Parameters.Clear();
MyCommand.Parameters.Add("Title", OdbcType.VarChar, 50); // 50 char string length
MyCommand.Parameters.Add("Description", OdbcType.Text);   // Memo field

MyCommand.Parameters["Title"].Value = "MyTitle";
MyCommand.Parameters["Description"].Value = Description.Text;   // Data from memo edit

MyCommand.ExecuteNonQuery();

Hope this helps

Chris Holland



Deneys wrote:
> Hi Chris Holland
>
> Thanks for your input I can now insert a new record and update that record as well, using the
> INSERT TO and UPDATE SQL statements this all works fine But.....
>
>
> I'm trying to update a memo field and if the input has a new line break for example it
> is a users notes then I get an error
>
>
>
>
>
> --------------------------------------------------------------------------
>
> Chris Holland <mail@chrisholland.me.uk> wrote:
>
> The RS RecordSet is a "SELECT" statement.
> To change the data in the table you will need to use either an "INSERT" or and "UPDATE"
> command.
>
> As this code is in Visual Basic, something that I know nothing about I cannot give you an
> example.
> If you search the internet you should find plenty of examples of how to change data in VB
> using ODBC drivers
>
> Chris Holland
>
>
> Deneys wrote:
>> Hi to all
>>
>> Please could some one help ?
>>
>> I have the 425b2 dbisam odbc std version using Vb6
>>
>> I can retrieve data from the database but have a problem updating or inserting a new record
>>
>> ---------------------------------------------------------------------------------
>>
>>
>> Private RS As Recordset
>> Private DB As Database
>> Private WS As Workspace
>>
>>
>> Public Sub UpdateNote(Symbol As String)
>>
>>  Caption = "User notes - " & Symbol
>>  
>>  Set WS = CreateWorkspace("", "", "", dbUseODBC)
>>  Set DB = WS.OpenDatabase("Database", False, True, "ODBC;dsn=Smart
>> Charts;TablePassword1=Pass;")
>>  Set RS = DB.OpenRecordset("SELECT * FROM UserNotes WHERE sName = '" & Symbol & "'",
>> dbOpenDynaset, dbExecDirect, dbOptimisticValue)
>>
>>  'This works perfect and I add the data to a text box
>>  If RS.RecordCount <> 0 Then
>>    txtNotes.Text = RS("sNote")
>>  End If
>>  
>>  'This below does nada nothing at all
>>
>>  With RS
>>    .Edit
>>    .Fields(0) = "New"
>>    .Fields(1) = "Record"
>>    .Fields(2) = "BLA BLA BLA"
>>    .Update
>>  End With
>>   
>> End Sub
>>
>>
>> Ta
>>
>

Deneys,

<< I'm trying to update a memo field and if the input has a new line break
for example it is a users notes then I get an error >>

If you are trying to input text that contains CRLF pairs, then you need to
change them from this:

'My Text with the
carriage return and line feed pair'

to this:

'My Text with the '+#13+#10+' carriage return and line feed pair'

in order to have the SQL statements execute properly.

--
Tim Young
Elevate Software
www.elevatesoft.com

Deneys,

<< I can retrieve data from the database but have a problem updating or
inserting a new record >>

Use the dbOpenDynamic type instead:

Set RS = DB.OpenRecordset("SELECT * FROM UserNotes WHERE sName = '" &
Symbol & "'",
>>>> dbOpenDynamic <<<<, dbExecDirect, dbOptimisticValue)

What is happening is that you are updating a static result set, which means
that the updates are not propgated back to the original dataset(s) that make
up the query.

--
Tim Young
Elevate Software
www.elevatesoft.com