Click here to Skip to main content
15,905,967 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
How can i check data type of table fields(mysql) in vb.net 10 as well as change
Posted
Comments
MalwareTrojan 25-Feb-13 0:47am    
You want to Chk and change Datatype during Run Time?
RDBurmon 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

Below is the query which you need to execute using SqlConnection and SqlCommand object in .net
SQL
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
SQL
ALTER TABLE `mydb`.`mytable` MODIFY COLUMN `mycol` TIMESTAMP;
 
Share this answer
 
v2
Comments
AnnuBhai 25-Feb-13 1:38am    
if i want only 1 column info
so hows query for that
RDBurmon 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
RDBurmon 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.
You can run this query from VB.net using DATAReader and Data connection object

SQL
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[^]
 
Share this answer
 
v4
VB
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
 
Share this answer
 
VB
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
 
Share this answer
 
v2

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900