ASP.NET Proyect

[Gerardo Murillo]

Web form with a Datagrid pointing to the Northwind
databse and I have button to insert,delete,update,select
and refresh, for some reason the buttons:
insert,update,delete don't work.
Please help me.
Source code from Programming Microsoft Visual Basic.Net
for Access Databases book.
The full source code is:

Public Class WebForm1
Inherits System.Web.UI.Page
Dim cnn1 As New OleDb.OleDbConnection()
Dim dap1 As New OleDb.OleDbDataAdapter()
Protected WithEvents Button1 As
System.Web.UI.WebControls.Button
Protected WithEvents Button2 As
System.Web.UI.WebControls.Button
Protected WithEvents Button3 As
System.Web.UI.WebControls.Button
Protected WithEvents Button4 As
System.Web.UI.WebControls.Button
Protected WithEvents Button5 As
System.Web.UI.WebControls.Button
Protected WithEvents TextBox1 As
System.Web.UI.WebControls.TextBox
Protected WithEvents TextBox2 As
System.Web.UI.WebControls.TextBox
Protected WithEvents TextBox3 As
System.Web.UI.WebControls.TextBox
Protected WithEvents DataGrid1 As
System.Web.UI.WebControls.DataGrid
Dim das1 As New System.Data.DataSet()

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private
Sub InitializeComponent()

End Sub

