Insert values into database from form VB.NET

40 pts.
Tags:
SQL Database
VB.NET
Hi guys, Having a bit 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 columns (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 (that's members_date) form the datepicker and selected the calculate button How can I insert these values to my existing database? Thanks

Software/Hardware used:
software

Answer Wiki

Thanks. We'll let you know when a new response is added.

I did some coding 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','','','')

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Darthswift00
    Change to that on the trnsaction table it shold be:
    
    insert into trnsaction (trnsaction_id,member_id,album_id,issue_date,return_date) values 
    ('T001','M001','A001','2012-01-10','2012-01-15')
    insert into trnsaction (trnsaction_id,member_id,album_id,issue_date,return_date) values 
    ('T002','M002','A002','2012-01-12','2012-01-16')
    insert into trnsaction (trnsaction_id,member_id,album_id,issue_date,return_date) values 
    ('T003','M003','A003','2012-01-16','2012-01-21')
    insert into trnsaction (trnsaction_id,member_id,album_id,issue_date,return_date) values 
    ('T004','M004','A004','2012-01-18','2012-01-23')
    insert into trnsaction (trnsaction_id,member_id,album_id,issue_date,return_date) values 
    ('T005','M005','A005','2012-02-02','2012-02-07')
    insert into trnsaction (trnsaction_id,member_id,album_id,issue_date,return_date) values 
    ('T006','M006','A006','2012-02-08','2012-02-13')
    
    
    
    40 pointsBadges:
    report
  • carlosdl
    Darthswift00, it is not clear whether you still need help. Could you please confirm ?
    70,220 pointsBadges:
    report
  • Darthswift00
    Hi i do still need help im still new to this site so i do apologise if i have not said so. Do you understand my question that im having a problem with ?
    40 pointsBadges:
    report
  • carlosdl
    The code you posted: does it work ? at least partially ? does it generate errors ? Please be a little more specific about what you need help with. "tell me what you think" is way too broad.
    70,220 pointsBadges:
    report
  • Brijesh
    Hi,

    Check the resource 
    10,185 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following