Correct syntax for "insert"ing data

--0-1193863435-1114112642=:32787
Content-Type: text/plain; charset=us-ascii

I'm having problems with the syntax for entering data. I'm using:

objRS.AddNew
objRS("Name") = name
objRS("Address") = address
objRS("City") = city
objRS("State") = state
objRS("Zip") = zip
objRS("Phone") = phone
objRS.Update

for an "MS Access" database.

What would be the correct syntax adding to a MySQL database.

I was using:

sql="insert into contact_info.friends (Name, Address, City, State, Zip, Phone) values (name, address, city, state, zip, phone)"
set objRS=objConn.execute(sql)

but it errors out.

TIA
Ron


--0-1193863435-1114112642=:32787--

Re: Correct syntax for "insert"ing data

Ron,

You need to interpolate your variables, strings must be quoted. Looks like =
you're using some flavor of VB. Try something like this:

sql=3D"insert into contact_info.friends (Name, Address, City, State, Zip, =

Phone) values ('"&name&"', '"&address&"', '"&city&"', '"&state&"', '"+zip&"=
', '"&phone&"')"

set objRS=3DobjConn.execute(sql)


Randy Clamons
Systems Programming
Novaspace.com


> ------------Original Message------------
> From: Ron Smith
> To: "MySQL Mailing List"
> Date: Thu, Apr-21-2005 12:44 PM
> Subject: Correct syntax for 'insert'ing data
>
> I'm having problems with the syntax for entering data. I'm using:
> =

> objRS.AddNew
> objRS("Name") =3D name
> objRS("Address") =3D address
> objRS("City") =3D city
> objRS("State") =3D state
> objRS("Zip") =3D zip
> objRS("Phone") =3D phone
> objRS.Update
> =

> for an "MS Access" database.
> =

> What would be the correct syntax adding to a MySQL database.
> =

> I was using:
> =

> sql=3D"insert into contact_info.friends (Name, Address, City, State, Zip,=
=

> Phone) values (name, address, city, state, zip, phone)"
> set objRS=3DobjConn.execute(sql)
> =

> but it errors out.
> =

> TIA
> Ron
> =

> =



--
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: Correct syntax for "insert"ing data

You will need to write some thing like this (presuming this is VB and
all the data are text strings)

Sql =3D "insert into contact_info.friends " & _
" (Name, Address, City, State, Zip, Phone)" & _
" values ('" & name & "','" & _
" address & "','" & _
" city & "','" & _
" state & "','" & _
" zip & "','" & _
" phone & "')"
set objRS =3D objConn.execute(sql)

You don't have to spread it all out over several lines like I have
above, that's just the way I do it to make it more readable.

Depending on how you have created the recordset, you may also be able to
do it exactly like you do with Access but I haven't done it that way for
a long time.

John Bonnett

-----Original Message-----
From: Ron Smith [mailto:geeksatlarge@yahoo.com]=20
Sent: Friday, 22 April 2005 5:14 AM
To: MySQL Mailing List
Subject: Correct syntax for 'insert'ing data

I'm having problems with the syntax for entering data. I'm using:
=20
objRS.AddNew
objRS("Name") =3D name
objRS("Address") =3D address
objRS("City") =3D city
objRS("State") =3D state
objRS("Zip") =3D zip
objRS("Phone") =3D phone
objRS.Update
=20
for an "MS Access" database.
=20
What would be the correct syntax adding to a MySQL database.
=20
I was using:
=20
sql=3D"insert into contact_info.friends (Name, Address, City, State, =
Zip,
Phone) values (name, address, city, state, zip, phone)"
set objRS=3DobjConn.execute(sql)
=20
but it errors out.
=20
TIA
Ron
=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: Correct syntax for "insert"ing data

--0-1242813696-1114124320=:66255
Content-Type: text/plain; charset=us-ascii

Thanks Randy,

That did the trick! :-) ...one last point though. It has to do with the way MySQL handles the closing of the "recordset / table".

For the "MSAccess" DB, I was using:

objRS.close
set objRS="nothing"
objConn.close
set objConn="nothing"

for the cleanup of the objects, but I got :

Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed. <========
/listing_15.1_asp3.0_pg499/friends_submit_form.asp, line 56

It looks to me that the 'insert' command does the closing of the table on is own. So, I should elliminate the first two lines and keep the second two lines. Am I correct in my assumption, or is there some other MySQL statement that should replace the first two lines?

Thanks for your help, along with everyone else on the list.

Ron

Randy Clamons wrote:
Ron,

You need to interpolate your variables, strings must be quoted. Looks like you're using some flavor of VB. Try something like this:

sql="insert into contact_info.friends (Name, Address, City, State, Zip,
Phone) values ('"&name&"', '"&address&"', '"&city&"', '"&state&"', '"+zip&"', '"&phone&"')"

set objRS=objConn.execute(sql)


Randy Clamons
Systems Programming
Novaspace.com


