Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: 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 at 25-Feb-13 0:47am
   
You want to Chk and change Datatype during Run Time?
Rahul Dhoble at 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 at 25-Feb-13 1:36am
   
ok
AnnuBhai at 25-Feb-13 1:37am
   
i think Solution 1 is easy
AnnuBhai at 25-Feb-13 1:37am
   
i am trying with solution 1
AnnuBhai at 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 at 25-Feb-13 1:38am
   
if i want only 1 column info
so hows query for that
Rahul Dhoble at 25-Feb-13 4:44am
   
See my solution
AnnuBhai at 25-Feb-13 4:50am
   
wht is dis
AnnuBhai at 25-Feb-13 4:50am
   
dis is my code
Rahul Dhoble at 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 at 25-Feb-13 6:43am
   
Where solution
Rahul Dhoble at 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 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
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  

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

  Print Answers RSS
0 CPallini 550
1 Sergey Alexandrovich Kryukov 420
2 George Jonsson 288
3 Pheonyx 218
4 OriginalGriff 200
0 OriginalGriff 4,813
1 CPallini 3,850
2 Sergey Alexandrovich Kryukov 3,224
3 George Jonsson 2,552
4 Gihan Liyanage 2,186


Advertise | Privacy | Mobile
Web03 | 2.8.140905.1 | Last Updated 25 Feb 2013
Copyright © CodeProject, 1999-2014
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