473,574 Members | 18,283 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

insert record using stored proc returns no error but doesn't inserd record

Hi

I am using SQL server 7.0 and here is my code:

Dim conn
set conn = Server.CreateOb ject("ADODB.Con nection")
conn.open "websql"

If conn.state=adSt ateOpen then
Response.Write( "ConnMade : " & conn.state
& "<BR>")
Response.flush
else
Response.Write( "not ConnMade : " &
conn.state & "<BR>")
end if
'response.write sql
conn.execute("I nsert_Emailsell er" &_
" @fname='" & fname & "'," &_
" @lname='" & lname & "'," &_
" @dphone='" & dphone & "'," &_
" @ephone='" & ephone & "'," &_
" @email='" & email & "'," &_
" @comments='" & comments & "'," &_
" @chktrade='" & chktrade & "'," &_
" @chkfinance='" & chkfinance & "'," &_
" @ctime='" & ctime & "'," &_
" @prom='" & prom & "'," &_
" @dealerid='" & dealerid & "'," &_
" @vin='" & vin & "'," &_
" @dateandtime=" & dateandtime)
'conn.ExecuteCo mplete(sql)
'Response.write ("Record inserted. ID number was <b>" &
rs(0) & "</b>.")
'rs.close: set rs = nothing
conn.close: set conn = nothing

and my stored procedure is:
CREATE PROCEDURE dbo.Insert_Emai lseller

@fname varchar (50) ,
@lname varchar (50) ,
@dphone int ,
@ephone int ,
@email varchar (50) ,
@comments text ,
@chktrade bit ,
@chkfinance bit ,
@ctime char (10) ,
@prom tinyint ,
@dealerid char (10) ,
@vin varchar (18) ,
@dateandtime datetime
AS
BEGIN
SET NOCOUNT ON
DECLARE @new_emailselle r INT
INSERT caremail_leads
(
fname ,
lname ,
dphone ,
ephone ,
email ,
comments ,
chktrade ,
chkfinance ,
ctime ,
prom ,
dealerid ,
vin ,
dateandtime
)
VALUES
(
@fname ,
@lname ,
@dphone ,
@ephone ,
@email ,
@comments ,
@chktrade ,
@chkfinance ,
@ctime ,
@prom ,
@dealerid ,
@vin ,
@dateandtime
)
SELECT @new_emailselle r = @@IDENTITY
SELECT new_emailseller = @new_emailselle r
END
Jul 19 '05 #1
1 1792
Shouldn't the stored procedure be using INSERT INTO not just INSERT ??

<an*******@disc ussions.microso ft.com> wrote in message news:<04******* *************** ******@phx.gbl> ...
Hi

I am using SQL server 7.0 and here is my code:

Dim conn
set conn = Server.CreateOb ject("ADODB.Con nection")
conn.open "websql"

If conn.state=adSt ateOpen then
Response.Write( "ConnMade : " & conn.state
& "<BR>")
Response.flush
else
Response.Write( "not ConnMade : " &
conn.state & "<BR>")
end if
'response.write sql
conn.execute("I nsert_Emailsell er" &_
" @fname='" & fname & "'," &_
" @lname='" & lname & "'," &_
" @dphone='" & dphone & "'," &_
" @ephone='" & ephone & "'," &_
" @email='" & email & "'," &_
" @comments='" & comments & "'," &_
" @chktrade='" & chktrade & "'," &_
" @chkfinance='" & chkfinance & "'," &_
" @ctime='" & ctime & "'," &_
" @prom='" & prom & "'," &_
" @dealerid='" & dealerid & "'," &_
" @vin='" & vin & "'," &_
" @dateandtime=" & dateandtime)
'conn.ExecuteCo mplete(sql)
'Response.write ("Record inserted. ID number was <b>" &
rs(0) & "</b>.")
'rs.close: set rs = nothing
conn.close: set conn = nothing

and my stored procedure is:
CREATE PROCEDURE dbo.Insert_Emai lseller

