INSERT records

Dimitris Nikolakakis · Sep 13, 2006

I have a SP:

CREATE PROCEDURE qINSERTORDER @ORDERID nvarchar(40)
AS
INSERT INTO Orders (ORDERID)
SELECT @ORDERID

I have a form FINSERTORDER with a Field ORDERID and a button to call the SP.
In Input parameters I have:
ORDERID nvarchar(40) = [Forms]![FINSERTORDER ]![ORDERID].

When I press the button I am asked to enter parameter value for ORDERID.

Thanks
Dimitris

Sylvain Lafontaine · Sep 13, 2006

To my knowledge, the InputParameters is only for the record source of a
bound form or the rowsource of a control on that form (bound or unbound).

If you want to call a SP from a button, you have to use ADO objects;
something like:

Dim cmdMenu As ADODB.Command
Set cmdMenu = New ADODB.Command

Set cmdMenu.ActiveConnection = CurrentProject.Connection

cmdMenu.CommandType = adCmdStoredProc ' or: adCmdText.
cmdMenu.CommandText = "MyStoredProcedure"

cmdMenu.Parameters.Append cmdMenu.CreateParameter("@IdOrganisme", _
adInteger, adParamInput, , aIdOrganisme)

Dim rsMenu As ADODB.Recordset
Set rsMenu = New ADODB.Recordset

rsMenu.CursorLocation = adUseClient ' Default location: adUseServer
rsMenu.Open cmdMenu, , adOpenStatic, adLockOptimistic

Do While Not rsMenu.EOF
Debug.Print rsMenu ("FirstField")
rsMenu.MoveNext
Loop

rsMenu.Close
set rsMenu = Nothing

Sylvain Lafontaine · Sep 13, 2006

For inserting a record, you can call CurrentProject.Connection.Execute:

Dim sql as string
sql = "EXEC qINSERTORDER '" & [Forms]![FINSERTORDER ]![ORDERID] & "'"
CurrentProject.Connection.Execute (sql)

or you can open a recordset like previously and use the AddNew and Update
command:

....
cmdMenu.CommandType = adCmdText.
cmdMenu.CommandText = "Select * From Orders Where 1=0"
cmdMenu.AddNew
cmdMenu ("ORDERID") = [Forms]![FINSERTORDER ]![ORDERID]
cmdMenu.Update
....

If you want to get the identity value of the newly inserted record, read it
from the recordset after the Update command. Or course,
[Forms]![FINSERTORDER ]![ORDERID] can be replaced with Me.OrderID where
possible.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Sylvain Lafontaine said:
To my knowledge, the InputParameters is only for the record source of a
bound form or the rowsource of a control on that form (bound or unbound).

If you want to call a SP from a button, you have to use ADO objects;
something like:

Dim cmdMenu As ADODB.Command
Set cmdMenu = New ADODB.Command

Set cmdMenu.ActiveConnection = CurrentProject.Connection

cmdMenu.CommandType = adCmdStoredProc ' or: adCmdText.
cmdMenu.CommandText = "MyStoredProcedure"

cmdMenu.Parameters.Append cmdMenu.CreateParameter("@IdOrganisme", _
adInteger, adParamInput, , aIdOrganisme)

Dim rsMenu As ADODB.Recordset
Set rsMenu = New ADODB.Recordset

rsMenu.CursorLocation = adUseClient ' Default location: adUseServer
rsMenu.Open cmdMenu, , adOpenStatic, adLockOptimistic

Do While Not rsMenu.EOF
Debug.Print rsMenu ("FirstField")
rsMenu.MoveNext
Loop

rsMenu.Close
set rsMenu = Nothing

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Dimitris Nikolakakis said:

I have a SP:

CREATE PROCEDURE qINSERTORDER @ORDERID nvarchar(40)
AS
INSERT INTO Orders (ORDERID)
SELECT @ORDERID

I have a form FINSERTORDER with a Field ORDERID and a button to call the
SP. In Input parameters I have:
ORDERID nvarchar(40) = [Forms]![FINSERTORDER ]![ORDERID].

When I press the button I am asked to enter parameter value for ORDERID.

Thanks
Dimitris

Click to expand...

Dimitris Nikolakakis · Sep 14, 2006

