Problem With 'INSERT INTO'... 
Author Message
 Problem With 'INSERT INTO'...

Hi,
    I am having some porblems inserting some parameters into an Access
database, my problem is that in the 'with' statement of the command object i
am not sure what the different parameters mean. I can guess that the first 3
parameters after the .CreateParameter("RoomNo", adInteger, adParamInput, 0 ,
iRoomNo ) mean

field name
data type?
action type?

I do not know nor can i find what the next parameter is for? My code is as
follows:

------------------
Dim connDetails As ADODB.Connection
Dim rsDetails As ADODB.Recordset
Dim cmdDetails As ADODB.Command
Dim strConn As String
Dim strSQL As String
Dim iRoomNo As Integer
Dim strSurname As String
Dim strFirstname As String
Dim strStreet As String
Dim strTown As String
Dim strPostCode As String
Dim strPhone As String
Dim strEmail As String
Dim strArrival As String

iRoomNo = cboRoomNo.Text
strSurname = txtSurname.Text
strFirstname = txtFirstName.Text
strStreet = txtStreet.Text
strTown = txtTown.Text
strPostCode = txtPostCode.Text
strPhone = txtPhone.Text
strEmail = txtEmail.Text
strArrival = txtArrival.Text

strConn = "DSN=ParkLane"
strSQL = "INSERT INTO Clients (RoomNo, Surname, FirstName, " _
        & "Street, Town, Postcode, Phone, Email, Arrival) VALUES (" _
        & "?, ?, ?, ?, ?, ?, ?, ?, ?)"

Set connDetails = New ADODB.Connection

Set cmdDetails = New ADODB.Command

connDetails.Open strConn

With cmdDetails
    .CommandType = adCmdText
    .CommandText = strSQL
    .ActiveConnection = connDetails
    .Parameters.Append .CreateParameter("RoomNo", adInteger, adParamInput,
0, iRoomNo)
    .Parameters.Append .CreateParameter("Surname", adChar, adParamInput, 10,
strSurname)
    .Parameters.Append .CreateParameter("FirstName", adChar, adParamInput,
10, strFirstname)
    .Parameters.Append .CreateParameter("Street", adChar, adParamInput, 10,
strStreet)
    .Parameters.Append .CreateParameter("Town", adChar, adParamInput, 10,
strTown)
    .Parameters.Append .CreateParameter("Postcode", adChar, adParamInput,
10, strPostCode)
    .Parameters.Append .CreateParameter("Phone", adChar, adParamInput, 10,
strPhone)
    .Parameters.Append .CreateParameter("Email", adChar, adParamInput, 10,
strEmail)
    .Parameters.Append .CreateParameter("Arrival", adChar, adParamInput, 10,
strArrival)
    .Execute
End With

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

The error message i receice is:

Run-time error '3421'
Application uses a vlaue of the wrong type for the current operation

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

The data types in the Access database are the same as the variables but for
the 'arrival' field, which is a date?
Am i uisng the correct code or is there an easier/better way to do this?
-----------------------------------

Also, does anyone have any pointers as to how i would achiecve this using
the following:
------------------------------
with rs
    .AddNew
    .Fields("RoomNo") = iRoomNo
    .Fields("Surname") = strSurname
    etc.......
    .update
End With
------------------------------

I have tried this and although i get no errors, it does not insert the data
into the table.

Any help and advice would be greatly appreciated.

Regards
Darren



Sun, 22 May 2005 06:17:40 GMT  
 Problem With 'INSERT INTO'...
you need the ADO docs. you probably already have them on your system,
but if not, doawnload them from www.microsoft.com/data/

________________________________________
I got bored with my old signature,
so I changed it

Atrax. MVP, IIS
http://rtfm.atrax.co.uk/

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sun, 22 May 2005 11:51:11 GMT  
 Problem With 'INSERT INTO'...
Darren,

"RoomNo" - is a parameter name (not field name)
adInteger - is a datatype of parameter
adParamInput - is a direction of parameter
0 - is a size of parameter. For any numeric parameters, like adInteger etc,
you should skip it, since all numeric datatypes have predefined size
iRoomNo - is an actula value, which you are passing to your parameter. In
your case it is a variable, which contains value

--
Val Mazur
Microsoft MVP


Quote:
> Hi,
>     I am having some porblems inserting some parameters into an Access
> database, my problem is that in the 'with' statement of the command object
i
> am not sure what the different parameters mean. I can guess that the first
3
> parameters after the .CreateParameter("RoomNo", adInteger, adParamInput, 0
,
> iRoomNo ) mean

> field name
> data type?
> action type?

> I do not know nor can i find what the next parameter is for? My code is as
> follows:

> ------------------
> Dim connDetails As ADODB.Connection
> Dim rsDetails As ADODB.Recordset
> Dim cmdDetails As ADODB.Command
> Dim strConn As String
> Dim strSQL As String
> Dim iRoomNo As Integer
> Dim strSurname As String
> Dim strFirstname As String
> Dim strStreet As String
> Dim strTown As String
> Dim strPostCode As String
> Dim strPhone As String
> Dim strEmail As String
> Dim strArrival As String

> iRoomNo = cboRoomNo.Text
> strSurname = txtSurname.Text
> strFirstname = txtFirstName.Text
> strStreet = txtStreet.Text
> strTown = txtTown.Text
> strPostCode = txtPostCode.Text
> strPhone = txtPhone.Text
> strEmail = txtEmail.Text
> strArrival = txtArrival.Text

> strConn = "DSN=ParkLane"
> strSQL = "INSERT INTO Clients (RoomNo, Surname, FirstName, " _
>         & "Street, Town, Postcode, Phone, Email, Arrival) VALUES (" _
>         & "?, ?, ?, ?, ?, ?, ?, ?, ?)"

> Set connDetails = New ADODB.Connection

> Set cmdDetails = New ADODB.Command

> connDetails.Open strConn

> With cmdDetails
>     .CommandType = adCmdText
>     .CommandText = strSQL
>     .ActiveConnection = connDetails
>     .Parameters.Append .CreateParameter("RoomNo", adInteger, adParamInput,
> 0, iRoomNo)
>     .Parameters.Append .CreateParameter("Surname", adChar, adParamInput,
10,
> strSurname)
>     .Parameters.Append .CreateParameter("FirstName", adChar, adParamInput,
> 10, strFirstname)
>     .Parameters.Append .CreateParameter("Street", adChar, adParamInput,
10,
> strStreet)
>     .Parameters.Append .CreateParameter("Town", adChar, adParamInput, 10,
> strTown)
>     .Parameters.Append .CreateParameter("Postcode", adChar, adParamInput,
> 10, strPostCode)
>     .Parameters.Append .CreateParameter("Phone", adChar, adParamInput, 10,
> strPhone)
>     .Parameters.Append .CreateParameter("Email", adChar, adParamInput, 10,
> strEmail)
>     .Parameters.Append .CreateParameter("Arrival", adChar, adParamInput,
10,
> strArrival)
>     .Execute
> End With

> ----------------------------------------

> The error message i receice is:

> Run-time error '3421'
> Application uses a vlaue of the wrong type for the current operation

> -------------------------------------

> The data types in the Access database are the same as the variables but
for
> the 'arrival' field, which is a date?
> Am i uisng the correct code or is there an easier/better way to do this?
> -----------------------------------

> Also, does anyone have any pointers as to how i would achiecve this using
> the following:
> ------------------------------
> with rs
>     .AddNew
>     .Fields("RoomNo") = iRoomNo
>     .Fields("Surname") = strSurname
>     etc.......
>     .update
> End With
> ------------------------------

> I have tried this and although i get no errors, it does not insert the
data
> into the table.

> Any help and advice would be greatly appreciated.

> Regards
> Darren



Sun, 22 May 2005 12:03:20 GMT  
 Problem With 'INSERT INTO'...

Darren

1. Why not use the connection object to insert a new record ?

 strSQL = "INSERT INTO Clients (RoomNo, Surname, FirstName, " _
         & "Street, Town, Postcode, Phone, Email, Arrival) VALUES ('"_
         &  IRoomNo & "','" &  strSurname & "','" and so on....   & "')"

conDetails.Open
conDetails.Execute strSQL

2. You can also try by enclosing strArrival  in # sign, instead of '

strSQL = "INSERT INTO Clients (RoomNo, Surname, FirstName, " _
         & "Street, Town, Postcode, Phone, Email, Arrival) VALUES ('"_
         &  IRoomNo & "','" &  strSurname & "','" ............ & "',#  &
strArrival  & "#)"

Sukesh


Quote:
> Hi,
>     I am having some porblems inserting some parameters into an Access
> database, my problem is that in the 'with' statement of the command object
i
> am not sure what the different parameters mean. I can guess that the first
3
> parameters after the .CreateParameter("RoomNo", adInteger, adParamInput, 0
,
> iRoomNo ) mean

> field name
> data type?
> action type?

