How can i check data type of table fields(mysql) in vb.net 10 as well as change

Question

1.00/5 (1 vote)

See more:

VB

Posted 24-Feb-13 18:22pm

AnnuBhai

Add a Solution

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

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

4 solutions

Solution 4

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

Posted 24-Feb-13 20:05pm

AnnuBhai

Updated 24-Feb-13 22:47pm

v2

Solution 3

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

Posted 24-Feb-13 19:10pm

Just eg

Solution 2

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[^]

Posted 24-Feb-13 19:05pm

RDBurmon

Updated 24-Feb-13 19:12pm

v4

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

sandeep207 · Accepted Answer · 2013-02-24T18:46:00

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;

Posted 24-Feb-13 18:46pm

sandeep207

Updated 24-Feb-13 19:11pm

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.

AnnuBhai 25-Feb-13 6:43am

Where solution

RDBurmon 25-Feb-13 6:47am

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