I have use in the button the following:
--------------------------------------------------------
Dim sql As String
sql = "EXEC [ORD-qInsertOrderFromFile-1]
Forms.[ORD-FInsertOrderFromFile].[OrderID],
Forms.[ORD-FInsertOrderFromFile].[ClientID]"
CurrentProject.Connection.Execute sql
--------------------------------------------------------
My SP is:
--------------------------------------------------------
CREATE PROCEDURE [ORD-qInsertOrderFromFile-1]
@OrderID nvarchar(40), @ClientID int
AS
INSERT INTO [ORD-Orders] (TypeID, OrderID, ClientID)
SELECT 'ORD', @OrderID, @ClientID
--------------------------------------------------------

When I press the button I get error:
--------------------------------------------------------
Run-time error '-2147217900 (80040e14)'
Incorrect Syntax near '.'

Dimitris

Ï "Sylvain Lafontaine said:
For inserting a record, you can call CurrentProject.Connection.Execute:

Dim sql as string
sql = "EXEC qINSERTORDER '" & [Forms]![FINSERTORDER ]![ORDERID] & "'"
CurrentProject.Connection.Execute (sql)

or you can open a recordset like previously and use the AddNew and Update
command:

...
cmdMenu.CommandType = adCmdText.
cmdMenu.CommandText = "Select * From Orders Where 1=0"
cmdMenu.AddNew
cmdMenu ("ORDERID") = [Forms]![FINSERTORDER ]![ORDERID]
cmdMenu.Update
...

If you want to get the identity value of the newly inserted record, read
it from the recordset after the Update command. Or course,
[Forms]![FINSERTORDER ]![ORDERID] can be replaced with Me.OrderID where
possible.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Sylvain Lafontaine said:

To my knowledge, the InputParameters is only for the record source of a
bound form or the rowsource of a control on that form (bound or unbound).

If you want to call a SP from a button, you have to use ADO objects;
something like:

Dim cmdMenu As ADODB.Command
Set cmdMenu = New ADODB.Command

Set cmdMenu.ActiveConnection = CurrentProject.Connection

cmdMenu.CommandType = adCmdStoredProc ' or: adCmdText.
cmdMenu.CommandText = "MyStoredProcedure"

cmdMenu.Parameters.Append cmdMenu.CreateParameter("@IdOrganisme", _
adInteger, adParamInput, , aIdOrganisme)

Dim rsMenu As ADODB.Recordset
Set rsMenu = New ADODB.Recordset

rsMenu.CursorLocation = adUseClient ' Default location: adUseServer
rsMenu.Open cmdMenu, , adOpenStatic, adLockOptimistic

Do While Not rsMenu.EOF
Debug.Print rsMenu ("FirstField")
rsMenu.MoveNext
Loop

rsMenu.Close
set rsMenu = Nothing

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Dimitris Nikolakakis said:

I have a SP:

CREATE PROCEDURE qINSERTORDER @ORDERID nvarchar(40)
AS
INSERT INTO Orders (ORDERID)
SELECT @ORDERID

I have a form FINSERTORDER with a Field ORDERID and a button to call the
SP. In Input parameters I have:
ORDERID nvarchar(40) = [Forms]![FINSERTORDER ]![ORDERID].

When I press the button I am asked to enter parameter value for ORDERID.

Thanks
Dimitris

Click to expand...

Click to expand...

Sylvain Lafontaine · Sep 14, 2006

You cannot send thing like « Forms.[ORD-FInsertOrderFromFile].[OrderID] » to
the SQL-Server. You must translate these values locally using the string
operator & :

sql = "EXEC [ORD-qInsertOrderFromFile-1] " &
Forms.[ORD-FInsertOrderFromFile].[OrderID] & " ....

Don't forget to double any enclosed " into two double quotes "" and don't
forget any required blank space, to!

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Dimitris Nikolakakis said:
I have use in the button the following:
--------------------------------------------------------
Dim sql As String
sql = "EXEC [ORD-qInsertOrderFromFile-1]
Forms.[ORD-FInsertOrderFromFile].[OrderID],
Forms.[ORD-FInsertOrderFromFile].[ClientID]"
CurrentProject.Connection.Execute sql
--------------------------------------------------------
My SP is:
--------------------------------------------------------
CREATE PROCEDURE [ORD-qInsertOrderFromFile-1]
@OrderID nvarchar(40), @ClientID int
AS
INSERT INTO [ORD-Orders] (TypeID, OrderID, ClientID)
SELECT 'ORD', @OrderID, @ClientID
--------------------------------------------------------

When I press the button I get error:
--------------------------------------------------------
Run-time error '-2147217900 (80040e14)'
Incorrect Syntax near '.'

Dimitris

Ï "Sylvain Lafontaine said:

For inserting a record, you can call CurrentProject.Connection.Execute:

