Insert Datat

[m_nathani]

Hi , Every body

I want first insert data in data set and then stored data in data source using Stored Procedure.

Dataset is typed data set

I have following code but it didt work.
Plz help me

Try
            Dim SQLConn As SqlConnection
            Dim SQLDaMtrfitt As SqlDataAdapter
            Dim sqlCmdBldr As SqlCommandBuilder
            sqlCmdBldr = New SqlCommandBuilder(SQLDaMtrfitt)
            SQLDaMtrfitt = New SqlDataAdapter()

            With SQLDaMtrfitt
                .SelectCommand = New SqlCommand()

                With .SelectCommand
                    .CommandType = CommandType.StoredProcedure
                    .CommandText = "A11InsertMtrfitt"
                    .Connection = New SqlConnection(ConnectionString)
                End With
                .Fill(dsa11mtrfitt, "A11MtrFitt")
            End With
            Dim Dr As DataRow = dsa11mtrfitt.Tables("A11MtrFitt").NewRow
            Dr.BeginEdit()
            Dr.Item(0) = CType(CbEmpname.Items(CbEmpname.SelectedIndex), ListItem).ID
            Dr.Item(1) = DtPicker1.Text
            Dr.Item(2) = LTrim(txtHpPhase1.Text)
            Dr.Item(3) = LTrim(txtHpPhase2.Text)
            Dr.Item(4) = LTrim(txtHpPhase3.Text)
            Dr.Item(5) = LTrim(txtHpPhase4.Text)
            Dr.Item(6) = LTrim(txtType.Text)
            Dr.Item(7) = LTrim(txtTotal.Text)
            Dr.Item(8) = LTrim(txtother.Text)
            Dr.Item(9) = LTrim(txtremarks.Text)
            Dr.EndEdit()
            dsa11mtrfitt.Tables("A11MtrFitt").Rows.Add(Dr)
            dsa11mtrfitt.GetChanges()
            SQLDaMtrfitt.InsertCommand.Connection.Open()
            SQLDaMtrfitt.Update(dsa11mtrfitt)
            SQLDaMtrfitt.InsertCommand.Connection.Close()

        Catch ex As Exception
        End Try

But there are somting wrong above command . 
All line are skiped after  .Fill(dsa11mtrfitt,"A11MtrFitt") and directly jump on catch ex as exception




Paramatrised Query

Try
            Dim SQLConn As SqlConnection
            Dim SQLDaMtrfitt As SqlDataAdapter
            Dim sqlCmdBldr As SqlCommandBuilder
            sqlCmdBldr = New SqlCommandBuilder(SQLDaMtrfitt)
            SQLDaMtrfitt = New SqlDataAdapter()

            With SQLDaMtrfitt
                .SelectCommand = New SqlCommand()
                With .SelectCommand
                    .CommandType = CommandType.StoredProcedure
                    .CommandText = "A11InsertMtrfitt"
                    .Connection = New SqlConnection(ConnectionString)
                    .CommandType = CommandType.StoredProcedure
                    .CommandText = "A11InsertMtrfitt"
                    .Parameters.Add("@empid", SqlDbType.Int, 4).Value = 

CType(CbEmpname.Items(CbEmpname.SelectedIndex), ListItem).ID
                    .Parameters("@empid").Direction = ParameterDirection.Input
                    .Parameters("@empid").SourceColumn = "A11empid"

                    .Parameters.Add("@date", SqlDbType.DateTime, 8).Value = DtPicker1.Text
                    .Parameters("@date").Direction = ParameterDirection.Input
                    .Parameters("@date").SourceColumn = "A11date"


                    .Parameters.Add("@hpphase1", SqlDbType.VarChar, 30)
                    .Parameters("@hpphase1").SourceColumn = LTrim(txtHpPhase1.Text)

                    .Parameters.Add("@hpphase2", SqlDbType.VarChar, 30)
                    .Parameters("@hpphase2").SourceColumn = LTrim(txtHpPhase2.Text)
                    .Parameters.Add("@hpphase3", SqlDbType.VarChar, 30)
                    .Parameters("@hpphase3").SourceColumn = LTrim(txtHpPhase3.Text)

                    .Parameters.Add("@hpphase4", SqlDbType.VarChar, 30)
                    .Parameters("@hpphase4").SourceColumn = LTrim(txtHpPhase4.Text)

                    .Parameters.Add("@type", SqlDbType.VarChar, 30)
                    .Parameters("@type").SourceColumn = LTrim(txtType.Text)

                    .Parameters.Add("@total", SqlDbType.VarChar, 30)
                    .Parameters("@total").SourceColumn = LTrim(txtTotal.Text)

                    .Parameters.Add("@others", SqlDbType.VarChar, 30)
                    .Parameters("@others").SourceColumn = LTrim(txtother.Text)

                    .Parameters.Add("@Remarks", SqlDbType.VarBinary, 30)
                    .Parameters("@remarks").SourceColumn = LTrim(txtremarks.Text)
                   .Connection = New SqlConnection(ConnectionString)
                  End With
                .Connection.Open()
                dsa11mtrfitt.AcceptChanges()
                .Fill(dsa11mtrfitt, "A11MtrFitt")
                .Update(dsa11mtrfitt, "A11MtrFitt")
                .InsertCommand.Dispose()
                .InsertCommand.Connection.Close()
           End With

But there are somting wrong above command .
All line are skiped after .Fill(dsa11mtrfitt,"A11MtrFitt") and directly jump on catch ex as exception

 

[jfackler]

What does your catch see? i.e. whats the error message.

My bet, your instantiating your commandbulilder object with an adapter object that has not been instantiated.

Move the command builder line to follow the instantiation of the adapter and give it another go.

Dim SQLConn As SqlConnection
            Dim SQLDaMtrfitt As SqlDataAdapter
            Dim sqlCmdBldr As SqlCommandBuilder
           
            SQLDaMtrfitt = New SqlDataAdapter()

            With SQLDaMtrfitt
                .SelectCommand = New SqlCommand()

                With .SelectCommand
                    .CommandType = CommandType.StoredProcedure
                    .CommandText = "A11InsertMtrfitt"
                    .Connection = New SqlConnection(ConnectionString)
                End With
 sqlCmdBldr = New SqlCommandBuilder(SQLDaMtrfitt) move it here
                .Fill(dsa11mtrfitt, "A11MtrFitt")

Your command builder uses your select string to build insert, update and delete statements.

Cant do that without first defining the sql select statement.

One other thing: Nersues pointed this out to a few of us this week (Thanks Ner)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/CommandBuilder.asp


Jon