INSERT records

[Dimitris Nikolakakis]

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]

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]

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

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

 

[Dimitris Nikolakakis]

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

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

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

 

[Sylvain Lafontaine]

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

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

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

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

 

[Malcolm Cook]

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

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

 

[aaron.kempf]

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]

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

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

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

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

 

[Sylvain Lafontaine]

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

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

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

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