getting return from adding to a table

Hi,
I have a table with an auto-increment field that is
the ID. when i run the following update, is there any
function for me to retrieve this ID ?

sqlstr = "select * from policies where 0 = 1;"
Set rs = New ADODB.Recordset
rs.ActiveConnection = connMySQL
rs.Open sqlstr, connMySQL, adOpenKeyset,
adLockOptimistic
rs.AddNew
rs!clientid = newdisp.formid.Text
rs!polnumber = Text1.Text
rs!insureco = Combo1.Text
rs!poltype = Combo2.Text
rs!commcash = Text3.Text
rs!commperc = Text4.Text
rs!Description = Text6.Text
rs.Update


many thanks


__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: getting return from adding to a table

Mark;

After the update try accessing the ID in the recordset. If memory serves the ID
value of the recordset will be populated by the update.

Mike


Mark Mchugh wrote:
> Hi,
> I have a table with an auto-increment field that is
> the ID. when i run the following update, is there any
> function for me to retrieve this ID ?
>
> sqlstr = "select * from policies where 0 = 1;"
> Set rs = New ADODB.Recordset
> rs.ActiveConnection = connMySQL
> rs.Open sqlstr, connMySQL, adOpenKeyset,
> adLockOptimistic
> rs.AddNew
> rs!clientid = newdisp.formid.Text
> rs!polnumber = Text1.Text
> rs!insureco = Combo1.Text
> rs!poltype = Combo2.Text
> rs!commcash = Text3.Text
> rs!commperc = Text4.Text
> rs!Description = Text6.Text
> rs.Update
>
>
> many thanks
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>

--
Mike Hillyer, Technical Writer
Lethbridge, Alberta, Canada
MySQL AB, www.mysql.com


"The Open Source movement has become a major force across the software industry,
and MySQL is the world's most popular open source database."
--Fortune Magazine

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: getting return from adding to a table

Hi,
i can't understand which ID you want to retrieve since select * from policies
where 0 = 1 is always empty.

Mathias

Selon Mark Mchugh :

> Hi,
> I have a table with an auto-increment field that is
> the ID. when i run the following update, is there any
> function for me to retrieve this ID ?
>
> sqlstr = "select * from policies where 0 = 1;"
> Set rs = New ADODB.Recordset
> rs.ActiveConnection = connMySQL
> rs.Open sqlstr, connMySQL, adOpenKeyset,
> adLockOptimistic
> rs.AddNew
> rs!clientid = newdisp.formid.Text
> rs!polnumber = Text1.Text
> rs!insureco = Combo1.Text
> rs!poltype = Combo2.Text
> rs!commcash = Text3.Text
> rs!commperc = Text4.Text
> rs!Description = Text6.Text
> rs.Update
>
>
> many thanks
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=mfatene@free.fr
>
>



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: getting return from adding to a table

Here is some code to get the last autoinc ID generated.=20
Put a call to this function just after rs.Update.

Public Function GetLastInsertID() AS Long
Dim rs as ADODB.Recordset
Set rs =3D New ADODB.Recordset
rs.Open "SELECT LAST_INSERT_ID()", connMySQL, adOpenStatic,
adLockReadOnly, adCmdText =20
GetLastInsertID =3D CLng(rs.Fields(0).Value)
rs.Close
End Function

By the way, I would usually write like this.

I have a function RunSQL to do most of the work.

Public Function RunSQL(SQL As String) As ADODB.Recordset
Dim rs As ADODB.Recordset
Dim Cmd As String
Dim Msg As String
Dim E As ADODB.Error
=20
On Local Error GoTo RunSQL_Err
=20
Cmd =3D UCase(Left(SQL, 4))
If Cmd =3D "SELE" Or Cmd =3D "SHOW" Then
' SELECT or SHOW can return a recordset
Set rs =3D New ADODB.Recordset
rs.Open SQL, connMySQL, adOpenStatic, adLockReadOnly, adCmdText
Set RunSQL =3D rs
Else
' An action query, don't worry about recordset
connMySQL.Execute SQL, gRecordsAffected, adCmdText
End If

