Insert values into database from form

[darthswift00]

Hi guys, Having abit of trouble with inserting values that i have in my form into my sql database.

Basically it supposed to calculate a customer who borrowed a DVD or CD. I select the date that the customer brings the returned item by selecting a datepicker. The calculation works fine, but i want to insert 3 values into my database under column names that i already have. The table name is Trnsaction. it has the following coloumn names:

• Trnsaction_id
• Member_id
• Album_id
• issue_date
• return_date
• members_date
• days_delayed
• fine

The last 3 coloumns(members_date ,days_delayed ,fine) are values i wish to add to my database. The values days_delayed and fine only pop up when i have selected my date (thats members_date) form the datepicker and selected the calculate button

How can i insert these values to my existing database?

Thanks

[Mitja Bonca]

Where can we get foreign keys of Member_id, and Album_id?
And we even have to get primary key of th table (Trnsaction_id), how do we get it? One option is to create a method, that looks for last inserted id, and we only increment it by 1, so we get next new number (id).
But about those two foerign keys? Do you have any data on the form of getting ids based on their names?

[darthswift00]

Hey Mitja thanks again for your help i did some coding here for the form. But i must mention that it also has to update the album that is suposed to be returned in another form.

I did somecoding here have a look and tell me what you think:

Public Class PDL_Transaction_Fines_Form
Dim bm As BindingManagerBase
Dim mdate As String
Dim ddelay As String
Dim fine As Integer
Dim holder As String

Private Sub PDL_Transaction_Fines_Form_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    SqlDAtransFines.Fill(DataSetFinesTrans1, "album")
    bm = BindingContext(DataSetFinesTrans1, "album")
    bm.Position = 0
    cbTID.DropDownStyle = ComboBoxStyle.DropDownList
End Sub

Private Sub btnCalc_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCalc.Click
    Dim issueD, returnD, mdate As New Date
    Dim memberD As New Integer
    Dim fine, result As New Integer

    issueD = CType(txtIsDate.Text, Date)
    returnD = CType(txtRDate.Text, Date)
    mdate = CType(txtMDate.Text, Date)
    fine = DateDiff(DateInterval.Day, returnD, mdate)
    txtDispFine.Text = fine.ToString
    result = fine
    fine = (result * 10)
    txtFines.Text = "$" & fine
    If (CType(txtDispFine.Text, Integer) <= 0) Then
        MessageBox.Show("No Fine! Album is returned.", "NoFineMessageBox", MessageBoxButtons.OK, MessageBoxIcon.Information)
        mdate = txtMDate.Text
        ddelay = txtDispFine.Text

        holder = PDL_Request_form.txtAID.Text
        Dim sqls As String = "UPDATE album SET quantity_available= quantity_available + 1 where album_id='" + holder + "' "
        Dim con As New SqlClient.SqlConnection("Data Source=CHETS-TOSHIBA\SQLEXPRESS;Initial Catalog=PlanetDiscLibrary;Integrated Security=True")
        Dim cmd As New SqlClient.SqlCommand(sqls, con)

        con.Open()
        cmd.ExecuteNonQuery()
        SqlDACollAdd.UpdateCommand = SqlSelectCommand1
        SqlDACollAdd.Update(DataSetCollAdd1, "album")
        con.Close()

        Dim sql As String = "insert into trnsaction (members_date,days_delayed,fine)values('" + mdate + "','" + ddelay + "','" + fine + "')"
        Dim con1 As New SqlClient.SqlConnection("Data Source=CHETS-TOSHIBA\SQLEXPRESS;Initial Catalog=PlanetDiscLibrary;Integrated Security=True")
        Dim cmd1 As New SqlClient.SqlCommand(sql, con1)
        con.Open()
        cmd1.ExecuteNonQuery()
        SqlDAtransFines.UpdateCommand = SqlSelectCommand1
        SqlDAtransFines.Fill(DataSetFinesTrans1, "trnsaction")

        txtMDate.Text = ""
        txtDispFine.Text = ""
        txtFines.Text = ""

        Return
    Else
        MessageBox.Show("The Fine is :" & " $" & fine & " ,Please pay your fine ASAP!", "FineIssueMessageBox", MessageBoxButtons.OK, MessageBoxIcon.Warning)
        Dim sql As String = "insert into trnsaction (members_date,days_delayed,fine)values('" + mdate + "','" + ddelay + "','" + fine + "')"
        Dim con As New SqlClient.SqlConnection("Data Source=CHETS-TOSHIBA\SQLEXPRESS;Initial Catalog=PlanetDiscLibrary;Integrated Security=True")
        Dim cmd As New SqlClient.SqlCommand(sql, con)
        con.Open()
        cmd.ExecuteNonQuery()
        SqlDAtransFines.UpdateCommand = SqlSelectCommand1
        SqlDAtransFines.Fill(DataSetFinesTrans1, "trnsaction")

        txtMDate.Text = ""
        txtDispFine.Text = ""
        txtFines.Text = ""

        Return
    End If
End Sub

