Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    I need some help writing a Stored Procedure

    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)

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    Last edited by blindman; 09-10-08 at 11:18.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Aug 2004
    Posts
    364
    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 -
    (
    @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.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hmmmm. I thought we had a .NET forum. I've answered the odd question there!

    Mods - are you playing tricks on me again?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Aug 2004
    Posts
    364
    Ok thanks

  9. #9
    Join Date
    Aug 2004
    Posts
    364
    I cant see a .net forum. Am I bind?

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Aug 2004
    Posts
    364
    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.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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".
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Aug 2004
    Posts
    364
    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.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Heh - so now we've gone from stock to customer Same difference.

    Is BookingsID an Identity column?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •