Problem With 'INSERT INTO'...
| Author |
Message |
|
darre
#1 / 6
|
 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 |
|
 |
|
Atrax
#2 / 6
|
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 |
|
|
|
Val Mazu
#3 / 6
|
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 |
|
|
|
Sukesh Hooga
#4 / 6
|
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 |
|
|
|
Sukesh Hooga
#5 / 6
|
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 |
|
|
|
darre
#6 / 6
|
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 |
|
| |
|
