how will i edit data through database in aspnet
Dim oDS As New DataSet()
Con.Open()
Dim row As Integer
Con = New SqlClient.SqlConnection("initial catalog=datasj;integrated security=SSPI;persist security info=False;workstation id=Dotnet;packet size=4096")
Dim cmdinsert As New SqlClient.SqlCommand()
'Create the DataTable "Orders" in the Dataset and the OrdersDataAdapter
Dim oOrdersDataAdapter As New SqlClient.SqlDataAdapter(New SqlCommand("SELECT * FROM Order_Master where orderid= " & txtsearch.Text & ",", Con))
oOrdersDataAdapter.UpdateCommand = New SqlCommand("Update_Order_Master", Con)
cmdinsert = oOrdersDataAdapter.UpdateCommand
cmdinsert.CommandType = CommandType.StoredProcedure
cmdinsert.Parameters.Add(New SqlParameter("@OrderID", SqlDbType.Int))
cmdinsert.Parameters("@OrderID").Direction = ParameterDirection.Input
cmdinsert.Parameters("@OrderID").SourceColumn = "OrderID"
cmdinsert.Parameters("@OrderID").SourceVersion = DataRowVersion.Original
'cmdinsert.Parameters.Add(New SqlParameter("@BuyersOrderID_2", SqlDbType.Int, 4, "BuyersOrderID"))
cmdinsert.Parameters.Add(New SqlParameter("@CustID_2", SqlDbType.NChar, 5, "CustID"))
cmdinsert.Parameters("@CustID_2").SourceColumn = "CustID"
cmdinsert.Parameters("@CustID_2").SourceVersion = DataRowVersion.Current
'cmdinsert.Parameters.Add(New SqlParameter("@OrderDate_4", SqlDbType.DateTime, 8, "OrderDate"))
'cmdinsert.Parameters.Add(New SqlParameter("@DeliveryDate_5", SqlDbType.DateTime, 8, "DeliveryDate"))
'cmdinsert.Parameters.Add(New SqlParameter("@PaymentTerm_6", SqlDbType.Int, 4, "PaymentTerm"))
cmdinsert.Parameters.Add(New SqlParameter("@OrderType_3", SqlDbType.NVarChar, 14, "OrderType"))
cmdinsert.Parameters("@OrderType_3").SourceColumn = "OrderType"
cmdinsert.Parameters("@OrderType_3").SourceVersion = DataRowVersion.Current
'cmdinsert.Parameters.Add(New SqlParameter("@CategoryType_4", SqlDbType.NVarChar, 14, "CategoryType"))
cmdinsert.Parameters.Add(New SqlParameter("@Remarks_4", SqlDbType.NVarChar, 400, "Remarks"))
cmdinsert.Parameters("@Remarks_4").SourceColumn = "Remarks"
cmdinsert.Parameters("@Remarks_4").SourceVersion = DataRowVersion.Current
oOrdersDataAdapter.FillSchema(oDS, SchemaType.Source)
Dim pTable As DataTable = oDS.Tables("Table")
pTable.TableName = "Order_Master"
' Create the DataTable "OrderDetails" in the
'Dataset and the OrderDetailsDataAdapter
Dim oOrderDetailsDataAdapter As New SqlClient.SqlDataAdapter(New SqlCommand("SELECT * FROM Order_Details", Con))
oOrderDetailsDataAdapter.UpdateCommand = New SqlCommand("Update_Order_Details", Con)
cmdinsert = oOrderDetailsDataAdapter.UpdateCommand
cmdinsert.CommandType = CommandType.StoredProcedure
cmdinsert.Parameters.Add(New SqlParameter("@OrderId", SqlDbType.Int))
cmdinsert.Parameters("@OrderId").SourceColumn = "OrderId"
cmdinsert.Parameters("@OrderId").SourceVersion = DataRowVersion.Original
cmdinsert.Parameters.Add(New SqlParameter("@CategoryID_2", SqlDbType.Int, 4, "CategoryID"))
cmdinsert.Parameters("@CategoryID_2").SourceColumn = "CategoryID"
cmdinsert.Parameters("@CategoryID_2").SourceVersio n = DataRowVersion.Current
cmdinsert.Parameters.Add(New SqlParameter("@AccID_3", SqlDbType.Int, 4, "AccID"))
cmdinsert.Parameters("@AccID_3").SourceColumn = "AccID"
cmdinsert.Parameters("@AccID_3").SourceVersion = DataRowVersion.Current
cmdinsert.Parameters.Add(New SqlParameter("@SemiCategoryID_4", SqlDbType.Int, 4, "SemiCategoryID"))
cmdinsert.Parameters("@SemiCategoryID_4").SourceCo lumn = "SemicategoryID"
cmdinsert.Parameters("@SemiCategoryID_4").SourceVe rsion = DataRowVersion.Current
cmdinsert.Parameters.Add(New SqlParameter("@Colour_5", SqlDbType.NVarChar, 15, "Colour"))
cmdinsert.Parameters("@Colour_5").SourceVersion = DataRowVersion.Current
cmdinsert.Parameters.Add(New SqlParameter("@Quantities_6", SqlDbType.Decimal, 9, "Quantities"))
cmdinsert.Parameters("@quantities_6").SourceColumn = "Quantities"
cmdinsert.Parameters("@Quantities_6").SourceVersio n = DataRowVersion.Current
cmdinsert.Parameters.Add(New SqlParameter("@Unit_7", SqlDbType.NVarChar, 5, "Unit"))
cmdinsert.Parameters("@Unit_7").SourceVersion = DataRowVersion.Current
cmdinsert.Parameters.Add(New SqlParameter("@UnitPrice_8", SqlDbType.Money, 8, "UnitPrice"))
cmdinsert.Parameters("@UnitPrice_8").SourceColumn = "UnitPrice"
cmdinsert.Parameters("@UnitPrice_8").SourceVersion = DataRowVersion.Current
oOrderDetailsDataAdapter.FillSchema(oDS, SchemaType.Source)
pTable = oDS.Tables("Table")
pTable.TableName = "Order_Details"
' Create the relationship between the two tables
oDS.Relations.Add(New DataRelation("ParentChild", oDS.Tables("Order_Master").Columns("OrderId"), oDS.Tables("Order_Details").Columns("OrderId")))
oDS.EnforceConstraints = False
Dim oOrderRow As DataRow = oDS.Tables("Order_Master").NewRow
'oOrderRow("BuyersOrderID") = txtbpoid.Text
oOrderRow(2)("CustID") = txtCustID.Text
'oOrderRow("OrderDate") = Label20.Text
'oOrderRow("DeliveryDate") = txtdeliverydate.Text
'oOrderRow("PaymentTerm") = txtpayterm.Text
oOrderRow(5)("OrderType") = ddlorder.SelectedItem.Value
'oOrderRow("CategoryType") = ddltype.SelectedItem.Value
oOrderRow(7)("Remarks") = UCase(txtremarks.Text)
oDS.Tables("Order_Master").Rows.Add(oOrderRow)
If ddltype.SelectedItem.Text = "Raw Meterials" Then
If txtquan.Text = "" Then
Label23.Text = " You have not selected any item."
Else
Dim oDetailsRow As DataRow = oDS.Tables("Order_Details").NewRow()
oDetailsRow("CategoryId") = txtitemid.Text
oDetailsRow("Colour") = UCase(txtcolour.Text)
oDetailsRow("Quantities") = txtquan.Text
oDetailsRow("Unit") = txtunit.Text
oDetailsRow("UnitPrice") = txtprice.Text
oDetailsRow.SetParentRow(oOrderRow)
oDS.Tables("Order_Details").Rows.Add(oDetailsRow)
End If
If txtquan1.Text = "" Then
Label23.Text = ""
Label23.Text = " You have selected only one item."
Else
Dim oDetailsRow1 As DataRow = oDS.Tables("Order_Details").NewRow()
oDetailsRow1("CategoryId") = txtitemid1.Text
oDetailsRow1("Colour") = UCase(txtcolour1.Text)
oDetailsRow1("Quantities") = txtquan1.Text
oDetailsRow1("Unit") = txtunit1.Text
oDetailsRow1("UnitPrice") = txtprice1.Text
oDetailsRow1.SetParentRow(oOrderRow)
oDS.Tables("Order_Details").Rows.Add(oDetailsRow1)
End If
If txtquan2.Text = "" Then
Label23.Text = ""
Label23.Text = " You have selected only two item."
Else
Dim oDetailsRow2 As DataRow = oDS.Tables("Order_Details").NewRow()
oDetailsRow2("CategoryId") = txtitemid2.Text
oDetailsRow2("Colour") = UCase(txtcolour2.Text)
oDetailsRow2("Quantities") = txtquan2.Text
oDetailsRow2("Unit") = txtunit2.Text
oDetailsRow2("UnitPrice") = txtprice2.Text
oDetailsRow2.SetParentRow(oOrderRow)
oDS.Tables("Order_Details").Rows.Add(oDetailsRow2)
End If
If txtquan3.Text = "" Then
Label23.Text = ""
Label23.Text = " You have selected only three item."
Else
Dim oDetailsRow3 As DataRow = oDS.Tables("Order_Details").NewRow()
oDetailsRow3("CategoryId") = txtitemid3.Text
oDetailsRow3("Colour") = UCase(txtcolour3.Text)
oDetailsRow3("Quantities") = txtquan3.Text
oDetailsRow3("Unit") = txtunit3.Text
oDetailsRow3("UnitPrice") = txtprice3.Text
oDetailsRow3.SetParentRow(oOrderRow)
oDS.Tables("Order_Details").Rows.Add(oDetailsRow3)
End If
If txtquan4.Text = "" Then
Label23.Text = ""
Label23.Text = " You have selected only three item."
Else
Dim oDetailsRow4 As DataRow = oDS.Tables("Order_Details").NewRow()
oDetailsRow4("CategoryId") = txtitemid4.Text
oDetailsRow4("Colour") = UCase(txtcolour4.Text)
oDetailsRow4("Quantities") = txtquan4.Text
oDetailsRow4("Unit") = txtunit4.Text
oDetailsRow4("UnitPrice") = txtprice4.Text
oDetailsRow4.SetParentRow(oOrderRow)
oDS.Tables("Order_Details").Rows.Add(oDetailsRow4)
End If
End If
oOrdersDataAdapter.Update(oDS, "Order_Master")
oOrderDetailsDataAdapter.Update(oDS, "Order_Details")
'oDS.AcceptChanges()
Response.Write("Your Order is Saved")
Label14.Text = "Your Order is Saved"
butsub.Enabled = False
'butrpt.Enabled = True
Con.Close()
End Sub
|