Dim sql as string
sql = "EXEC qINSERTORDER '" & [Forms]![FINSERTORDER ]![ORDERID] & "'"
CurrentProject.Connection.Execute (sql)

or you can open a recordset like previously and use the AddNew and Update
command:

...
cmdMenu.CommandType = adCmdText.
cmdMenu.CommandText = "Select * From Orders Where 1=0"
cmdMenu.AddNew
cmdMenu ("ORDERID") = [Forms]![FINSERTORDER ]![ORDERID]
cmdMenu.Update
...

If you want to get the identity value of the newly inserted record, read
it from the recordset after the Update command. Or course,
[Forms]![FINSERTORDER ]![ORDERID] can be replaced with Me.OrderID where
possible.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Sylvain Lafontaine said:

To my knowledge, the InputParameters is only for the record source of a
bound form or the rowsource of a control on that form (bound or
unbound).

If you want to call a SP from a button, you have to use ADO objects;
something like:

Dim cmdMenu As ADODB.Command
Set cmdMenu = New ADODB.Command

Set cmdMenu.ActiveConnection = CurrentProject.Connection

cmdMenu.CommandType = adCmdStoredProc ' or: adCmdText.
cmdMenu.CommandText = "MyStoredProcedure"

cmdMenu.Parameters.Append cmdMenu.CreateParameter("@IdOrganisme", _
adInteger, adParamInput, , aIdOrganisme)

Dim rsMenu As ADODB.Recordset
Set rsMenu = New ADODB.Recordset

rsMenu.CursorLocation = adUseClient ' Default location: adUseServer
rsMenu.Open cmdMenu, , adOpenStatic, adLockOptimistic

Do While Not rsMenu.EOF
Debug.Print rsMenu ("FirstField")
rsMenu.MoveNext
Loop

rsMenu.Close
set rsMenu = Nothing

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

I have a SP:

CREATE PROCEDURE qINSERTORDER @ORDERID nvarchar(40)
AS
INSERT INTO Orders (ORDERID)
SELECT @ORDERID

I have a form FINSERTORDER with a Field ORDERID and a button to call
the SP. In Input parameters I have:
ORDERID nvarchar(40) = [Forms]![FINSERTORDER ]![ORDERID].

When I press the button I am asked to enter parameter value for
ORDERID.

Thanks
Dimitris

Click to expand...

Click to expand...

Malcolm Cook · Sep 14, 2006

I think Dimitris can simply directly call his stored proc as though it were amethod of CurrentProject.Connection, like this:

CurrentProject.Connection.qINSERTORDER ([Forms]![FINSERTORDER ]![ORDERID])

ADP magic will figure out all the parameter binding cruft for him.

--
Malcolm Cook - (e-mail address removed)
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA

Sylvain Lafontaine said:
To my knowledge, the InputParameters is only for the record source of a
bound form or the rowsource of a control on that form (bound or unbound).

If you want to call a SP from a button, you have to use ADO objects;
something like:

Dim cmdMenu As ADODB.Command
Set cmdMenu = New ADODB.Command

Set cmdMenu.ActiveConnection = CurrentProject.Connection

cmdMenu.CommandType = adCmdStoredProc ' or: adCmdText.
cmdMenu.CommandText = "MyStoredProcedure"

cmdMenu.Parameters.Append cmdMenu.CreateParameter("@IdOrganisme", _
adInteger, adParamInput, , aIdOrganisme)

Dim rsMenu As ADODB.Recordset
Set rsMenu = New ADODB.Recordset

rsMenu.CursorLocation = adUseClient ' Default location: adUseServer
rsMenu.Open cmdMenu, , adOpenStatic, adLockOptimistic

Do While Not rsMenu.EOF
Debug.Print rsMenu ("FirstField")
rsMenu.MoveNext
Loop

rsMenu.Close
set rsMenu = Nothing

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Dimitris Nikolakakis said:

I have a SP:

CREATE PROCEDURE qINSERTORDER @ORDERID nvarchar(40)
AS
INSERT INTO Orders (ORDERID)
SELECT @ORDERID

I have a form FINSERTORDER with a Field ORDERID and a button to call the
SP. In Input parameters I have:
ORDERID nvarchar(40) = [Forms]![FINSERTORDER ]![ORDERID].

When I press the button I am asked to enter parameter value for ORDERID.

Thanks
Dimitris

Click to expand...

aaron.kempf · Sep 14, 2006

yeah i've used ADP every day for almost 7 years now

and i've sure never used an inputParameters or resync command