RunSQL_Exit:
Exit Function
=20
RunSQL_Err:
If connMySQL.Errors.Count <> 0 Then
Msg =3D "SQL Error " & CStr(Err.Number) & ": " & Err.Description
For Each E In connMySQL.Errors
Msg =3D Msg & vbCrLf & E.Description
Next E
connMySQL.Errors.Clear
Msg =3D Msg & vbCrLf & "SQL: " & SQL
MsgBox Msg, , "RunSQL"
Else
MsgBox "Error " & CStr(Err.Number) & ": " & Err.Description, ,
"RunSQL"
End If
Resume RunSQL_Exit
End Function

Then I can write your code like this, once I have established the
connection.

RunSQL "INSERT INTO policies (newdisp, polnumber, insureco, poltype,
commcash, commperc, Description) " & _
"VALUES ('" & newdisp.formid.Text & "','" & _
Text1.Text & "','" & __
Combo1.Text & "','" & _
Combo2.Text & "','" & _
Text3.Text & "','" & _
Text4.Text & "','" & _
Text6.Text & "')"
ID =3D GetLastInsertID

I usually use VBMySQLDirect
http://www.vbmysql.com/projects/vbmysqldirect/ instead of ADO too.

John Bonnett

-----Original Message-----
From: mfatene@free.fr [mailto:mfatene@free.fr]=20
Sent: Tuesday, 31 May 2005 5:18 AM
To: Mark Mchugh
Cc: mysql list
Subject: Re: getting return from adding to a table

Hi,
i can't understand which ID you want to retrieve since select * from
policies
where 0 =3D 1 is always empty.

Mathias

Selon Mark Mchugh :

> Hi,
> I have a table with an auto-increment field that is
> the ID. when i run the following update, is there any
> function for me to retrieve this ID ?
>
> sqlstr =3D "select * from policies where 0 =3D 1;"
> Set rs =3D New ADODB.Recordset
> rs.ActiveConnection =3D connMySQL
> rs.Open sqlstr, connMySQL, adOpenKeyset,
> adLockOptimistic
> rs.AddNew
> rs!clientid =3D newdisp.formid.Text
> rs!polnumber =3D Text1.Text
> rs!insureco =3D Combo1.Text
> rs!poltype =3D Combo2.Text
> rs!commcash =3D Text3.Text
> rs!commperc =3D Text4.Text
> rs!Description =3D Text6.Text
> rs.Update
>
>
> many thanks
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: =
http://lists.mysql.com/win32?unsub=3Dmfatene@free.fr
>
>



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

Re: getting return from adding to a table

--=_alternative 0053B88A85257013_=
Content-Type: text/plain; charset="US-ASCII"

mfatene@free.fr wrote on 05/30/2005 03:48:28 PM:

> Hi,
> i can't understand which ID you want to retrieve since select * from
policies
> where 0 = 1 is always empty.
>
> Mathias

It's an ADO hack (you have obviously never tried to use the .AddNew/Update
method of adding records using the ADODB.Recordset object). What that
statement does is to initialize an ADO Recordset object with no data but a
full set of FIELD definitions (names, sizes, etc). Then you can tell ADO
to create a new (blank) record (rs.AddNew), populate the "new" record with
values (the "rs!" statements that follow), and finally send the "new"
record to the database server (rs.Update). Without the Recordset object
being pre-loaded with the table definition, this whole process fails.

You can thank Mr. Gates and his band of "innovators" for coming up with
this bass-ackwards way of doing things. I have found it much easier and
reliable to compose my own INSERT statement, in text, and use the
Connection.Execute method to make it happen. One advantage to using the
"all code" method is you don't have to pre-escape your text fields and
your date-time values are almost handled correctly. The big excepttion is
the value of '0000-00-00' and its other "all zeroes" relatives. Those
values are illegal in MS scripting languages so the built-in datetime
variable types choke on them.

Does that help you understand what his snippet was doing?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine






