|
I need to create a form which will access data residing on a MySQL database stored on our website. I will need to be able to view and edit all records in the database. I am an experienced VB6 programmer, but have let things slide a bit over the last 5-6 years. I now only have VS 2005.
Is the an existing wizard, or form template out there that I can use to expedite development of this project?
Thanks for any help!
Dan Johnson
Dan Johnson | | DDJJ Monday, November 09, 2009 3:40 PM |
Hi Dan,
Welcometo MSDN forums!
1. Usually, there are such four methods tomake a simple Data Access application(Next, Previous, First, Last, Update,Delete,Insert, Save). Take MS Access database for examples. You can refer to them combining with MySQL batabase connection string and adapt it to your specific needs.
Method 1:Update (Insert/Update/Delete) data back into MS Access database from DataGridView.
http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/5980181e-f666-4f0a-ab50-c4ebecf96f02/
|
Imports System.Data.OleDb
Public Class Form1
Dim myDA As OleDbDataAdapter
Dim myDataSet As DataSet
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=|DataDirectory|\myDB.mdb")
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Table1", con)
con.Open()
myDA = New OleDbDataAdapter(cmd)
'Here one CommandBuilder object is required.
'It will automatically generate DeleteCommand,UpdateCommand and InsertCommand for DataAdapter object
Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(myDA)
myDataSet = New DataSet()
myDA.Fill(myDataSet, "MyTable")
DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView
con.Close()
con = Nothing
End Sub
' Save data back into database
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.Validate()
Me.myDA.Update(Me.myDataSet.Tables("MyTable"))
Me.myDataSet.AcceptChanges()
End Sub
End Class
|
Method 2: ExecuteSelect/Insert/Delete/Update T-SQL commandsin VB.NET code
Code sample: How toSelect/Insert/Delete/Updaterecords in MS Access databasein VB.NET
http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/87913f28-992d-4705-963b-cb0ffa53d8dd/
Code sample: How toSelect/Insert/Delete/Updaterecords in SQL Serverdatabasein VB.NET
http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/89e1067d-16e7-44e8-b12d-d78845bf255f/
Method 3.Using Data Wizard with BindingNavigator control.
Please check the 11th post in this threadfor detailed walkthrough:
http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/ff3f953b-da66-4f03-b4e4-981bab7d783b/
Method 4. Using DataSet/DataTable/DataAdapter in VB.NET code
Please check the 12th post and 13th post in this thread for detailed code sample:
http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/ff3f953b-da66-4f03-b4e4-981bab7d783b/
2. How to connect and accessMySQL databasein VB.NET?
· Using MySQL Native .NET Providers
· The ODBC.NET Solution - MyODBC Driver
· Using the OLEDB.NET Solution - MyOLDDB Provider
Please check this document for detailed instruction and code sample:
Exploring MySQL in the Microsoft .NET Environment.
Here are various connection strings to MySQL in .NET.
http://www.connectionstrings.com/?carrier=mysql
Code sample via MySQL Native .NET Providers.
|
Imports MySql.Data
Imports MySql.Data.MySqlClient
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim con As MySqlConnection = New MySqlConnection("Server=IPOFSERVERHERE;Database=conf;User ID=myuid;Password=mypwhere;")
Dim sql As MySqlCommand = New MySqlCommand("SELECT uid, pwd FROM emp_info",con)
con.open()
Dim ds As DataSet = New DataSet()
Dim DataAdapter1 As MySqlDataAdapter = New MySqlDataAdapter(sql)
Dim cb As MySqlCommandBuilder = New MySqlCommandBuilder(DataAdapter1)
MessageBox.Show("success message")
DataAdapter1.Fill(ds, "table1")
DataGridView1.DataSource = ds.Tables("table1").DefaultView
con.Close()
con = Nothing
End Sub
End Class
|
Best regards,
Martin Xie
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg@microsoft.com
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us. - Marked As Answer byDDJJ Thursday, November 12, 2009 3:13 PM
-
| | Martin Xie - MSFT Tuesday, November 10, 2009 5:20 AM |
Hi Dan,
Welcometo MSDN forums!
1. Usually, there are such four methods tomake a simple Data Access application(Next, Previous, First, Last, Update,Delete,Insert, Save). Take MS Access database for examples. You can refer to them combining with MySQL batabase connection string and adapt it to your specific needs.
Method 1:Update (Insert/Update/Delete) data back into MS Access database from DataGridView.
http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/5980181e-f666-4f0a-ab50-c4ebecf96f02/
|
Imports System.Data.OleDb
Public Class Form1
Dim myDA As OleDbDataAdapter
Dim myDataSet As DataSet
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=|DataDirectory|\myDB.mdb")
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Table1", con)
con.Open()
myDA = New OleDbDataAdapter(cmd)
'Here one CommandBuilder object is required.
'It will automatically generate DeleteCommand,UpdateCommand and InsertCommand for DataAdapter object
Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(myDA)
myDataSet = New DataSet()
myDA.Fill(myDataSet, "MyTable")
DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView
con.Close()
con = Nothing
End Sub
' Save data back into database
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.Validate()
Me.myDA.Update(Me.myDataSet.Tables("MyTable"))
Me.myDataSet.AcceptChanges()
End Sub
End Class
|
Method 2: ExecuteSelect/Insert/Delete/Update T-SQL commandsin VB.NET code
Code sample: How toSelect/Insert/Delete/Updaterecords in MS Access databasein VB.NET
http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/87913f28-992d-4705-963b-cb0ffa53d8dd/
Code sample: How toSelect/Insert/Delete/Updaterecords in SQL Serverdatabasein VB.NET
http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/89e1067d-16e7-44e8-b12d-d78845bf255f/
Method 3.Using Data Wizard with BindingNavigator control.
Please check the 11th post in this threadfor detailed walkthrough:
http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/ff3f953b-da66-4f03-b4e4-981bab7d783b/
Method 4. Using DataSet/DataTable/DataAdapter in VB.NET code
Please check the 12th post and 13th post in this thread for detailed code sample:
http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/ff3f953b-da66-4f03-b4e4-981bab7d783b/
2. How to connect and accessMySQL databasein VB.NET?
· Using MySQL Native .NET Providers
· The ODBC.NET Solution - MyODBC Driver
· Using the OLEDB.NET Solution - MyOLDDB Provider
Please check this document for detailed instruction and code sample:
Exploring MySQL in the Microsoft .NET Environment.
Here are various connection strings to MySQL in .NET.
http://www.connectionstrings.com/?carrier=mysql
Code sample via MySQL Native .NET Providers.
|
Imports MySql.Data
Imports MySql.Data.MySqlClient
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim con As MySqlConnection = New MySqlConnection("Server=IPOFSERVERHERE;Database=conf;User ID=myuid;Password=mypwhere;")
Dim sql As MySqlCommand = New MySqlCommand("SELECT uid, pwd FROM emp_info",con)
con.open()
Dim ds As DataSet = New DataSet()
Dim DataAdapter1 As MySqlDataAdapter = New MySqlDataAdapter(sql)
Dim cb As MySqlCommandBuilder = New MySqlCommandBuilder(DataAdapter1)
MessageBox.Show("success message")
DataAdapter1.Fill(ds, "table1")
DataGridView1.DataSource = ds.Tables("table1").DefaultView
con.Close()
con = Nothing
End Sub
End Class
|
Best regards,
Martin Xie
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg@microsoft.com
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us. - Marked As Answer byDDJJ Thursday, November 12, 2009 3:13 PM
-
| | Martin Xie - MSFT Tuesday, November 10, 2009 5:20 AM | Hello Dan,
Does this help? If you have any further questions or concerns, please feel free to let me know.
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us. | | Martin Xie - MSFT Thursday, November 12, 2009 7:47 AM | Yes Martin, it answers my question. Sorry for the delay. Seems like I get followup notifications only sporadically.
Thank you--I'm playing with your Method 3. It's not quite working yet, but I'm comfortable I'll get there. Next, I need to figure out how to send Outlook emails using VB in VS 2005.
Dan
Dan Johnson | | DDJJ Thursday, November 12, 2009 3:12 PM | Hello Dan Johnson,
I'm glad to hear that! Thank you for your feedback.
"Next, I need to figure out how to send Outlook emails using VB in VS 2005."
-> If you have new question, it would be better to open a new thread.
We usually use System.Net.Mail namespace to send email in VB.NET.
|
Imports System.Net.Mail
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim mailInstance As MailMessage = New MailMessage("FromMailAdress", "ToMailAdress", "Subject", "Body")
mailInstance.Attachments.Add(New Attachment("filename")) 'Optional
Dim mailSenderInstance As SmtpClient = New SmtpClient("smtpHostAdress", 25) '25 is the port of the SMTP host
mailSenderInstance.Credentials = New System.Net.NetworkCredential("LoginAccout", "Password")
mailSenderInstance.Send(mailInstance)
mailInstance.Dispose() 'Please remember to dispose this object
End Sub
End Class
|
Also, we cansend email via Outlook Automation technology in VB.NET.
Please check this thread for details:
http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/1a47815d-2b7e-4002-8368-50d5449170f6/
Best regards,
Martin Xie
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg@microsoft.com
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us. | | Martin Xie - MSFT Friday, November 13, 2009 7:34 AM |
|
Home > Visual Basic Power Packs > VB Database Form Templates for VS 2005?