Insert into relational database

[davy_g]

Hi all,

I want to create a relational database (MS SQL Server) with 2 tables:
- Caller
- Incident
There is a link between Caller and Incident. caller.caller_id is the primary key and incident.caller_id is the foreign key.
1 caller can have multiple incidents on it's name (1 to n).

I will have a form in which I want to create a new incident. The field Caller should be a dropdown-menu which lists all callers in the table caller.
When I want to insert a new incident, how should I handle this?

    con.Open()

    command = New SqlCommand("INSERT incident_first (id, request, action, solved) " & "VALUES (@id, @request, @action, @solved) ")
    command.Parameters.Add("@id",txtId.Text)
    command.Parameters.Add("@request", txtRequest.Text)
    command.Parameters.Add("@action", txtAction.Text)
    command.Parameters.Add("@solved", txtSolved.Text)
    command.ExecuteNonQuery()
    MsgBox("Data inserted")

    con.Close()

How do I implement this caller_id also? I don't know what the caller_id of John Doe is.
How should I change my insert-statement?

Thanks

 

[davy_g]

Perhaps I need to split it up into different actions:
- First find out what the caller_id is; create a SELECT-statement which gets the name out of the box and searches for the caller_id.
- Fetch the caller_id into a variable.
- Use this variable in my INSERT-statement.

    con.Open()

    ...create my SELECT-statement (based on the given name in the box), do it with the dataset and sqldataadapter and fetch it into a variable... (have no idea how)  

    da = New sqlDataAdapter(sql, con)
    da.Fill(ds, "get_caller")

    caller = ds.Tables("get_caller").Rows(0).Item(0)

    command = New SqlCommand("INSERT incident_first (id, caller, request, action, solved) " & "VALUES (@id, @caller, @request, @action, @solved) ")
    command.Parameters.Add("@id",txtId.Text)
    command.Parameters.Add("@caller", caller)
    command.Parameters.Add("@request", txtRequest.Text)
    command.Parameters.Add("@action", txtAction.Text)
    command.Parameters.Add("@solved", txtSolved.Text)
    command.ExecuteNonQuery()
    MsgBox("Data inserted")

    con.Close()

Sorry did not defined all variables but just wanted to show what I mean.

Or am I all wrong here?

 

[cjard]

Maybe not all wrong, but making it hard

Read the DW2 link in my sig, section "Creating a SImple Data App"

It shows aparent/child relationship and shows how to select, update, insert and delete