Private Sub Page_Init(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web
Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
'Initialize page
If Not IsPostBack Then
'assign text property values for buttons
Button1.Text = "Select"
Button2.Text = "Delete"
Button3.Text = "Update"
Button4.Text = "Insert"
Button5.Text = "Refresh"
End If
're-assing connection string for current opening
'of the page
Dim str1 As String = "Provider =
Microsoft.Jet.OLEDB.4.0;"
str1 &= "Data Source = c:\Archivos de programa\"
& "Microsoft Office\Office10\Samples\Northwind.mdb"
cnn1.ConnectionString = str1
'invoke refresh to populate grid control
Refresh()
End Sub
Sub Refresh()
'instantiate connection string
Dim cmd1 As New OleDb.OleDbCommand()
'return all rows from the VBDotNetShippers
'table from the northwind database
With cmd1
.Connection = cnn1
.CommandText = "SELECT * FROM
VBDotNetShippers"
End With
'fill vbdotnetshippers datatable object in dap1
'dataadapter object
Dim dap1 As New OleDb.OleDbDataAdapter(cmd1)
dap1.Fill(das1, "VBDotNetShippers")
'bind datagrid1 control to vbdotnetshippers
datatable object
DataGrid1.DataSource = (das1.Tables
("VBDotNetShippers"))
DataGrid1.DataBind()
End Sub

Private Sub Button1_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
Button1.Click
'populate textbox2 y textbox3 with companyname
'and phone column values for designated
'shipperID in textbox1
Dim int1 As Integer
For int1 = 0 To das1.Tables(0).Rows.Count - 1
If das1.Tables(0).Rows(int1)(0) = CInt
(TextBox1.Text) Then
TextBox2.Text = das1.Tables(0).Rows(int1)
(1)
TextBox3.Text = das1.Tables(0).Rows(int1)
(2)
End If
Next
End Sub

Private Sub Button2_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
Button2.Click
'trap for missing row in the database table
If IsThere(TextBox1.Text) = False Then
Response.Write("DELETE exit trap")
Exit Sub
End If
'specify update SQL string
Dim str1 As String = "DELETE FROM
VBDotNetShippers" & "WHERE ShipperID=" & TextBox1.Text
'run sql string
RunSQLString(str1)
'clear controls and refresh from northwind
ClearAllAndRefresh()
End Sub

Private Sub Button3_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
Button3.Click
'trap for missing row in databse table
If IsThere(TextBox1.Text) = False Then
Response.Write("UPDATE exit trap")
Exit Sub
End If
'specifi update SQL string
Dim str1 As String = "UPDATE VBDotNetShippers"
& "SET CompanyName='" & TextBox2.Text & "'," & "Phone='"
& TextBox3.Text & "'WHERE ShipperID=" & TextBox1.Text
'run SQL string
RunSQLString(str1)
'Clear controls and refresh from northwind
ClearAllAndRefresh()
End Sub

Private Sub Button4_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
Button4.Click
'specify insert SQL string
Dim str1 As String = "INSERT INTO
VBDotNetShippers" & "(CompanyName,Phone)VALUES('" &
TextBox2.Text & "','" & TextBox3.Text & "')"
'run SQL string
'RunSQLString(str1)
'clear controls and refresh from northwind
ClearAllAndRefresh()
End Sub

Private Sub Button5_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
Button5.Click
'clear controls and refresh from northwind
ClearAllAndRefresh()
End Sub
Function IsThere(ByVal str1) As Boolean
Dim cmd1 As New OleDb.OleDbCommand()
Dim int1 As Integer
'Run SQL statement to determine if shipperID
'colomn values exists
With cmd1
.Connection = cnn1
.CommandText = "SELECT * FROM
VBDotNetShippers" & "WHERE ShipperID=" & str1
cnn1.Open()
int1 = .ExecuteScalar
cnn1.Close()
End With
'return true if int1 is greater than 0
If int1 > 0 Then Return True
End Function
Sub RunSQLString(ByVal str1 As String)
'instantiate oledbcommand object
Dim cmd1 As New OleDb.OleDbCommand()
'assign connection and commandtext property
'values before invoking the command
With cmd1
.Connection = cnn1
.CommandText = str1
cnn1.Open()
.ExecuteNonQuery()
cnn1.Close()
End With
End Sub
Sub ClearAllAndRefresh()
'clear local datatable object and
'populate from nortwind database
das1.Clear()
Refresh()
'clear text boxes
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
End Sub


Private Sub DataGrid1_SelectedIndexChanged(ByVal
sender As System.Object, ByVal e As System.EventArgs)
Handles DataGrid1.SelectedIndexChanged

End Sub
End Class

 

[Scott M.]

Well, I see many things that I might do to condense your code as well as
make it more efficient as well as correct some erros....

1. Set the text of your buttons in the Properties designer window instead of
the load event.
2. Only call the DataGrid's DataBind method on Not IsPostBack or after
calling the DataAdapter's Update method.
3. Don't set your connection string to have a physical path to the DB in it.
4. You could let your DataAdapter do the work for you via its built in
Command objects (Insert, Update, Delete, Select)

*****NOTE THAT YOUR SQL STATEMENTS DIDN'T PUT SPACES BETWEEN CONCATENATED
SECTIONS - THIS COULD CAUSE THE STATEMENTS TO FAIL...

(i.e. "John" & "Doe" = "JohnDoe" not "John Doe")

"Select * FROM VBDotNetShippers"

'No need to break the strings as you did

"INSERT INTO VBDotNetShippers (CompanyName,Phone) VALUES ('" & TextBox2.Text
& "','" & TextBox3.Text & "')"

"UPDATE VBDotNetShippers SET CompanyName='" & TextBox2.Text & "'," &
"Phone='" & TextBox3.Text & "' WHERE ShipperID=" & TextBox1.Text

"DELETE FROM VBDotNetShippers WHERE ShipperID=" & TextBox1.Text

 

[Herfried K. Wagner [MVP]]

Hello,

Web form with a Datagrid pointing to the Northwind
databse and I have button to insert,delete,update,select
and refresh, for some reason the buttons:
insert,update,delete don't work.

Error message?

This is a VB .NET language ng. Please turn to the ng for ASP.NET questions:

news://msnews.microsoft.com/microsoft.public.dotnet.framework.aspnet

Web interface:

http://msdn.microsoft.com/newsgroup...roup=microsoft.public.dotnet.framework.aspnet

ADO.NET ng:

news://msnews.microsoft.com/microsoft.public.dotnet.framework.adonet

Web interface:

http://msdn.microsoft.com/newsgroup...roup=microsoft.public.dotnet.framework.adonet

HTH,
Herfried K. Wagner