Solved

inserting into mysql using vb.net

Posted on 2004-09-29
4
246 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now