I need some help writing a Stored Procedure

moss2076 · #1 (**permalink**) 09-10-08, 09:14

Hello I need some help with writing out a stored procedure. I am fairly new to SQL/stored procedures and am having a bit of trouble making things work...

Im using SQL Server Management Studio Express for my database, and vb.net/visual studio2008 to design my forms.

I have 2 tables - BookingsItems, and Stock. The two tables are displayed in vb.net as DataGridViews, called dgvBookingsItems and dgvStock.

I want to create a booking by selecting a particular row in dgvStock (from stock table) and have the selected StockID inserted into the selected row and StockID field of BookingsItems. The BookingsItems row execute the insert into is selected by choosing a row in dgvBookingsItems.

The command will also insert the related bookingsID field from bookings table into bookingsItems table - (one bookingID can have many bookingsItemsID). I have already created a relationship in vb.net between the bookings table and bookingsItems table, so getting the bookingsID value into the bookingsitems table is not a problem, its just the stockID value from table stock which I cannot obtain.

I have the insert command in vb.net -

Code:

  Dim insert As New SqlCommand("test", conn)
        insert.CommandType = CommandType.StoredProcedure
        insert.Parameters.Add("@stockID", SqlDbType.Int, 100, "stockID")
        insert.Parameters.Add("@bookingsID", SqlDbType.Int, 100, "bookingsID")
        BookingsItemsDataAdapter.InsertCommand = insert

And in SQL server I have the Stored Procedure -

Code:

(
@StockID as int,
@bookingsId as int
)
as

SELECT stockID
FROM tblstock  
WHERE stockID = @StockID

SELECT bookingsID
FROM tblbookings
where bookingsid = @bookingsID

INSERT INTO tblBookingsItems (stockID, bookingsID)

VALUES(@stockID, @bookingsID)

pootle flump · #2 (**permalink**) 09-10-08, 09:47

Hi moss2076

I have no idea what you are asking I'm afraid - there is lots of talk about datagridviews and .NET and other stuff that isn't relevant to a SQL Server problem.

Rather than saying what you have got, could you tell us what you want? And when doing so please could you try to reduce it down to what you want to send to SQL Server, what you want SQL Server to do, and what you want SQL Server to return (if anything). All the front end stuff just obfuscates the problem.

pootle flump · #3 (**permalink**) 09-10-08, 09:48

Oh - just noticed where this is - I thought this was in the SQL Server forum.

This is not the right forum - please let me know if this is a .NET or SQL Server question and I'll move it to the appropriate forum.

blindman · #4 (**permalink**) 09-10-08, 10:04

Code:

(
@StockID as int,
@bookingsId as int
)
as

INSERT INTO tblBookingsItems (StockID, BookingsID)
VALUES(@stockID, @bookingsID)

Not sure what the point of those "select" statements was.

moss2076 · #5 (**permalink**) 09-10-08, 11:13

Oopps sorry Im not too sure if it is more an SQL Server question regarding the design of a stored procedure, or more of a vb.net question..it may be both!

I thought Id explained what I wanted the sproc to do - take the StockID value from Stock table (via selecting the desired row in dgvStock) and insert it into bookingsItems table (via selecting the desired row to insert into in dgvBookingsItems).

Using this -

Quote:

(
@StockID as int,
@bookingsId as int
)
as

INSERT INTO tblBookingsItems (StockID, BookingsID)
VALUES(@stockID, @bookingsID)

-does not work as I want, it does work if I type the stockID figure manually into the dgvBookingsItems, but I want to make it work by selecting a row from dgvStock, then selectingt the row in dgvBookingsItems which I want the StockID inserted into.

That is why I included the vb.net code, just to show how I was attempting it.

pootle flump · #6 (**permalink**) 09-10-08, 11:17

Ok - a SQL Server stored procedure has no concept, knowledge or understanding of DataGridViews and nor should it. I'll chuck it in the .NET forum.

pootle flump · #7 (**permalink**) 09-10-08, 11:19

Hmmmm. I thought we had a .NET forum. I've answered the odd question there!

