Can someone please look at the source code below and help me determine why the database is being inserted with all "1"s instead of the value I type in the gridview? (Button 2 Click Event)
1 Imports System.Data
2 Imports System.Data.OleDb
3 Imports System.Configuration
4 Imports System.Web.Configuration
5
6
7
8 Public Class Form1
9
10 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
11
12 End Sub
13 Dim coil As DataSet = New DataSet
14
15 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
16 ''' we need oledbconnection for ACCESS database to connect and get data or save data
17
18 ''' for that we need connection to be made from connectionstring from app.config file which is
19
20 ''' <add key="dbconn" value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mwst.mdb"/>
21
22
23 Dim connection As OleDb.OleDbConnection = New OleDb.OleDbConnection(System.Configuration.ConfigurationSettings.AppSettings("dbconn"))
24
25 ' above line can be done as below as well
26 ' Dim connection As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mwst.mdb")
27
28 ' reading the table to show in the dataset and grid view - we need select command string as below
29 Dim queryString As String = "SELECT * from slitcoils"
30
31 'need adapter to create bridge between access data and our GUI screen
32 Dim adapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(queryString, connection)
33
34 'creating any dataset name could be abc, coil, xyz
35
36
37 ' removing old rows
38 If coil.Tables.Count > 0 Then
39 coil.Tables(0).Rows.Clear()
40 End If
41
42 ' adapter receives data which we name coil, we can put any
43
44 adapter.Fill(coil, "coil")
45
46 ' getting first table of our dataaset, dataset can have hundreds of tables inside
47 Me.DataGridView1.DataSource = coil.Tables(0)
48
49
50 End Sub
51
52 Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
53 If coil.Tables(0).Rows.Count = 0 Then
54 MsgBox("No data has been entered")
55 Exit Sub
56 End If
57
58 ' if data is there then we do below processing
59 '
60 Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Family\Documents\mwst.mdb")
61 ' connection string above can be read different way as below as well
62 ' System.Configuration.ConfigurationSettings.AppSettings("dbconn")
63
64 'connection should be open before inserting data into it
65 con.Open()
66
67 ' command is required to insert / update data, it needs 2nd parameter as connection as well
68 '
69 Dim cmd As New OleDbCommand("INSERT INTO `SlitCoils` (`Date`, `Ga`, `Grade`, `Width`, `Weight`, `PO#`, `BOLno`, `MillNo`, `ShipTagNo`, `Descriptoion`, `Owner`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", con)
70
71 ' this is datarow variable which is getting
72 Dim dr As DataRow = coil.Tables(0).Rows(0)
73
74 ' adding parameters to pass value to the above insert command
75 ' we have got 11 parameters to be passed to the above command
76 ' they are passed from the grid view to access db file
77 '
78 cmd.Parameters.AddWithValue("date", dr.Item("date"))
79 cmd.Parameters.AddWithValue("Ga", dr.Item("Ga"))
80 cmd.Parameters.AddWithValue("Grade", dr.Item("Grade"))
81 cmd.Parameters.AddWithValue("Width", dr.Item("Width"))
82 cmd.Parameters.AddWithValue("Weight", dr.Item("Weight"))
83 cmd.Parameters.AddWithValue("PO#", dr.Item("PO#"))
84 cmd.Parameters.AddWithValue("BOLno", dr.Item("BOLno"))
85 cmd.Parameters.AddWithValue("MillNo", dr.Item("MillNo"))
86 cmd.Parameters.AddWithValue("ShipTagNo", dr.Item("ShipTagNo"))
87 cmd.Parameters.AddWithValue("Descriptoion", dr.Item("Descriptoion"))
88 cmd.Parameters.AddWithValue("Owner", dr.Item("Owner"))
89
90 ''' command is being executed to insert / update
91 cmd.ExecuteNonQuery()
92
93 ''' connection must be closed otherwise it will give u error next time
94 con.Close()
95
96 End Sub
97 End Class