0 Replies - 1479 Views - Last Post: 07 December 2012 - 01:49 AM Rate Topic: -----

#1 purvabhilare1587  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 27-May 12

inserting values in tables with 1:m relationship

Posted 07 December 2012 - 01:49 AM

Hi,
I want to insert values into 2 tables Product_Table and Service_Centre_Table which are related to each
other by one to many relationship.Product_Table has S_Code which is primary key of Service_Centre_Table.
So first I am inserting values in the Service_Centre_Table.Before entering values in service centre table
I am checking if the details for that service centre already exist.If the details dont exist,only then the values
are inserted.Now,while entering values into Product_Table I am reading the S_Code value for the service centre from the database and then inserting it into the Product_Table.Values dont get saved to the Product_Table.I dont get any error.

 ElseIf ProductTypeComboBox.Text = "REPAIRED(INWARD)" Then
            Using sqlcon As New SqlConnection(My.Settings.Hardware_DatabaseConnectionString)

                sqlcon.Open()

                Dim sqlcmdscheck As New SqlCommand("SELECT count(*) FROM Service_Centre_Table WHERE S_Code=@scode", sqlcon)
                sqlcmdscheck.Parameters.AddWithValue("scode", Trim(ServiceCentreCodeTextBox.Text))
                Dim scount As Int32 = Convert.ToInt32(sqlcmdscheck.ExecuteScalar)

                If scount <= 0 Then
                    Dim sqlcmdv As New SqlCommand("INSERT INTO Service_Centre_Table([S_Name],[S_Address],[S_Cntct],[S_Cntct_Prsn]) VALUES(@sname,@saddress,@scntct,@scntctprsn)", sqlcon)

                    sqlcmdv.Parameters.AddWithValue("@sname", Trim(ServiceCentreNameComboBox.Text))
                    sqlcmdv.Parameters.AddWithValue("@saddress", ServiceCentreAddressRichTextBox.Text)
                    sqlcmdv.Parameters.AddWithValue("@scntct", Trim(ServiceCentreContactTextBox.Text))
                    sqlcmdv.Parameters.AddWithValue("@scntctprsn", Trim(ServiceCentreContactPersonTextBox.Text))
                    sqlcmdv.ExecuteNonQuery()
                    MsgBox("Data added to service centre successfully")
                End If

                Dim sqlcmdp As New SqlCommand("INSERT INTO Product_Table([Prd_Sr_Key_No],[Prd_Name],[Prd_Brand],[Prd_Type],[Prd_Detail],[S_Code],[Dte_Stck_In],[Brght_By],[Note]) VALUES(@srkeyno,@prdname,@prdbrand,@prdtype,@prddetail,@scode,@dtestckin,@brghtby,@note)", sqlcon)

                sqlcmdp.Parameters.AddWithValue("@srkeyno", Trim(SrKeyNoTextBox.Text))
                sqlcmdp.Parameters.AddWithValue("@prdname", Trim(ProductNameTextBox.Text))
                sqlcmdp.Parameters.AddWithValue("@prdbrand", Trim(BrandNameComboBox.Text))
                sqlcmdp.Parameters.AddWithValue("@prdtype", Trim(ProductTypeComboBox.Text))
                sqlcmdp.Parameters.AddWithValue("@prddetail", Trim(DetailRichTextBox.Text))

                Dim sqlcmdreadscode As New SqlCommand("SELECT S_Code FROM Service_Centre_Table WHERE S_Name='" & ServiceCentreNameComboBox.Text & "'", sqlcon)
                Dim sdrreadscode As SqlDataReader
                sdrreadscode = sqlcmdreadscode.ExecuteReader()

                If sdrreadscode.HasRows Then
                    While sdrreadscode.Read
                        sqlcmdp.Parameters.AddWithValue("@scode", sdrreadscode.Item("S_Code"))
                    End While
                End If

                sqlcmdp.Parameters.AddWithValue("@dtestckin", ddComboBox.Text + "/" + mmComboBox.Text + "/" + yyyyComboBox.Text)
                sqlcmdp.Parameters.AddWithValue("@brghtby", Trim(BroughtByTextBox.Text))
                sqlcmdp.Parameters.AddWithValue("@note", NoteRichTextBox.Text)
                MsgBox("Data added to Product table successfully")
End Using



Is This A Good Question/Topic? 0
  • +

Page 1 of 1