?
Solved

Can't get VB to add a row using a datatableadapter

Posted on 2012-08-16
9
Medium Priority
?
360 Views
Last Modified: 2012-08-20
Hello all,

I am not new to VB, but I am new to datasets, datatables and datatableadapters.

I am trying to insert a record into my SQL Server table.  I have tried two different ways, but the data never shows up in the table.

I have a dataset created, as well as a table adapter.  I created a custom insert query on my table adapter too, called InsSoldSeats.  My query looks like this:

INSERT INTO [dbo].[SeatSales] ([Patron], [Performance], [SeatsSold], [PaymentAmount], [PaymentType], [CheckNumber], [ShowID]) VALUES (@Patron, @Performance, @SeatsSold, @PaymentAmount, @PaymentType, @CheckNumber, @ShowID)

Here is my VB statement executing the custom query:
SeatSalesTableAdapter.InsSoldSeats(txtPatronName.Text, cbPerformance.SelectedIndex, txtFirstSeat.Text, txtAmtPaid.Text, payType, chkNum, CInt(txtLastShow.Text))

Open in new window


That didn't work, so I tried the direct approach:
seatSale = Me.TicketsDataSet.SeatSales.NewSeatSalesRow()
            seatSale.Patron = txtPatronName.Text
            seatSale.Performance = cbPerformance.SelectedIndex
            seatSale.SeatsSold = txtFirstSeat.Text
            seatSale.PaymentAmount = payAmt
            seatSale.PaymentType = payType
            seatSale.CheckNumber = chkNum
            seatSale.ShowID = CInt(txtLastShow.Text)
            Me.TicketsDataSet.SeatSales.Rows.Add(seatSale)
            SeatSalesTableAdapter.Update(seatSale)

Open in new window


That didn't work either.  What am I missing?
0
Comment
Question by:Barry62
  • 5
  • 4
9 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38303881
Why do you need to create a custom query? If you have a select query which references one table and has a primary key then other statements are generated automatically. If not, you can use commandbuilder

http://msdn.microsoft.com/en-us/library/tf579hcz.aspx
0
 
LVL 8

Author Comment

by:Barry62
ID: 38304649
OK, I don't need a custom query.  As I said, I am new to tableadapters.  

I just tried the default Insert.  It didn't work, either.  And what about the second method I tried above?
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38306384
Both methods are using the same Insert statement so either both will work or both wont. Can you show your select statement and the Insert statement that gets generated?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Author Comment

by:Barry62
ID: 38306451
I'm not using a select statement.  I am trying to insert a record.  I am very confused.
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 38312654
If all you want to do is insert a row then use following approach

Dim dbcon As New SqlConnection("your connection string here")
dbcon.Open()
Dim dbcmd As New SqlCommand("Insert Into ...", dbcon)
dbcmd.ExecuteNonQuery()
dbcmd.Dispose
dbcon.Dispose
0
 
LVL 8

Author Comment

by:Barry62
ID: 38313690
Well, I already have tableadapters set up and they work when I do a select to populate my form.  Why can't I use the same tableadapters to insert records?

I'll use the nonquery approach for now.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38313824
Adapters are used for bulk operations.
0
 
LVL 8

Author Comment

by:Barry62
ID: 38313893
OK.  Point taken.  I'll let you know if the nonquery approach works for me.
0
 
LVL 8

Author Closing Comment

by:Barry62
ID: 38314000
OK, things work fine.  Thanks for clarifying when to use table adapters.  I am all clear on it now.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Screencast - Getting to Know the Pipeline
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question