Click here to Skip to main content
12,304,884 members (67,171 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: VB VB.NET
How can i check data type of table fields(mysql) in vb.net 10 as well as change
Posted 24-Feb-13 18:22pm
Comments
Vardhan Desai 25-Feb-13 0:47am
   
You want to Chk and change Datatype during Run Time?
Rahul Dhoble 25-Feb-13 1:30am
   
Dear AnnuBhai ,

Lots of solutions you have now, see below
Create your solution by reading all these suggestion and post your final code here so that everyone can understand what you have done to resolve this.
AnnuBhai 25-Feb-13 1:36am
   
ok
AnnuBhai 25-Feb-13 1:37am
   
i think Solution 1 is easy
AnnuBhai 25-Feb-13 1:37am
   
i am trying with solution 1
AnnuBhai 25-Feb-13 2:04am
   
Private Sub CheckFieldDataType(ByVal srcSQL As String)

'Here v hv 2 pass query which showed in solution 1

1: Try
2: With CNN1
3: If .State = ConnectionState.Open Then .Close()
4: .ConnectionString = connectiondatabase
5: .Open()
6: End With
Try
7: Dim cmd As MySqlCommand = New MySqlCommand(srcSQL, CNN1) 'CNN1 is mysqlconnection
'create data reader
8: Dim rdr_mysql As MySqlDataReader = cmd.ExecuteReader

9: Do While rdr_mysql.Read

10: If rdr_mysql(0) = "LostDt" Then
11: If rdr_mysql(1) = "char(11)" Then
12: sSQL = "ALTER TABLE `lab_lostcardmaster`" +
" CHANGE COLUMN `LostDt` `LostDt` DATE NULL DEFAULT NULL AFTER `LabourID`;"
FireSQL(sSQL) 'function for executes query
Exit Do
13: End If

14: End If
15: Loop


16: Catch ex As MySqlException
17: mod_ErrorLogger.WriteToErrorLog(ex.Message, ex.StackTrace, "Error")
18: End Try

19: Catch ex As MySqlException
20: mod_ErrorLogger.WriteToErrorLog(ex.Message, ex.StackTrace, "Error")
21: Finally
22: If CNN1.State <> ConnectionState.Closed Then CNN1.Close()
23: End Try

End Sub
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Below is the query which you need to execute using SqlConnection and SqlCommand object in .net
SHOW COLUMNS FROM mytable FROM mydb;

This query will return columns of the table with their respective data type

For changind data type you can use Alter tbale query
ALTER TABLE `mydb`.`mytable` MODIFY COLUMN `mycol` TIMESTAMP;
  Permalink  
v2
Comments
AnnuBhai 25-Feb-13 1:38am
   
if i want only 1 column info
so hows query for that
Rahul Dhoble 25-Feb-13 4:44am
   
See my solution
AnnuBhai 25-Feb-13 4:50am
   
wht is dis
AnnuBhai 25-Feb-13 4:50am
   
dis is my code
Rahul Dhoble 25-Feb-13 6:35am
   
You asked Sandeep207 for query changes which will give you information about only one column
that changes you can see in my solution.
AnnuBhai 25-Feb-13 6:43am
   
Where solution
Rahul Dhoble 25-Feb-13 6:47am
   
select data_type
from information_schema.columns
where table_schema = 'myschema'
and table_name = 'mytable'
and column_name = 'mycol'
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

You can run this query from VB.net using DATAReader and Data connection object

select data_type
from information_schema.columns
where table_schema = 'myschema'
and table_name = 'mytable'
and column_name = 'mycol'

Replace your database , table , field names

You can run query for alteration of field to change type , size in my sql

read below mysql blog to know more about ALTER query

ALTER TABLE Syntax[^]
  Permalink  
v4
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

Imports MySql.Data.MySqlClient
 
Public Sub DataLoad(ByVal mMySQLConnectionString As String, ByVal pStoredProcedureString As String)
    Try
        Using mMySqlConnection As New MySqlConnection(mMySQLConnectionString)
            mMySqlConnection.Open()
            Using mMySqlCommand As New MySqlCommand
                With mMySqlCommand
                    .Connection = mMySqlConnection
                    .CommandType = CommandType.StoredProcedure
                    .CommandText = pStoredProcedureString
                    mMySqlDataReader = .ExecuteReader(CommandBehavior.SingleResult)
                End With
                For i As Integer = 0 To mMySqlDataReader.FieldCount - 1
                    Debug.print(CStr(mMySqlDataReader.GetFieldType(i)))
                Next            
        End Using
    Catch exErr As Exception
        MsgBox(exErr.Message)
    Finally
        If Not IsNothing(mMySqlDataReader) Then
            mMySqlDataReader.Close()
            mMySqlDataReader = Nothing
        End If
    End Try
End Sub
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

Private Sub CheckFieldDataType(ByVal srcSQL As String) 
'Here v hv 2 pass query which showed in solution 1 
1: 
Try 
2: 
With CNN1 3: 
  If .State = ConnectionState.Open 
  Then .Close() 
4: 
  .ConnectionString = connectiondatabase 
5: 
  .Open() 
6: End With 
Try 7: 
Dim cmd As MySqlCommand = New MySqlCommand(srcSQL, CNN1) 
'CNN1 is mysqlconnection 
'create data reader 
8: 
Dim rdr_mysql As MySqlDataReader = cmd.ExecuteReader 
9: 
 Do While rdr_mysql.Read 
10: 
If rdr_mysql(0) = "LostDt" 
Then 
11: 
If rdr_mysql(1) = "char(11)" 
Then 
12: 
sSQL = "ALTER TABLE `lab_lostcardmaster`" + " CHANGE COLUMN `LostDt` `LostDt` DATE NULL DEFAULT NULL 
AFTER `LabourID`;" FireSQL(sSQL) 
'function for executes query 
Exit Do 
13: 
End If 
14: 
End If 
15: 
Loop 
16: 
Catch ex As MySqlException 
17: 
mod_ErrorLogger.WriteToErrorLog(ex.Message, ex.StackTrace, "Error") 
18: 
End Try 
19: 
Catch ex As MySqlException 
20: 
mod_ErrorLogger.WriteToErrorLog(ex.Message, ex.StackTrace, "Error") 
21: Finally 
22: 
If CNN1.State <> ConnectionState.Closed 
Then 
CNN1.Close() 
23: 
End Try 
End Sub
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160530.1 | Last Updated 25 Feb 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100