> ------------Original Message------------
> From: Ron Smith
> To: "MySQL Mailing List"
> Date: Thu, Apr-21-2005 12:44 PM
> Subject: Correct syntax for 'insert'ing data
>
> I'm having problems with the syntax for entering data. I'm using:
>
> objRS.AddNew
> objRS("Name") = name
> objRS("Address") = address
> objRS("City") = city
> objRS("State") = state
> objRS("Zip") = zip
> objRS("Phone") = phone
> objRS.Update
>
> for an "MS Access" database.
>
> What would be the correct syntax adding to a MySQL database.
>
> I was using:
>
> sql="insert into contact_info.friends (Name, Address, City, State, Zip,
> Phone) values (name, address, city, state, zip, phone)"
> set objRS=objConn.execute(sql)
>
> but it errors out.
>
> TIA
> Ron
>
>



--0-1242813696-1114124320=:66255--

RE: Correct syntax for "insert"ing data

--0-812201767-1114124962=:13442
Content-Type: text/plain; charset=us-ascii

Thanks David,

"jbonnett" and "Randy", from the list, came up with a solution that works for me. However, I'm keeping what you sent me because it looks to me like another way of setting things up. Thanks for the tip.

Ron

David Parham wrote:
This absolutely drove me crazy last week when I was trying to do this in a
VB app. I had the best MySQL guys I knew look at it, and no one could give
me a clue as to what was happening. I'm not sure what fixed it for sure,
but once I opened the database manually in the code and did the update with
ado recordset, and then CLOSED the recordset, all my problems went away. I
had earlier tried to use the ADO control that we use to populate a grid.
The code that works for me now is:


Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Dim sql As String

Set conn = New ADODB.Connection
conn.ConnectionString = " DRIVER={mySQL ODBC 3.51 Driver} ;" _
& "SERVER=DELLLAPTOP2;" _
& "DATABASE=DPAMYSQL; " _
& "UID=root;PWD=xxxx; OPTION=3"

conn.Open

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
selstring = "select * from todo where tdunq = " & txttdunq
rs.Open selstring, conn


If rs.EOF Then
rs.AddNew
End If

With rs
!tdcid = txttdcid
!tdunq = txttdunq
!tddat = txttddat
!tdmem = txttdmem
!tdln1 = txttdmem
!tdcom = txttdcom
!tdpri = txttdpri
!tdprj = txttdprj
!tddel = txttddel
..Update
..UpdateBatch
End With

rs.Close



msg = "Record Updated"

I am still using the ADO record set to refresh the grid... Both reference
the same file. This is the code below the above code to move me to the next
record in the file and populate the unbound fields to the work fields.

Adodc1.Recordset.MoveNext

If Adodc1.Recordset.EOF Then
Adodc1.Recordset.MoveLast
msg = "End of File Encountered.. showing last record "
MsgBox (msg)
End If
Call DIMoveFs
Form1.txttdcid.SetFocus
Form1.Refresh


I know this is VB code and not access, but I'll bet if you close and reopen
the recordset, your problem will clear up.

Hope this helps,

David Parham

-----Original Message-----
From: Ron Smith [mailto:geeksatlarge@yahoo.com]
Sent: Thursday, April 21, 2005 2:44 PM
To: MySQL Mailing List
Subject: Correct syntax for 'insert'ing data

I'm having problems with the syntax for entering data. I'm using:

objRS.AddNew
objRS("Name") = name
objRS("Address") = address
objRS("City") = city
objRS("State") = state
objRS("Zip") = zip
objRS("Phone") = phone
objRS.Update

for an "MS Access" database.

What would be the correct syntax adding to a MySQL database.

I was using:

sql="insert into contact_info.friends (Name, Address, City, State, Zip,
Phone) values (name, address, city, state, zip, phone)"
set objRS=objConn.execute(sql)

but it errors out.

TIA
Ron




--0-812201767-1114124962=:13442--

Re: Correct syntax for "insert"ing data

Ron,

The insert command does not return a recordset, so it throws an error when =
you try to close it. All you need to close is the connection.

Randy Clamons
Systems Programming
Novaspace.com


> ------------Original Message------------
> From: Ron Smith
> To: "MySQL Mailing List"
> Date: Thu, Apr-21-2005 3:59 PM
> Subject: Re: Correct syntax for 'insert'ing data
>
> Thanks Randy,
> =

> That did the trick! :-) ...one last point though. It has to do with the =

> way MySQL handles the closing of the "recordset / table".
> =

> For the "MSAccess" DB, I was using:
> =

> objRS.close
> set objRS=3D"nothing"
> objConn.close
> set objConn=3D"nothing"
> =

> for the cleanup of the objects, but I got :
> =

> Error Type:
> ADODB.Recordset (0x800A0E78)
> Operation is not allowed when the object is closed. <=======
==
> /listing_15.1_asp3.0_pg499/friends_submit_form.asp, line 56
> =

> It looks to me that the 'insert' command does the closing of the table =

> on is own. So, I should elliminate the first two lines and keep the =

> second two lines. Am I correct in my assumption, or is there some other =

> MySQL statement that should replace the first two lines?
> =

