f



stored procedure error

I'm trying to call a ms sqlserver stored procedure in vb that takes in
a varchar input parameter.
here's the vb code:

    Dim connection As ADODB.connection
    Dim command As ADODB.command
    Dim user As String
    
    user = "john_smith"
    
    Set connection = New ADODB.connection
    Set command = New ADODB.command
    
    connection.Open "DSN=my_dsn;UID=someuser;PWD=somepassword;DATABASE=my_db"
    Set command.ActiveConnection = connection
    command.CommandType = adCmdStoredProc
    command.CommandText = "user_save"
    command.Parameters.Append command.CreateParameter("user_name",
adVarChar, adParamInput, user)
    command.Execute


and here's the sqlserver stored procedure:

CREATE PROCEDURE user_save
	@user_name varchar(40)
AS
BEGIN
	insert into users(user_name) values (@user_name)
END
GO


I get a "Run-time error '13': Type mismatch" error on the line:
command.Parameters.Append command.CreateParameter("user_name",
adVarChar, adParamInput, user)

What am I doing incorrectly in this line?
-james
0
jtsao
4/22/2004 6:52:17 PM
comp.basic.visual.database 1601 articles. 0 followers. Post Follow

2 Replies
311 Views

Similar Articles

[PageSpeed] 48

"james tsao" <jtsao@go.com> wrote in message
news:8c426184.0404221052.762c5f69@posting.google.com...
>
> I get a "Run-time error '13': Type mismatch" error on the line:
> command.Parameters.Append command.CreateParameter("user_name",
> adVarChar, adParamInput, user)
>
> What am I doing incorrectly in this line?
> -james

Omitting the size. Or a comma, anyway.

The declaration is:
    CreateParameter (Name, Type, Direction, Size, Value)

Size is optional, so you could write
....CreateParameter ("user_name", adVarChar, adParamInput, , user)

but it may be required for an adVarChar, in which case write
....CreateParameter ("user_name", adVarChar, adParamInput, 128, user)


0
Steve
4/23/2004 2:22:21 AM
Also, it should be "@user_name".

"Steve Gerrard" <notstevegerrard@comcast.net> wrote in message news:<4aWdnbZ9vd1L4BXdRVn-tA@comcast.com>...
> "james tsao" <jtsao@go.com> wrote in message
> news:8c426184.0404221052.762c5f69@posting.google.com...
> >
> > I get a "Run-time error '13': Type mismatch" error on the line:
> > command.Parameters.Append command.CreateParameter("user_name",
> > adVarChar, adParamInput, user)
> >
> > What am I doing incorrectly in this line?
> > -james
> 
> Omitting the size. Or a comma, anyway.
> 
> The declaration is:
>     CreateParameter (Name, Type, Direction, Size, Value)
> 
> Size is optional, so you could write
> ...CreateParameter ("user_name", adVarChar, adParamInput, , user)
> 
> but it may be required for an adVarChar, in which case write
> ...CreateParameter ("user_name", adVarChar, adParamInput, 128, user)
0
david_wimbush
4/26/2004 2:49:59 PM
Reply:

Similar Artilces:

Stored procedure in database X, executes stored procedure in database Y, wrapped in transaction?
Is it possible to execute a stored procedure in one database, which then itself executes a stored procedure from another database? We have decide to split our data into a tree structure (DB1) and data blobs (DB2) (we are using MSDE and we have a 2gb limit with each DB so we've done it this way for that reason). I would like to, say, execute a stored procedure in DB1, passing in the data blob and other details, DB1 will create a tree node in DB1 and then add the blob record to DB2. DB1 will wrap in a transaction of course, as will DB2 when it adds the blob. Is this possible? [poste...

Error in Visual Basic closes the database
Hello, I am working in a new form and still making changes to the visual basic code of the form. Not always, but very often when I try to save my changes in the visual basic window and press the icon, this window closes(without saving) and the whole database, too. I already tried to switch everything in a new form and error occurs again after starting to work in the visual basic code of this specific form not in others and I did compile and repair the db, too. I would appreciate any other ideas. Thanks Nicole Did you try a decompile? This sequence should work: 1. Uncheck the boxes under...

Call Stored Procedure from Visual Basic Script
Hi, I would like to call a stored procedure from a vbs. I am a newbie when it comes to this so the more detailed the better. Basically I have a stored proc that has already been created. This store proc does the following steps: 1 - Create temp table 2 - Populate temp table 3 - Select statement from temp table 4 - Drop temp table What I would like my vbs to do is call the stored proc and loop through each row in the select statement and right this to a file. When it reaches the end of the rows it drops the temp table (step 4). Can somebody help me with this please. Thanks Kyle nameless...

Stored Procedure Problem with SQL Server and Visual Basic!
Hi, I am at my almost wits end trying to work out what is going on with a SQL server Stored Procedure, that works perfectly well in SQL Server(returns expected value executed in query analyzer) but gives me an empty recordset and all sorts of headaches when executed from VB. Any Ideas? here is the SP: It creates a unique record number based on the year and then an incremented number.(eg 04/9999, 05/0001, 05/0002 etc etc) CREATE PROCEDURE Get_MyJobNo AS DECLARE @JobNo varchar(10), @JobPrefix varchar(2), @CurrentYear varchar(4), @iPos smallint, @NextJobNo smallint /* Se...

[Info-ingres] Ingres Stored Procedure Visual Basic .net
This is a multi-part message in MIME format. ------_=_NextPart_001_01C70D53.8F234E2D Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Sorry to bring up this old chestnut... We use Visual Basic .net along with Ingres ODBC. It works great. I want to use stored procedures to retrieve record sets and return value (i.e. 0 success, 1 fail). I've searched the net, but just can't find a good example, or best practice to follow. All my attempts fail (it executes the stored procedure but I just can't get the return value...

Stored procedure from stored procedure
Is it possible to create a stored procedure from a stored procedure? When I attempt this inanity, it doesn't blow up until syntax error at the first "end procedure" statement of the SP I'm trying to create within the outer SP. Can this be done? Using IDS 7.31-UC4 on Solaris 2.7/Intel (don't ask). On 24 Sep 2003 11:47:09 -0700, red_valsen@yahoo.com (Red Valsen) wrote: >Is it possible to create a stored procedure from a stored procedure? >When I attempt this inanity, it doesn't blow up until syntax error at >the first "end procedure" ...

Calling Oracle 8 Stored Procedure via Access 2000/3 or Visual Basic
Does anybody know how to call and execute an Oracle 8 Stored Procedure via ODBC or OLEDB, ADO or preferrably a Access passthrough query. The Stored Procedure runs Delete statements against tables in the database which is much faster than executing deletes against tables tables linked to Oracle in Access. I have tried the following Syntax: {Call sp_DELETETABLE()} {Begin Call sp_DELETETABLE() End;} I can execute the sp_DELETETABLE in PL / SQL with no problems. Thanks! ...

Accessing BTrieve database from Visual C++/Visual Basic
Hi! I have been looking on the pervasive website but things don't get really clear to me. Does anybody know how to access a betrieve database from either Visual Basic or from Visual C++? Is there some sort of ODBC driver or anything alike? Do you know where to get it and if it is free or needs to be payed for? I'm looking for this because we use a product in our clinic that makes use of a BTrieve database from which we would like to query some information that we would like to use in some other system. If we can access the BTrieve database we are able to treat our patient be...

calling a stored procedure from a stored procedure
Hi Folks, I'm trying to define a (I thought) trivial stored procedure (SP) as a front-end to the standard SP "dbms_system.set_ev", which I want to use to generate a 10046 tracing event. I'm trying to execute the following PL/SQL: create or replace procedure rob_enable_tracing ( sid in integer, serial in integer ) is begin dbms_system.set_ev(sid, serial, 10046, 4, ''); end; and getting the error: PLS-00201: identifier 'DBMS_SYSTEM.SET_EV' must be declared If I provide the schema in which "set_ev" is defined, in this ...

Trigger: To fill another Database with using Stored Procedures of the other Database
Hello everyone, I face currently a problem where I could need some input for searching the source of the Problem System: SQL Server 9.0 I fill from Database A with triggers Database B, everything works fine. On Database B there is a Stored Procedures that checks the records and add additional information accordingly, this Stored Procedures is normally called by the application on "update and insert" in the according table. When I try to call this Stored Procedures from the Database A, the trigger does not work anymore, even if I do a try catch over the whole trigger, he never rea...

Storing all stored procedures in one database to minimize different connection strings
In order to minimize the number of connection strings I have to use to access different databases on the same Sql Server, I was considering storing all stored procedures in just one database. I want to do this because connection pooling in my application - ASP.NET is based on this connection string. So if I need to access 6 different databases on one sql server & set 6 different connection strings, I end up creating 6 different connection pools. Other than it might create more management work for the DBA, are there any performance implications with implementing this scheme? Do stored p...

Calling a stored procedure from another stored procedure...
Goodmorning, I'm quite new to PostgreSQL, started off with version 8.0 some time ago. Recently I've begun working with stored procedures and now I've come accross a little problem / issue I can't seem to figure out. A quick overview of the situation: I have one stored procedure with returns a SET OF a new (record) type I've declared. For example: mytype index INTEGER; description VARCHAR; cost FLOAT; function calculate_cost(...): returns set of mytype; This stored procedure works without a problem. I can just do a &quo...

Error in store procedure
Hi I've the following SP: ----------- CREATE PROCEDURE spServicios @numero int, @maxdif int, @resultado int OUTPUT AS BEGIN SET ROWCOUNT 1 UPDATE control SET registro = getdate() WHERE (fecha >= getdate() AND fecha < dateadd(mi, @maxdif, getdate()) AND clientes_codigo IN (SELECT clientes_codigo FROM telefonos WHERE numero = @numero) SELECT @resultado = @@rowcount SET ROWCOUNT 0 END SELECT @resultado ------------ But when I try to compile I get the message: Incorrect syntax near the keyword 'S...

Getting Data from a storeed procedure in a stored procedure
What I am looking to do is use a complicated stored procedure to get data for me while in another stored procedure. Its like a view, but a view you can't pass parameters to. In essence I would like a sproc that would be like this Create Procedure NewSproc AS Select * from MAIN_SPROC 'a','b',..... WHERE ......... Or Delcare Table @TEMP @Temp = MAIN_SPROC 'a','b',..... Any ideas how I could return rows of data from a sproc into another sproc and then run a WHERE clause on that data? Thanks Chris Auer Instead of using the complicated stored proced...

Errors in stored procedure
Hi, I can not get any error from a stored procedure, it seem the execution was succeful even if there was primary key violations, unknow foreign key or RAISERROR. Have you same hints for me? Thanks in advance. This is a code snippet: oCmd :=AdoCommand{} oCmd:ActiveConnection:= self:oConnection oPars := oCmd:PARAMETERS_ oCmd:CommandText := "myProc" oCmd:CommandType := AdCmdStoredProc oPars := oCmd:PARAMETERS_ oPars:Item("@iMyPar1"):VALUE:=iMyPar1 oPars:Item("@iMyPar2"):VALUE:=iMyPar2 oCmd:Execute(nil,nil,nil) ...

Web resources about - stored procedure error - comp.basic.visual.database

Invasiveness of surgical procedures - Wikipedia, the free encyclopedia
There are three main categories which describe the invasiveness of surgical procedures . These are: non-invasive procedures , minimally invasive ...

Rushed cosmetic procedures a 'recipe for disaster'
&#8203;When Chanelle O'Hare went searching online for a deal on potential cosmetic procedures, she could not have imagined that what she ended ...

Jetstar procedures under investigation after planes took off too heavy
Jetstar's procedures for calculating the weight of its aircraft are under review by the Australian Transport Safety Bureau after two of its planes ...

Old Lady Lawyer: Uncivil Procedure
What is some of the worst behavior you've witnesses by attorneys?

NFL Announces Changes to Officiating Procedures for Playoffs - Bleacher Report
The NFL formally approved changes to its postseason officiating procedures to allow referees the opportunity to consult Vice President of Officiating ...

We need to reform the culture of law enforcement, not just the procedures
We need to reform the culture of law enforcement, not just the procedures by digby I have a new piece up at Salon this morning about police ...

Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...
Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...

Rescued tiger cub "Himmel" dies during medical procedure - CBS News 8 - San Diego, CA News Station ...
Alpine animal rescue sanctuary Lions, Tigers & Bears is sadly reporting Himmel, the tiger cub found roaming the streets of Hemet and rescued ...

Catholic hospital allowed to deny sterilization procedure, judge says
Hospital in California argued that the procedure known as ‚Äčtubal ligation would violate its religious freedom

TSA Updates Screening Procedure, Will Mandate Some Passengers Use Full-Body Scanners
... Imaging Technologies, or AIT, in favor of full-body pat-downs by TSA agents. Under the new mandate, not everyone can opt for the pat-down procedure. ...

Resources last updated: 1/28/2016 12:41:37 AM