update tables using ADO 
Author Message
 update tables using ADO

Hi

how to update a table using ado.
I wish to update several fields at once.
Would like a good working example if possible.
I know how to update 1 field but not more than 1.

This works for one field:

' Set the variables
    Dim adoConn As New ADODB.Connection
    Dim adoCmd As New ADODB.Command
    Dim strConn As String, strSQL As String

' Open a Connection object
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
    strConn = strConn & "c:\mydb.mdb"
    adoConn.ConnectionString = strConn
    adoConn.Open

' Define a query string

    strSQL = "UPDATE calls SET [closedetails]='"
    strSQL = strSQL & txtCloseDetails.Text
    strSQL = strSQL & "' WHERE [callid]=" & CInt(txtSearchCallID.Text)

' Set up the Command object
    adoCmd.CommandText = strSQL
    adoCmd.CommandType = adCmdText
    adoCmd.ActiveConnection = adoConn
    adoCmd.Execute

' Tidy up
    Set adoCmd = Nothing
    adoConn.Close
    Set adoConn = Nothing

How can I add to this to also update the database field: _dateclosed_
from the text control _txtdateclosed.text_

thanks

--
Paj



Wed, 05 Jan 2005 03:22:56 GMT  
 update tables using ADO
    strSQL = "UPDATE calls SET [closedetails]='"
    strSQL = strSQL & txtCloseDetails.Text
    strSQL = strSQL & "', [dateclosed]=#"
    strSQL = strSQL & txtdateclosed.text
    strSQL = strSQL & "# WHERE [callid]=" & CInt(txtSearchCallID.Text)


Quote:
> Hi

> how to update a table using ado.
> I wish to update several fields at once.
> Would like a good working example if possible.
> I know how to update 1 field but not more than 1.

> This works for one field:

> ' Set the variables
>     Dim adoConn As New ADODB.Connection
>     Dim adoCmd As New ADODB.Command
>     Dim strConn As String, strSQL As String

> ' Open a Connection object
>     strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
>     strConn = strConn & "c:\mydb.mdb"
>     adoConn.ConnectionString = strConn
>     adoConn.Open

> ' Define a query string

>     strSQL = "UPDATE calls SET [closedetails]='"
>     strSQL = strSQL & txtCloseDetails.Text
>     strSQL = strSQL & "' WHERE [callid]=" & CInt(txtSearchCallID.Text)

> ' Set up the Command object
>     adoCmd.CommandText = strSQL
>     adoCmd.CommandType = adCmdText
>     adoCmd.ActiveConnection = adoConn
>     adoCmd.Execute

> ' Tidy up
>     Set adoCmd = Nothing
>     adoConn.Close
>     Set adoConn = Nothing

> How can I add to this to also update the database field: _dateclosed_
> from the text control _txtdateclosed.text_

> thanks

> --
> Paj



Wed, 05 Jan 2005 03:32:15 GMT  
 update tables using ADO
Hi Leo

Thanks for this. I got there in the end.
One last question.
Does the # indicate it is numeric?

thx.

--
Paj

On Fri, 19 Jul 2002 15:32:15 -0400, "Leo Walter"

Quote:

>    strSQL = "UPDATE calls SET [closedetails]='"
>    strSQL = strSQL & txtCloseDetails.Text
>    strSQL = strSQL & "', [dateclosed]=#"
>    strSQL = strSQL & txtdateclosed.text
>    strSQL = strSQL & "# WHERE [callid]=" & CInt(txtSearchCallID.Text)



>> Hi

>> how to update a table using ado.
>> I wish to update several fields at once.
>> Would like a good working example if possible.
>> I know how to update 1 field but not more than 1.

>> This works for one field:

>> ' Set the variables
>>     Dim adoConn As New ADODB.Connection
>>     Dim adoCmd As New ADODB.Command
>>     Dim strConn As String, strSQL As String

>> ' Open a Connection object
>>     strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
>>     strConn = strConn & "c:\mydb.mdb"
>>     adoConn.ConnectionString = strConn
>>     adoConn.Open

>> ' Define a query string

>>     strSQL = "UPDATE calls SET [closedetails]='"
>>     strSQL = strSQL & txtCloseDetails.Text
>>     strSQL = strSQL & "' WHERE [callid]=" & CInt(txtSearchCallID.Text)

>> ' Set up the Command object
>>     adoCmd.CommandText = strSQL
>>     adoCmd.CommandType = adCmdText
>>     adoCmd.ActiveConnection = adoConn
>>     adoCmd.Execute

>> ' Tidy up
>>     Set adoCmd = Nothing
>>     adoConn.Close
>>     Set adoConn = Nothing

>> How can I add to this to also update the database field: _dateclosed_
>> from the text control _txtdateclosed.text_

>> thanks

>> --
>> Paj



Wed, 05 Jan 2005 09:24:07 GMT  
 update tables using ADO
DAO uses the # delimiter to signify a date/time field...


Quote:
> Hi Leo

> Thanks for this. I got there in the end.
> One last question.
> Does the # indicate it is numeric?

> thx.

> --
> Paj

> On Fri, 19 Jul 2002 15:32:15 -0400, "Leo Walter"

> >    strSQL = "UPDATE calls SET [closedetails]='"
> >    strSQL = strSQL & txtCloseDetails.Text
> >    strSQL = strSQL & "', [dateclosed]=#"
> >    strSQL = strSQL & txtdateclosed.text
> >    strSQL = strSQL & "# WHERE [callid]=" & CInt(txtSearchCallID.Text)



> >> Hi

> >> how to update a table using ado.
> >> I wish to update several fields at once.
> >> Would like a good working example if possible.
> >> I know how to update 1 field but not more than 1.

> >> This works for one field:

> >> ' Set the variables
> >>     Dim adoConn As New ADODB.Connection
> >>     Dim adoCmd As New ADODB.Command
> >>     Dim strConn As String, strSQL As String

> >> ' Open a Connection object
> >>     strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
> >>     strConn = strConn & "c:\mydb.mdb"
> >>     adoConn.ConnectionString = strConn
> >>     adoConn.Open

> >> ' Define a query string

> >>     strSQL = "UPDATE calls SET [closedetails]='"
> >>     strSQL = strSQL & txtCloseDetails.Text
> >>     strSQL = strSQL & "' WHERE [callid]=" & CInt(txtSearchCallID.Text)

> >> ' Set up the Command object
> >>     adoCmd.CommandText = strSQL
> >>     adoCmd.CommandType = adCmdText
> >>     adoCmd.ActiveConnection = adoConn
> >>     adoCmd.Execute

> >> ' Tidy up
> >>     Set adoCmd = Nothing
> >>     adoConn.Close
> >>     Set adoConn = Nothing

> >> How can I add to this to also update the database field: _dateclosed_
> >> from the text control _txtdateclosed.text_

> >> thanks

> >> --
> >> Paj



Sun, 09 Jan 2005 03:36:48 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Batch update for multiple tables using single ADO Recordset (VB )

2. Batch update for multiple tables using single ADO Recordset (VB )

3. Update a table using ado

4. how to update a Numeric field in a table using VB6 ADO

5. Using ado, table is not updated

6. ADO, SQL Server Updating Recordset using 2 Tables

7. VB update to Access2000 table using ADO ?

8. Using--After Update-- to update field in table.

9. Updating Tables with SQL in VB6 using vfoxpro tables

10. How to update datetime field with NULL using ADO update

11. Controlling Updates in ADO using timestamp/update criteria

12. Using a defined Variable to Update a Table using DoCmd.RunSQL

 

 
Powered by phpBB® Forum Software