> Selon Mark Mchugh :
>
> > Hi,
> > I have a table with an auto-increment field that is
> > the ID. when i run the following update, is there any
> > function for me to retrieve this ID ?
> >
> > sqlstr = "select * from policies where 0 = 1;"
> > Set rs = New ADODB.Recordset
> > rs.ActiveConnection = connMySQL
> > rs.Open sqlstr, connMySQL, adOpenKeyset,
> > adLockOptimistic
> > rs.AddNew
> > rs!clientid = newdisp.formid.Text
> > rs!polnumber = Text1.Text
> > rs!insureco = Combo1.Text
> > rs!poltype = Combo2.Text
> > rs!commcash = Text3.Text
> > rs!commperc = Text4.Text
> > rs!Description = Text6.Text
> > rs.Update
> >
> >
> > many thanks
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam? Yahoo! Mail has the best spam protection around
> > http://mail.yahoo.com
> >
> > --
> > MySQL Windows Mailing List
> > For list archives: http://lists.mysql.com/win32
> > To unsubscribe: http://lists.mysql.com/win32?unsub=mfatene@free.fr
> >
> >
>
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=sgreen@unimin.com
>

--=_alternative 0053B88A85257013_=--

Re: getting return from adding to a table

Thanks Mr. Gates, your band of "innovators" and Shawn

mathias
Selon SGreen@unimin.com:

> mfatene@free.fr wrote on 05/30/2005 03:48:28 PM:
>
> > Hi,
> > i can't understand which ID you want to retrieve since select * from
> policies
> > where 0 = 1 is always empty.
> >
> > Mathias
>
> It's an ADO hack (you have obviously never tried to use the .AddNew/Update
> method of adding records using the ADODB.Recordset object). What that
> statement does is to initialize an ADO Recordset object with no data but a
> full set of FIELD definitions (names, sizes, etc). Then you can tell ADO
> to create a new (blank) record (rs.AddNew), populate the "new" record with
> values (the "rs!" statements that follow), and finally send the "new"
> record to the database server (rs.Update). Without the Recordset object
> being pre-loaded with the table definition, this whole process fails.
>
> You can thank Mr. Gates and his band of "innovators" for coming up with
> this bass-ackwards way of doing things. I have found it much easier and
> reliable to compose my own INSERT statement, in text, and use the
> Connection.Execute method to make it happen. One advantage to using the
> "all code" method is you don't have to pre-escape your text fields and
> your date-time values are almost handled correctly. The big excepttion is
> the value of '0000-00-00' and its other "all zeroes" relatives. Those
> values are illegal in MS scripting languages so the built-in datetime
> variable types choke on them.
>
> Does that help you understand what his snippet was doing?
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
>
>
>
>
>
> > Selon Mark Mchugh :
> >
> > > Hi,
> > > I have a table with an auto-increment field that is
> > > the ID. when i run the following update, is there any
> > > function for me to retrieve this ID ?
> > >
> > > sqlstr = "select * from policies where 0 = 1;"
> > > Set rs = New ADODB.Recordset
> > > rs.ActiveConnection = connMySQL
> > > rs.Open sqlstr, connMySQL, adOpenKeyset,
> > > adLockOptimistic
> > > rs.AddNew
> > > rs!clientid = newdisp.formid.Text
> > > rs!polnumber = Text1.Text
> > > rs!insureco = Combo1.Text
> > > rs!poltype = Combo2.Text
> > > rs!commcash = Text3.Text
> > > rs!commperc = Text4.Text
> > > rs!Description = Text6.Text
> > > rs.Update
> > >
> > >
> > > many thanks
> > >
> > >
> > > __________________________________________________
> > > Do You Yahoo!?
> > > Tired of spam? Yahoo! Mail has the best spam protection around
> > > http://mail.yahoo.com
> > >
> > > --
> > > MySQL Windows Mailing List
> > > For list archives: http://lists.mysql.com/win32
> > > To unsubscribe: http://lists.mysql.com/win32?unsub=mfatene@free.fr
> > >
> > >
> >
> >
> >
> > --
> > MySQL Windows Mailing List
> > For list archives: http://lists.mysql.com/win32
> > To unsubscribe: http://lists.mysql.com/win32?unsub=sgreen@unimin.com
> >
>



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

config a linux from windows

dear frieds..

is there any mean for config and add databases to mysql (on linux)
from a pc windows ??
any tool.. any form ??
pls..i need your help

gracias!

amigos.. hay alguna formra de configurar el mysql (instalado en un
linux )desde una pc o server de windows???

gracias!






______________________________________________
Renovamos el Correo Yahoo!
Nuevos servicios, más seguridad
http://correo.yahoo.es


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: config a linux from windows

Yes... the mysql.exe command line client. If you want a GUI, perhaps try
using the MySQL Query Browser. Both are available from the mysql.com
site. Cheers.

Armando

JP Files wrote:
> dear frieds..
>
> is there any mean for config and add databases to mysql (on linux)
> from a pc windows ??
> any tool.. any form ??
> pls..i need your help
>
> gracias!
>
> amigos.. hay alguna formra de configurar el mysql (instalado en un
> linux )desde una pc o server de windows???
>
> gracias!
>
>
>
>
>
>
> ______________________________________________
> Renovamos el Correo Yahoo!
> Nuevos servicios, más seguridad
> http://correo.yahoo.es
>

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: config a linux from windows

MySQL is portable, allowing the user to admin and use the databases at
any server that can run the MySQL tools, provided at the site, one
thing that most people don't know is that when you download the zipped
version of MySQL for Windows, or install it with the MSI, you get the
client together, and you don't need to RUN the server to use the
client to access MySQL servers in other platform and server.

Also, the MySQL Administrator and Query Browser are windows specific
tools that allow you to administer and use the MySQL databases
anywhere.

On 7/12/05, JP Files wrote:
> dear frieds..
>=20
> is there any mean for config and add databases to mysql (on linux)
> from a pc windows ??
> any tool.. any form ??
> pls..i need your help
>=20
> gracias!
>=20
> amigos.. hay alguna formra de configurar el mysql (instalado en un
> linux )desde una pc o server de windows???
>=20
> gracias!
>=20
>=20
>=20
>=20
>=20
>=20
> ______________________________________________
> Renovamos el Correo Yahoo!
> Nuevos servicios, m=E1s seguridad
> http://correo.yahoo.es
>=20
>=20
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=3Ddanieldaveiga@gma=
il.com
>=20
>=20


--=20
Daniel da Veiga
Computer Operator - RS - Brazil

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

Re: config a linux from windows

Look to www.mysqlfront.de ;-)


Petr Vileta, Czech republic
(My server reject all messages from Yahoo and Hotmail. Send me your mail
from another non-spammer site please.)



----- Original Message -----
From: "JP Files"
To: "mysql list"
Sent: Wednesday, July 13, 2005 4:04 AM
Subject: config a linux from windows


> dear frieds..
>
> is there any mean for config and add databases to mysql (on linux)
> from a pc windows ??
> any tool.. any form ??
> pls..i need your help
>
> gracias!
>
> amigos.. hay alguna formra de configurar el mysql (instalado en un
> linux )desde una pc o server de windows???
>
> gracias!
>
>
>
>
>
>
> ______________________________________________
> Renovamos el Correo Yahoo!
> Nuevos servicios, más seguridad
> http://correo.yahoo.es
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=petr@practisoft.cz
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: config a linux from windows

And the MySQL Administrator is worth mentioning too, also from the MySQL =
site.

My MySQL production servers run under Linux and I administer them from a =
Windows desktop using these tools. I have also used others like =
MySQL-Front.

John Bonnett

-----Original Message-----
From: Armando [mailto:dijital@shaw.ca]=20
Sent: Wednesday, 13 July 2005 2:12 PM
To: mysql list
Subject: Re: config a linux from windows

Yes... the mysql.exe command line client. If you want a GUI, perhaps try =

using the MySQL Query Browser. Both are available from the mysql.com=20
site. Cheers.

Armando

JP Files wrote:
> dear frieds..
>=20
> is there any mean for config and add databases to mysql (on linux)
> from a pc windows ??
> any tool.. any form ??
> pls..i need your help
>=20
> gracias!
>=20
> amigos.. hay alguna formra de configurar el mysql (instalado en un=20
> linux )desde una pc o server de windows???
>=20
> gracias!
>=20
>=20
>=20
>=20
>=20
> =09
> ______________________________________________=20
> Renovamos el Correo Yahoo!=20
> Nuevos servicios, m=E1s seguridad=20
> http://correo.yahoo.es
>=20


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

Re: config a linux from windows

Or Armando can to install "MyPHPAdmin" to Linux server ;-)
But for windows users is better to use some GUI tool as MySQL-Front, SQLy=
og
or other. I'm using MySQL-Front and this costs 25 Euro only ;-)

Petr Vileta, Czech republic
(My server reject all messages from Yahoo and Hotmail. Send me your mail
from another non-spammer site please.)


