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

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

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

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

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

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

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