Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

vb.net datagridview insert with windows form

Posted on 2009-05-06
5
Medium Priority
?
523 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 24321422
Have you tried specifying the AutoIncrementSeed and AutoIncrementStep?
0
 

Author Comment

by:holemania
ID: 24336777
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:
Christian de Bellefeuille earned 1500 total points
ID: 24338209
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
ID: 24391416
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
ID: 31578701
Didn't give me the information I need, but did attempt to help so I'll award the points.
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

722 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