Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB How-To
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
VB How-To Ask your "How do I do this with VB?" questions in this forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB How-To section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 6th, 2003, 12:15 PM
Friend of Wrox
 
Join Date: Aug 2003
Location: Halifax, Nova Scotia, Canada.
Posts: 166
Thanks: 0
Thanked 0 Times in 0 Posts
Default Adding a new record to a sql server DB

How do I add a new record to my sql server database using the execute sql statement in VB. I have a stored procedure created for the update but I can't figure out how to use the info from my text boxes to insert a new record with. Please help if you can :)


__________________
-----------------------------------------------------------
\"Don\'t follow someone who\'s not going anywhere\" John Mason
Reply With Quote
  #2 (permalink)  
Old August 6th, 2003, 12:29 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Default

INSERT INTO <tablename> (<The column names you want to update>) VALUES (<your values here separated by commas - they must match the order of the column names>)

Conn.Execute("Insert INTO...")

Chris
Reply With Quote
  #3 (permalink)  
Old August 6th, 2003, 12:32 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: McPherson, Kansas, USA.
Posts: 128
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Try this using ADO

Assuming you have a connection object named Cn and you are
already connected to the database.

' Create a command object
Dim Cmd as New Adodb.Command
' Set the command object equal to the connection object
Set Cmd.ActiveConnection = Cn
' Define the Store procedure
With Cmd
     .CommandText = "sp_YourSprocsName"
     .CommandType = adCmdStoredProc
     ' Set parameters
     ' Substitute "@MyParam1" with the name of any parameters
     ' required by the sproc
     ' Substitute Value1 with the parameter value
     .Parameter("@MyParam1") = Value1
     ' Repeat this for each parameter
     .Parameter("@MyParamX") = ValueX
     ' Execute the stored procedure
     ' If you are not returning records then
     .Execute
     ' If you are returning records and assuming
     ' your recordset object is named rs
     Set rs = .Execute()
     ' You can now reference your recordset object as usual
End With
'
I just went through some of the same stuff. I had a hard time finding some good information on how to execute a stored procedure. What I was reading in the books I have seemed to allow me to execute the stored procedure once and then I would receive an error the next time the code ran. This works and is a combination of my readings and debugging.

There seems to be a method to have the stored procedure return a value as well as the recordset object. I haven't tried that yet but will someday.

I know this isn't a lot of information but I hope it helps. If I need to explain anything further, let me know.



Larry Asher
Reply With Quote
  #4 (permalink)  
Old August 6th, 2003, 12:41 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Default

If your insert is in a stored proc as well you can call it the same way you call any other stored proc. If you have a field that has an identity key in it (autonumber) that you want to get back from the execute, you will have to use the command object in a previous post on this topic.

Chris
Reply With Quote
  #5 (permalink)  
Old August 6th, 2003, 12:49 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: McPherson, Kansas, USA.
Posts: 128
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by kilika
 INSERT INTO <tablename> (<The column names you want to update>) VALUES (<your values here separated by commas - they must match the order of the column names>)

Conn.Execute("Insert INTO...")

Chris
Not to be too critical, But I think you are confused or not explaining yourself very well.

First off, if you have a stored procedure to do the insert why do you want to pass an SQL statement to the connection object? You are only duplicating code and your sproc is not even going to run. Seems Like a waste of resources to me.

In respond to your second post "... you can call it the same way you call any other stored proc ...". I think that was the whole point of the initial post - How do you execute the sproc from within VB.

I am I wrong or the only one who sees it this way?


Larry Asher
Reply With Quote
  #6 (permalink)  
Old August 6th, 2003, 01:44 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Default

If you will notice from the original post, there was no mention of a stored proc existing for the insert. It was only said that there was one for the update.

The original post wanted to insert data using the "execute" SQL statement which can be done several ways. You can either call a stored proc like you would any other proc, just use the straight SQL statement, or use the command object.

Yes, had the original poster declared he had a stored proc created for inserting, then yes, it would be redundant and a waste of code to execute the "INSERT INTO..." code instead of calling the stored proc.

I feel I explained myself well enough. Hopefully everyone understands well enough now what I was saying.

Chris
Reply With Quote
  #7 (permalink)  
Old August 6th, 2003, 06:15 PM
Friend of Wrox
 
Join Date: Aug 2003
Location: Halifax, Nova Scotia, Canada.
Posts: 166
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks a lot for your help. But maybe I didn't understand exactly what I wanted to do. If I have an insert stored procedure then how do I call it within VB and pass it the variables from my text boxes, assuming the DB is connected to and the fileds match all fields in the DB. I think I am doing to many steps as was mention aboved.

Thanks for help

Jamie

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I write out a record to an SQL db table? furjaw Visual Basic 2005 Basics 1 April 26th, 2006 05:16 PM
errro trying to insert record to sql server method ASP.NET 1.0 and 1.1 Basics 1 May 20th, 2005 08:24 PM
Move SQL DB from one sql to another sql server Israr SQL Server 2000 3 January 24th, 2005 02:13 PM
access db to sql server db mikersantiago Classic ASP Basics 4 November 16th, 2004 03:33 AM
How to unlock record in SQL Server database DDB001 SQL Server 2000 2 August 2nd, 2004 09:46 AM



All times are GMT -4. The time now is 11:51 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.