Hi everyone im beginner and was required to write some code in vb.net connected to oracle databse.
'cmd' and 'dr' errors..it says they are not declared and may be inaccessible due to its protection level..
Here is my code:
Imports
Oracle.DataAccess.Client
Public
Class frmAmdStock
Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
End
End Sub
Private Sub btnGet_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
'If neither StockNo OR Description are entered
If txtStockNoSrh.Text = "" And txtDescSrh.Text = "" Then
MsgBox(
"You must enter either StockNo or Description")
txtStockNoSrh.Focus()
Exit Sub
'If both StockNo AND Description are entered
ElseIf txtStockNoSrh.Text <> "" And txtDescSrh.Text <> "" Then
MsgBox(
"You may not enter both StockNo and Description")
txtStockNoSrh.Focus()
Exit Sub
ElseIf txtStockNoSrh.Text <> "" Then 'Retrieve data using StockNo
If Not IsNumeric(txtStockNoSrh.Text) Then
MsgBox(
"StockNo must be numeric")
txtStockNoSrh.Focus()
Exit Sub
End If
'search by Description
DisplayStock()
Else 'Retrieve data using Description
conn.Open()
'Define the SQL Query to retrieve the data (StockNo and Description only)
strSQL =
"SELECT StockNo, Description FROM Stock WHERE StockNo = " + txtDescSrh.Text
'Execute the SQL Query (resulting records put in a DataSet and then a grid)
Dim da = New OracleDataAdapter(strSQL, conn)
Dim ds As DataSet = New DataSet
da.Fill(ds)
grdStock.DataSource = ds.Tables(0)
'If No matching records found
If grdStock.RowCount = 1 Then
MsgBox(
"No Details found for this Description")
txtDescSrh.Focus()
Exit Sub
End If
'Close database
conn.Close()
'Display the groupbox/grid grpSelect.visible = true
End If
End Sub
Private Sub DisplayStock()
conn.Open()
'retrieve data from DB into OracleDataReader
'define the SQL query as a string
strSQL =
"SELECT * FROM Stock WHERE StockNo = " + txtStockNoSrh.Text
cmd.Connection = conn
cmd.CommandText = strSQL
cmd.CommandType =
CommandType.Text
dr = cmd.ExecuteReader()
'execute the Oracle Command cmd
'load data into form controls
'Read the retrieved details
If Not dr.Read() Then 'if no data to be read
MsgBox(
" No details found for this stock number")
txtStockNoSrh.Focus()
'close the database
conn.Close()
Exit Sub
End If
'display the data (load selected details into groupbox grpStock controls)
txtStockNo.Text = dr.Item(
"StockNo")
txtDesc.Text = dr.Item(
"Description")
txtCostPrice.Text = Format(dr.Item(
"Costprice"), "###0.00")
txtSalePrice.Text = Format(dr.Item(
"SalePrice"), "###0.00")
txtQty.Text = dr.Item(
"Qty")
txtReorderLevel.Text = dr.Item(
"ReorderLevel")
'close the database
conn.Close()
'make groupbox visible
grpStock.Visible =
True
End Sub
Private Sub btnAmd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
'validate the input data
If txtDesc.Text = "" Then
MsgBox(
"Description must be entered")
txtDesc.Focus()
Exit Sub
ElseIf txtCostPrice.Text = "" Then
MsgBox(
"Cost Price must be entered")
txtCostPrice.Focus()
Exit Sub
ElseIf txtSalePrice.Text = "" Then
MsgBox(
"Sale Price must be entered")
txtCostPrice.Focus()
Exit Sub
ElseIf Not IsNumeric(txtCostPrice.Text) Then
MsgBox(
"Cost Price must be numeric")
txtCostPrice.Focus()
Exit Sub
ElseIf txtCostPrice.Text < 0 Then
MsgBox(
"Cost Price must be greater than zero")
txtCostPrice.Focus()
Exit Sub
ElseIf Val(txtSalePrice.Text) < Val(txtCostPrice.Text) Then
MsgBox(
"Sale Price must exceed Cost Price")
txtCostPrice.Focus()
Exit Sub
Else 'all data is valid
'open the database
conn.Open()
'define the SQL UPDATE query to be executed
strSQL =
"UPDATE Stock SET Description = '" + txtDesc.Text + "', CostPrice=" + txtCostPrice.Text _
+
", SalePrice = " + txtSalePrice.Text + ", Qty =" + txtQty.Text _
+
", ReorderLevel =" + txtReorderLevel.Text + " WHERE StockNo = " + txtStockNo.Text
cmd.CommandText = strSQL.ToString
'Update record in stock table
cmd.CommandType =
CommandType.Text
cmd.ExecuteNonQuery()
'Close the database
conn.Close()
'reset the form
grpStock.Visible =
False
txtStockNoSrh.Text =
""
txtDescSrh.Text =
""
txtStockNoSrh.Focus()
End If
End Sub
Private Sub grdStock_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles grdStock.CellContentClick
'extract the StockNo from the selected row of the datagrid
Dim intStockNo As Integer
Dim row As Integer
row = grdStock.CurrentRow.Index
intStockNo = grdStock.Item(0, row).Value()
'Retrieve and display the selected stock details using StockNo
strSQL =
"SELECT * FROM Stock WHERE StockNo = " + intStockNo.ToString
conn.Open()
cmd.Connection = conn
cmd.CommandText = strSQL
cmd.CommandType =
CommandType.Text
dr = cmd.ExecuteReader()
'load data into form controls
'Read the retrieved details
If Not dr.Read() Then
MsgBox(
" No details found for this stock number")
txtStockNoSrh.Focus()
'close the database
conn.Close()
Exit Sub
End If
'display the data
'load selected details into groupbox grpStock controls
txtStockNo.Text = dr.Item(
"StockNo")
txtDesc.Text = dr.Item(
"Description")
txtCostPrice.Text = Format(dr.Item(
"Costprice"), "###0.00")
txtSalePrice.Text = Format(dr.Item(
"SalePrice"), "###0.00")
txtQty.Text = dr.Item(
"Qty")
txtReorderLevel.Text = dr.Item(
"ReorderLevel")
'close the database
conn.Close()
'make groupbox visible
grpStock.Visible =
True
End Sub
Private Sub btnAmend_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAmend.Click
End Sub
End
Class
'cmd' and 'dr' errors..it says they are not declared and may be inaccessible due to its protection level..
Here is my code:
Imports
Oracle.DataAccess.Client
Public
Class frmAmdStock
Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
End
End Sub
Private Sub btnGet_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
'If neither StockNo OR Description are entered
If txtStockNoSrh.Text = "" And txtDescSrh.Text = "" Then
MsgBox(
"You must enter either StockNo or Description")
txtStockNoSrh.Focus()
Exit Sub
'If both StockNo AND Description are entered
ElseIf txtStockNoSrh.Text <> "" And txtDescSrh.Text <> "" Then
MsgBox(
"You may not enter both StockNo and Description")
txtStockNoSrh.Focus()
Exit Sub
ElseIf txtStockNoSrh.Text <> "" Then 'Retrieve data using StockNo
If Not IsNumeric(txtStockNoSrh.Text) Then
MsgBox(
"StockNo must be numeric")
txtStockNoSrh.Focus()
Exit Sub
End If
'search by Description
DisplayStock()
Else 'Retrieve data using Description
conn.Open()
'Define the SQL Query to retrieve the data (StockNo and Description only)
strSQL =
"SELECT StockNo, Description FROM Stock WHERE StockNo = " + txtDescSrh.Text
'Execute the SQL Query (resulting records put in a DataSet and then a grid)
Dim da = New OracleDataAdapter(strSQL, conn)
Dim ds As DataSet = New DataSet
da.Fill(ds)
grdStock.DataSource = ds.Tables(0)
'If No matching records found
If grdStock.RowCount = 1 Then
MsgBox(
"No Details found for this Description")
txtDescSrh.Focus()
Exit Sub
End If
'Close database
conn.Close()
'Display the groupbox/grid grpSelect.visible = true
End If
End Sub
Private Sub DisplayStock()
conn.Open()
'retrieve data from DB into OracleDataReader
'define the SQL query as a string
strSQL =
"SELECT * FROM Stock WHERE StockNo = " + txtStockNoSrh.Text
cmd.Connection = conn
cmd.CommandText = strSQL
cmd.CommandType =
CommandType.Text
dr = cmd.ExecuteReader()
'execute the Oracle Command cmd
'load data into form controls
'Read the retrieved details
If Not dr.Read() Then 'if no data to be read
MsgBox(
" No details found for this stock number")
txtStockNoSrh.Focus()
'close the database
conn.Close()
Exit Sub
End If
'display the data (load selected details into groupbox grpStock controls)
txtStockNo.Text = dr.Item(
"StockNo")
txtDesc.Text = dr.Item(
"Description")
txtCostPrice.Text = Format(dr.Item(
"Costprice"), "###0.00")
txtSalePrice.Text = Format(dr.Item(
"SalePrice"), "###0.00")
txtQty.Text = dr.Item(
"Qty")
txtReorderLevel.Text = dr.Item(
"ReorderLevel")
'close the database
conn.Close()
'make groupbox visible
grpStock.Visible =
True
End Sub
Private Sub btnAmd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
'validate the input data
If txtDesc.Text = "" Then
MsgBox(
"Description must be entered")
txtDesc.Focus()
Exit Sub
ElseIf txtCostPrice.Text = "" Then
MsgBox(
"Cost Price must be entered")
txtCostPrice.Focus()
Exit Sub
ElseIf txtSalePrice.Text = "" Then
MsgBox(
"Sale Price must be entered")
txtCostPrice.Focus()
Exit Sub
ElseIf Not IsNumeric(txtCostPrice.Text) Then
MsgBox(
"Cost Price must be numeric")
txtCostPrice.Focus()
Exit Sub
ElseIf txtCostPrice.Text < 0 Then
MsgBox(
"Cost Price must be greater than zero")
txtCostPrice.Focus()
Exit Sub
ElseIf Val(txtSalePrice.Text) < Val(txtCostPrice.Text) Then
MsgBox(
"Sale Price must exceed Cost Price")
txtCostPrice.Focus()
Exit Sub
Else 'all data is valid
'open the database
conn.Open()
'define the SQL UPDATE query to be executed
strSQL =
"UPDATE Stock SET Description = '" + txtDesc.Text + "', CostPrice=" + txtCostPrice.Text _
+
", SalePrice = " + txtSalePrice.Text + ", Qty =" + txtQty.Text _
+
", ReorderLevel =" + txtReorderLevel.Text + " WHERE StockNo = " + txtStockNo.Text
cmd.CommandText = strSQL.ToString
'Update record in stock table
cmd.CommandType =
CommandType.Text
cmd.ExecuteNonQuery()
'Close the database
conn.Close()
'reset the form
grpStock.Visible =
False
txtStockNoSrh.Text =
""
txtDescSrh.Text =
""
txtStockNoSrh.Focus()
End If
End Sub
Private Sub grdStock_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles grdStock.CellContentClick
'extract the StockNo from the selected row of the datagrid
Dim intStockNo As Integer
Dim row As Integer
row = grdStock.CurrentRow.Index
intStockNo = grdStock.Item(0, row).Value()
'Retrieve and display the selected stock details using StockNo
strSQL =
"SELECT * FROM Stock WHERE StockNo = " + intStockNo.ToString
conn.Open()
cmd.Connection = conn
cmd.CommandText = strSQL
cmd.CommandType =
CommandType.Text
dr = cmd.ExecuteReader()
'load data into form controls
'Read the retrieved details
If Not dr.Read() Then
MsgBox(
" No details found for this stock number")
txtStockNoSrh.Focus()
'close the database
conn.Close()
Exit Sub
End If
'display the data
'load selected details into groupbox grpStock controls
txtStockNo.Text = dr.Item(
"StockNo")
txtDesc.Text = dr.Item(
"Description")
txtCostPrice.Text = Format(dr.Item(
"Costprice"), "###0.00")
txtSalePrice.Text = Format(dr.Item(
"SalePrice"), "###0.00")
txtQty.Text = dr.Item(
"Qty")
txtReorderLevel.Text = dr.Item(
"ReorderLevel")
'close the database
conn.Close()
'make groupbox visible
grpStock.Visible =
True
End Sub
Private Sub btnAmend_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAmend.Click
End Sub
End
Class
