Solved

vb.net datagridview insert with windows form

Posted on 2009-05-06
5
514 Views
Last Modified: 2012-05-06
I created a windows form application with a datagridview.  I can delete, update, as well as insert a record.  However, I am running into an issue where I can only add 1 record at a time.  For example if I click on a new row and do an insert, it will only insert that one row.  Is it possible to do a loop so that it would update all rows that's been created?  Another thing is that I also need it to auto-increment the ID to the next highest number. Below is what I have done as far as inserting and auto-increment.
Imports System.Data.SqlClient
 

Public Class Form1
 

    Dim myDA As SqlDataAdapter

    Dim myDataSet As DataSet

    Public WithEvents strDETAIL_ID As String
 

    Dim builder As SqlCommandBuilder

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim con As SqlConnection = New SqlConnection("Data Source=SRVRNAME;persist security info=False;initial catalog=DATABASENAME;UID=sa;PWD=PASSWORD")

        Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM TIME_DETAIL", con)
 

        con.Open()
 

        myDA = New SqlDataAdapter(cmd)
 

        'Automatically generate deletecommand, updatecommand, insertcommand

        'Dim builder As SqlCommandBuilder = New SqlCommandBuilder(myDA)
 

        builder = New SqlCommandBuilder(myDA)

        myDataSet = New DataSet()

        myDA.Fill(myDataSet, "TIME_DETAIL")
 
 

        myDataSet.Tables("TIME_DETAIL").Columns("EMP_ID").ColumnMapping = MappingType.Hidden

        myDataSet.Tables("TIME_DETAIL").Columns("TIME_ID").ColumnMapping = MappingType.Hidden
 

        myDataSet.Tables("TIME_DETAIL").Columns("DETAIL_ID").ReadOnly = True
 

        dgViewDetail.DataSource = myDataSet.Tables("TIME_DETAIL").DefaultView
 

 	'Set this to auto-increment, but not working

	myDataSet.Tables("TIME_DETAIL").Columns("DETAIL_ID").AutoIncrement = True
 

        con.Close()
 

        con = Nothing
 

    End Sub
 
 

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
 

        Me.myDataSet.GetChanges()

        builder.GetInsertCommand()

        Me.myDA.Update(myDataSet, "TIME_DETAIL")
 

        Me.myDA.Fill(Me.myDataSet, "TIME_DETAIL")
 
 

End Sub

Open in new window

0
Comment
Question by:holemania
  • 3
  • 2
5 Comments
 
LVL 10

Expert Comment

by:cdebel
Comment Utility
Have you tried specifying the AutoIncrementSeed and AutoIncrementStep?
0
 

Author Comment

by:holemania
Comment Utility
I tried that, but it starts from 0 and then each time i click it would increment by 1.  Doesn't seem to increment from currentrow or something.  I manage to hardcode an autoincrement using sql query to find max ID and than adding a 1 to it.

Now issue is I can't just Insert.  I need to select all the lines first and then insert new record.  Is there a way to modify below code so that I don't have to select all the new line I create for it to loop and insert one by one?  
Dim i As Integer

        Try

            For i = Me.dgViewDetail.SelectedRows.Count - 1 To 0 Step -1

                cmd.CommandText = "INSERT INTO TIME_DETAIL VALUES(" & strID & ", 2, 2, " & _

                                    "'" & dgViewDetail.SelectedRows(i).Cells("START").Value & "', " & _

                                    "'" & dgViewDetail.SelectedRows(i).Cells("END").Value & "', " & _

                                    "'" & dgViewDetail.SelectedRows(i).Cells("JOB_ID").Value & "', " & _

                                    "'" & dgViewDetail.SelectedRows(i).Cells("OPERATION").Value & "', " & _

                                    "'" & dgViewDetail.SelectedRows(i).Cells("PIECE_NO").Value & "', GETDATE())"

                con.Open()

                cmd.ExecuteNonQuery()

                con.Close()

                GetID()

            Next

         

            MsgBox("Save Successful.")

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

Open in new window

0
 
LVL 10

Accepted Solution

by:
cdebel earned 500 total points
Comment Utility
about the auto-increment,  there's some explanations here:
http://www.experts-exchange.com/Programming/Languages/.NET/Q_23248836.html

But for the your loop, i can't tell.  I'm not familiar with this.   Some other Experts might help you
0
 

Author Comment

by:holemania
Comment Utility
Thanks for the help.  Manage to reverse engineer to insert on cell edit leave if there are any data.
0
 

Author Closing Comment

by:holemania
Comment Utility
Didn't give me the information I need, but did attempt to help so I'll award the points.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now