Stored Procedure
How would I execute a stored procedure on my SQL server which accpepts a parameter, and returns a recordset. (I have written the stored proc, but am unsure how to call it in VB)
thanks
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
How To Pass An Array To An IN Parameter Of Stored Procedure(of Oracle) When This Stored Procedure Is
first of all thanks for all the answers you guys are sending.they are really helping me in my project.here comes another problem.
i have this stored procedure in oracle
CREATE OR REPLACE PROCEDURE VARRAY_TEST
(SUBJECT_IN IN SUBJECT_TYPE)
IS
I NUMBER ;
BEGIN
FOR I IN SUBJECT_IN.FIRST..SUBJECT_IN.LAST LOOP
INSERT INTO empl VALUES(SUBJECT_IN(I));
END LOOP;
END;
where SUBJECT_TYPE is a varray declared using the following statement:
CREATE OR REPLACE TYPE SUBJECT_TYPE AS VARRAY(10) OF VARCHAR2(10);
and empl is a table name that exists in the database.
i want to call this stored procedure from VB ,passing the values to IN Parameter SUBJECT_IN.
how can i do that.
i tried this code :
Private Sub Command4_Click()
Dim passarray(9) As String
Dim cmdarr As New ADODB.Command
Dim pararr As New ADODB.Parameter
Dim i As Integer
For i = 0 To 9
passarray(i) = "maths"
Next i
Set cmdarr = New ADODB.Command
cmdarr.ActiveConnection = goConn
cmdarr.CommandText = "varray_test"
cmdarr.CommandType = adCmdText
Set pararr = New ADODB.Parameter
pararr.Name = "passarray"
pararr.Direction = adParamInput
pararr.Type = adArray
pararr.Size = 10
pararr.Value = passarray
cmdarr.Parameters.Append pararr
cmdarr.Execute
End Sub
but it doesnt work.gives an error message which says " Arguments are of the wrong type,are out of acceptable range,or are in conflict with one another."
please also try to a sample code.
thanks
regards
Sumit Gulati
Recordset Form Stored Procedure That Call Other Stored Procedure
Hi
I call a Stored Procedure from VB that return a recordsets and values:
select field1,field2 from mytables
return (value)
But It call other stored procedure that too return
recordset and value
How do I do to received this in the recordset in the VB, When I look in my recordset , It is with value of the internal procedure:
example
in my vb code I call procedure sp_se_ag06, this procedure call the procedure sp_ge_ag15 that return recordsets and value, when I look in my recordset It is with value of the procedure sp_ge_ag15, and not with values of the procedure sp_se_ag06, that I want. I tried rs.nextrecordset, but do not work
thank you in advance
Stored Procedure - Series Of Check In One Stored Procedure
I am developing the fixed asset module. I would be happy if some one could help me out with stored procedure.
I am using Visual basic 2005 w/SQL Server express edition.
Before loading the Master asset form I would like to check the in one stored procedure the record counts in Asset group table, Transaction type table, Master Supplier, Master VAT table andto result in the stored procedure variables and then further to VB variable.
I would be happy if some one could help me out ASAP.
Thanks in advance
SathyanCK
Stored Procedure 101
I'm new to stored procedures, so this may seem really basic.
In SQL Server 2K, how do I create a Proc that will just move all the records from one table to another that fall between id #s of say 1-80? I can't seem to find anything online that is like a basic explanation and steps to start with.
Thanks!
Bryan
Stored Procedure??
:wall:
I have a table that contains around 50k rows. Lets say in that table I have 3 columns, A,B, and C. I want a stored procedure that will go row by row throught he table and take the values that are in columns A and B do a calculation and store the result in column C.
A B C
6 / 2 = 3
Can this be done with a stored procedure???
Stored Procedure
How do i get a formula to return DAYOFWEEK in access from a data/time field.
Stored Procedure
Hello there,
i am looking for a stored procedure for use in access 2000 to return the date from a date/time field.
ADO Stored Procedure Add-in
Hi
In my never ending search for more information on writing and calling Stored Procedures I came across this handy little add-in. Although it does not do anything earth-shattering it is a nice add-in for VB which could save you a bit of time when calling a stored procedure. (especially if you are new to it )
The source Code is also included.
Many of you probably came across it before but Here it is again anyway for people who did not know about it.
Shane
Stored Procedure
I have a stored procedure which has user-defined error messages in. I want these errors return to my Visual Basic application. Can anyone point me in the right direction to let me know where I might be going wrong? I'm using Sybase as my database.
How To Run Stored Procedure Thru VB
Hi,
I have created a stored procedure in SQL Server
named "details_proc". I want to run this stored
procedure from my VB App.. I have used a data
environment and added this stored procedure
to it.
But when I run this stored procedure using
---------------------------------------
DataEnvironment1.dbo_details_proc
---------------------------------------
no result is displayed.
Also if I want to display the result of the stored
procedure in a text box, how to do that ?
Rohit.
Stored Procedure Help
I'm having trouble with this stored procedure. I'm trying to get a cumulative count on several queries using the UNION statement. It works great until I slide in the @outparam... then it errors on me:
"ADO error: Only the first query in a UNION statement can have a SELECT with an assignment"
It seems as if it is a violation include @outparam in more than one select statement? Or perhaps I'm going about this wrong?
Code:
Alter Procedure sp_BIO_Count
(
@outparm int OUTPUT,
)
AS
SELECT @outparam=COUNT(*)
From blah blah blah...
UNION
SELECT @outparam=COUNT(*)
From blah blah blah
UNION
SELECT @outparam=COUNT(*)
From blah blah blah
Any advice???
diver
Stored Procedure
Hello folks. Having a little trouble with a .dll and I don't know what the problem is. I am new to this stuff, so I am probably looking over something really simple. I appreciate any help.
dave
Here is the code. I am calling a query in an Access database which uses a parameter(strMessageID) to get a single value from the database, which I am storing in UpdateDB. I am not too sure about the "createparameter" part of the code...probably is part of the problem.
Public Function UpdateDB(ByVal strDbConnectionString As String, ByVal strSQL As String, ByVal strMessageID As String) As Variant
Dim oRs As New ADODB.Recordset
Dim oCmd As New ADODB.Command
Dim oConn As New ADODB.Connection
Dim Parm As Parameter
oConn.Open strDbConnectionString
oCmd.CommandText = strSQL
oCmd.CommandType = adCmdStoredProc
Set oCmd.ActiveConnection = oConn
Set Parm = oCmd.CreateParameter("variableID", adIUnknown, adParamInput, Len(strMessageID), strMessageID)
Param1.Value = strMessageID
Call oCmd.Parameters.Append(Parm)
Set Parm = Nothing
Set oRs = oCmd.Execute()
UpdateDB = oRs.getRows
oRs.Close
oConn.Close
Set oRs = Nothing
Set oCmd = Nothing
Set oConn = Nothing
End Function
I get the error message below. I am running this as a .dll from an ASP page.
Error Type:
ADODB.Command (0x800A0D5D)
Application uses a value of the wrong type for the current operation.
Stored Procedure
How can i call the SQL SERVER stored procedure in visual basic? I dont know syntax on how to call a stored procedure and the variable definition.
Stored Procedure
Does anybody have idea how I can transform this Access query into SQL Server procedure?
UPDATE Inventory INNER JOIN POSInvoiceLineitems ON [Inventory].[Item Number]=[POSInvoiceLineitems].[Item Number] SET Inventory.[Quantity in Stock] = [Inventory].[Quantity in Stock]-[POSInvoiceLineitems].[Quantity]
WHERE ((([POSInvoiceLineitems].[Invoice Number])=[Forms]![Form1]![Text8]));
Stored Procedure
Hi,
I have a problem with a VB 6.0 application that I am developing that loads txt files into a SQL Server 2000 database. I am having a problem with a stored procedure called ProcessBCP that fails on execute:
With dbcommand
.ActiveConnection = oConn
.CommandType = ADODB.adCmdStoredProc
.Parameters.Append .CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 0)
.CommandText = "ProcessBCP"
.Execute , , adExecuteNoRecords
End With
A description of the error is not very helpful:
Err.Description = Unspecified Error
Err.Number = #-2147467259
Err.LastDllError = #0
Err.Source = #Microsoft OLE DB Provider for SQL Server
dbcommand(0) is null
The stored procedure is:
CREATE PROCEDURE ProcessBCP
AS
BEGIN
set nocount on
set rowcount 1
DECLARE bcp_in_cursor CURSOR GLOBAL FOR
SELECT * FROM bcp_in WHERE RecordType = '1'
OPEN bcp_in_cursor
FETCH NEXT FROM bcp_in_cursor
WHILE @@FETCH_STATUS = 0
BEGIN
exec UpdateHQTable
delete bcp_in where current of bcp_in_cursor
FETCH NEXT FROM bcp_in_cursor
END
CLOSE bcp_in_cursor
DEALLOCATE GLOBAL bcp_in_cursor
END
GO
If I run the VB code up to the call to the stored procedure, then execute the stored procedure in the Query Analyzer, it works perfectly.
The application successfully executes 3 other stored procedures before this one. I can see that the bcp_in table has appropriate records including those with RecordType = '1'.
I would be grateful of any help.
Stored Procedure Please Help
Hi I am trying to get this stored procedure working but no matter what I do it gives an error of Error converting data type varchar to datetime.
I cannot find where the mistake is can someone please help... Here is the stored procedure. This is driving me crazy
<VB>
CREATE PROCEDURE [acSearchDate]
@from as datetime,
@to as datetime
AS
declare @datefrom as datetime,
@dateto as datetime
set @datefrom = convert(datetime,(convert(datetime,@from,103)))
set @dateto = convert(datetime,(convert(datetime,@to,103)))
--Load exchanges that have new in them
SELECT distinct o.exchid
FROM baOrders o, baexch e,baregion r
WHERE o.exchid=e.exchid and e.regid=r.regid
AND o.exchid <> ''
AND o.status > ''
AND o.strname <> ''
AND (o.status IN ('20', '60') OR
(o.source = 'R' AND o.status <> 'SFIN'))
AND o.tcd between convert(datetime,(convert(datetime,@datefrom,103)))
AND convert(datetime,(convert(datetime,@dateto,103)))
AND o.cidn NOT IN
(SELECT cidn
FROM cidnexcl)
AND r.name ='Vic Country'
AND o.exchid+o.strname in (SELECT DISTINCT o.exchid+o.strname
FROM baorders o, baorderbundle ob, baregion r, baexch e
WHERE o.ordnr = ob.ordnr AND e.regid = r.regid AND o.exchid = e.exchid AND (o.status IN ('20', '60') OR
(o.source = 'R' AND o.status <> 'SFIN')) AND o.status > ' ' AND o.exchid <> '' AND o.strname <> ''
AND o.tcd between convert(datetime,(convert(datetime,@datefrom,103)))
AND convert(datetime,(convert(datetime,@dateto,103)))
AND o.cidn NOT IN
(SELECT cidn
FROM cidnexcl)
GROUP BY o.exchid+o.strname
HAVING COUNT(DISTINCT LEFT(o.ordnr, 10)) >= 1)
GROUP BY o.ExchID,o.strname
HAVING (COUNT(DISTINCT LEFT(o.OrdNr, 10)) >= 2)
ORDER BY O.EXCHID
GO
</VB>
Stored Procedure Need Help
I have the table customer which has
CustID,FirstName, LastName,InitialName
CustID is primary key, the initial name = first character of Firstname+ first character of last name and haven't populate yet
I want to create the store procedure that generate the initial name automatically for each customer.
How can i do that some one please help?
Thank you
Stored Procedure
I'd like to create a Stored Procedure with SQL-Server 2000
the complete Where-clause should be a variable, but i get an error message from the sql server. how is the syntax to do this?
eg: Select * From Table Where @sWhere
Stored Procedure
Hello,
can anyone help, i want a stored procedure to run at the start up of my program. i want to pass into the procedure the users network id which will be a user setup in the sql db.
what i want the query to do is create a veiw using this parameter as the schema name.
i have this so far but it does not work.
Code:
CREATE PROCEDURE [dbo].[sp_Tri_CreateMyViews] @NetworkID nvarchar(20) AS
CREATE VIEW @NetworkID.[Tri_ViewClinicPrep]
AS
SELECT TOP (100) PERCENT dbo.Tri_Booking.Clinic, dbo.Tri_Booking.Slots, dbo.Tri_Booking.DateBooked, ISNULL(dbo.Tri_Booking.Comments, N'')
AS Comments, ISNULL(dbo.Tri_Booking.BookedBy, N'') AS BookedBy, ISNULL(dbo.Tri_Booking.UrgencyID, N'') AS Urgency,
dbo.Tri_Booking.ProcedureID, dbo.Tri_ClinicTypes.Description AS ClinicName, dbo.Tri_ClinicProcedures.Description AS [Procedure],
dbo.Tri_Clinic_New.date AS ClinicDate, ISNULL(dbo.Tri_PatientDetails.FORENAME, N'Anonymous') + ' ' + ISNULL(dbo.Tri_PatientDetails.SURNAME,
N'') AS Patient, dbo.Tri_Booking.PatientID, dbo.Tri_Booking.[B-id], dbo.Tri_PatientDetails.DOB, dbo.Tri_PatientDetails.Unit_No,
dbo.Tri_Booking.BookedTime, dbo.Tri_Booking.PrintComments, dbo.Tri_ClinicProcedures.Leaflet, dbo.Tri_Clinic_New.PasCode,
dbo.Tri_Booking.OnWarfarin, dbo.Tri_Booking.OnClopidrogel, dbo.Tri_Booking.OnAspirin, dbo.Tri_Booking.OnInsulin,
dbo.Tri_Booking.OnOralHypoglycaemics, dbo.Tri_Booking.RenalImpairment, dbo.Tri_Booking.PrepSent, dbo.Tri_ClinicProcedures.Prep AS PrepNeeded,
dbo.Tri_Clinic_New.Location, dbo.Tri_ClinicTypes.ID AS ClinicTypeID
FROM dbo.Tri_Booking INNER JOIN
dbo.Tri_Clinic_New ON dbo.Tri_Booking.Clinic = dbo.Tri_Clinic_New.C_NO INNER JOIN
dbo.Tri_ClinicTypes ON dbo.Tri_Clinic_New.[Clinic Type] = dbo.Tri_ClinicTypes.ID LEFT OUTER JOIN
dbo.Tri_ClinicProcedures ON dbo.Tri_Booking.ProcedureID = dbo.Tri_ClinicProcedures.ID LEFT OUTER JOIN
dbo.Tri_PatientDetails ON dbo.Tri_Booking.PatientID = dbo.Tri_PatientDetails.PatientID
WHERE (dbo.Tri_Clinic_New.Deleted = 0) AND (dbo.Tri_Booking.Deleted = 1) AND (dbo.Tri_Booking.PrepSent = 0) AND (dbo.Tri_Clinic_New.date >= GETDATE())
AND (dbo.Tri_ClinicProcedures.Prep = 1)
ORDER BY ClinicDate, dbo.Tri_Booking.ProcedureID DESC
do i need to declare something as a schema ?
any advice ?
Get Stored Procedure
i am using sql server 2005 express edition with
visual basic 6.0
How Can i Get Stored Procedure Code In Vb from sql server.
Stored Procedure In VB.6
Hi Guys! How do i call a stored Procedure from my Database in VB.6?
This is my database connection and current Recordset
Code:
Set mRecords = New ADODB.Recordset
Set mRecords.ActiveConnection = mConnection
mRecords.CursorLocation = adUseServer
mRecords.CursorType = adOpenKeyset
mRecords.LockType = adLockOptimistic
mRecords.Open "SELECT * FROM Customers ORDER BY ACCOUNTNO;"
Stored Procedure Help
I need to run a stored procedure from my vb app. The only thing i know about them is that it's a script that runs on the server? I would love any info you could give me that would help me understand them better (so i can add it to my app eventually)
Stored Procedure
hello, I am trying to call a stored procedure to see if a file exists, I have no clue how to call a stored procedure and get variables from it. I can do it with a sequal statement is it anything like that?
ADO And Stored Procedure
I am a little bit surprised how slow stored procedure works.
I had regular INSERT TO query which I sent with ADO CommandText to transfer data on sql server 2000 from table to table and
I was thinking will be faster if I create procedure and transfer data from table to table by ADO and stored procedure.
It working much slower with stored procedure. I measured ...4 seconds with query, 15 seconds with stored procedure ..
Is there something else I need to add to stored procedure ....
Here is my procedure ...
CREATE PROCEDURE Prodaja_Rijec_04
@date_from datetime,
@date_to datetime,
@trazilica nvarchar(30),
@ReciNum integer
AS
SET NOCOUNT ON
DECLARE @datum_od datetime
DECLARE @datum_do datetime
DECLARE @srchalica nvarchar(30)
DECLARE @recko integer
SET @datum_od = convert(varchar,@date_from,101)
SET @datum_do = convert(varchar,@date_to,101)
SET @srchalica = '%'+ @trazilica + '%'
SET @recko = @ReciNum
INSERT INTO arhPOSInvoiceItems_Sales ( [Description], [Item Number], Quantity, [UPC Code], [Quantity In Stock], [Entry Date],RepID )
SELECT arhPOSInvoiceItems_2004.[Description], arhPOSInvoiceItems_2004.[Item Number],
Sum(arhPOSInvoiceItems_2004.Quantity) AS [SumOfQuantity], Inventory.[UPC Code], Inventory.[Quantity in Stock], Inventory.PoljeTri, @recko
FROM arhPOSInvoiceItems_2004
INNER JOIN Inventory
ON arhPOSInvoiceItems_2004.[Item Number] = Inventory.[Item Number]
WHERE arhPOSInvoiceItems_2004.[Ship Date] Between @datum_od And @datum_do And arhPOSInvoiceItems_2004.[Description] Like @srchalica
GROUP BY arhPOSInvoiceItems_2004.[Description], arhPOSInvoiceItems_2004.[Item Number], Inventory.[UPC Code], Inventory.[Quantity in Stock], Inventory.PoljeTri
GO
Stored Procedure
Hello
I try to create table in SQL 7 with Stored procedure
Code:
CREATE PROCEDURE [CreateDataTable]
@FileName varchar (50),
@FieldName varchar(50),
@FieldNameValue varchar(10),
@CreateDataTable varchar(50)
AS
SELECT @FileName .* INTO @CreateDataTable FROM @FileName WHERE @FieldNames= @FieldNameValue
I call this from the Function
But this give me error: Invalid object name '@FileName'
Code:
Public Function CreateDataTable(filename As String, FieldNames As String, FieldNameValue As Integer) As String
connection.Execute ("CreateDataTable @FileName=" & filename & ", @FieldName=" & filename & ", @FieldNameValue=" & FieldNameValue & ", @CreateDataTable=" & CreateDataTable & " ")
any suggestions?
Thank you
RL
Stored Procedure From Vb6
Hello Guys
I need an example of how to execute a (Oracle) stored procedure from vb6 code and get the results into a recordset.
Can anyone help me please
Thanks in Advance
Stored Procedure In VB6.0
hi all,
last week i attended one interview. there i have been asked this question.."shall we use stored procedure in VB6.0?
i need the answer
-keyares
Stored Procedure
Just want to confirm something about stored procedures wih parameters..... do the parameters ahve to be in any order???? (do I have initalise them in the order I call them as parameters when inserting into table) When creating the parameters in VB, do I need to create them in the same order as they were created in the stored procedure........
thanks in advance!
btw im using SQL Server 2000, wiv vb6 with ado connection!
Stored Procedure
Im not sure if this the correct forum but anyway...
i'm am rewriting some update code which is used to update several tables in my sql server 2000 database, and am considering using stored procedure as I have read they help to improve performance (and I might require introduce some more security which can be done using stored procedure...)
I am very new to programming…..
1)when writing the sp can I write 1 stored procedure to update all tables, or is that not possible since some parameters have differernt names in the different tables??? And should all tables be updated using stored procedure..
SQL Stored Procedure
Hi,
I recently found an example of code to retrieve the ID of the last entered item in a database (when entered using a multiple-user program):
http://www.dotnetbips.com/displayarticle.aspx?id=235
My problem is how would I implement this within VB, preferebly using 'dataenvironment1.connection1.execute SQL Code', and then returning the ID to a variable.
Thanks in advance.
VB & Stored Procedure.
I have a stored procedure and need to execute it from my VB program. But what i found is I execute the stored procedure from query analyser is faster than I execute the stored procedure from my VB program.
Is this normal???
Stored Procedure
Can someone here please give me an example of how to use a stored procedure in Oracel that would return a string?
We here at work are tossing around the idea od using stored procedures and I really have no clue as how to use one in my code..
Thanks for the tip,
Rudy
Stored Procedure
I have a test stored procedure in sql server. For eg
CREATE PROCEDURE SP_test @sum varchar(10) output
As
BEGIN
select @sum = '1000'
end
go
Please let me know the syntax, how do I get its return
value using ADO command object in VB6
Stored Procedure
I have posted code of my stored procedure that perform add new department and I have 2 problem about it:
1. I use smallint for DepartmentName column and set identity(1, 1). When I delete record, I found that SQL Server generate new number that is next from last. I want SQL Server find gap between record. In SQL Book Online has some code that they said that it use to solve this problem but it doesn't work or I may not know how to use it (Commented code at bottom).
2.This stored procedure will return DepartmentID that SQL Server has generate and I don't know how to use ADODB.Recordset or ADODB.Connection to get this value. What should I do?
Thank you,
Here is code:
IF EXISTS (SELECTname
FROMsysobjects
WHEREname = 'AddDepartment' AND type = 'P')
DROP PROCEDURE AddDepartment
GO
CREATE PROCEDURE AddDepartment (@DepartmentName nvarchar(256)) AS
DECLARE @DepartmentID smallint
SET @DepartmentName = REPLACE(@DepartmentName, '''', '''''')
IF NOT EXISTS (SELECTDepartmentID
FROMDepartments
WHEREDepartmentName = @DepartmentName)
BEGIN
SET @DepartmentName = UPPER(@DepartmentName)
INSERT INTO tblDepartments(DepartmentName)
VALUES(@DepartmentName)
SELECTDepartmentID
FROMDepartments
WHEREDepartmentName = @DepartmentName
RETURN
END
ELSE
BEGIN
RAISERROR (50001, 16, 1, @DepartmentName)
RETURN(-1)
END
/*
SET IDENTITY_INSERT tblDepartments ON
DECLARE @minidentval smallint
DECLARE @nextidentval smallint
SELECT @minidentval = MIN(IDENTITYCOL) FROM tblDepartments
IF @minidentval = IDENT_SEED('tblDepartments')
SELECT @nextidentval = MIN(IDENTITYCOL) + IDENT_INCR('tblDepartments')
FROM tblDepartments t1
WHERE IDENTITYCOL BETWEEN IDENT_SEED('tblDepartments') AND
32766 AND
NOT EXISTS (SELECT * FROM tblDepartments t2
WHERE t2.IDENTITYCOL = t1.IDENTITYCOL +
IDENT_INCR('tblDepartments'))
ELSE
SELECT @nextidentval = IDENT_SEED('tblDepartments')
SET IDENTITY_INSERT tblDepartments OFF
*/
GO
Stored Procedure....???
OK, I have a VIEW, which gives me the following
Code:
ID WipID
1 Fish
2 growl
3 Badger
Now, I have a table
Code:
ID Lot
1 Berries
1 Sausages
1 Stew
2 Lemons
2 Grass
3 Burgers
3 Bleech
the ID's match each other, so WipID = FISH has Berries, Sausages and Stew as it's main food groups...
Understand so far...right then I'll carry on
Now, using an SP I would like data in the following format...
Code:
ID WipID Lots
1 Fish Berries, Sausages, Stew
2 growl Lemons, Grass
3 Badger Burgers, Bleech
How can I do this?
If it can be done in a view, which I doubt it, then this would be better, but an SP will do...anyone...???
Woka
Stored Procedure
Hi All,
How to retrieve recordsets from sql server stored procedures in visual basic. Help me in this...Thanks
Stored Procedure In SQL
I want to write a stored procedure that will insert into my table then return the ID that it inserted.
Do you know the correct syntax for that, or could u give me a wee kick start with some basic code?. Im getting a syntax error, not sure how to return the ID after record is inserted.
Thank u
Stored Procedure??
I am using stored procedure to select some records.
I want to know whether I can pass 'Where' condition dynamically (from my application) to the procedure. (Select criteria doesn't change)
Thanks in advance.
DAO Stored Procedure SQL 7.0
Who can make this work ???
I have a sored procedure called "USERTEST_001 " and it inserts a value in a new record.
when I execute I get an error : Cannot execute a select query.
What am I doing wrong ???
Thanks
Code:
Sub DoStoredProcedure()
On Error GoTo sDebug
Dim db_1 As DAO.Database
Dim rs_2 As DAO.Recordset
Dim Connect As String
Dim sProc
Connect = "ODBC;DSN=" & "HARDWARE" & ";UID=" & "test" & ";PWD=" & "test" & ";DATABASE=" & "CEMA_Hardware" & ";"
Set db_1 = OpenDatabase("", False, False, Connect$)
sProc = "USERTEST_001 'test'"
'USERTEST_001 is as stored procedure (this is on the SQL server)
'*******************************
'CREATE PROCEDURE UserTest_001
'@par1 char(10)
'AS
'insert into hardwaretest(BORNR)
'values (@par1)
'*******************************
Call db_1.Execute(sProc)
sDebug:
Debug.Print Err.Description
'Cannot execute a select query. --> this is my error
End Sub
Stored Procedure
Code:
Create Procedure spLogin(@UserName VarChar,
@Password VarChar,
@CustID varchar OUTPUT)
AS SELECT *
FROM tKA_Customer
WHERE UserName = @UserName AND Pass = @Password
Select @CustID = CustID
Return @CustID
the error i am getting is "CustID" is invalid column name
even though i know for a fact that column name is in my table
i also tried
Code:
Create Procedure spLogin(@UserName VarChar,
@Password VarChar,
@CustID varchar OUTPUT)
AS SELECT *
FROM tKA_Customer
WHERE UserName = @UserName AND Pass = @Password
Set @CustID = CustID
Return @CustID
same error
Stored Procedure - SQL
Hi,
I need help writing a stored procedure for an SQL Server that will stop the SQL Server agent at say 3am and restart it at 5am.
Any ideas.
Thank.
Stored Procedure From VB
Hi
Im trying to execute a stored Procedure from VB.
It is no problem if i declare a connection object but since i have a dataenvironment and a connection there i dont want to use a new connection object.
but i cant get it to work.
anybody with an idea of this
this is my code:
'declare
Dim objSpComm As ADODB.Command
Dim rs As ADODB.Recordset
Dim parameter As integer
Set objSpComm = New ADODB.Command
Set rs = New ADODB.Recordset
'sp
objSpComm.CommandType = adCmdStoredProc
objSpComm.ActiveConnection = this is where it goes wrong ? (dataenvironment.cn.open ?)
objSpComm.CommandText = "dbo_Sp_GetAll"
'parameter in
parameter = 2
'execute
Set rs = objSpComm.Execute(, parameter)
Set objComm = Nothing
thanks in advance
Micke
Stored Procedure?
How do I execute a stored procedure on the SQL server, in VB?
thanks
(is there a way to do it without a DSN?)
Stored Procedure
How do I correctly execute a stored procedure in visual basic ?
Adodc1.RecordSource = "exec sms_cell5 '1', '+27835567033', 'test'"
Adodc1.Refresh
I get an error 'operation not allowed while object is closed'
and I get taken to the adodc1.refresh line...
any ideas?
Stored Procedure
Hi, how can I read/return a recordset from a stored procedure in SQL Server 7? For SQL, I normally use something like:
Code:
cmd.CommandText = "SELECT Name, Addr FROM Employee WHERE Name Like 'A%'
Set rstLocal = cmd.Execute(, , vbReadOnly)
So for a Stored Procedure that I created, I tried:
Code:
cmd.CommandText = "EXEC km_TEST 'dimaccow'" 'This works from Query Analyzer but not VB
Set rstLocal = cmd.Execute(, , vbReadOnly)
km_Test is the Stored Proc and I'm passing a name. Doesn't work from VB for me. What am I doing wrong?
Thanks in advance,
Wade
|
TRACKER