> I do not know nor can i find what the next parameter is for? My code is as
> follows:

> ------------------
> Dim connDetails As ADODB.Connection
> Dim rsDetails As ADODB.Recordset
> Dim cmdDetails As ADODB.Command
> Dim strConn As String
> Dim strSQL As String
> Dim iRoomNo As Integer
> Dim strSurname As String
> Dim strFirstname As String
> Dim strStreet As String
> Dim strTown As String
> Dim strPostCode As String
> Dim strPhone As String
> Dim strEmail As String
> Dim strArrival As String

> iRoomNo = cboRoomNo.Text
> strSurname = txtSurname.Text
> strFirstname = txtFirstName.Text
> strStreet = txtStreet.Text
> strTown = txtTown.Text
> strPostCode = txtPostCode.Text
> strPhone = txtPhone.Text
> strEmail = txtEmail.Text
> strArrival = txtArrival.Text

> strConn = "DSN=ParkLane"
> strSQL = "INSERT INTO Clients (RoomNo, Surname, FirstName, " _
>         & "Street, Town, Postcode, Phone, Email, Arrival) VALUES (" _
>         & "?, ?, ?, ?, ?, ?, ?, ?, ?)"

> Set connDetails = New ADODB.Connection

> Set cmdDetails = New ADODB.Command

> connDetails.Open strConn

> With cmdDetails
>     .CommandType = adCmdText
>     .CommandText = strSQL
>     .ActiveConnection = connDetails
>     .Parameters.Append .CreateParameter("RoomNo", adInteger, adParamInput,
> 0, iRoomNo)
>     .Parameters.Append .CreateParameter("Surname", adChar, adParamInput,
10,
> strSurname)
>     .Parameters.Append .CreateParameter("FirstName", adChar, adParamInput,
> 10, strFirstname)
>     .Parameters.Append .CreateParameter("Street", adChar, adParamInput,
10,
> strStreet)
>     .Parameters.Append .CreateParameter("Town", adChar, adParamInput, 10,
> strTown)
>     .Parameters.Append .CreateParameter("Postcode", adChar, adParamInput,
> 10, strPostCode)
>     .Parameters.Append .CreateParameter("Phone", adChar, adParamInput, 10,
> strPhone)
>     .Parameters.Append .CreateParameter("Email", adChar, adParamInput, 10,
> strEmail)
>     .Parameters.Append .CreateParameter("Arrival", adChar, adParamInput,
10,
> strArrival)
>     .Execute
> End With

> ----------------------------------------

> The error message i receice is:

> Run-time error '3421'
> Application uses a vlaue of the wrong type for the current operation

> -------------------------------------

> The data types in the Access database are the same as the variables but
for
> the 'arrival' field, which is a date?
> Am i uisng the correct code or is there an easier/better way to do this?
> -----------------------------------

> Also, does anyone have any pointers as to how i would achiecve this using
> the following:
> ------------------------------
> with rs
>     .AddNew
>     .Fields("RoomNo") = iRoomNo
>     .Fields("Surname") = strSurname
>     etc.......
>     .update
> End With
> ------------------------------

> I have tried this and although i get no errors, it does not insert the
data
> into the table.

> Any help and advice would be greatly appreciated.

> Regards
> Darren



Sun, 22 May 2005 12:51:12 GMT  
 Problem With 'INSERT INTO'...
Darren

In continuation

You are using a prepared statement to insert a record  using a command
object with parameters (perfectly OK & allowed). IMO, this approach would be
OK, if the users  were allowed to construct sql statement at run time and
would run the same statement a number of times with different values for the
parameters.

But instead, it would make more sense to execute command object with
parameters to insert records using stored procedures.

Moreover for prepared statements, the name of the parameter is optional
(actually not required) only type, direction, value and size (in certain
cases) is required. For multiple parameters, the order of appending them
would be important.

Sukesh


Quote:
> Hi,
>     I am having some porblems inserting some parameters into an Access
> database, my problem is that in the 'with' statement of the command object
i
> am not sure what the different parameters mean. I can guess that the first
3
> parameters after the .CreateParameter("RoomNo", adInteger, adParamInput, 0
,
> iRoomNo ) mean

> field name
> data type?
> action type?

> I do not know nor can i find what the next parameter is for? My code is as
> follows:

> ------------------
> Dim connDetails As ADODB.Connection
> Dim rsDetails As ADODB.Recordset
> Dim cmdDetails As ADODB.Command
> Dim strConn As String
> Dim strSQL As String
> Dim iRoomNo As Integer
> Dim strSurname As String
> Dim strFirstname As String
> Dim strStreet As String
> Dim strTown As String
> Dim strPostCode As String
> Dim strPhone As String
> Dim strEmail As String
> Dim strArrival As String

> iRoomNo = cboRoomNo.Text
> strSurname = txtSurname.Text
> strFirstname = txtFirstName.Text
> strStreet = txtStreet.Text
> strTown = txtTown.Text
> strPostCode = txtPostCode.Text
> strPhone = txtPhone.Text
> strEmail = txtEmail.Text
> strArrival = txtArrival.Text

> strConn = "DSN=ParkLane"
> strSQL = "INSERT INTO Clients (RoomNo, Surname, FirstName, " _
>         & "Street, Town, Postcode, Phone, Email, Arrival) VALUES (" _
>         & "?, ?, ?, ?, ?, ?, ?, ?, ?)"

> Set connDetails = New ADODB.Connection

> Set cmdDetails = New ADODB.Command

> connDetails.Open strConn

> With cmdDetails
>     .CommandType = adCmdText
>     .CommandText = strSQL
>     .ActiveConnection = connDetails
>     .Parameters.Append .CreateParameter("RoomNo", adInteger, adParamInput,
> 0, iRoomNo)
>     .Parameters.Append .CreateParameter("Surname", adChar, adParamInput,
10,
> strSurname)
>     .Parameters.Append .CreateParameter("FirstName", adChar, adParamInput,
> 10, strFirstname)
>     .Parameters.Append .CreateParameter("Street", adChar, adParamInput,
10,
> strStreet)
>     .Parameters.Append .CreateParameter("Town", adChar, adParamInput, 10,
> strTown)
>     .Parameters.Append .CreateParameter("Postcode", adChar, adParamInput,
> 10, strPostCode)
>     .Parameters.Append .CreateParameter("Phone", adChar, adParamInput, 10,
> strPhone)
>     .Parameters.Append .CreateParameter("Email", adChar, adParamInput, 10,
> strEmail)
>     .Parameters.Append .CreateParameter("Arrival", adChar, adParamInput,
10,
> strArrival)
>     .Execute
> End With

> ----------------------------------------

> The error message i receice is:

> Run-time error '3421'
> Application uses a vlaue of the wrong type for the current operation

> -------------------------------------

> The data types in the Access database are the same as the variables but
for
> the 'arrival' field, which is a date?
> Am i uisng the correct code or is there an easier/better way to do this?
> -----------------------------------

> Also, does anyone have any pointers as to how i would achiecve this using
> the following:
> ------------------------------
> with rs
>     .AddNew
>     .Fields("RoomNo") = iRoomNo
>     .Fields("Surname") = strSurname
>     etc.......
>     .update
> End With
> ------------------------------

> I have tried this and although i get no errors, it does not insert the
data
> into the table.

> Any help and advice would be greatly appreciated.

> Regards
> Darren



Sun, 22 May 2005 13:31:41 GMT  
 Problem With 'INSERT INTO'...
Thank you so much to all of you who took the tim eto reply to my, very
lengthy, question.
My code now is as follows:
-------------------------------------
strSQL = "INSERT INTO Clients (RoomNo, Surname, FirstName, " _
        & "Street, Town, Postcode, Phone, Email, Arrival) VALUES (" &
iRoomNo & ", '" & _
        strSurname & "', '" & strFirstname & "', '" & strStreet & "', '" &
strTown & "', '" _
        & strPostCode & "', '" & strPhone & "', '" & strEmail & "', #" &
strArrival & "#)"

connDetails.Open strConn
connDetails.Execute strSQL
--------------------------------------

It now works perfectly :-)

Thank you all.

Best Regards
Darren



Mon, 23 May 2005 03:09:19 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. intercept and modify 'Insert Caption'

2. setup.exe failure: 'insert disk 0'

3. Problem using ' apostrophy in insert statement

4. 'INSERT' query not working

5. Problem Using Apostrophy ' in Insert Statement (Urgent)

6. 'INSERT INTO' SQL

7. **************!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!Help me !!!!!!!!!!!!!!!!!!!!!!!!'''''''''''''''''''''''*************

8. invalid SQL statement..expected 'DELETE','INSERT'.....

9. 'addnew' or sql-'insert into ..'?

10. 'Poke'ing and 'peek'ing other process's memory in VB6

11. Problem with sentences with '#' and '''

12. problems when using 'fix' function

 

 
Powered by phpBB® Forum Software