Insert values into database from form VB.NET
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
Software/Hardware used:
software
Software/Hardware used:
software
ASKED:
March 24, 2012 9:52 AM
UPDATED:
March 28, 2012 5:43 PM
Answer Wiki:
I did somecoding here have a look and tell me what you think:
<pre>
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-TOSHIBASQLEXPRESS;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-TOSHIBASQLEXPRESS;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-TOSHIBASQLEXPRESS;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-TOSHIBASQLEXPRESS;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
</pre>
Here is my sql that i created where the info is being read from the form:
<pre>
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','','','')
</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
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')Darthswift00, it is not clear whether you still need help.
Could you please confirm ?
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 ?
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.