Mods - are you playing tricks on me again?

moss2076 · #8 (**permalink**) 09-10-08, 11:22

Ok thanks

moss2076 · #9 (**permalink**) 09-10-08, 11:28

I cant see a .net forum. Am I bind?

blindman · #10 (**permalink**) 09-10-08, 13:06

Quote:

Originally Posted by moss2076

I thought Id explained what I wanted the sproc to do - take the StockID value from Stock table (via selecting the desired row in dgvStock) and insert it into bookingsItems table (via selecting the desired row to insert into in dgvBookingsItems).

Using this -
-does not work as I want, it does work if I type the stockID figure manually into the dgvBookingsItems, but I want to make it work by selecting a row from dgvStock, then selectingt the row in dgvBookingsItems which I want the StockID inserted into.

Epic fail! (As my kids would say).
I'm still clueless as to what you are trying to do, and a basic rule of programming is: If you can't explain a requirement, you can't code the requirement.

moss2076 · #11 (**permalink**) 09-10-08, 13:52

Christ..

2 tables

STOCKID field in Stock table. STOCKID field in BookingsItems table.

Insert the selected StockID field from Stock table into the selected StockID field in BookingsItems table - WITHOUT having to type in the stockID into the BookingsItems table manually eg -

Do it by selecting the row holding the desired STOCKID from STOCK tables datagridview and selecting the bookingsitem row to insert to from the BookingsItems datagridview.

blindman · #12 (**permalink**) 09-10-08, 15:16

Quote:

Originally Posted by moss2076

Christ..

I prefer simply "blindman".

Quote:

Originally Posted by moss2076

Insert the selected StockID

...you have a selected StockID, so presumably you know its value.

Quote:

Originally Posted by moss2076

...selecting the bookingsitem row to insert to from the BookingsItems datagridview.

...you have a selected bookingsitem row, so presumably you know the BookingsID....

The SQL solution for this would be what I posted before:

Code:

INSERT INTO tblBookingsItems (StockID, BookingsID)
VALUES(@stockID, @bookingsID)

...and you did, after all, post your question on a database forum and indicate that you wanted help with stored procedures.

So. I have provided a solution to your problem the way you have described it. Again, if this does not solve your problem then you will actually need to explain it more clearly, rather than just using the same words in each post, but in different orders. More clearly, from the database perspective is:
"I need a stored procedure that will accept X and Y as parameters and perform function F on those parameters".

pootle flump · #13 (**permalink**) 09-10-08, 17:21

Yeah - there is no .NET forum - I could have sworn we had one floating around somewhere

Now - your "clarification" highlights the point. You now seem to be using tables and datagridview interchangeably (in fact you even use the term "table datagridview"). A datagridview is a .NET control - it doesn't store anything, it displays stuff. A table (in SQL Server) is a persistent data store. I think you also have tables in .NET DataAdapters too but I forget.

As said, let's sort out exactly what you want the proc to do (what goes in, what it does, what comes out). If you can't do that without referring to the datagridview then I think we can deduce this is not a stored procedure problem.

moss2076 · #14 (**permalink**) 09-11-08, 10:45

Its a one-to-many relationship between the two tables Customer and Bookings. When I select a customer (customerID) their related bookings are shown. The front end is two datagridviews and a button. I select a customer from dgvCustomers and then select an empty row in dgvBookings to create a bookingsID automatically for that row and add the customerID to that row.

Customer
CustomerID (pk)
CustomerName
CustomerAddress

Bookings
BookingsID (pk)
CustomerID

So want the proc to - Save the BookingsID into Bookings, and take the related CustomerID from Customers and save it into Bookings CustomerID field.

Back on my datagridviews I can get it to work if I type in the CustomerID into Bookings, but cannot get it to work just by the relationship showing the customerID in Bookings. It must be typed in to make it save.

pootle flump · #15 (**permalink**) 09-11-08, 11:12

Heh - so now we've gone from stock to customer

Same difference.

Is BookingsID an Identity column?

Internet Services

Web Development

Digital Marketing

Consumer Tech