Private Sub btnTMenu_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTMenu.Click
    Me.Hide()
    PDL_Transaction_Form.Show()
End Sub
Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
    bm.Position = 0
End Sub

Private Sub btnPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrev.Click
    bm.Position -= 1
End Sub

Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
    bm.Position += 1
End Sub

Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
    bm.Position = bm.Count - 1
End Sub

Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
    lblDTime.Text = Date.Now.ToString()
End Sub

Private Sub btnRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRefresh.Click
    Dim conn As New SqlClient.SqlConnection("Data Source=CHETS-TOSHIBA\SQLEXPRESS;Initial Catalog=PlanetDiscLibrary;Integrated Security=True")
    Dim da As New SqlClient.SqlDataAdapter("SELECT  member.member_id, trnsaction.trnsaction_id, album.album_id, album.album_name, trnsaction.issue_date, trnsaction.return_date FROM  member INNER JOIN trnsaction ON member.member_id = trnsaction.member_id INNER JOIN album ON trnsaction.album_id = album.album_id", conn)
    Dim dt As New DataTable

    dt.Clear()
    conn.Open()
    da.Fill(dt)
    DataSetFinesTrans1.Clear()
    SqlDAtransFines.Fill(DataSetFinesTrans1, "album")
    bm = BindingContext(DataSetFinesTrans1, "album")
    bm.Position = 0
    cbTID.DropDownStyle = ComboBoxStyle.DropDownList
    conn.Close()
End Sub

Here is my sql that i created where the info is being read from the form:

create table trnsaction
(trnsaction_id varchar(10) primary key constraint chktrnsaction_id Check
(trnsaction_id Like ('T[0-9][0-9][0-9]')),
member_id varchar(10) references member (member_id),
album_id varchar(10) references album(album_id),
issue_date varchar(20) not null,
return_date varchar(20) not null,
members_date varchar(20) not null,
days_delayed int not null, fine varchar(10))

select * from trnsaction

drop table trnsaction

--issue and return_date formats are yy-mm-dd--
insert into trnsaction values
('T001','M001','A001','2012-01-10','2012-01-15','','','')
insert into trnsaction values
('T002','M002','A002','2012-01-12','2012-01-16','','','')
insert into trnsaction values
('T003','M003','A003','2012-01-16','2012-01-21','','','')
insert into trnsaction values
('T004','M004','A004','2012-01-18','2012-01-23','','','')
insert into trnsaction values
('T005','M005','A005','2012-02-02','2012-02-07','','','')
insert into trnsaction values
('T006','M006','A006','2012-02-08','2012-02-13','','','')

I also will attach a picture of the form so you can get an idea as to how it would look

Thanks again for your help Mitja

[darthswift00]

If you will see here, the album gets updated when there is no fine as the member has reurned it on time. I did a test with this coding it kept sayn

Dim sql As String = "insert into trnsaction (members_date,days_delayed,fine)values('" + mdate + "','" + ddelay + "','" + fine + "')"
Conversion from string "insert into trnsaction (members_" to type 'Double' is not valid.

What does it mean by that?

[darthswift00]

Yeah i do have the foreign keys for them

These are my sql codes-

Member table is the following:
create table member
(member_id varchar(10)primary key constraint chkmember_id Check
(member_id Like ('M[0-9][0-9][0-9]')),
member_name char(20), m_address varchar(30), city char(20),m_state char(10),
m_zipcode int,contact_no int, date_of_membership varchar(20))

select * from member

insert into member values
('M001','Jack Daniels','4 Deathvally','Navada','NY','1001','0234598612','2005/05/08')
insert into member values
('M002','Betty White','202 jamestown','Texas','TX','9823','03984732','2004/10/11')
insert into member values
('M003','Ntutukho Wiseman','203 Solarplace','Durban','KZN','4031','0314785973','2008/04/12')
insert into member values
('M004','Brandon Mckuur','12 Pampaly Way','Durban','KZN','1004','0324578312','2009/06/08')
insert into member values
('M005','Cuan Lee Fayers','11 ChurchStreet','Durban','KZN','2314','0216741238','2007/03/02')
insert into member values
('M006','Donovan Gordon','206 Soccerville','Durban','KZN','4567','0119846723','2006/11/26')

Album table is:

create table album
(album_id varchar(10)primary key constraint chkalbum_id Check
(album_id Like ('A[0-9][0-9][0-9]')),
album_type varchar (10)not null,
quantity_available int not null,
album_name varchar(30) not null)

select * from album

insert into album values
('A001','DVD','30','JamesBond')
insert into album values
('A002','CD','20','Top 10 Hits 2012')
insert into album values
('A003','DVD','25','Harry Potter 1')
insert into album values
('A004','CD','25','Kelly Clarkson')
insert into album values
('A005','DVD','25','Lord of the rings 1,2 & 3')
insert into album values
('A006','CD','20','Madonna')

Also i have forms that do that increment by 1 for memberID and for AlbumID. Do you want the coding for that? As those work fine interms of incrementing by 1 at a time.