> Thanks for your help, along with everyone else on the list.
> =

> Ron
> =

> Randy Clamons wrote:
> Ron,
> =

> You need to interpolate your variables, strings must be quoted. Looks =

> like you're using some flavor of VB. Try something like this:
> =

> sql=3D"insert into contact_info.friends (Name, Address, City, State, Zip,=
=

> =

> Phone) values ('"&name&"', '"&address&"', '"&city&"', '"&state&"', =

> '"+zip&"', '"&phone&"')"
> =

> set objRS=3DobjConn.execute(sql)
> =

> =

> Randy Clamons
> Systems Programming
> Novaspace.com
> =

> =

> > ------------Original Message------------
> > From: Ron Smith =

> > To: "MySQL Mailing List" =

> > Date: Thu, Apr-21-2005 12:44 PM
> > Subject: Correct syntax for 'insert'ing data
> >
> > I'm having problems with the syntax for entering data. I'm using:
> > =

> > objRS.AddNew
> > objRS("Name") =3D name
> > objRS("Address") =3D address
> > objRS("City") =3D city
> > objRS("State") =3D state
> > objRS("Zip") =3D zip
> > objRS("Phone") =3D phone
> > objRS.Update
> > =

> > for an "MS Access" database.
> > =

> > What would be the correct syntax adding to a MySQL database.
> > =

> > I was using:
> > =

> > sql=3D"insert into contact_info.friends (Name, Address, City, State, =

> Zip, =

> > Phone) values (name, address, city, state, zip, phone)"
> > set objRS=3DobjConn.execute(sql)
> > =

> > but it errors out.
> > =

> > TIA
> > Ron
> > =

> > =

> =

> =

> =



--
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: Correct syntax for "insert"ing data

Also=20

Set objConn =3D Nothing

Nothing is a special variable and should not have quotes around it.

John Bonnett

-----Original Message-----
From: Randy Clamons [mailto:randy@novaspace.com]=20
Sent: Friday, 22 April 2005 9:56 AM
To: Ron Smith; MySQL Mailing List
Subject: Re: Correct syntax for 'insert'ing data

Ron,

The insert command does not return a recordset, so it throws an error
when you try to close it. All you need to close is the connection.

Randy Clamons
Systems Programming
Novaspace.com


> ------------Original Message------------
> From: Ron Smith
> To: "MySQL Mailing List"
> Date: Thu, Apr-21-2005 3:59 PM
> Subject: Re: Correct syntax for 'insert'ing data
>
> Thanks Randy,
> =20
> That did the trick! :-) ...one last point though. It has to do with
the=20
> way MySQL handles the closing of the "recordset / table".
> =20
> For the "MSAccess" DB, I was using:
> =20
> objRS.close
> set objRS=3D"nothing"
> objConn.close
> set objConn=3D"nothing"
> =20
> for the cleanup of the objects, but I got :
> =20
> Error Type:
> ADODB.Recordset (0x800A0E78)
> Operation is not allowed when the object is closed. =
<========
> /listing_15.1_asp3.0_pg499/friends_submit_form.asp, line 56
>=20
> It looks to me that the 'insert' command does the closing of the table

> on is own. So, I should elliminate the first two lines and keep the=20
> second two lines. Am I correct in my assumption, or is there some
other=20
> MySQL statement that should replace the first two lines?
> =20
> Thanks for your help, along with everyone else on the list.
> =20
> Ron
>=20
> Randy Clamons wrote:
> Ron,
>=20
> You need to interpolate your variables, strings must be quoted. Looks=20
> like you're using some flavor of VB. Try something like this:
>=20
> sql=3D"insert into contact_info.friends (Name, Address, City, State,
Zip,=20
>=20
> Phone) values ('"&name&"', '"&address&"', '"&city&"', '"&state&"',=20
> '"+zip&"', '"&phone&"')"
>=20
> set objRS=3DobjConn.execute(sql)
>=20
>=20
> Randy Clamons
> Systems Programming
> Novaspace.com
>=20
>=20
> > ------------Original Message------------
> > From: Ron Smith=20
> > To: "MySQL Mailing List"=20
> > Date: Thu, Apr-21-2005 12:44 PM
> > Subject: Correct syntax for 'insert'ing data
> >
> > I'm having problems with the syntax for entering data. I'm using:
> >=20
> > objRS.AddNew
> > objRS("Name") =3D name
> > objRS("Address") =3D address
> > objRS("City") =3D city
> > objRS("State") =3D state
> > objRS("Zip") =3D zip
> > objRS("Phone") =3D phone
> > objRS.Update
> >=20
> > for an "MS Access" database.
> >=20
> > What would be the correct syntax adding to a MySQL database.
> >=20
> > I was using:
> >=20
> > sql=3D"insert into contact_info.friends (Name, Address, City, State, =

> Zip,=20
> > Phone) values (name, address, city, state, zip, phone)"
> > set objRS=3DobjConn.execute(sql)
> >=20
> > but it errors out.
> >=20
> > TIA
> > Ron
> >=20
> >=20
>=20
>=20
>=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