.ExecuteNonQuery()

  • Thread starter Thread starter Primillo
  • Start date Start date
P

Primillo

'Insert, delete and update buttons
'don't work, the full source code is:
Public Class WebForm1
Inherits System.Web.UI.Page
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 cnn1 As New OleDb.OleDbConnection()
Dim dap1 As New OleDb.OleDbDataAdapter()
Dim das1 As New System.Data.DataSet()

Private Sub Page_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
If Not IsPostBack Then
Button1.Text = "Select"
Button2.Text = "Delete"
Button3.Text = "Update"
Button4.Text = "Insert"
Button5.Text = "Refresh"
'Tab index property
Button1.TabIndex = 2
TextBox1.TabIndex = 1
TextBox2.TabIndex = 3
TextBox3.TabIndex = 4
Button2.TabIndex = 5
Button3.TabIndex = 6
Button4.TabIndex = 7
Button5.TabIndex = 8
End If
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()
Dim cmd1 As New OleDb.OleDbCommand()
With cmd1
.Connection = cnn1
.CommandText = "SELECT * FROM
VBDotNetShippers"
End With
Dim dap1 As New OleDb.OleDbDataAdapter(cmd1)
dap1.Fill(das1, "VBDotNetShippers")
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
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
If IsThere(TextBox1.Text) = False Then
Response.Write("DELETE exit trap")
Exit Sub
End If
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
If IsThere(TextBox1.Text) = False Then
Response.Write("UPDATE exit trap")
Exit Sub
End If
'Specify 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)
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 & "')"
RunSQLString(str1)
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
'column value 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()
'Assing Connection and CommandText property
'values before invoking the commad
With cmd1
.Connection = cnn1
.CommandText = str1
cnn1.Open()
.ExecuteNonQuery()
cnn1.Close()
End With
End Sub
Sub ClearAllAndRefresh()
das1.Clear()
Refresh()
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
End Sub

End Class
 
Where is RunSQLString and ClearAllAndRefresh? that's where the problem
probably is. BTW, what specific exception are you getting?
 
Sub RunSQLString(ByVal str1 As String)
'Instantiate OleDBCommand object
'At the end:
Dim cmd1 As New OleDb.OleDbCommand()
'Assing Connection and CommandText property
'values before invoking the commad
With cmd1
.Connection = cnn1
.CommandText = str1
cnn1.Open()
.ExecuteNonQuery()
cnn1.Close()
End With
End Sub
Sub ClearAllAndRefresh()
das1.Clear()
Refresh()
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
End Sub
 
Back
Top