@fname varchar (50) ,
@lname varchar (50) ,
@dphone int ,
@ephone int ,
@email varchar (50) ,
@comments text ,
@chktrade bit ,
@chkfinance bit ,
@ctime char (10) ,
@prom tinyint ,
@dealerid char (10) ,
@vin varchar (18) ,
@dateandtime datetime
AS
BEGIN
SET NOCOUNT ON
DECLARE @new_emailselle r INT
INSERT caremail_leads
(
fname ,
lname ,
dphone ,
ephone ,
email ,
comments ,
chktrade ,
chkfinance ,
ctime ,
prom ,
dealerid ,
vin ,
dateandtime
)
VALUES
(
@fname ,
@lname ,
@dphone ,
@ephone ,
@email ,
@comments ,
@chktrade ,
@chkfinance ,
@ctime ,
@prom ,
@dealerid ,
@vin ,
@dateandtime
)
SELECT @new_emailselle r = @@IDENTITY
SELECT new_emailseller = @new_emailselle r
END

Jul 19 '05 #2

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

Similar topics

2
4180
by: Tavish Muldoon | last post by:
What a pain trying to insert data into a table from a stored proc. My webform asks for 16 pieces of data - which then gets written to the database. I found this easier than the crap below (after the ***********). This was eaiser: get a list of all the parameters you want to pass - put them in a string:
16
1902
by: LP | last post by:
Hi, Every morning a .NET application downloads a file with cumulative data which needs to be appended to SQL Server table. This program needs to identify records that have not been previously inserted (there's a unique identifier field) and only insert those. Also I must reuse our class that does updates, it basically can update any table...
1
1168
by: Max | last post by:
I'm looking for best methods in terms of performance and simplicity to do an INSERT and UPDATE. Using Microsoft's Application Blocks and SQL Server stored procedures, this is simple: Insert: iNewCustomerID = Convert.ToInt32(SqlHelper.ExecuteScalar(strConn, _ "myStoredProc", _ param1, _ param2, _ param3
3
1713
by: ASP .NET Newbie | last post by:
I am trying to insert a new record into my database, and have it return a uniqueidentifier as the "newcatid". I don't use integers as my "id"'s, but rather uniqueidentifiers. Here's my Stored Proc: ALTER PROCEDURE dbo.spNewCat ( @newcatidID uniqueidentifier OUTPUT, @catname varchar(50) ) AS INSERT INTO Categories (catname) VALUES...
6
2349
by: SandySears | last post by:
I am trying to use a stored procedure to insert a record using VS 2005, VB and SQL Server Express. The code runs without errors or exceptions, and returns the new identifer in the output parameters. It returns my error text message in another output parameter as "ok", which is the value that is set in the stored procedure prior to doing the...
4
4667
by: Dabbler | last post by:
I'm using an SQLCommand to insert row using a text command. Is there a way to return the IDENTITY key value after the insert ? Thanks much!
3
3451
by: Tim Satterwhite | last post by:
Hi All, I think this is a thorny problem, and I'm hoping you can help. I've not found this exact issue described anywhere yet. I have a stored procedure that calls BULK INSERT on a set of text files. These files are FTP'd from a legacy system (a mainframe running MVS). Sometimes, the process steps on iteslf, whereby the bulk insert is...
4
6378
by: ryushinyama | last post by:
I had to do a lot of searching to get this one to work and in doing so I saw a lot of different sites where people were looking for this answer so I thought I would put it up. If you are trying to put data in to a table where you don't want any duplicate data a primary key, unique constraint and all that prevention is the best way to go. ...
0
1981
by: mirandacascade | last post by:
Questions toward the bottom of the post. Situation is this: 1) Access 97 2) SQL Server 2000 3) The Access app: a) sets up pass-thru query b) .SQL property of querydef is a string, the contents of which comprise the call to a stored proc
0
7820
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
7738
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
8258
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...
1
7835
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8121
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
5325
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3759
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...
0
3777
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2255
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

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.