[dba-VB] [dba-SQLServer] [AccessD] ADO Control With Stored Procedure as Recordset

MartyConnelly martyconnelly at shaw.ca
Thu Jul 31 14:59:34 CDT 2003


This might work
Assuming you have an ADO Data control called Ado1 - you can put up two
input boxes and get the params you want - set the CommandType and
RecordSource properties of the ADO Data control in
code by which you pass the parameters to the SQL stored procedure.  Make
sure that you put some dummy parameters in for the RecordSource property in
design mode which you can null out in code before setting the RecordSource
property again.

    Dim intCustomers As Integer
    Dim lngPeriod As Long
    intCustomers = InputBox("Customer :", "Enter a Value")
    lngPeriod = InputBox("Date YYYYMMDD:", "Enter Date")
    strRecordSource = "MyProcData;1(" & intCustomers & "," & lngPeriod & ")"
    'watch wrap and spaces
    Ado1.ConnectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=DatabaseName;Data
Source=YourMachineName"
    Ado1.ConnectionTimeout = 30
    Ado1.CommandType = adCmdStoredProc
    Ado1.RecordSource = ""
    Ado1.RecordSource = strRecordSource
    Ado1.Refresh

paul.hartland at fsmail.net wrote:

>To all,
>
>I have a SQL Server 7.0 BE, with a Visual Basic 6 FE.  In SQl Server 7.0 I have a stored procedure which requires two parameters (Payroll, JobDate) and this returns all the jobs that a person is programmed to do.  On my VB6 FE I have a form with a DataGrid and an ADO Control, with the Datagrid pointing to the ADO Control for it's datasource.  When the form opens I want to pass the payroll and jobdate to the SQL Server stored procedure and return the results to the ADO Control which will then be displayed in the DataGrid.
>
>Is there anyone out there that can tell me how to do this, or has some sample code that I could look at to show me exactly how this can be acheived.
>
>Thanks in advance.
>
>Paul Hartland
>
>__________________________________________________________________________
>Join Freeserve http://www.freeserve.com/time/
>
>Winner of the 2003 Internet Service Providers' Association awards for Best Unmetered ISP and Best Consumer Application.
>
>
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>_______________________________________________
>dba-VB mailing list
>dba-VB at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-vb
>http://www.databaseadvisors.com
>
>
>  
>




More information about the dba-SQLServer mailing list