• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 665
  • Last Modified:

vb.net and mysql client application

im trying to figgure out how to setup vb.net to allow me to pull a mysql data base and then modify the data w/o a data grid... Im trying to create a front end for the users here at work for repair jobs.
They will need to enter the customers info or select the customer that allready exists, and add in the computer problem or issue.

In the end im looking for a clean fourm layout that asks info as a normal application would and not a data grid.

thanks for all the help in advance
dw5304
current tables
 
/*!40101 SET NAMES utf8 */;
 
/*!40101 SET SQL_MODE=''*/;
 
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
 
CREATE DATABASE /*!32312 IF NOT EXISTS*/`repair` /*!40100 DEFAULT CHARACTER SET utf8 */;
 
USE `repair`;
 
/*Table structure for table `customer` */
 
DROP TABLE IF EXISTS `customer`;
 
CREATE TABLE `customer` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `first_name` tinytext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `last_name` tinytext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `phone_1` int(10) NOT NULL,
  `phone_2` int(10) DEFAULT NULL,
  `address` tinytext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `city` tinytext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `state` tinytext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `zip` int(9) NOT NULL,
  `notes` longblob,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;
 
/*Table structure for table `parts` */
 
DROP TABLE IF EXISTS `parts`;
 
CREATE TABLE `parts` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `repair` int(8) NOT NULL,
  `name` longtext,
  `extra` longblob,
  `serial` longtext,
  `cost` double DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `repair` (`repair`),
  CONSTRAINT `repair` FOREIGN KEY (`repair`) REFERENCES `computer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
/*Table structure for table `repair` */
 
DROP TABLE IF EXISTS `repair`;
 
CREATE TABLE `repair` (
  `id` int(8) NOT NULL AUTO_INCREMENT COMMENT 'repair ID',
  `computer` int(8) NOT NULL COMMENT 'Computer that needs repair',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'timestamp created',
  `status` int(1) DEFAULT NULL COMMENT 'repair status',
  `issue` longblob COMMENT 'customer issue',
  `response` longblob COMMENT 'what was done to fix',
  `private_info` longblob COMMENT 'extra info for record keeping',
  `labor_cost` double DEFAULT NULL COMMENT 'labor costs',
  `assigned_to` int(8) DEFAULT NULL COMMENT 'assigned repairer',
  `repaired_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'timestamp repaired',
  `labor_hours` int(3) DEFAULT NULL COMMENT 'hours worked on repair',
  `priority` int(1) DEFAULT NULL COMMENT 'priority of repair',
  `warrenty_repair` int(1) DEFAULT NULL COMMENT 'warrenty or free repair =)',
  `contacted` int(1) DEFAULT NULL COMMENT 'completion call status',
  PRIMARY KEY (`id`),
  KEY `computer` (`computer`),
  CONSTRAINT `computer` FOREIGN KEY (`computer`) REFERENCES `computer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
/*Table structure for table `techs` */
 
DROP TABLE IF EXISTS `techs`;
 
CREATE TABLE `techs` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `name` tinytext NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

Open in new window

0
03671328
Asked:
03671328
  • 2
1 Solution
 
nmarunCommented:
Here's an example:

http://www.15seconds.com/issue/050210.htm

I know it is for asp.net, but you can use the same logic for win forms.
0
 
nmarunCommented:
03671328, were you able to get this to work?
0
 
03671328Author Commented:
I figured it out on my own....
for anyone else that wants to do this
i used the fallowing code to complete the task

to use the code
 Txt_First_Name.Text = GetSql("first_name").ToString
and it will fill the txt box with the info u asked of it.

if u want to fill a combo box


 For Each _row As DataRow In userinfo.Tables("last_name").Rows
                    lastrow = lastrow + 1
                    If _row.Item("last_name").ToString = combo_Last_Name.SelectedItem.ToString() And count < 1 Then
                        count = count + 1
                        Txt_customer_notes.Text = (_row.Item("id").ToString())
                    End If

Hope this info will help out. As i have searched all over the place for it.
Imports MySql.Data.MySqlClient
 
    Dim conn As New MySqlConnection
    Dim myCommand As New MySqlCommand
    Dim myAdapter As New MySqlDataAdapter
    Dim SQL As String
    Dim myConnString As String
    Dim userinfo As DataSet = New DataSet
 
forum load
 
   myConnString = "server=ip address here;" _
          & "user id=username here;" _
          & "password=password here;" _
          & "database=data base here"
 
 
 
 
 Public Function GetSql(ByVal quary As String) As Object
 
        SQL = "SELECT " & quary.ToString & " FROM customer where id = " & Chr(34) & idtrack & Chr(34)
        conn.ConnectionString = myConnString
        Try
            conn.Open()
            Try
                Dim usercontent As New DataSet
                myCommand.Connection = conn
                myCommand.CommandText = SQL
                myAdapter.SelectCommand = myCommand
                myAdapter.Fill(usercontent, quary)
                For Each _row1 As DataRow In usercontent.Tables(quary).Rows
                    quary = (_row1.Item(quary).ToString())
                Next
            Catch myerror As MySqlException
                MsgBox("There was an error reading from the database: " & myerror.Message)
            End Try
        Catch myerror As MySqlException
            MessageBox.Show("Error connecting to the database: " & myerror.Message)
        Finally
            If conn.State <> ConnectionState.Closed Then conn.Close()
        End Try
        Return quary
    End Function

Open in new window

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now