Web Database (Insert)

[Primillo]

'Full source
'Insert, delete and update don't work
Public Class WebForm1
Inherits System.Web.UI.P age
Protected WithEvents Button1 As
System.Web.UI.W ebControls.Butt on
Protected WithEvents Button2 As
System.Web.UI.W ebControls.Butt on
Protected WithEvents Button3 As
System.Web.UI.W ebControls.Butt on
Protected WithEvents Button4 As
System.Web.UI.W ebControls.Butt on
Protected WithEvents Button5 As
System.Web.UI.W ebControls.Butt on
Protected WithEvents TextBox1 As
System.Web.UI.W ebControls.Text Box
Protected WithEvents TextBox2 As
System.Web.UI.W ebControls.Text Box
Protected WithEvents TextBox3 As
System.Web.UI.W ebControls.Text Box
Protected WithEvents DataGrid1 As
System.Web.UI.W ebControls.Data Grid
Dim cnn1 As New OleDb.OleDbConn ection()
Dim dap1 As New OleDb.OleDbData Adapter()
Dim das1 As New System.Data.Dat aSet()
Private Sub Page_Load(ByVal sender As System.Object,
ByVal e As System.EventArg s) 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.TabInde x = 2
TextBox1.TabInd ex = 1
TextBox2.TabInd ex = 3
TextBox3.TabInd ex = 4
Button2.TabInde x = 5
Button3.TabInde x = 6
Button4.TabInde x = 7
Button5.TabInde x = 8
End If
Dim str1 As String = "Provider =
Microsoft.Jet.O LEDB.4.0;"
str1 &= "Data Source = c:\Archivos de
programa\" & "Microsoft Office\Office10
\Samples\Northw ind.mdb"
cnn1.Connection String = str1
'Invoke Refresh to populate grid control
Refresh()
End Sub
Sub Refresh()
Dim cmd1 As New OleDb.OleDbComm and()
With cmd1
.Connection = cnn1
.CommandText = "SELECT * FROM
VBDotNetShipper s"
End With
Dim dap1 As New OleDb.OleDbData Adapter(cmd1)
dap1.Fill(das1, "VBDotNetShippe rs")
DataGrid1.DataS ource = (das1.Tables
("VBDotNetShipp ers"))
DataGrid1.DataB ind()
End Sub

Private Sub Button1_Click(B yVal sender As
System.Object, ByVal e As System.EventArg s) 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(B yVal sender As
System.Object, ByVal e As System.EventArg s) Handles
Button2.Click
If IsThere(TextBox 1.Text) = False Then
Response.Write( "DELETE exit trap")
Exit Sub
End If
Dim str1 As String = "DELETE FROM
VBDotNetShipper s " & "WHERE ShipperID=" & TextBox1.Text
'Run SQL String
RunSQLString(st r1)
'Clear controls and refresh from Northwind
ClearAllAndRefr esh()
End Sub

Private Sub Button3_Click(B yVal sender As
System.Object, ByVal e As System.EventArg s) Handles
Button3.Click
If IsThere(TextBox 1.Text) = False Then
Response.Write( "UPDATE exit trap")
Exit Sub
End If
'Specify update SQL string
Dim str1 As String = "UPDATE
VBDotNetShipper s " & "SET CompanyName='" & TextBox2.Text
& "', " & "Phone='" & TextBox3.Text & "' WHERE
ShipperID=" & TextBox1.Text
'Run SQL string
RunSQLString(st r1)
ClearAllAndRefr esh()
End Sub

Private Sub Button4_Click(B yVal sender As
System.Object, ByVal e As System.EventArg s) Handles
Button4.Click
'Specify insert SQL string
Dim str1 As String = "INSERT INTO
VBDotNetShipper s " & "(CompanyNa me, Phone) VALUES('" &
TextBox2.Text & "', '" & TextBox3.Text & "')"
RunSQLString(st r1)
ClearAllAndRefr esh()
End Sub

Private Sub Button5_Click(B yVal sender As
System.Object, ByVal e As System.EventArg s) Handles
Button5.Click
'Clear controls and refresh from Northwind
ClearAllAndRefr esh()
End Sub
Function IsThere(ByVal str1) As Boolean
Dim cmd1 As New OleDb.OleDbComm and()
Dim int1 As Integer
'Run SQL statement to determine if ShipperID
'column value exists
With cmd1
.Connection = cnn1
.CommandText = "SELECT * FROM
VBDotNetShipper s " & "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(By Val str1 As String)
'Instantiate OleDBCommand object
Dim cmd1 As New OleDb.OleDbComm and()
'Assing Connection and CommandText property
'values before invoking the commad
With cmd1
.Connection = cnn1
.CommandText = str1
cnn1.Open()
.ExecuteNonQuer y()
cnn1.Close()
End With
End Sub
Sub ClearAllAndRefr esh()
das1.Clear()
Refresh()
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
End Sub

End Class

[Herfried K. Wagner [MVP]]

Hello,

"Primillo" <pr********@hot mail.com> schrieb:

'Full source
'Insert, delete and update don't work

Doesn't work? Error message?

This is a VB.NET language group. Notice that you will have a better
chance to get an answer if you post to the ADO.NET newsgroup in future:

news://msnews.microsoft.com/microsof...amework.adonet

Web interface:

http://msdn.microsoft.com/newsgroups...amework.adonet

--
Herfried K. Wagner
MVP · VB Classic, VB.NET
http://www.mvps.org/dotnet