----- Original Message -----=20
From:
To: ;
Sent: Thursday, July 14, 2005 2:19 AM
Subject: RE: config a linux from windows


> And the MySQL Administrator is worth mentioning too, also from the MySQ=
L
site.
>
> My MySQL production servers run under Linux and I administer them from =
a
Windows desktop using these tools. I have also used others like MySQL-Fro=
nt.
>
> John Bonnett
>
> -----Original Message-----
> From: Armando [mailto:dijital@shaw.ca]
> Sent: Wednesday, 13 July 2005 2:12 PM
> To: mysql list
> Subject: Re: config a linux from windows
>
> Yes... the mysql.exe command line client. If you want a GUI, perhaps tr=
y
> using the MySQL Query Browser. Both are available from the mysql.com
> site. Cheers.
>
> Armando
>
> JP Files wrote:
> > dear frieds..
> >
> > is there any mean for config and add databases to mysql (on linux)
> > from a pc windows ??
> > any tool.. any form ??
> > pls..i need your help
> >
> > gracias!
> >
> > amigos.. hay alguna formra de configurar el mysql (instalado en un
> > linux )desde una pc o server de windows???
> >
> > gracias!
> >
> >
> >
> >
> >
> >
> > ______________________________________________
> > Renovamos el Correo Yahoo!
> > Nuevos servicios, m=E1s seguridad
> > http://correo.yahoo.es
> >
>
>
> --=20
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=3Dpetr@practisoft=
..cz
>


--=20
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gma=
ne.org

Re: config a linux from windows

Yes, PHPMyAdmin is pretty nice as well, and I have it installed for
managing the database on one of my domains that is hosted remotely. The
only other way I can manage that database is via SSL so if I don't
happen to have SSL access handy (pretty much every Windows machine I've
ever used that doesn't have a third party utility installed) then
PHPMyAdmin has really saved the day :) I've not used MySQL Front yet,
but I hear it is also a decent utility. SQLyog works well also.

Mind you my personal preference is always command line where possible.
Can't learn it if you don't do it, right?! Cheers!

Armando

Petr Vileta wrote:
> Or Armando can to install "MyPHPAdmin" to Linux server ;-)
> But for windows users is better to use some GUI tool as MySQL-Front, SQLyog
> or other. I'm using MySQL-Front and this costs 25 Euro only ;-)
>
> Petr Vileta, Czech republic
> (My server reject all messages from Yahoo and Hotmail. Send me your mail
> from another non-spammer site please.)
>
>
> ----- Original Message -----
> From:
> To: ;
> Sent: Thursday, July 14, 2005 2:19 AM
> Subject: RE: config a linux from windows
>
>
>
>>And the MySQL Administrator is worth mentioning too, also from the MySQL
>
> site.
>
>>My MySQL production servers run under Linux and I administer them from a
>
> Windows desktop using these tools. I have also used others like MySQL-Front.
>
>>John Bonnett
>>
>>-----Original Message-----
>>From: Armando [mailto:dijital@shaw.ca]
>>Sent: Wednesday, 13 July 2005 2:12 PM
>>To: mysql list
>>Subject: Re: config a linux from windows
>>
>>Yes... the mysql.exe command line client. If you want a GUI, perhaps try
>>using the MySQL Query Browser. Both are available from the mysql.com
>>site. Cheers.
>>
>>Armando
>>
>>JP Files wrote:
>>
>>>dear frieds..
>>>
>>>is there any mean for config and add databases to mysql (on linux)
>>>from a pc windows ??
>>>any tool.. any form ??
>>>pls..i need your help
>>>
>>>gracias!
>>>
>>>amigos.. hay alguna formra de configurar el mysql (instalado en un
>>>linux )desde una pc o server de windows???
>>>
>>>gracias!
>>>
>>>
>>>
>>>
>>>
>>>
>>>______________________________________________
>>>Renovamos el Correo Yahoo!
>>>Nuevos servicios, más seguridad
>>>http://correo.yahoo.es
>>>
>>
>>
>>--
>>MySQL Windows Mailing List
>>For list archives: http://lists.mysql.com/win32
>>To unsubscribe: http://lists.mysql.com/win32?unsub=petr@practisoft.cz
>>
>
>
>

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org