Dimitris Nikolakakis · Sep 15, 2006

This worked! thanks

I have tried the SP with a second parameter: @ClientID int

In my code I have:

sql = "EXEC [ORD-qInsertOrderFromFile-1] '&
Forms.[ORD-FInsertOrderFromFile].[OrderID] &', '&
(Forms.[ORD-FInsertOrderFromFile].[Client]) &' "

And I get the error:

Error converting varchar to int.

I have tried many options with brackets etc.....but still problem.

thanks
Dimitris

Ï "Sylvain Lafontaine said:
You cannot send thing like « Forms.[ORD-FInsertOrderFromFile].[OrderID] »
to the SQL-Server. You must translate these values locally using the
string operator & :

sql = "EXEC [ORD-qInsertOrderFromFile-1] " &
Forms.[ORD-FInsertOrderFromFile].[OrderID] & " ....

Don't forget to double any enclosed " into two double quotes "" and don't
forget any required blank space, to!

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Dimitris Nikolakakis said:

I have use in the button the following:
--------------------------------------------------------
Dim sql As String
sql = "EXEC [ORD-qInsertOrderFromFile-1]
Forms.[ORD-FInsertOrderFromFile].[OrderID],
Forms.[ORD-FInsertOrderFromFile].[ClientID]"
CurrentProject.Connection.Execute sql
--------------------------------------------------------
My SP is:
--------------------------------------------------------
CREATE PROCEDURE [ORD-qInsertOrderFromFile-1]
@OrderID nvarchar(40), @ClientID int
AS
INSERT INTO [ORD-Orders] (TypeID, OrderID, ClientID)
SELECT 'ORD', @OrderID, @ClientID
--------------------------------------------------------

When I press the button I get error:
--------------------------------------------------------
Run-time error '-2147217900 (80040e14)'
Incorrect Syntax near '.'

Dimitris

Ï "Sylvain Lafontaine said:

For inserting a record, you can call CurrentProject.Connection.Execute:

Dim sql as string
sql = "EXEC qINSERTORDER '" & [Forms]![FINSERTORDER ]![ORDERID] & "'"
CurrentProject.Connection.Execute (sql)

or you can open a recordset like previously and use the AddNew and
Update command:

...
cmdMenu.CommandType = adCmdText.
cmdMenu.CommandText = "Select * From Orders Where 1=0"
cmdMenu.AddNew
cmdMenu ("ORDERID") = [Forms]![FINSERTORDER ]![ORDERID]
cmdMenu.Update
...

If you want to get the identity value of the newly inserted record, read
it from the recordset after the Update command. Or course,
[Forms]![FINSERTORDER ]![ORDERID] can be replaced with Me.OrderID where
possible.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message To my knowledge, the InputParameters is only for the record source of a
bound form or the rowsource of a control on that form (bound or
unbound).

If you want to call a SP from a button, you have to use ADO objects;
something like:

Dim cmdMenu As ADODB.Command
Set cmdMenu = New ADODB.Command

Set cmdMenu.ActiveConnection = CurrentProject.Connection

cmdMenu.CommandType = adCmdStoredProc ' or: adCmdText.
cmdMenu.CommandText = "MyStoredProcedure"

cmdMenu.Parameters.Append cmdMenu.CreateParameter("@IdOrganisme", _
adInteger, adParamInput, , aIdOrganisme)

Dim rsMenu As ADODB.Recordset
Set rsMenu = New ADODB.Recordset

rsMenu.CursorLocation = adUseClient ' Default location:
adUseServer
rsMenu.Open cmdMenu, , adOpenStatic, adLockOptimistic

Do While Not rsMenu.EOF
Debug.Print rsMenu ("FirstField")
rsMenu.MoveNext
Loop

rsMenu.Close
set rsMenu = Nothing

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

I have a SP:

CREATE PROCEDURE qINSERTORDER @ORDERID nvarchar(40)
AS
INSERT INTO Orders (ORDERID)
SELECT @ORDERID

I have a form FINSERTORDER with a Field ORDERID and a button to call
the SP. In Input parameters I have:
ORDERID nvarchar(40) = [Forms]![FINSERTORDER ]![ORDERID].

When I press the button I am asked to enter parameter value for
ORDERID.

Thanks
Dimitris

Click to expand...

Click to expand...

Sylvain Lafontaine · Sep 15, 2006

Don't enclose integer values between quotes. For dates, you enclose them
between quotes like it is for the strings and not between # like it is
usually under Access.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Dimitris Nikolakakis said:
This worked! thanks

I have tried the SP with a second parameter: @ClientID int

In my code I have:

sql = "EXEC [ORD-qInsertOrderFromFile-1] '&
Forms.[ORD-FInsertOrderFromFile].[OrderID] &', '&
(Forms.[ORD-FInsertOrderFromFile].[Client]) &' "

And I get the error:

Error converting varchar to int.

I have tried many options with brackets etc.....but still problem.

thanks
Dimitris

Ï "Sylvain Lafontaine said:

You cannot send thing like « Forms.[ORD-FInsertOrderFromFile].[OrderID] »
to the SQL-Server. You must translate these values locally using the
string operator & :

sql = "EXEC [ORD-qInsertOrderFromFile-1] " &
Forms.[ORD-FInsertOrderFromFile].[OrderID] & " ....

Don't forget to double any enclosed " into two double quotes "" and don't
forget any required blank space, to!

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Dimitris Nikolakakis said:

I have use in the button the following:
--------------------------------------------------------
Dim sql As String
sql = "EXEC [ORD-qInsertOrderFromFile-1]
Forms.[ORD-FInsertOrderFromFile].[OrderID],
Forms.[ORD-FInsertOrderFromFile].[ClientID]"
CurrentProject.Connection.Execute sql
--------------------------------------------------------
My SP is:
--------------------------------------------------------
CREATE PROCEDURE [ORD-qInsertOrderFromFile-1]
@OrderID nvarchar(40), @ClientID int
AS
INSERT INTO [ORD-Orders] (TypeID, OrderID, ClientID)
SELECT 'ORD', @OrderID, @ClientID
--------------------------------------------------------

When I press the button I get error:
--------------------------------------------------------
Run-time error '-2147217900 (80040e14)'
Incorrect Syntax near '.'

Dimitris

Ï "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)> Ýãñáøå óôï ìÞíõìá For inserting a record, you can call CurrentProject.Connection.Execute:

Dim sql as string
sql = "EXEC qINSERTORDER '" & [Forms]![FINSERTORDER ]![ORDERID] & "'"
CurrentProject.Connection.Execute (sql)

or you can open a recordset like previously and use the AddNew and
Update command:

...
cmdMenu.CommandType = adCmdText.
cmdMenu.CommandText = "Select * From Orders Where 1=0"
cmdMenu.AddNew
cmdMenu ("ORDERID") = [Forms]![FINSERTORDER ]![ORDERID]
cmdMenu.Update
...

If you want to get the identity value of the newly inserted record,
read it from the recordset after the Update command. Or course,
[Forms]![FINSERTORDER ]![ORDERID] can be replaced with Me.OrderID where
possible.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message To my knowledge, the InputParameters is only for the record source of
a bound form or the rowsource of a control on that form (bound or
unbound).

If you want to call a SP from a button, you have to use ADO objects;
something like:

Dim cmdMenu As ADODB.Command
Set cmdMenu = New ADODB.Command

Set cmdMenu.ActiveConnection = CurrentProject.Connection

cmdMenu.CommandType = adCmdStoredProc ' or: adCmdText.
cmdMenu.CommandText = "MyStoredProcedure"

cmdMenu.Parameters.Append cmdMenu.CreateParameter("@IdOrganisme", _
adInteger, adParamInput, , aIdOrganisme)

Dim rsMenu As ADODB.Recordset
Set rsMenu = New ADODB.Recordset

rsMenu.CursorLocation = adUseClient ' Default location:
adUseServer
rsMenu.Open cmdMenu, , adOpenStatic, adLockOptimistic

Do While Not rsMenu.EOF
Debug.Print rsMenu ("FirstField")
rsMenu.MoveNext
Loop

rsMenu.Close
set rsMenu = Nothing

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

I have a SP:

CREATE PROCEDURE qINSERTORDER @ORDERID nvarchar(40)
AS
INSERT INTO Orders (ORDERID)
SELECT @ORDERID

I have a form FINSERTORDER with a Field ORDERID and a button to call
the SP. In Input parameters I have:
ORDERID nvarchar(40) = [Forms]![FINSERTORDER ]![ORDERID].

When I press the button I am asked to enter parameter value for
ORDERID.

Thanks
Dimitris

Click to expand...

Click to expand...

INSERT records

Dimitris Nikolakakis

Sylvain Lafontaine

Sylvain Lafontaine

Dimitris Nikolakakis

Sylvain Lafontaine

Malcolm Cook

aaron.kempf

Dimitris Nikolakakis

Sylvain Lafontaine