473,574 Members | 2,914 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Inserting data into Access form from SQL Stored Procedure

I am attempting to create an Access database which uses forms to enter
data. The issue I am having is returning the query results from the
Stored Procedure back in to the Access Form.

tCetecM1CUST (SQL Table that contains the Customer Information)
tAccountingDeta il (SQL Table that contains the information in the
form)
frmAccountingEn try (Access form used to enter data)
spGetCustomerIn formation (Stored Procedure which returns data using
variable CUSTOMER_NUMBER entered in the Access.)

Scenario is this. Open form, Enter 'Job Number' and 'Customer Number',
form uses 'AfterUpdate' to run this...

Private Sub CUSTOMER_NUMBER _AfterUpdate()
Set gcn = Nothing
Dim sConnect As String
sConnect = "PROVIDER=SQLOL EDB.1;INTEGRATE D SECURITY=SSPI;P ERSIST
SECURITY INFO=FALSE;INIT IAL CATALOG=x;DATA SOURCE=x"
Set gcn = New ADODB.Connectio n
gcn.CursorLocat ion = adUseClient
gcn.Open sConnect

'On Error GoTo ExitProcedure
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
'Call doConnect

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

With cmd
.CommandText = "spGetCustomerI nformation"
.CommandType = adCmdStoredProc
.Parameters.App end .CreateParamete r("@CUSTOMER_NU MBER", adVarChar,
adParamInput, 6, Forms!frmAccoun tingEntry!CUSTO MER_NUMBER.Valu e)
Set .ActiveConnecti on = gcn
End With

Set rs = New ADODB.Recordset
rs.CursorLocati on = adUseServer
rs.Open cmd, , adOpenStatic, adLockReadOnly
Set rs = cmd.Execute
If Not (rs.EOF And rs.BOF) Then
MaybeMatch = True
Else
MaybeMatch = False
End If

ExitProcedure:
On Error Resume Next
Set rs = Nothing

End Sub

This passes the variable CUSTOMER_NUMBER which is located in the
Access form to the Stored Procedure which is here..

CREATE PROCEDURE dbo.spGetCustom erInformation
(@CUSTOMER_NUMB ER varchar(6))
AS
SELECT CUSTOMER_NUMBER , CUSTOMER_NAME, ADDRESS_1, ADDRESS_2,
ADDRESS_3, ADDRESS_4, SHIP_ADDRESS_1, SHIP_ADDRESS_2, SHIP_ADDRESS_3,
SHIP_ADDRESS_4
FROM dbo.tCetecM1CUS T
WHERE (CUSTOMER_NUMBE R = @CUSTOMER_NUMBE R)

Which then does nothing as far as returning the data to the current
form. I can run the stored procedure in Access and a Message Box will
come up prompting me to enter the 'CUSTOMER_NUMBE R'. If the number
entered matches a record, then the record is displayed.

So, what am I missing here? I feel like there must be another piece of
code that puts the data back into the current record or form.

Thanks to anyone out there who has a suggestion.

-Josh
Nov 12 '05 #1
2 11681
Josh Strickland wrote:
I am attempting to create an Access database which uses forms to enter
data. The issue I am having is returning the query results from the
Stored Procedure back in to the Access Form.

tCetecM1CUST (SQL Table that contains the Customer Information)
tAccountingDeta il (SQL Table that contains the information in the
form)
frmAccountingEn try (Access form used to enter data)
spGetCustomerIn formation (Stored Procedure which returns data using
variable CUSTOMER_NUMBER entered in the Access.)

Scenario is this. Open form, Enter 'Job Number' and 'Customer Number',
form uses 'AfterUpdate' to run this...

Private Sub CUSTOMER_NUMBER _AfterUpdate()
Set gcn = Nothing
Dim sConnect As String
sConnect = "PROVIDER=SQLOL EDB.1;INTEGRATE D SECURITY=SSPI;P ERSIST
SECURITY INFO=FALSE;INIT IAL CATALOG=x;DATA SOURCE=x"
Set gcn = New ADODB.Connectio n
gcn.CursorLocat ion = adUseClient
gcn.Open sConnect

'On Error GoTo ExitProcedure
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
'Call doConnect

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

With cmd
.CommandText = "spGetCustomerI nformation"
.CommandType = adCmdStoredProc
.Parameters.App end .CreateParamete r("@CUSTOMER_NU MBER", adVarChar,
adParamInput, 6, Forms!frmAccoun tingEntry!CUSTO MER_NUMBER.Valu e)
Set .ActiveConnecti on = gcn
End With

Set rs = New ADODB.Recordset
rs.CursorLocati on = adUseServer
rs.Open cmd, , adOpenStatic, adLockReadOnly
Set rs = cmd.Execute
If Not (rs.EOF And rs.BOF) Then
MaybeMatch = True
Else
MaybeMatch = False
End If

ExitProcedure:
On Error Resume Next
Set rs = Nothing

End Sub

This passes the variable CUSTOMER_NUMBER which is located in the
Access form to the Stored Procedure which is here..

CREATE PROCEDURE dbo.spGetCustom erInformation
(@CUSTOMER_NUMB ER varchar(6))
AS
SELECT CUSTOMER_NUMBER , CUSTOMER_NAME, ADDRESS_1, ADDRESS_2,
ADDRESS_3, ADDRESS_4, SHIP_ADDRESS_1, SHIP_ADDRESS_2, SHIP_ADDRESS_3,
SHIP_ADDRESS_4
FROM dbo.tCetecM1CUS T
WHERE (CUSTOMER_NUMBE R = @CUSTOMER_NUMBE R)

Which then does nothing as far as returning the data to the current
form. I can run the stored procedure in Access and a Message Box will
come up prompting me to enter the 'CUSTOMER_NUMBE R'. If the number
entered matches a record, then the record is displayed.

So, what am I missing here? I feel like there must be another piece of
code that puts the data back into the current record or form.

Thanks to anyone out there who has a suggestion.

-Josh

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I don't work w/ ADO - mainly 'cuz I can get the info I need in DAO in
about 5 lines of code where ADO requires 10-15 lines. Anyway, . . .
I'll assume you're using Access 2000, or Access XP; can't you set the
form's Recordset to the recordset returned by the stored procedure?
E.g.:

Set Me.Recordset = rs

Here's some code from Acc XP Help on "Recordset Property."

Global rstSuppliers As ADODB.Recordset
Sub MakeRW()
DoCmd.OpenForm "Suppliers"
Set rstSuppliers = New ADODB.Recordset
rstSuppliers.Cu rsorLocation = adUseClient
rstSuppliers.Op en "Select * From Suppliers", _
CurrentProject. Connection, adOpenKeyset, adLockOptimisti c

' See - this line sets the form's recordset...::: mgf
Set Forms("Supplier s").Recordse t = rstSuppliers

' Not sure why this line is needed, but it works...:::mgf
Forms("Supplier s").UniqueTa ble = "Suppliers"

End Sub

HTH,
- --
MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP4dfQYechKq OuFEgEQKQGwCdHA 2DZvZUlq7C82CXf TdxrysnsNwAn2vd
AQO2Wudr+5FPVuV 37Fangt8/
=Rb4i
-----END PGP SIGNATURE-----

Nov 12 '05 #2
"Josh Strickland" <js*********@bt celectronics.co m> wrote in message
news:ad******** *************** ***@posting.goo gle.com...
I am attempting to create an Access database which uses forms to enter
data. The issue I am having is returning the query results from the
Stored Procedure back in to the Access Form.

tCetecM1CUST (SQL Table that contains the Customer Information)
tAccountingDeta il (SQL Table that contains the information in the
form)
frmAccountingEn try (Access form used to enter data)
spGetCustomerIn formation (Stored Procedure which returns data using
variable CUSTOMER_NUMBER entered in the Access.)

Scenario is this. Open form, Enter 'Job Number' and 'Customer Number',
form uses 'AfterUpdate' to run this...

Private Sub CUSTOMER_NUMBER _AfterUpdate()
Set gcn = Nothing
Dim sConnect As String
sConnect = "PROVIDER=SQLOL EDB.1;INTEGRATE D SECURITY=SSPI;P ERSIST
SECURITY INFO=FALSE;INIT IAL CATALOG=x;DATA SOURCE=x"
Set gcn = New ADODB.Connectio n
gcn.CursorLocat ion = adUseClient
gcn.Open sConnect

'On Error GoTo ExitProcedure
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
'Call doConnect

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

With cmd
.CommandText = "spGetCustomerI nformation"
.CommandType = adCmdStoredProc
.Parameters.App end .CreateParamete r("@CUSTOMER_NU MBER", adVarChar,
adParamInput, 6, Forms!frmAccoun tingEntry!CUSTO MER_NUMBER.Valu e)
Set .ActiveConnecti on = gcn
End With

Set rs = New ADODB.Recordset
rs.CursorLocati on = adUseServer
rs.Open cmd, , adOpenStatic, adLockReadOnly
Set rs = cmd.Execute
If Not (rs.EOF And rs.BOF) Then
MaybeMatch = True
Else
MaybeMatch = False
End If

ExitProcedure:
On Error Resume Next
Set rs = Nothing

End Sub

This passes the variable CUSTOMER_NUMBER which is located in the
Access form to the Stored Procedure which is here..

CREATE PROCEDURE dbo.spGetCustom erInformation
(@CUSTOMER_NUMB ER varchar(6))
AS
SELECT CUSTOMER_NUMBER , CUSTOMER_NAME, ADDRESS_1, ADDRESS_2,
ADDRESS_3, ADDRESS_4, SHIP_ADDRESS_1, SHIP_ADDRESS_2, SHIP_ADDRESS_3,
SHIP_ADDRESS_4
FROM dbo.tCetecM1CUS T
WHERE (CUSTOMER_NUMBE R = @CUSTOMER_NUMBE R)

Which then does nothing as far as returning the data to the current
form. I can run the stored procedure in Access and a Message Box will
come up prompting me to enter the 'CUSTOMER_NUMBE R'. If the number
entered matches a record, then the record is displayed.

So, what am I missing here? I feel like there must be another piece of
code that puts the data back into the current record or form.

Thanks to anyone out there who has a suggestion.

-Josh
It's a bit hard to see exactly where it's going wrong but I would check here
first:
rs.Open cmd, , adOpenStatic, adLockReadOnly
Set rs = cmd.Execute


You've already set rs, then you open it, then you re-set it? Why?

As you step through your code, can you check this is giving the value you
expect, before passing it to the stored procedure:

Forms!frmAccoun tingEntry!CUSTO MER_NUMBER.Valu e

Also what about some more general error handling for the sub and why is
there no dim statement for MaybeMatch and why does it not follow a naming
convention.
Just some ideas

Fletcher
Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
5376
by: sedefo | last post by:
I ran into this Microsoft Patterns & Practices Enterprise Library while i was researching how i can write a database independent data access layer. In my company we already use Data Access Application Block (DAAB) in our .Net projects. We use SqlHelper in SQL based projects, and OracleHelper in Oracle based ones. OracleHelper was not...
0
1158
by: Sal | last post by:
I just had a C# conversion project thrown at me, and must bind several text boxes on a form to different columns from a table. We have to use stored procedures that are called through data layer (csData) from the form (frmPackingList). I know it can be done with a datagrid, but simple binding rules do not cover what I'm trying to do. I know...
5
2183
by: hfk0 | last post by:
Hi, I'm new to ASP.net, SQL Server and visual studio.net, and I'm having problem inserting and storing data from a web form to a SQL database. I created a simple ASP.NET web form, a simple SQL database, a database connection (using the SQlDataSource Web Control from the Toolbox), and created the following stored procedure in Visual...
7
3439
by: Dabbler | last post by:
I'm using an ObjectDataSource with a stored procedure and am getting the following error when trying to update (ExecuteNonQuery): System.Data.SqlClient.SqlException: Procedure or Function 'UpdateRegistrant' expects parameter '@EMail', which was not supplied. The field value was null in the database and not changed in the FormView so is...
2
1719
by: hooterbite | last post by:
I have a simple form. I would like to insert the values from the form into a SQL table. What is the best way to do it? I assume that using a stored procedure is preferable to using the UpdateCommand="Insert into..." When using a stored procedure, is it better to use a SqlDataSource or an ObjectDataSource? Is it better to make it formview and...
11
4357
by: c676228 | last post by:
Hi everyone, I am just wodering in asp program, if there is anybody writing store procedure for inserting data into database since there are so many parameters need to be passed into store procedure(assume there are many columns in the table). I need to insert data into two separate tables, the relation between these two tables is 1 row of...
4
2265
by: Dia | last post by:
Hi there, I struggle to get this going i would like to insert data into 2 tmp tables in a view. If i run the code on it's own it works perfectly until i want to create a view it complains about the INSERT this is my code
6
6610
by: binky | last post by:
Hi folks, I'm just learning how to use MS Access as a front end for SQL Server, and have a question: I have a stored procedure that returns a set of records from a SQL Server and loads it into a form in my Access application. This works as intended, but I'm having trouble trying to figure out how to modify records on the form... whenever I try...
2
2627
by: cluce | last post by:
I am trying to read a csv file with user info (username, password, email, address, city, zip, state, etc.) I am inserting the username, password, email into the aspnet_memberhsip table using the membership class and trying to insert the rest of the related info with a stored procedure into the custom table I created called aspnet_UserInfo using a...
0
7805
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7726
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8237
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
8098
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6454
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5622
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3741
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2245
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1341
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.