How can I update relationship tables?

<----Ihave 2 tables are: 'customers (parent)' and 'open_ac (child)'

<---Ihave tried to insert and update data into sql database by using textboxes(don't use datagrid)

<---Mytables details are below

<---thistable uses for keeping user data

customersfields:

Columnname type length Description

cu_id int 4 Primary key Identifiers

cu_fname nvarchar 20 allow null first name

cu_lname nvarchar 40 allow null last name

cu_nat nvarchar 20 allownull nationality

cu_add nvarchar 40 allow null address

cu_wplace nvarchar 40 allownull workplace

cu_tel nvarchar 10 allownull telephone

cu_fax nvarchar 10 allow null fax

cu_email nvarchar 10 allownull email

<--theopen_ac uses for keeping register date/time of customers

open_acfields:

Columnname type length Description

cu_id int 4 link key

op_date date/time 8 register date

<----mycode

ImportsSystem.Data.SqlClient

Public Class cus_reg
Inherits System.Web.UI.Page
Dim DS As DataSet
Dim iRec As Integer 'Current Record
Dim m_Error As String = ""

Public Property MyError() As String
Get
Returnm_Error
End Get
Set(ByVal Value As String)
m_Error =Value
IfTrim(Value) = "" Then
Label3.Visible = False
Else
Label3.Text = Value
Label3.Visible = True
End If
End Set
End Property

Private Sub Page_Load(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
Dim C1 AsNew MISSQL
'DS =C1.GetDataset("select * from customers;select * from open_ac;select * fromaccounts")
DS =C1.GetDataset("select * from customers;select * from open_ac")

Session("data") = DS
iRec = 0
Viewstate("iRec") = iRec
Me.MyDataBind()

Dim Dtr AsDataRow = DS.Tables(0).NewRow
DS.Tables(0).Rows.Add(Dtr)
iRec =DS.Tables(0).Rows.Count - 1
viewstate("iRec") = iRec
Me.Label2.Text = DateTime.Now
Me.MyDataBind()
Else
DS =Session("data")
iRec =ViewState("iRec")
End If
Me.MyError = ""
End Sub

Public Function BindField(ByVal FieldName As String) AsString
Dim DT As DataTable = DS.Tables(0)
Return DT.Rows(iRec)(FieldName)& ""
End Function
Public Sub MyDataBind()
Label1.Text = "Record : "& iRec + 1 & " of " & DS.Tables(0).Rows.Count
txtcu_id.DataBind()
txtcu_fname.DataBind()
txtcu_lname.DataBind()
txtcu_add.DataBind()
txtcu_occ.DataBind()
txtcu_wplace.DataBind()
txtcu_nat.DataBind()
txtcu_tel.DataBind()
txtcu_fax.DataBind()
txtcu_email.DataBind()
End Sub

Here isupdate code

Private Sub bUpdate_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles bUpdate.Click
Dim DT As DataTable = DS.Tables(0)
Dim DR As DataRow = DT.Rows(iRec)
'Dim DR1 As DataRow = DT1.Rows(iRec)

If DR.RowState = DataRowState.AddedThen
Iftxtcu_id.Text.Trim = "" Then
Me.MyError = "please enter your id"
Exit Sub
End If
DR("cu_id")= txtcu_id.Text
End If

If txtcu_fname.Text.Trim ="" Then
Me.MyError ="please enter your name"
Exit Sub
Else
DR("cu_fname") = txtcu_fname.Text
End If

If txtcu_lname.Text.Trim ="" Then
Me.MyError ="please enter your last name"
Exit Sub
Else
DR("cu_lname") = txtcu_lname.Text
End If

If txtcu_add.Text.Trim ="" Then
Me.MyError ="please enter your address"
Exit Sub
Else
DR("cu_add") = txtcu_add.Text
End If

If txtcu_occ.Text.Trim ="" Then
Me.MyError ="please enter your occupation"
Exit Sub
Else
DR("cu_occ") = txtcu_occ.Text
End If

If txtcu_wplace.Text.Trim ="" Then
Me.MyError ="please enter your workplace"
Exit Sub
Else
DR("cu_wplace") = txtcu_wplace.Text
End If

If txtcu_nat.Text.Trim ="" Then
Me.MyError ="Please enter your nationality"
Exit Sub
Else
DR("cu_nat") = txtcu_nat.Text
End If

If txtcu_tel.Text.Trim ="" Then
DR("cu_tel") = DBNull.Value
Else
DR("cu_tel") = txtcu_tel.Text
End If

If txtcu_tel.Text.Trim ="" Then
DR("cu_fax") = DBNull.Value
Else
DR("cu_fax") = txtcu_fax.Text
End If

If txtcu_email.Text.Trim ="" Then
DR("cu_email") = DBNull.Value
Else
DR("cu_email") = txtcu_email.Text
End If

Dim Strsql As String
If DR.RowState = DataRowState.AddedThen
Strsql ="insert into customers (cu_id,cu_fname,cu_lname,cu_add,cu_occ,cu_wplace,cu_nat,cu_tel,cu_fax,cu_email)values (@.P1,@.P2,@.P3,@.P4,@.P5,@.P6,@.P7,@.P8,@.P9,@.P10)"
Else
Strsql ="update customers setcu_fname=@.P2,cu_lname=@.P3,cu_add=@.P4,cu_occ=@.P5,cu_wplace=@.P6,cu_nat=@.P7,cu_tel=@.P8,cu_fax=@.P9,cu_email=@.P10where cu_id =@.P1"
End If
Dim C1 As New MISSQL
Dim cmd As SqlCommand =C1.CreateCommand(Strsql)
C1.CreateParam(cmd,"ITTTTTTTTT")
cmd.Parameters("@.P1").Value = DR("cu_id")
cmd.Parameters("@.P2").Value= DR("cu_fname")
cmd.Parameters("@.P3").Value = DR("cu_lname")
cmd.Parameters("@.P4").Value = DR("cu_add")
cmd.Parameters("@.P5").Value = DR("cu_occ")
cmd.Parameters("@.P6").Value = DR("cu_wplace")
cmd.Parameters("@.P7").Value= DR("cu_nat")
cmd.Parameters("@.P8").Value = DR("cu_tel")
cmd.Parameters("@.P9").Value = DR("cu_fax")
cmd.Parameters("@.P10").Value = DR("cu_email")

Dim Y As Integer = C1.Execute(cmd)
If Y > 0 Then
DR.AcceptChanges()
Else
Me.MyError ="Can not register"
End If

<---code above in this sub it can update only customers tables and when I tried to coding below

<----it alerts can not update

Dim DT1 As DataTable = DS.Tables(1)
Dim DR1 As DataRow = DT1.Rows(iRec)
If DR1.RowState = DataRowState.AddedThen
Iftxtcu_id.Text.Trim = "" Then
Me.MyError = "Please enter id"
Exit Sub
End If
DR1("cu_id")= txtcu_id.Text
End If
If Label2.Text.Trim = ""Then
DR1("op_date") = Label2.Text
End If

Dim StrSql1 As String
If DR1.RowState =DataRowState.Deleted Then
StrSql1 ="insert into open_ac (cu_id,op_date) values (@.P13,@.P14)"
Else
StrSql1 ="update open_ac set op_date=@.P14 where cu_id=@.P13"
End If
Dim C2 As New MISSQL
Dim cmd2 As SqlCommand =C2.CreateCommand(StrSql1)
C2.CreateParam(cmd2, "ID")
cmd2.Parameters("@.P1").Value = DR1("cu_id")
cmd2.Parameters("@.P2").Value = DR1("op_date")

Dim Y1 As Integer = C2.Execute(cmd2)
If Y1 > 0 Then
DR1.AcceptChanges()
Else
Me.MyError ="Can not register"
End If
End Sub
End Class

<--thisis class I use for connecting to database and call parameters...

MISSQLclass

ImportsSystem.Data.SqlClient
Public Class MISSQL
Dim PV As String ="Server=web_proj;uid=sa;pwd=sqldb;"
Dim m_Database As String = "c1_itc"
Public Strcon As String
Public Sub New()
Strcon = PV &"database=" & m_Database
End Sub
Public Sub New(ByVal DBName As String)
m_Database = DBName
Strcon = PV &"database=" & m_Database
End Sub
Public Property Database() As String
Get
Returnm_Database
End Get
Set(ByVal Value As String)
m_Database =Value
Strcon = PV& "database=" & m_Database
End Set
End Property

Public Function GetDataset(ByVal Strsql As String, _
Optional ByVal DatasetName As String= "Dataset1", _
Optional ByVal TableName As String ="Table") As DataSet
Dim DA As New SqlDataAdapter(Strsql,Strcon)
Dim DS As New DataSet(DatasetName)
Try
DA.Fill(DS,TableName)
Catch x1 As Exception
Err.Raise(60002, , x1.Message)
End Try
Return DS
End Function

Public Function GetDataTable(ByVal Strsql As String, _
Optional ByVal TableName AsString = "Table") As DataTable
Dim DA As New SqlDataAdapter(Strsql,Strcon)
Dim DT As New DataTable(TableName)
Try
DA.Fill(DT)
Catch x1 As Exception
Err.Raise(60002, , x1.Message)
End Try
Return DT
End Function

Public Function CreateCommand(ByVal Strsql As String) AsSqlCommand
Dim cmd As New SqlCommand(Strsql)
Return cmd
End Function

Public Function Execute(ByVal Strsql As String) AsInteger
Dim cmd As New SqlCommand(Strsql)
Dim X As Integer = Me.Execute(cmd)
Return X
End Function

Public Function Execute(ByRef Cmd As SqlCommand) AsInteger
Dim Cn As New SqlConnection(Strcon)
Cmd.Connection = Cn
Dim X As Integer
Try
Cn.Open()
X =Cmd.ExecuteNonQuery()
Catch
X = -1
Finally
Cn.Close()
End Try
Return X
End Function

Public Sub CreateParam(ByRef Cmd As SqlCommand, ByValStrType As String)
'T:Text, M:Memo, Y:Currency,D:Datetime, I:Integer, S:Single, B:Boolean, P: Picture
Dim i As Integer
Dim j As String
For i = 1 To Len(StrType)
j =UCase(Mid(StrType, i, 1))
Dim P1 AsNew SqlParameter
P1.ParameterName = "@.P" & i
Select Casej
Case "T"
P1.SqlDbType = SqlDbType.NVarChar
Case "M"
P1.SqlDbType = SqlDbType.Text
Case "Y"
P1.SqlDbType = SqlDbType.Money
Case "D"
P1.SqlDbType = SqlDbType.DateTime
Case "I"
P1.SqlDbType = SqlDbType.Int
Case "S"
P1.SqlDbType = SqlDbType.Decimal
Case "B"
P1.SqlDbType = SqlDbType.Bit
Case "P"
P1.SqlDbType = SqlDbType.Image
End Select
Cmd.Parameters.Add(P1)
Next
End Sub
End Class

<---Thank you in advance

<---and Thank you very much for all help

Hi,

Try the following code :(one part of your code)

Dim DT1As DataTable = DS.Tables(1)
Dim DR1As DataRow = DT1.Rows(iRec)
If DR1.RowState = DataRowState.AddedThen
If txtcu_id.Text.Trim =""Then
Me.MyError ="Please enter id"
Exit Sub
End If
DR1("cu_id") = txtcu_id.Text
End If

////If Label2.Text.Trim ="" Then
//// DR1("op_date") = Label2.Text
////End If

If Label2.Text.Trim <>""Then
DR1("op_date") = Label2.Text
End If

Dim StrSql1As String
////If DR1.RowState = DataRowState.Deleted Then
If DR1.RowState = DataRowState.AddedThen
StrSql1 ="insert into open_ac (cu_id,op_date) values (@.P13,@.P14)"
Else
StrSql1 ="update open_ac set op_date=@.P14 where cu_id=@.P13"
End If
Dim C2As New MISSQL
Dim cmd2As SqlCommand = C2.CreateCommand(StrSql1)
C2.CreateParam(cmd2,"ID")
cmd2.Parameters("@.P1").Value = DR1("cu_id")
cmd2.Parameters("@.P2").Value = DR1("op_date")

Dim Y1As Integer = C2.Execute(cmd2)
If Y1 > 0Then
DR1.AcceptChanges()
Else
Me.MyError ="Can not register"
End If

Thanks.

|||

So thank you very much for help for coding.

I tried to follow your code below.

>Dim DT1As DataTable = DS.Tables(1)
> Dim DR1As DataRow = DT1.Rows(iRec)
> If DR1.RowState = DataRowState.AddedThen
> If txtcu_id.Text.Trim =""Then
> Me.MyError ="Please enter id"
> Exit Sub
> End If
> DR1("cu_id") = txtcu_id.Text
> End If////If Label2.Text.Trim ="" Then
> //// DR1("op_date") = Label2.Text
> ////End If

> If Label2.Text.Trim <>""Then
> DR1("op_date") = Label2.Text
> End If

> Dim StrSql1As String
> ////If DR1.RowState = DataRowState.Deleted Then
> If DR1.RowState = DataRowState.AddedThen
> StrSql1 ="insert into open_ac (cu_id,op_date) values (@.P13,@.P14)"
> Else
> StrSql1 ="update open_ac set op_date=@.P14 where cu_id=@.P13"
> End If
> Dim C2As New MISSQL
> Dim cmd2As SqlCommand = C2.CreateCommand(StrSql1)
> C2.CreateParam(cmd2,"ID")
> cmd2.Parameters("@.P1").Value = DR1("cu_id")
> cmd2.Parameters("@.P2").Value = DR1("op_date")

> Dim Y1As Integer = C2.Execute(cmd2)
> If Y1 > 0Then
> DR1.AcceptChanges()
> Else
> Me.MyError ="Can not register"
> End If

But still show "Can not register" I don't know why?

and I try another way by follow your code and editing something below this can work. But I don't know what I did wrong above

Dim DT1 As DataTable = DS.Tables(1)
Dim DR1 As DataRow = DT1.Rows(iRec)
If DR1.RowState = DataRowState.Added Then
DR1("cu_id") = txtcu_id.Text
End If

'////If Label2.Text.Trim = "" Then
' //// DR1("op_date") = Label2.Text
' ////End If

If Label2.Text.Trim <> "" Then
DR1("op_date") = Label2.Text
End If

Dim StrSql1 As String
'////If DR1.RowState = DataRowState.Deleted Then
If DR1.RowState = DataRowState.Added Then
StrSql1 = "insert into open_ac (cu_id,op_date) values (@.P_C1,@.P_C2)"
Else
StrSql1 = "update open_ac set op_date=@.P_C2 where cu_id=@.P_C1"
End If
Dim C2 As New MISSQL
Dim cmd_child As SqlCommand = C2.CreateCommand(StrSql1)
C2.CreateParam_child(cmd_child, "TD")
cmd_child.Parameters("@.P_C1").Value = DR1("cu_id")
cmd_child.Parameters("@.P_C2").Value = DR1("op_date")
Dim Y1 As Integer = C2.Execute(cmd_child)
If Y1 > 0 Then
DR1.AcceptChanges()
End If
End Sub

Thank you very very much for your help