Add, delete, edit and update database using Datagrid

Question

0.00/5 (No votes)

See more:

Hi Everyone,

I've been dealing with this issue for four hours now and almost giving up. I just want to ask how create, delete, update and delete can be done with the use of datagrid and update SQL server.

This is what I have:

Private Sub LoadData()

VB

Try

      
        Dim connectionString As String = "Data Source=PHEMSSCGP-L655\SQLEXPRESS;Initial Catalog=COE;" & "Integrated Security=SSPI;"
      
        conn = New SqlConnection(connectionString)
        conn.Open()
         
            Dim adap As New SqlDataAdapter("Select SerialNum , BuildNum, PartBuildID , OrderNum , XPart , YPart ,Rot, Height , PartMass , SupportMass , Special FROM tblBuildPlan where BuildNum =" & cmbBuildNum.SelectedValue(), conn)
            Dim ds As New DataSet
        adap.Fill(ds, "BAM")
        GVBuilPlan.DataSource = ds.Tables(0)

        Catch ex As Exception
            MessageBox.Show(ex.Message)

        End Try

For update button this is what I have:

VB

Try
           Dim cmbl As SqlCommandBuilder = New SqlCommandBuilder(da)
           cmbl.GetUpdateCommand()
           da.Update(ds, "BAM")
           MessageBox.Show("Success")
       Catch ex As Exception
           MessageBox.Show(ex.Message)
       End Try

I can successfully display the values in the db in the gridview but the problem happens when I try to edit or add data and hit update button.

This is the error:

Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

According to what I read, I have to have a primiary key which I do, and it is the SerialNum from the tblBuildMaster table.

Any help, would be greatly appreciated.

Thank you everyone!

Posted 12-Jul-15 5:33am

serigraphie

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2015-07-12T06:12:00

Solution 1

Based on the error it looks like you don't have a primary key on the table. Please check again that you have a PRIMARY KEY constraint on the table, not a unique index or nothing similar. You can use for example SSMS ability to script the table to see what are the constraints on the table.

As a side note I'd really suggest using SqlParameter[^] in your select statement in orderto be safe from SQL injections, conversion problems etc.

Posted 12-Jul-15 6:12am

Wendelius

Comments

serigraphie 12-Jul-15 14:38pm

Hi Mika, the primary key issue is driving me nuts. Almost all suggestions on the internet suggest that I may not have a PK defined in my table. But I do, at least I think I do. Here is my create table script and I hope you can further guide me:

CREATE TABLE [dbo].[tblBuildPlan](
[SerialNum] [int] IDENTITY(1,1) NOT NULL,
[BuildNum] [int] NOT NULL,
[PartBuildID] [varchar](50) NOT NULL,
[OrderNum] [varchar](50) NOT NULL,
[XPart] [decimal](4, 2) NOT NULL,
[YPart] [decimal](4, 2) NOT NULL,
[Rot] [int] NOT NULL,
[Height] [int] NOT NULL,
[PartMass] [int] NOT NULL,
[SupportMass] [int] NOT NULL,
[Special] [varchar](max) NOT NULL,
CONSTRAINT [PK_tblBuildPlan] PRIMARY KEY CLUSTERED
(
[SerialNum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Wendelius 12-Jul-15 15:17pm

This really sounds odd. Few things you could try.
- First of all, make absolutely sure that the data adapter in update button (da) is the exact same object as what you use in LoadData (adap). Based on the names you potentially use a different adapter in the update method.
- In the SELECT statement instead of listing the columns to fetch try using *. This is a bad habit and shouldn't be left in the program but just out of curiosity...
- Try calling RefreshSchema[^] before trying to get the update command

serigraphie 13-Jul-15 3:17am

Hi Mika thanks for replying and spotting the disconnect between adap and da, I have updated the code and it throws a new error : Update unable to find TableMapping['BAM'] or DataTable 'BAM'.

For some reason, it is not finding the BAM now.

Wendelius 13-Jul-15 3:22am

Ok, sounds like similar problem. The dataset you use doesn't contain a table named BAM. From your previous code I take it you create it in the beginning into some dataset but is this the same dataset you use later on?