Solved

inserting into mysql using vb.net

Posted on 2004-09-29
4
263 Views
Last Modified: 2010-04-23
hi, i designed a vb.net application that has fields . how do i do to store the user inputs (textfield,combo box) from the interface to the database that is mysql?I use odbc for my connection. I have an idea that i am supposed to use datareader or some like that.
0
Comment
Question by:webusername
  • 3
4 Comments
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 12187006
Well You'll need ODBCDataReader and ODBCCommand objects for reading and writing data to MySql look for the following link


http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataodbcodbcdatareaderclasstopic.asp

0
 
LVL 11

Expert Comment

by:ajaikumarr
ID: 12187081
Hai Try This Sample

        Dim Server As String = "ngws01", LaBase As String = "CMPAjaiDev", User As String = "CMPAjaiDev", Password As String = "CMPAjaiDev"
        Dim ODBCConnectString As String = _
        "DRIVER={MySQL ODBC 3.51 Driver};SERVER=" & Server _
        & ";DATABASE=" & LaBase & ";UID=" & User _
        & ";PASSWORD=" & Password & ";OPTION=3;"


        Dim ConnectionObject As New System.Data.Odbc.OdbcConnection(ODBCConnectString)
        Try
            'To retrive values from database
            Dim svlQuery = "Select * From Table1"
            Dim CommandObject As New System.Data.odbc.OdbcCommand(svlQuery.ToString.Trim, ConnectionObject)
            ConnectionObject.Open()
            Dim rsRecordSet As System.Data.Odbc.OdbcDataReader
            rsRecordSet = CommandObject.ExecuteReader
            While rsRecordSet.Read()
                'MsgBox(rsRecordSet.GetValue(1))
            End While
            rsRecordSet.Close()
            rsRecordSet = Nothing
            ConnectionObject.Close()

            'To add values to database
            Dim ComboBoxValue As String = "1"
            Dim TextBoxValue As String = "One"
            svlQuery = "Insert Into Table1 Values ('" + ComboBoxValue + "', '" + TextBoxValue + "')"
            Dim CommandObject1 As New System.Data.Odbc.OdbcCommand(svlQuery, ConnectionObject)
            ConnectionObject.Open()
            CommandObject1.ExecuteNonQuery()
            ConnectionObject.Close()

            'To delete values to database
            Dim CommandObject2 As New System.Data.Odbc.OdbcCommand("Delete From Table1 Where ID = '1'", ConnectionObject)
            ConnectionObject.Open()
            CommandObject1.ExecuteNonQuery()
            ConnectionObject.Close()
        Catch MyException As Exception
            MsgBox(MyException.ToString)
        End Try


        ConnectionObject = Nothing

Bye
Ajai
0
 
LVL 11

Accepted Solution

by:
ajaikumarr earned 500 total points
ID: 12187106
Hai,

Try the below sample code

Public Class Form1
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents Button1 As System.Windows.Forms.Button
    Friend WithEvents ComboBox1 As System.Windows.Forms.ComboBox
    Friend WithEvents TextBox1 As System.Windows.Forms.TextBox
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.Button1 = New System.Windows.Forms.Button
        Me.ComboBox1 = New System.Windows.Forms.ComboBox
        Me.TextBox1 = New System.Windows.Forms.TextBox
        Me.SuspendLayout()
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(96, 176)
        Me.Button1.Name = "Button1"
        Me.Button1.Size = New System.Drawing.Size(112, 32)
        Me.Button1.TabIndex = 0
        Me.Button1.Text = "Add"
        '
        'ComboBox1
        '
        Me.ComboBox1.Items.AddRange(New Object() {"One", "Two", "Three"})
        Me.ComboBox1.Location = New System.Drawing.Point(80, 8)
        Me.ComboBox1.Name = "ComboBox1"
        Me.ComboBox1.Size = New System.Drawing.Size(121, 21)
        Me.ComboBox1.TabIndex = 1
        Me.ComboBox1.Text = "ComboBox1"
        '
        'TextBox1
        '
        Me.TextBox1.Location = New System.Drawing.Point(80, 88)
        Me.TextBox1.Name = "TextBox1"
        Me.TextBox1.Size = New System.Drawing.Size(120, 20)
        Me.TextBox1.TabIndex = 2
        Me.TextBox1.Text = ""
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(292, 273)
        Me.Controls.Add(Me.TextBox1)
        Me.Controls.Add(Me.ComboBox1)
        Me.Controls.Add(Me.Button1)
        Me.Name = "Form1"
        Me.Text = "Form1"
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'Assigning MYSQL Server Location, Database Name, UserName & Password on temporary variable
        Dim Server As String = "MYSQLServerLocation", DataBaseName As String = "MYSQLDatabaseName", User As String = "MYSQLUserName", Password As String = "MYSQLPassword"

        'Preparation for Connect string
        Dim ODBCConnectString As String = _
            "DRIVER={MySQL ODBC 3.51 Driver};SERVER=" & Server _
            & ";DATABASE=" & DataBaseName & ";UID=" & User _
            & ";PASSWORD=" & Password & ";OPTION=3;"

        'Open Database
        Dim ConnectionObject As New System.Data.Odbc.OdbcConnection(ODBCConnectString)
        Try
            'Prepare query to insert record
            Dim svlQuery As String = "Insert Into TableName Values ('" + Me.ComboBox1.Text + "', '" + Me.TextBox1.Text + "')"

            'To open command object to execute query
            Dim CommandObject As New System.Data.odbc.OdbcCommand(svlQuery.ToString.Trim, ConnectionObject)
            ConnectionObject.Open()

            'To Execute query
            CommandObject.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("Error Occured :" & vbCrLf & ex.Message)
        End Try
        'To close the opened databse
        ConnectionObject.Close()
        ConnectionObject = Nothing
    End Sub
End Class


Bye
Ajai
0
 
LVL 11

Expert Comment

by:ajaikumarr
ID: 12187113
Hai,

Note You have to change the follow things

Variable declartions [Server, DataBaseName, User, Password] to your server details

[Dim svlQuery As String = "Insert Into TableName Values ('" + Me.ComboBox1.Text + "', '" + Me.TextBox1.Text + "')"] to your comboname & textbox name and also change the tablename.

Bye
Ajai
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Change Format on Data when exporting to Excel 4 34
Point to Current Row Ater Refresh Datagridview 3 25
VB.NET (2008) - Refactoring Question 2 21
Store results in vb.net 3 22
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question