Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM ODBC Driver » View Thread |
Messages 1 to 6 of 6 total |
Update Record in database |
Tue, Apr 24 2007 5:16 AM | Permanent Link |
Deneys | 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 |
Tue, Apr 24 2007 6:55 AM | Permanent Link |
Chris Holland SEC Solutions Ltd. Team Elevate | 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 > |
Tue, Apr 24 2007 8:22 AM | Permanent Link |
Deneys | 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 > |
Tue, Apr 24 2007 10:52 AM | Permanent Link |
Chris Holland SEC Solutions Ltd. Team Elevate | 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 >> > |
Wed, Apr 25 2007 8:23 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Apr 25 2007 8:28 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
This web page was last updated on Monday, April 29, 2024 at 05:23 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |